Optimizer using the "wrong" index

This query runs in 7 minutes and uses the clustered index on Integer1. When I
force the query to use the non-clustered index on (Integer2, DateTime1) using
index hints, it runs under a minute. The table has 87 million rows. The stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!
Consider clustering on DateTime1.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1)
using
index hints, it runs under a minute. The table has 87 million rows. The
stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!

Optimizer using the "wrong" index

This query runs in 7 minutes and uses the clustered index on Integer1. When I
force the query to use the non-clustered index on (Integer2, DateTime1) using
index hints, it runs under a minute. The table has 87 million rows. The stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
--
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
> This query runs in 7 minutes and uses the clustered index on Integer1.
> When I
> force the query to use the non-clustered index on (Integer2, DateTime1)
> using
> index hints, it runs under a minute. The table has 87 million rows. The
> stats
> for the table are updated and the indexes have been rebuilt (reindexed and
> defragged for good measure).
> Does anyone have ideas to as to why the Optimizer refuses to use the more
> ideal index?
> --
> select integer1, intrger2, integer3, integer4, integer5
> from MainTable
> where interger2 in (1,2)
> and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
> Indexes:
> Clustered index on Integer1
> Non-Clustered on (Integer2, DateTime1)
> Non-Clustered on (DateTime1)
>
Try with @.StartDate and @.EndDate replaced with hard-coded date literals.
SQL Server is reluctant to commit to using a date index for a date range
with bind variables specifying the ends, because the using the index might
be pretty cheap when @.StartDate and @.EndDate are close together, but
horribly expensive when @.StartDate and @.EndDate are far apart. So this
might be one of those rare times when query hints are desirable.
You can also change the indexing scheme to make this query more attractive.
Options include changing the clustered index or adding
(integer3,integer4,integer5) to the non-clustered index to make it a
covering index for this query.
David|||Consider clustering on DateTime1.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1)
using
index hints, it runs under a minute. The table has 87 million rows. The
stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
--
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!

Optimizer using the "wrong" index

This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1) usin
g
index hints, it runs under a minute. The table has 87 million rows. The stat
s
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!Consider clustering on DateTime1.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1)
using
index hints, it runs under a minute. The table has 87 million rows. The
stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!

Optimiser and UDF's

I came across something today that hopefully if my explanation is
wrong someone could correct me.
I was trying to speed up a stored proc that took an integer as a
parameter and then ran around a dozen SQL statements to populate a
table variable.
1st query was :-
select <some columns> from A where A.id = @.id
The others were along those lines but joined to several lookup tables
and other combinations. Basically though it was hitting table A with
the same search conditions each time (id = @.id).
Thinking this was a waste I wrote a UDF that took the id as a
parameter and returned a table with just the relevant records from
table A in it. The SQL in the stored proc was then changed as follows
:-
select <some columns> from myUDF(@.id)
The new stored proc now runs twice as fast, uses half the cpu time and
a third of the disk io.
Would I be right in saying that SQL2k has realised that the UDF had
been called a dozen times with the same parameters, ran it once and
cached the results?
Its the only reason I can see for such a big performance improvement.
cheers.No, UDF results are not cached in SQL 2k, but that might be a nice feature
for Yukon... Sybase, for instance, does cache udf results...If your efforts
lowered the number of joins that were being done, I suspect the improvement
lies there.
"Mike Watson" <mike@.prog99.com> wrote in message
news:q1h7lvoluabdh7ulaigatf1ndn05gaofo8@.4ax.com...
> I came across something today that hopefully if my explanation is
> wrong someone could correct me.
> I was trying to speed up a stored proc that took an integer as a
> parameter and then ran around a dozen SQL statements to populate a
> table variable.
> 1st query was :-
> select <some columns> from A where A.id = @.id
> The others were along those lines but joined to several lookup tables
> and other combinations. Basically though it was hitting table A with
> the same search conditions each time (id = @.id).
> Thinking this was a waste I wrote a UDF that took the id as a
> parameter and returned a table with just the relevant records from
> table A in it. The SQL in the stored proc was then changed as follows
> :-
> select <some columns> from myUDF(@.id)
> The new stored proc now runs twice as fast, uses half the cpu time and
> a third of the disk io.
> Would I be right in saying that SQL2k has realised that the UDF had
> been called a dozen times with the same parameters, ran it once and
> cached the results?
> Its the only reason I can see for such a big performance improvement.
> cheers.
>|||On Tue, 2 Sep 2003 07:45:12 -0400, "Wayne Snyder"
<wsnyder@.computeredservices.com> wrote:
>No, UDF results are not cached in SQL 2k, but that might be a nice feature
>for Yukon... Sybase, for instance, does cache udf results...If your efforts
>lowered the number of joins that were being done, I suspect the improvement
>lies there.
>
Thanks for the reply, I'm surprised that the overhead of calling the
UDF around a dozen times is so much cheaper than doing a striaght join
on the table.