[Solved] SET ROWCOUNT 1 performance issue

EverSQL Database Performance Knowledge Base

SET ROWCOUNT 1 performance issue

Database type:

I face an issue with SQL Server 2008 R2. If I execute the following statement, it takes less than 1 sec.

SELECT 
    A.REFERENCE_DATE AS REFERENCE_DATE
    ,A.MIS_BANK_ID AS BANK_ID
    ,'Unknown credit line' AS QUERY_LOG
    ,'STG_MIS_CREDIT_LINES_VALUE' AS [TABLE]
    ,'MIS_CREDIT_LINE_BRANCH_ID - MIS_CREDIT_LINE_ID' AS FIELD
    ,( 'MIS_CREDIT_LINE_BRANCH_ID: ' + A.MIS_CREDIT_LINE_BRANCH_ID + ' # MIS_CREDIT_LINE_ID: ' + A.MIS_CREDIT_LINE_ID ) AS [KEY]
    ,( A.MIS_CREDIT_LINE_BRANCH_ID + ' - ' + A.MIS_CREDIT_LINE_ID ) AS RESULT_1
    ,NULL AS RESULT_2
    ,NULL AS OUTSTANDING
FROM 
    STG_MIS_CREDIT_LINES_VALUE A
LEFT JOIN 
    STG_MIS_REG_CREDIT_LINES B ON A.REFERENCE_DATE = B.REFERENCE_DATE
                                  AND A.MIS_BANK_ID = B.MIS_BANK_ID
                                  AND A.MIS_CREDIT_LINE_BRANCH_ID = B.MIS_CREDIT_LINE_BRANCH_ID
                                  AND A.MIS_CREDIT_LINE_ID = B.MIS_CREDIT_LINE_ID
WHERE 
    B.MIS_CREDIT_LINE_ID IS NULL
    AND A.REFERENCE_DATE = '20131231'

While when I add a "SET ROWCOUNT 1" statement at the top, it takes more than 20 minutes to execute!

SET ROWCOUNT 1

SELECT 
    A.REFERENCE_DATE AS REFERENCE_DATE
    ,A.MIS_BANK_ID AS BANK_ID
    ,'Unknown credit line' AS QUERY_LOG
    ,'STG_MIS_CREDIT_LINES_VALUE' AS [TABLE]
    ,'MIS_CREDIT_LINE_BRANCH_ID - MIS_CREDIT_LINE_ID' AS FIELD
    ,( 'MIS_CREDIT_LINE_BRANCH_ID: ' + A.MIS_CREDIT_LINE_BRANCH_ID + ' # MIS_CREDIT_LINE_ID: ' + A.MIS_CREDIT_LINE_ID ) AS [KEY]
    ,( A.MIS_CREDIT_LINE_BRANCH_ID + ' - ' + A.MIS_CREDIT_LINE_ID ) AS RESULT_1
    ,NULL AS RESULT_2
    ,NULL AS OUTSTANDING
FROM 
    STG_MIS_CREDIT_LINES_VALUE A
LEFT JOIN 
    STG_MIS_REG_CREDIT_LINES B ON A.REFERENCE_DATE = B.REFERENCE_DATE
                                  AND A.MIS_BANK_ID = B.MIS_BANK_ID
                                  AND A.MIS_CREDIT_LINE_BRANCH_ID = B.MIS_CREDIT_LINE_BRANCH_ID
                                  AND A.MIS_CREDIT_LINE_ID = B.MIS_CREDIT_LINE_ID
WHERE 
    B.MIS_CREDIT_LINE_ID IS NULL
    AND A.REFERENCE_DATE = '20131231'

If I change the ROWCOUNT parameter to 2 or 0 or 100, or anything else, the query runs fast again (less than 1 sec).

NOTICE 1: The query actually returns 0 rows (and this is OK for me).

NOTICE 2: I cannot change the query adding a TOP 1 statement because mine is an ETL software and queries are "composed" by a fixed SET ROWCOUNT 1 (which is hardcoded for technical reasons) and a free query written by corporate users; something like 

SET ROWCOUNT 1

... SQL statement written by user ...

Thanks in advance.

Nicola

[EDIT]

Execution plan (with ROWCOUNT 1) http://pastebin.com/RambD7Aj

Execution plan (wiht ROWCOUNT 2) http://pastebin.com/cG4ngE4h

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Use Numeric Column Types For Numeric Values (query line: 21): Referencing a numeric value (e.g. 20131231) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Optimal indexes for this query:
CREATE INDEX stg_credit_idx_reference_date ON STG_MIS_CREDIT_LINES_VALUE (REFERENCE_DATE);
CREATE INDEX stg_reg_idx_mis_id_referen_mis_id_mis_id ON STG_MIS_REG_CREDIT_LINES (MIS_CREDIT_LINE_ID,REFERENCE_DATE,MIS_BANK_ID,MIS_CREDIT_LINE_BRANCH_ID);
The optimized query:
SELECT
        A.REFERENCE_DATE AS REFERENCE_DATE,
        A.MIS_BANK_ID AS BANK_ID,
        'Unknown credit line' AS QUERY_LOG,
        'STG_MIS_CREDIT_LINES_VALUE' AS [TABLE],
        'MIS_CREDIT_LINE_BRANCH_ID - MIS_CREDIT_LINE_ID' AS FIELD,
        ('MIS_CREDIT_LINE_BRANCH_ID: ' + A.MIS_CREDIT_LINE_BRANCH_ID + ' # MIS_CREDIT_LINE_ID: ' + A.MIS_CREDIT_LINE_ID) AS [KEY],
        (A.MIS_CREDIT_LINE_BRANCH_ID + ' - ' + A.MIS_CREDIT_LINE_ID) AS RESULT_1,
        NULL AS RESULT_2,
        NULL AS OUTSTANDING 
    FROM
        STG_MIS_CREDIT_LINES_VALUE A 
    LEFT JOIN
        STG_MIS_REG_CREDIT_LINES B 
            ON A.REFERENCE_DATE = B.REFERENCE_DATE 
            AND A.MIS_BANK_ID = B.MIS_BANK_ID 
            AND A.MIS_CREDIT_LINE_BRANCH_ID = B.MIS_CREDIT_LINE_BRANCH_ID 
            AND A.MIS_CREDIT_LINE_ID = B.MIS_CREDIT_LINE_ID 
    WHERE
        B.MIS_CREDIT_LINE_ID IS NULL 
        AND A.REFERENCE_DATE = '20131231'

Related Articles



* original question posted on StackOverflow here.