 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Other SQL Server Topics / November 2007many "or" operation make system choose incorrect index
Hi All, I have one question about many "or" operation make system choose incorrect index There is one table TT ( C1 VARCHAR(15) NOT NULL, C2 VARCHAR(15) NOT NULL, C3 VARCHAR(15) NOT NULL, C4 VARCHAR(15) NOT NULL C5 VARCHAR2(200), ) Primary Key TT_PK (C1, C2, C3, C4) SELECT C1, C2, C3, C4 FROM TT WHERE C1 = 'TEST' AND ((C2 = '07RES' AND C3 = '00000' AND C4 = '02383') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02382') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02381') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02380') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02379') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02378') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02377') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02376') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02375') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02374') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02373') OR (C2 = '07RES' AND C3 = '00000' AND C4 = '02372') ... about 100 or operations OR (C2 = '07COM' AND C3 = '00000' AND C4 = '00618') OR (C2 = '07COM' AND C3 = '00000' AND C4 = '00617') OR (C2 = '07COM' AND C3 = '00000' AND C4 = '00616') OR (C2 = '07COM' AND C3 = '00000' AND C4 = '00608') ) The system choose index prefix, and query all index leaf with C1='TEST' Prefix: [dbo].[TT].C1 = 'TEST' After I reduce the OR operators to 50, it use choose Prefix: [dbo].[TT].C1, [dbo].[TT].C2,[dbo].[TT].C3,[dbo].[TT].C4= 'TEST, '07RES', '00000', '02383' Then Merge Join, it is very quick, Can anyone help on this, do I have to reduce the OR operator to 50? Thanks in advance! lsllcm, Is there a join in the query? I get the feeling you did not post all relevant parts of the query. Why would you get a merge join? And what does "The system choose index prefix" mean? There is no hard or fast rule for this. Although I could imagine that too many predicates would disqualify index seeks, in general it is all about selectivity. During compilation the optimizer will try to determine whether index seeks (followed by bookmark lookups) are faster than (partially) scanning the (clustered) index, based on the estimate of the number of qualifying rows. Please note that there is a certain point at which the compilation time grows a lot for each addition predicate you add to the WHERE clause. If the compilation time exceeds the estimated gains, the optimizer will stop compilation and simply choose a "good enough" plan. If the performance of this query is very important to you, and the structure of the predicates is as "simple" and predictable as your example, then you could consider rewriting the query as below: SELECT C1, C2, C3, C4 FROM TT WHERE C1 = 'TEST' AND C2 = '07RES' AND C3 = '00000' AND C4 IN ('02383','02382','02381','02380','02379', ...) UNION ALL SELECT C1, C2, C3, C4 FROM TT WHERE C1 = 'TEST' AND C2 = '07COM' AND C3 = '00000' AND C4 IN ('00618','00617','00616', ...)  SignatureGert-Jan > Hi All, > [quoted text clipped - 42 lines] > > Thanks in advance! > Please note that there is a certain point at which the compilation time > grows a lot for each addition predicate you add to the WHERE clause. If > the compilation time exceeds the estimated gains, the optimizer will > stop compilation and simply choose a "good enough" plan. Indeed. Many OR clauses, or many values in IN can result in horrendeous compilation times. SQL 2005 fare a lot better than SQL 2000, but the cost is still high. One thing I've notice that when there are more than 63 values (I think that was the value), SQL Server stashes all the constants into a work table, and you get the same result as you had the values in a temp table. At least that was what I saw in a test that I ran. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Gert-Jan/Erland Sommarskog, Thanks for your input. The whole sql is very long. SELECT SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3, B1_CHECKLIST_COMMENT FROM BCHCKBOX P WHERE SERV_PROV_CODE = 'SACCO' AND ((B1_PER_ID1 = '07COM' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '00628') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02386') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02385') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02384') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02383') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02382') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02381') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02380') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02379') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02378') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02377') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02376') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02375') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02374') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02373') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02372') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02371') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02370') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02369') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02368') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02367') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02366') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02365') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02364') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02363') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02362') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02361') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02360') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02359') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02358') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02357') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02356') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02355') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02354') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02353') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02352') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02351') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02350') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02349') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02348') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02347') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02346') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02345') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02344') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02343') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02342') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02341') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02340') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02339') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02338') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02337') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02336') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02335') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02334') OR (B1_PER_ID1 = '07RES' AND B1_PER_ID2 = '00000' AND B1_PER_ID3 = '02333') ) yes, I have reduced the OR number to 62, the optimizer chooses different index plan. When there are 63 OR operators, the optimizer chooses the partial index scan Seek predicts : Prefix: [sacco].[dbo].[BCHCKBOX].SERV_PROV_CODE = 'SACCO' The method is one way to workaround, but it will make sql very long. SELECT C1, C2, C3, C4 FROM TT WHERE C1 = 'TEST' AND C2 = '07RES' AND C3 = '00000' AND C4 IN ('02383','02382','02381','02380','02379', ...) UNION ALL SELECT C1, C2, C3, C4 FROM TT WHERE C1 = 'TEST' AND C2 = '07COM' AND C3 = '00000' AND C4 IN ('00618','00617','00616', ...) I will change OR number to 50. Where can I found the document of 63 is the value? Because multiple client access the site, I don't know how to use temporary table to do it, could you give me one example? Thanks Jacky Hi Gert-Jan, Is there a join in the query? I get the feeling you did not post all relevant parts of the query. Why would you get a merge join? And what does "The system choose index prefix" mean? There is no join in the query, I think the merge join is to merge the results of different OR. The system choose run the partial index of beginning "SERV_PROV_CODE". Thanks Jacky > Hi Gert-Jan/Erland Sommarskog, > [quoted text clipped - 8 lines] > Seek predicts : Prefix: [sacco].[dbo].[BCHCKBOX].SERV_PROV_CODE = > 'SACCO' Seeing you SQL, performance appears to be your smallest problem. That code must be about unmaintainable. I would get those values in to a table. That may or may not help you to the best performance, but at least you will be better equipped to battle the problems. > Where can I found the document of 63 is the value? I doubt that it's documented. I found it on my own by testing. > Because multiple client access the site, I don't know how to use > temporary table to do it, could you give me one example? I don't see why the multiple clients would cause the problem. But since I don't where you get all these values from or much at all about your system, I cannot any examples. Did you say which version of SQL Server you are using`? SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thank you The version mssql 2005 sp1 > > Hi Gert-Jan/Erland Sommarskog, > [quoted text clipped - 32 lines] > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Erland Sommarskog, Could you provide the test step of 63 value Thanks > Could you provide the test step of 63 value I am sorry, but I don't have anything canned that I easily can share. I did this observation while working with my article on arrays and list in SQL Server, for which I ran a bunch of performance tests. The tests are described on http://www.sommarskog.se/arrays-in-sql-perftest.html, but all parts for the test is not available for download. (The test data is too big to warrant that.) But you could try running queries like SELECT * FROM [Order Details] WHERE ProductID IN (.....) in the Northgale database, which is another test database of mine. You find it at http://www.sommarskog.se/dyn-search.html#Northgale. I cannot promise that you will actually see this 63-value limit. Who knows, the optimizer may use some metric to determine when to set the limit. SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.