Performance

The SRS engine generates and executes SQL queries within the database. To optimize performance, consider the following recommendations:

1. Validate and prepare data with srssetup

Use srssetup to validate input, check business logic, and prepare data before running SRS queries.

2. Use proper parameter types

Ensure that parameter types match the expected data type in the database.

Example: defining a uniqueidentifier parameter

<srs>
  <def>
    <itm model="param" type="uniqueidentifier" name="userid"></itm>
  </def>
</srs>

See param for the full list of supported types.

3. Simplify queries and use indexes

• Keep queries simple to enhance readability and performance.
• Preprocess parameters when applicable.
• Add indexes on the columns used in joins and filters.

Example: preprocessing parameters

Before:

<srs>
  <def>
<itm><![CDATA[
SELECT  
...
FROM 
  a 
  INNER JOIN b ON a.ID = b.ID
  INNER JOIN c ON c.ID = b.ID
...
WHERE b.CODE = @warehousecode
]]></itm>
  </def>
</srs>

After:

<srs>
  <def>
<itm><![CDATA[
DECLARE @warehouseid UNIQUEIDENTIFIER;
SELECT TOP 1 @warehouseid = warehouseid FROM wms.location WHERE warehouseCode = @warehousecode;

SELECT  
...
FROM 
  a 
  INNER JOIN b ON a.ID = b.ID
  INNER JOIN c ON c.ID = b.ID
...
WHERE a.ID = @warehouseid;
]]></itm>
  </def>
</srs>

4. Avoid casting between data types

Prevent performance issues by avoiding unnecessary casting. For example, in SQL Server:

• Default text parameter type: nvarchar
• Avoid casting between nvarchar and varchar.

To specify varchar, use type="ansi" in your parameter definition.

5. Optimize queries with SRS engine hints

For complex queries with numerous joins, subqueries, or nullable parameters, the SQL Server optimizer may generate inefficient plans. Use the SRS engine hint /*jq-comment-if-null*/ to dynamically comment out null parameters.

Example: optimized query

Before:

SELECT * FROM table
  ... COMPLEX MULTIPLE JOINS ...
WHERE
 1=1
 AND (@param1 IS NULL OR col1 = @param1)
 AND (@param2 IS NULL OR col2 = @param2)
 AND (@param3 IS NULL OR col3 = @param3);

After:

SELECT * FROM table
  ... COMPLEX MULTIPLE JOINS ...
WHERE
 1=1
 AND col1 = @param1 /*jq-comment-if-null*/
 AND col2 = @param2 /*jq-comment-if-null*/
 AND col3 = @param3 /*jq-comment-if-null*/

Result (when all parameters are null):

SELECT * FROM table
  ... COMPLEX MULTIPLE JOINS ...
WHERE
 1=1
-- JQ COMMENT  AND col1 = @param1 /*jq-comment-if-null*/
-- JQ COMMENT  AND col2 = @param2 /*jq-comment-if-null*/
-- JQ COMMENT  AND col3 = @param3 /*jq-comment-if-null*/

How it works

1. The SRS engine checks the parameters passed.
2. If a parameter is null, the corresponding line is commented out.
3. Only the first parameter in a line is validated for nullability.
4. The hint works for SQL text commands, not for stored procedures.

6. Run commands in parallel

When an SRS has more than one SQL Server command, the commands run in parallel. The total time is close to the slowest command, not the sum of all commands.

• A command with link="self" waits until all previous commands finish.
• opts="jq-SkipOptimization" on the srs tag runs commands one by one, in the order of appearance. Use it only when one command depends on the side effects of another.

7. Cache results

Set cache on the srs tag to keep the result in memory for the given number of minutes.

<srs label="Stock summary" cache="10">

• Only GET requests are served from the cache. The cache key includes parameters, pscope, popts and pcolumns.
• A POST executes the SRS again and refreshes the cache.
• The response gets Cache-Control: private, max-age for the same time.

Warning

The cached result is shared by all users with access to the SRS. Do not use cache on an SRS whose result depends on the user, for example through column acl, dtacl or [[kv.v.user.*]] variables.

Adopting these practices ensures efficient and maintainable SQL query execution in your SRS engine.