Query tuning

I have the query below:
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
The fields RxSource and RxTarget have nonclustered indexes.
Density for the indexes is near E-5.
Moreover there are another indexes for the table.
The optimizer does not want to use the indexes and it uses the primary key.
If I force to use the indexes by hints, then it raise the indexes, but it does not select the needed rows.
select *
from
CDO_RXLINK T_CDO_RXLINK (index(rdb$foreign411, rdb$foreign412))
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Plan:
|--Compute Scalar(DEFINE[T_CDO_RXLINK].[TEXTBLOB]=[T_CDO_RXLINK].TEXTBLOB]))
|--Filter(WHERE(('Mar 29 2004 12:00AM'>=[T_CDO_RXLINK].[CREATED] AND 'Mar 29 2004 12:00AM'<=[T_CDO_RXLINK].[DELETED]) AND ([T_CDO_RXLINK].[CID]=100043 OR [T_CDO_RXLINK].[CID]=100041)) AND ([T_CDO_RXLINK].[RXSOURCE]=23921 OR [T_CDO_RXLINK].[RXTARGE
|--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[Vlad43].[dbo].[CDO_RXLINK] AS [T_CDO_RXLINK]))
|--Hash Match(Inner Join, HASH[T_CDO_RXLINK].[OID])=([T_CDO_RXLINK].[OID]))
|--Index Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK])) Index Scan OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK]), FORCEDINDEX
|--Index Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK])) Index Scan OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK]), FORCEDINDEX [T_CDO_RXLINK].[OID]
If I make one compound index for the two fields then it works well, and time of executing in 10 time better.
There are causes that I can not change the indexes.
How can I force the Server to make good plan?
Victor,
Are your statistics up to date? Running Update Stats might help the optimizer here. Failing that you might want to use index hints (with caution). Have you got an index on the correct columns? Posting a repro script here would help us to debug it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi Victor,
This query is too complex for the optimizer for the following reasons:
- It can't use any index on T_CDO_RXLINK.CID because of the IN clause.
- It won't use the index on T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget
because of the or clause
Because of this, the optimizer will estimate that a cluster index seek or a
table scan is probably the best execution plan.
You best bet (I think) is a combined index on T_CDO_RXLINK.Created and
T_CDO_RXLINK.Deleted .
Also replace the 'SELECT *' with only the column names you really need. If
this is a limited set, add them together with the T_CDO_RXLINK.CID,
T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget columns to the to make it a
covering index.
Karl Gram, BSc, MBA
http://www.gramonline.com
"Victor Kozel" <victor_kozel@.tut.by> wrote in message
news:egxlDkjFEHA.2976@.TK2MSFTNGP10.phx.gbl...
I have the query below:
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
The fields RxSource and RxTarget have nonclustered indexes.
Density for the indexes is near E-5.
Moreover there are another indexes for the table.
The optimizer does not want to use the indexes and it uses the primary key.
If I force to use the indexes by hints, then it raise the indexes, but it
does not select the needed rows.
select *
from
CDO_RXLINK T_CDO_RXLINK (index(rdb$foreign411, rdb$foreign412))
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Plan:
|--Compute
Scalar(DEFINE[T_CDO_RXLINK].[TEXTBLOB]=[T_CDO_RXLINK].TEXTBLOB]))
|--Filter(WHERE(('Mar 29 2004 12:00AM'>=[T_CDO_RXLINK].[CREATED] AND
'Mar 29 2004 12:00AM'<=[T_CDO_RXLINK].[DELETED]) AND
([T_CDO_RXLINK].[CID]=100043 OR [T_CDO_RXLINK].[CID]=100041)) AND
([T_CDO_RXLINK].[RXSOURCE]=23921 OR [T_CDO_RXLINK].[RXTARGE
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[Vlad43].[dbo].[CDO_RXLINK] AS [T_CDO_RXLINK]))
|--Hash Match(Inner Join,
HASH[T_CDO_RXLINK].[OID])=([T_CDO_RXLINK].[OID]))
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK]),
FORCEDINDEX
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK]),
FORCEDINDEX [T_CDO_RXLINK].[OID]
If I make one compound index for the two fields then it works well, and time
of executing in 10 time better.
There are causes that I can not change the indexes.
How can I force the Server to make good plan?
|||Hi Karl
If I remove the "Between" and "IN" conditions then the plan is OK.
The fields "CID" and "CREATED" have own indexes too, but if I disable their for optimizer It does not help.
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN ISNULL(NULLIF(1,1),0) + T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
ISNULL(NULLIF(1,1),0) + T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Also "*" I inserted for readability. This is a list of fields really
I divided the Query to two different queries and it works speedly.
I think the poor controllability of the sql server is a serious problem.
Thanks
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message news:OwSrV4kFEHA.2408@.TK2MSFTNGP10.phx.gbl...
Hi Victor,
This query is too complex for the optimizer for the following reasons:
- It can't use any index on T_CDO_RXLINK.CID because of the IN clause.
- It won't use the index on T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget
because of the or clause
Because of this, the optimizer will estimate that a cluster index seek or a
table scan is probably the best execution plan.
You best bet (I think) is a combined index on T_CDO_RXLINK.Created and
T_CDO_RXLINK.Deleted .
Also replace the 'SELECT *' with only the column names you really need. If
this is a limited set, add them together with the T_CDO_RXLINK.CID,
T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget columns to the to make it a
covering index.
Karl Gram, BSc, MBA
http://www.gramonline.com
"Victor Kozel" <victor_kozel@.tut.by> wrote in message
news:egxlDkjFEHA.2976@.TK2MSFTNGP10.phx.gbl...
I have the query below:
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
The fields RxSource and RxTarget have nonclustered indexes.
Density for the indexes is near E-5.
Moreover there are another indexes for the table.
The optimizer does not want to use the indexes and it uses the primary key.
If I force to use the indexes by hints, then it raise the indexes, but it
does not select the needed rows.
select *
from
CDO_RXLINK T_CDO_RXLINK (index(rdb$foreign411, rdb$foreign412))
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Plan:
|--Compute
Scalar(DEFINE[T_CDO_RXLINK].[TEXTBLOB]=[T_CDO_RXLINK].TEXTBLOB]))
|--Filter(WHERE(('Mar 29 2004 12:00AM'>=[T_CDO_RXLINK].[CREATED] AND
'Mar 29 2004 12:00AM'<=[T_CDO_RXLINK].[DELETED]) AND
([T_CDO_RXLINK].[CID]=100043 OR [T_CDO_RXLINK].[CID]=100041)) AND
([T_CDO_RXLINK].[RXSOURCE]=23921 OR [T_CDO_RXLINK].[RXTARGE
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[Vlad43].[dbo].[CDO_RXLINK] AS [T_CDO_RXLINK]))
|--Hash Match(Inner Join,
HASH[T_CDO_RXLINK].[OID])=([T_CDO_RXLINK].[OID]))
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK]),
FORCEDINDEX
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK]),
FORCEDINDEX [T_CDO_RXLINK].[OID]
If I make one compound index for the two fields then it works well, and time
of executing in 10 time better.
There are causes that I can not change the indexes.
How can I force the Server to make good plan?
|||Hi Mark,
Yes, I made updating for the statistic.
I have tried to use the query on Borland Interbase (the same db structure and data) It made good plan.
I think it does not have a sense to debug without data and the table have more than 150000 rows.
Thanks
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message news:E22497EA-36C8-4C1E-BA4E-86CA08578D6B@.microsoft.com...
Victor,
Are your statistics up to date? Running Update Stats might help the optimizer here. Failing that you might want to use index hints (with caution). Have you got an index on the correct columns? Posting a repro script here would help us to debug it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||thanks All,
This is the solution
select *
from
CDO_RXLINK T_CDO_RXLINK, CDO_RXLINK T_CDO_RXLINK1
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK1.RxSource = 23921 or T_CDO_RXLINK1.RxTarget = 23921)
and T_CDO_RXLINK1.OID = T_CDO_RXLINK.OID /*primary key*/
"Victor Kozel" <victor_kozel@.tut.by> wrote in message news:uJRJ2xvFEHA.3912@.TK2MSFTNGP10.phx.gbl...
Hi Karl
If I remove the "Between" and "IN" conditions then the plan is OK.
The fields "CID" and "CREATED" have own indexes too, but if I disable their for optimizer It does not help.
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN ISNULL(NULLIF(1,1),0) + T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
ISNULL(NULLIF(1,1),0) + T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Also "*" I inserted for readability. This is a list of fields really
I divided the Query to two different queries and it works speedly.
I think the poor controllability of the sql server is a serious problem.
Thanks
"Karl Gram" <NOSPAMkarl@.gramonline.nl> wrote in message news:OwSrV4kFEHA.2408@.TK2MSFTNGP10.phx.gbl...
Hi Victor,
This query is too complex for the optimizer for the following reasons:
- It can't use any index on T_CDO_RXLINK.CID because of the IN clause.
- It won't use the index on T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget
because of the or clause
Because of this, the optimizer will estimate that a cluster index seek or a
table scan is probably the best execution plan.
You best bet (I think) is a combined index on T_CDO_RXLINK.Created and
T_CDO_RXLINK.Deleted .
Also replace the 'SELECT *' with only the column names you really need. If
this is a limited set, add them together with the T_CDO_RXLINK.CID,
T_CDO_RXLINK.RxSource and T_CDO_RXLINK.RxTarget columns to the to make it a
covering index.
Karl Gram, BSc, MBA
http://www.gramonline.com
"Victor Kozel" <victor_kozel@.tut.by> wrote in message
news:egxlDkjFEHA.2976@.TK2MSFTNGP10.phx.gbl...
I have the query below:
select *
from
CDO_RXLINK T_CDO_RXLINK
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
The fields RxSource and RxTarget have nonclustered indexes.
Density for the indexes is near E-5.
Moreover there are another indexes for the table.
The optimizer does not want to use the indexes and it uses the primary key.
If I force to use the indexes by hints, then it raise the indexes, but it
does not select the needed rows.
select *
from
CDO_RXLINK T_CDO_RXLINK (index(rdb$foreign411, rdb$foreign412))
where
'03/29/2004' BETWEEN T_CDO_RXLINK.Created AND T_CDO_RXLINK.Deleted AND
T_CDO_RXLINK.CID IN (100041, 100043) and
( T_CDO_RXLINK.RxSource = 23921 or T_CDO_RXLINK.RxTarget = 23921)
Plan:
|--Compute
Scalar(DEFINE[T_CDO_RXLINK].[TEXTBLOB]=[T_CDO_RXLINK].TEXTBLOB]))
|--Filter(WHERE(('Mar 29 2004 12:00AM'>=[T_CDO_RXLINK].[CREATED] AND
'Mar 29 2004 12:00AM'<=[T_CDO_RXLINK].[DELETED]) AND
([T_CDO_RXLINK].[CID]=100043 OR [T_CDO_RXLINK].[CID]=100041)) AND
([T_CDO_RXLINK].[RXSOURCE]=23921 OR [T_CDO_RXLINK].[RXTARGE
|--Bookmark Lookup(BOOKMARK[Bmk1000]),
OBJECT[Vlad43].[dbo].[CDO_RXLINK] AS [T_CDO_RXLINK]))
|--Hash Match(Inner Join,
HASH[T_CDO_RXLINK].[OID])=([T_CDO_RXLINK].[OID]))
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN411] AS [T_CDO_RXLINK]),
FORCEDINDEX
|--Index
Scan(OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS
[T_CDO_RXLINK])) Index Scan
OBJECT[Vlad43].[dbo].[CDO_RXLINK].[RDB$FOREIGN412] AS [T_CDO_RXLINK]),
FORCEDINDEX [T_CDO_RXLINK].[OID]
If I make one compound index for the two fields then it works well, and time
of executing in 10 time better.
There are causes that I can not change the indexes.
How can I force the Server to make good plan?