Re: many "or" operation make system choose incorrect index

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
.

Relevant Pages

  • Re: many "or" operation make system choose incorrect index
    ... different index plan. ... When there are 63 OR operators, the optimizer ... Seeing you SQL, performance appears to be your smallest problem. ... Did you say which version of SQL Server you are using`? ...
    (comp.databases.ms-sqlserver)
  • Re: Can relational alegbra perform bulk operations?
    ... learn is to refrain from coding in such a way as to gove implicit "hints" to the optimizer in the way they express their queries. ... I do cringe a little when I see all those fancy "hints" which I've never really seen the occasion to use. ... Well, as I agreed with you, one shouldn't really be fiddling with the engine, but as I remarked in an earlier post if we were to port a complex database project from say, SQL Server to Oracle, and even if we didn't use any vendor-specific features, several SQL rewriting may be warranted simply because of performance differences due to the differences of how a SQL statement is parsed & optimized between two engines. ...
    (comp.databases.theory)
  • Performance: Query optimizer producing sub-optimal result?
    ... As part of a suite of SQL statements I am trying to write a data integrity ... SELECT UID, fnCheck ... The optimizer seems to move the "DISTINCT" action in the subquery to the end ... Define a View which performs the SELECT DISTINCT part of option A. My SQL ...
    (microsoft.public.sqlserver.programming)
  • Re: Poor performance after upgrading to sql server 2005
    ... We didn't just "throw the code at the optimizer", ... have to do the same thing for SQL 2005? ... Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.setup)
  • Re: Poor performance after upgrading to sql server 2005
    ... Andrew J. Kelly SQL MVP ... problem if the optimizer could not handle subqueries but I know for a ...
    (microsoft.public.sqlserver.setup)