Posted by Erland Sommarskog on 11/27/07 22:27

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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: ORDER BY AND GROUP BY CLAUSE
• Prev in forum: Data insertion too too slow...
• Thread view: Re: many "or" operation make system choose incorrect index

[Reply to this message]