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!
.

Relevant Pages

  • Re: SQL Server 2000 UDF Intermittent Slow Execution
    ... variable is not known at compilation time because it is not set until ... The query is compiled for an unknown value, ... but there are many rows for these schedules. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Using XML data for query
    ... Additionally I need to get XML as ouput param for later use from the dynamic ... (QUERY WITH XSL AND SP_EXECUTESQL) ... declare @paramdef nvarchar ... declare @predicates int ...
    (microsoft.public.sqlserver.xml)
  • Re: CONTAINS performance
    ... FTS predicates, such as CONTAINS. ... As an example, the following query: ... searches - Does it make a difference if B.id and C.id are unique keys? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Using XML data for query
    ... I would like to use the key-value pair in my SQL query dynamically. ... The essential problem here is that since the XQuery is done as part of the underlying execution plan, you'd have to construct this as a dynamic query -- a first query to resolve the where predicate columns and values and a second to actually do the work. ... declare @query nvarchar ... declare @predicates int ...
    (microsoft.public.sqlserver.xml)
  • Re: List of bad practices
    ... then using BETWEEN predicates to count how many ... >sessions each of those points falls inside of. ... Hi Joe, ... The beauty of Itzik's set-based query is that the derived table in effect ...
    (microsoft.public.sqlserver.programming)