Click here to Skip to main content
14,383,854 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi All,

The below query is taking a lot of time in executing i.e. upto almost 2 hr and sometime more also. The Tables LAR_PRODUCTS and STORE_RANGE_GRP_MATCH are physical tables and contain a record count of 432837 and 103038 respectively. Any suggestion to reduce the query execution time is welcomed.

Select 1                                  
      From LAR_PRODUCTS prd    with (nolock)                              
      Join STORE_RANGE_GRP_MATCH srg with (nolock)                                 
      On  prd.Store_Range_Grp_Id = srg.Orig_Store_Range_Grp_ID                                  
      And  srg.Match_Flag  = 'Y'                                  
      And  prd.Range_Event_Id = srg.LAR_Range_Event_Id                                  
                                  
      Where srg.Range_Event_Id Not IN (Select Range_Event_Id                                  
           From Last_Authorised_Range



What I have tried:

I have tried using indexed on both the tables but still it's taking a lot of time
Posted
Updated 24-Sep-19 20:49pm
v2
Comments
PIEBALDconsult 4-Feb-16 23:11pm
   
Do not _ever_ use IN or NOT IN with a subquery; convert it to a JOIN of some sort. Same for EXISTS.
Richard Deeming 5-Feb-16 13:09pm
   
Do you have anything to back that up?

This blog[^] suggests that a correlated NOT EXISTS tends to be the best solution. The results also suggest that NOT IN performs better than a LEFT JOIN.
jgakenhe 5-Feb-16 0:18am
   
I'd run the Execution Plan (google it) and see where it is hanging up. I'd then put an index on Match_Flag, because this is queried in the WHERE clause for a large amount of the records. And if you must use the NOT IN, then put it last as the dataset should be small by the time it gets there.

Execution Plan and Indexes: http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server
jaket-cp 5-Feb-16 5:22am
   
I was just wondering would there be a major benefit in having an index on Match_Flag if only two possible values of 'Y' and 'N' exist.
I would have suggested putting a clustered index on columns Store_Range_Grp_Id, Range_Event_Id.

As I said, just wondering that is all...
CHill60 5-Feb-16 8:30am
   
I agree - not worth it on Match_Flag, definitely index the Ids
Richard Deeming 5-Feb-16 13:06pm
   
However, if you're always querying for Match_Flag = 'Y', a filtered index might be useful.
CHill60 6-Feb-16 8:10am
   
Very good point and one I keep forgetting about. I've edited my solution (and credited you)
Rate this:
Please Sign up or sign in to vote.

Solution 1

As suggested by PIEBALDconsult replace the WHERE with a join
e.g.
LEFT join Last_Authorised_Range lar on lar.Range_Event_Id = srg.Range_Event_Id
WHERE lar.Range_Event_Id is null


Indexes on the ID fields in the tables should also help.

As Member 10454138 points out, there is no major benefit in having an index on Match_Flag if there are only two values

[EDIT] In regard to my last point on the column that only has two values - @RichardDeeming made an excellent point in a comment above. If you are always going to search for Match_Flag='Y' then using a Filtered Index will bring performance improvement - reference Introduction to SQL Server Filtered Indexes[^]
   
v2
Comments
Member 11706445 6-Mar-16 10:02am
   
how to use filtered index?
CHill60 6-Mar-16 12:09pm
   
There are examples on the link in my solution
Rate this:
Please Sign up or sign in to vote.

Solution 2

Hello All, I did used the Left join after removing the Not IN Clause, but this also didn't worked for me. Clustered Indexes are already there on ID columns on both the tables
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

HI Write not exists and also check for join condition left side table must contain less records.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100