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

On Nov 28, 6:27 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
lsllcm (lsl...@xxxxxxxxx) 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...@xxxxxxxxxxxxx

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

.

Relevant Pages

  • Re: Strange Performance question
    ... > FROM tranzbranch_archive.dbo.tblConsignment c WITH (NOLOCK) ... The optimizer in SQL 2000 optimizes an entire batch at a time. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: joins
    ... I expect the optimizer to see that the left join is ... really an inner join, and thus you would get the same query plan. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Slow Execution Performance
    ... on the market. ... Not that I know very much about the optimizer in, say, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • 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: local temp tables
    ... statistics, constraints, etc. to pass to the optimizer when the inline ... trying to say that MS SQL Server stinks and cannot handle CTE choices ... Richard found one published grid with 250+ valid answers! ... you said that you could write a better Sudoku query in five minutes ...
    (microsoft.public.sqlserver.programming)