Skip to content

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

XML
1
2
3
4
5
<srs>
  <def>
    <itm model="param" type="uniqueidentifier" name="userid"></itm>
  </def>
</srs>

Refer to Parameter Types Documentation for a full list of supported types.

3. Simplify Queries and Use Indexes

  • Keep queries simple to enhance readability and performance.
  • Preprocess parameters when applicable.

Example: Preprocessing Parameters

Before:

XML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
<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:

XML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
<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 instance, in MSSQL 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:

SQL
1
2
3
4
5
6
7
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:

SQL
1
2
3
4
5
6
7
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):

SQL
1
2
3
4
5
6
7
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*/

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.

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