0

The below query is taking over 3 hours. The tables LAR_PRODUCTS and STORE_RANGE_GRP_MATCH are physical tables.

Rows:

  • LAR_PRODUCTS: 432 837
  • STORE_RANGE_GRP_MATCH: 103 038
  • Last_Authorised_Range: 366 026

SQL:

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)

Current indexes are

Create Clustered Index Idx_tmpSTORE_RANGE_GRP_MATCH 
ON STORE_RANGE_GRP_MATCH (LAR_Range_Event_Id, Orig_Store_Range_Grp_ID)
James Z
  • 12,209
  • 10
  • 24
  • 44
Sumit Dwivedi
  • 21
  • 10
  • 1
    We can't really tell what's going on without knowing your tables and indexes and seeing a query plan. – Matt Gibson Feb 09 '16 at 11:11
  • paste execution plan of the query..http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan – TheGameiswar Feb 09 '16 at 11:24
  • At a guess, add required indexes on join columns and use `NOT EXISTS` instead of `NOT IN` - you didn't mention how many records are in `Last_Authorised_Range` – Nick.Mc Feb 09 '16 at 11:44
  • @Nick.McDermaid The number of records are 366026 in Last_Authorised_Range. Clustered indexes are already present on Join tables – Sumit Dwivedi Feb 10 '16 at 04:50
  • I hope u know GOOD BAD AND UGLY THINGS ABOUT NOLOCK http://stackoverflow.com/questions/24447345/to-nolock-or-not-to-nolock – Neeraj Prasad Sharma Feb 10 '16 at 06:29
  • @SumitDwivedi Your query is quite simple its very unlikely with little table cardinality query is taking 3 hours. Update STATISTICS OF ALL THREE TABLES CHANGE NOT IN TO NOT EXISTS." ARE YOU SURE NOTHING IS BLOCKING THIS QUERY? IN THE END I WOULD LOVE TO SEE QUERY EXECUTION PLAN AND TABLE SCHEMAs – Neeraj Prasad Sharma Feb 10 '16 at 06:45
  • I have gone through your comments and added them to the original question. If you have any more indexes, add them to the original question. – Nick.Mc Feb 10 '16 at 07:01
  • How many records in table STORE_RANGE_GRP_MATCH have Match_Flag = 'Y'? please find out and edit your question and add this detail. Haw many different values can Match_Flag have? – Nick.Mc Feb 10 '16 at 07:03
  • "Clustered Indexes are already present on these tables" -- what exactly are **all** the indexes on the tables – James Z Feb 12 '16 at 17:32
  • Also, I would recommend you stop using "with (nolock)". That can cause you all sorts of funny issues that are not so funny to solve. – James Z Feb 12 '16 at 17:34

2 Answers2

0

First of all, you could replace the NOT IN clause with a LEFT JOIN, like so:

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 
  LEFT JOIN Last_Authorised_Range lar
  ON srg.Range_Event_Id = lar.Range_Event_Id
  WHERE lar.Range_Event_Id IS NULL

Next it wouldn't hurt to create some indexes on each column involved in the query, so to speed up any comparison between them.

  • Index on STORE_RANGE_GRP_MATCH (as kindly suggested by user TT.)

    CREATE NONCLUSTERED INDEX STORE_RANGE_GRP_MATCH_idx1 ON STORE_RANGE_GRP_MATCH(Orig_Store_Range_Grp_ID,LAR_Range_Event_Id,Match_Flag);

  • Index on LAR_PRODUCTS:

    CREATE NONCLUSTERED INDEX LAR_PRODUCTS_idx1 ON LAR_PRODUCTS(Store_Range_Grp_Id,Range_Event_Id);

Hope this helps!

GigiSan
  • 1,170
  • 2
  • 19
  • 30
  • Clustered Indexes are already present on these tables. Should i used non clustered indexes? – Sumit Dwivedi Feb 10 '16 at 04:52
  • Clustered indexes are usually best suited to represent the columns that make each row unique. That been said, if the clustered indexes you have are on the exact same columns then you can leave it like that. Otherwise you should create new non clustered indexes as described above. Non clustered indexes are better suited for columns which might have duplicated values and are used as a quick pointer to each row in the table. – GigiSan Feb 10 '16 at 08:54
0

You are probably missing an index to speed up your query. One candidate INDEX that comes to mind is:

CREATE NONCLUSTERED INDEX srg_id_event_flag ON STORE_RANGE_GRP_MATCH(Orig_Store_Range_Grp_ID,LAR_Range_Event_Id,Match_Flag);

Execute that statement, then run the query again. It will likely improve performance.

TT.
  • 15,774
  • 6
  • 47
  • 88
  • Currently the index is created like this:- Create Clustered Index Idx_tmpSTORE_RANGE_GRP_MATCH ON STORE_RANGE_GRP_MATCH (LAR_Range_Event_Id,Orig_Store_Range_Grp_ID) – Sumit Dwivedi Feb 10 '16 at 04:47
  • Should i change clustered to non clustered index? – Sumit Dwivedi Feb 10 '16 at 04:47
  • @SumitDwivedi I suggested a non-clustered index because I assumed one would be present in the tabla already. IMO you should **not** change the index I proposed to a clustered one. Leave the clustered one that exists in the table there, and create the index I propsed non-clustered. – TT. Feb 10 '16 at 05:58
  • 1
    Quite possible even better would be a filtered index on Orig_Store_Range_Grp_ID,LAR_Range_Event_Id, filtered on Match_Flag='Y' but we really need to know more about the existing query plan and data – Nick.Mc Feb 10 '16 at 07:04