CODEWITHSUNDEEP
sqlsql-servert-sql
Mohamed Elsayed Ali
Mohamed Elsayed Ali

Reputation: 93

T-SQL Select where "IN" very bad Performance impact

when use the following code:

SELECT * INTO #StoreIdsPermision FROM (SELECT StoreId FROM dbo.FN_Inv_GetListOfStoreIdCheckPermission(15,3019)) StoreIdsPermision;

SELECT distinct

        Id,
        StoreName,
        ......,
        ......,

    from INV_Transactions
        inner join ....
        left outer join ....

    WHERE StoreId in (select Id from #StoreIdsPermision)

Execution time is 4 minutes.

and when use the same code with comma syntax in "WHERE IN" statement, the execution time is 3 seconds, why? and how fix that?

SELECT distinct

        Id,
        StoreName,
        ......,
        ......,

    from INV_Transactions
        inner join ....
        left outer join ....

    WHERE StoreId in (4,7,9,15,22,........)

NOTE: the Execution time for the following statement less than 1 second, and #StoreIdsPermision about 140 row only

SELECT * INTO #StoreIdsPermision FROM (SELECT StoreId FROM dbo.FN_Inv_GetListOfStoreIdCheckPermission(15,3019)) StoreIdsPermision;

Actual Execution Plan for Part 1 Actual Execution Plan

Actual Execution Plan for Part 2 Actual Execution Plan

Actual Execution Plan when use comma syntax Actual Execution Plan

UPDATE 1:

VERY GOOD Suggestion from "Eponyme Web" Suggestion Number Two, when added SET FORCEPLAN ON In the First and SET FORCEPLAN OFF in The last Query, the Execution Time is Normal, what Happen?

UPDATE 2:

I replaced all inner join to left outer join and worked fine also without FORCEPLAN ON

Upvotes: 2

Views: 417

Answers (3)

Eponyme Web
Eponyme Web

Reputation: 976

You have confirmed that the first part of the first query is not causing the issue ?

SELECT * 
INTO #StoreIdsPermision 
FROM 
    (SELECT StoreId 
     FROM dbo.FN_Inv_GetListOfStoreIdCheckPermission(15, 3019)) StoreIdsPermision;

If the above code is not the source of the performance issue, you can add an index to your #StoreIdsPermision temp table

CREATE INDEX sip1 ON #StoreIdsPermision (StoreId);

I would try two other things (always keeping the created index on the temp table)

1 - replacing your where clause with this one

WHERE EXISTS (SELECT 1 from #StoreIdsPermision SIP WHERE INV_Transactions.StoreId  = SIP.StoreId)

2 - put a forceplan on at the beginning of the second query and a forceplan off at the end. 99.9% of the time the optimiser gets it right or at least right enough, but sometimes it doesn't. In you case I wouldn't expect such a difference in performance from the two approaches.

Upvotes: 2

radarbob
radarbob

Reputation: 5101

where .... in ...(1,2,3) is essentially executing or against each and every element in that list. The 3 minutes is the (select Id from ...) without an index. @EponymeWeb says build an index; yes, but check that performance against #StoreIdsPermission as a permanent table with a permanent index. When you flush then refill that table be sure to rebuild statistics.

Upvotes: 0

Wei Lin
Wei Lin

Reputation: 3811

Normally it wouldn't be that bad, so it looks like the query will be 3 seconds because it's already been cached.

You can use below sql script get cache check query:

SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
where text like '%IN (4,7,9,15,22,........)%'
   or text like '% in (select Id from #StoreIdsPermision)%'
ORDER BY cplan.usecounts DESC

If temp table query not in cache and normal query in cache result then It explains why one takes four minutes and the other three seconds.

Upvotes: 0

Related Questions