The size of the input for a statement can have a big impact on the evaluation time of the query.
The input for a statement is defined by the FROM clause. If no FROM clause is provided, the input defaults to the NavStateRecords. When possible, use an already completed result from another statement, instead of using corpus records, to avoid inputting unnecessary records.
Consider the following queries. In the first query, the input to each statement is of a size on the order of the navigation state. In the first two statements, Sums and Totals, the data is aggregated at two levels of granularity. In the last statement, the data set is accessed again for the sole purpose of identifying the month/year combinations that are present in the data. The computations of interest are derived from previously-computed results.
DEFINE Sums AS SELECT SUM(a) AS MonthlyTotal GROUP BY month,year; DEFINE Totals AS SELECT SUM(a) AS YearlyTotal GROUP BY year; DEFINE Result AS SELECT Sums[month,year].MonthlyTotal AS MonthlyTotal, Sums[month,year].MonthlyTotal/Totals[year].YearlyTotal AS Fraction GROUP BY month,year
In the following rewrite of the query, the index is accessed only once. The first statement accesses the index to compute the monthly totals. The second statement has been modified to compute yearly totals using the results of the first statement. Assuming that there are many records per month, the savings could be multiple orders of magnitude. Finally, the last statement has also been modified to use the results of the first statement. The first statement has already identified all of the valid month/year combinations in the data set. Rather than accessing the broader data set (possibly millions of records) just to identify the valid combinations, the month/year pairs are read from the much smaller (probably several dozen records) previous result.
DEFINE Sums AS SELECT SUM(a) AS MonthlyTotal GROUP BY month,year; DEFINE Totals AS SELECT SUM(MonthlyTotal) AS YearlyTotal FROM Sums GROUP year; DEFINE Result AS SELECT MonthlyTotal AS MonthlyTotal, MonthlyTotal/Totals[year].YearlyTotal AS Fraction FROM Sums
A common practice is to define constants for a query through a single group, as shown in the first query below. Note that the input for this query is the entire navigation state, even though nothing from the input is used. Since none of the input is actually needed, restrict the input to the smallest size possible with a very restrictive filter, such as the one shown in the second example.
DEFINE Constants AS SELECT 500 AS DefaultQuota GROUP
DEFINE Constants AS SELECT 500 AS DefaultQuota WHERE "mdex-property_Key" IS NOT NULL GROUP