CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008-r2rowcount
ottomano
ottomano

Reputation: 13

SET ROWCOUNT 1 performance issue

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

Upvotes: 1

Views: 614

Answers (1)

user275683
user275683

Reputation:

Can't say for sure without looking at execution plans, but it sounds like the when you first add SET ROWCOUNT 1 it tries to create new execution plan that does not uses indexes, when you rerun it again it uses correct execution plan.

Here are couple things to try.

  • add OPTION (RECOMPILE) to the of your query.
  • Try replacing SET ROWCOUNT 1 with SELECT TOP 1
  • look at execution plans for both queries.
  • Add ROW_NUMBER() and turn your query into derived table with where clause where rn = 1

Upvotes: 1

Related Questions