many "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!

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

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', ...)

--
Gert-Jan



lsllcm wrote:
>
> 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!

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

Gert-Jan Strik (sorry@toomuchspamalready.nl) writes:
> 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.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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

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

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

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

lsllcm (lsllcm@gmail.com) writes:
> Hi Gert-Jan/Erland Sommarskog,
>
> Thanks for your input.
>
> The whole sql is very long.
>...
> 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'

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`?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

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

Thank you

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

The version mssql 2005 sp1

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

On Nov 28, 6:27 am, Erland Sommarskog wrote:
> lsllcm (lsl...@gmail.com) writes:
> > Hi Gert-Jan/Erland Sommarskog,
>
> > Thanks for your input.
>
> > The whole sql is very long.
> >...
> > 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'
>
> 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`?
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Hi Erland Sommarskog,

Could you provide the test step of 63 value

Thanks

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

lsllcm (lsllcm@gmail.com) writes:
> 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.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx