Query optimiser bug?

Hi all,
I'm having a real problem with a simple query. The table in question has
about 2 million entries with a clustered index and 2 normal indicies.
This query:
select * from uk_streets where search_thoroughfare like 'agsts drv%'
works fine and fast but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets where search_thoroughfare like @.q
takes about 20 times longer to execute. I figured it's to do with the query
plan being used by the optimiser so I've added an index hint:
select * from uk_streets (index=uk_streets) where search_thoroughfare
like 'agsts drv%'
is still very fast and uses the index but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets (index=uk_streets) where search_thoroughfare
like @.q
ignores the hint and still takes ages (using the clustered index instead).
I'm sure that I'm missing something but it's driving me bonkers! Can anyone
shed any light on the problem please?
Thanks in advance
Jamie.
PS: Here's the table and index structure:
CREATE TABLE UK_Streets
(
locality_key int NOT NULL ,
search_thoroughfare varchar (80) NOT NULL ,
outcode varchar (4) NOT NULL ,
sector char (1) NOT NULL ,
thoroughfare_key int NOT NULL,
thoroughfare_descriptor_key int NOT NULL
)
CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
search_thoroughfare)
CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
search_thoroughfare)Try out with updating The table Statistics ,still if you get the Same
check out the fragmentation using Dbcc and if its bad rebuild the Index
and See
Thanks,
Saradhi|||Hi Saradhi
Thanks for the message - I've run DBREINDEX and it's worked! Thank you so
much - I'd already rebuilt the stats which had no effect but that's
brilliant.
Thanks again!
Cheers
Jamie.
"saradhi" <saradhiY@.gmail.com> wrote in message
news:1124984188.908657.122330@.g43g2000cwa.googlegroups.com...
> Try out with updating The table Statistics ,still if you get the Same
> check out the fragmentation using Dbcc and if its bad rebuild the Index
> and See
> Thanks,
> Saradhi
>|||I am surprised that updating statistics made any difference at all..
The problem is around using variables in your where clause... When the query
is optimized, the optimizer can not see the value of the local variable, and
therefore is unable to use index stats to determine the best index. However
when the value is a literal , index stats can be used to determine the best
index, which can greatly improve performance.
This is not an issue with parameters which are passed in to a Stored proc
and used directly in a where clause... The optimizer CAN see and optimize
for this value - this feature is called Parameter sniffing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jamie Turner" <jamieturner@.fernhillsolutions.net> wrote in message
news:%23bKy5lXqFHA.712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I'm having a real problem with a simple query. The table in question has
> about 2 million entries with a clustered index and 2 normal indicies.
> This query:
> select * from uk_streets where search_thoroughfare like 'agsts drv%'
> works fine and fast but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets where search_thoroughfare like @.q
> takes about 20 times longer to execute. I figured it's to do with the
> query plan being used by the optimiser so I've added an index hint:
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like 'agsts drv%'
> is still very fast and uses the index but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like @.q
> ignores the hint and still takes ages (using the clustered index instead).
> I'm sure that I'm missing something but it's driving me bonkers! Can
> anyone shed any light on the problem please?
> Thanks in advance
> Jamie.
>
> PS: Here's the table and index structure:
> CREATE TABLE UK_Streets
> (
> locality_key int NOT NULL ,
> search_thoroughfare varchar (80) NOT NULL ,
> outcode varchar (4) NOT NULL ,
> sector char (1) NOT NULL ,
> thoroughfare_key int NOT NULL,
> thoroughfare_descriptor_key int NOT NULL
> )
> CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
> search_thoroughfare)
> CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
> CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
> search_thoroughfare)
>

Query optimiser bug?

Hi all,
I'm having a real problem with a simple query. The table in question has
about 2 million entries with a clustered index and 2 normal indicies.
This query:
select * from uk_streets where search_thoroughfare like 'agsts drv%'
works fine and fast but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets where search_thoroughfare like @.q
takes about 20 times longer to execute. I figured it's to do with the query
plan being used by the optimiser so I've added an index hint:
select * from uk_streets (index=uk_streets) where search_thoroughfare
like 'agsts drv%'
is still very fast and uses the index but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets (index=uk_streets) where search_thoroughfare
like @.q
ignores the hint and still takes ages (using the clustered index instead).
I'm sure that I'm missing something but it's driving me bonkers! Can anyone
shed any light on the problem please?
Thanks in advance
Jamie.
PS: Here's the table and index structure:
CREATE TABLE UK_Streets
(
locality_key int NOT NULL ,
search_thoroughfare varchar (80) NOT NULL ,
outcode varchar (4) NOT NULL ,
sector char (1) NOT NULL ,
thoroughfare_key int NOT NULL,
thoroughfare_descriptor_key int NOT NULL
)
CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
search_thoroughfare)
CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
search_thoroughfare)Try out with updating The table Statistics ,still if you get the Same
check out the fragmentation using Dbcc and if its bad rebuild the Index
and See
Thanks,
Saradhi|||Hi Saradhi
Thanks for the message - I've run DBREINDEX and it's worked! Thank you so
much - I'd already rebuilt the stats which had no effect but that's
brilliant.
Thanks again!
Cheers
Jamie.
"saradhi" <saradhiY@.gmail.com> wrote in message
news:1124984188.908657.122330@.g43g2000cwa.googlegroups.com...
> Try out with updating The table Statistics ,still if you get the Same
> check out the fragmentation using Dbcc and if its bad rebuild the Index
> and See
> Thanks,
> Saradhi
>|||I am surprised that updating statistics made any difference at all..
The problem is around using variables in your where clause... When the query
is optimized, the optimizer can not see the value of the local variable, and
therefore is unable to use index stats to determine the best index. However
when the value is a literal , index stats can be used to determine the best
index, which can greatly improve performance.
This is not an issue with parameters which are passed in to a Stored proc
and used directly in a where clause... The optimizer CAN see and optimize
for this value - this feature is called Parameter sniffing.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jamie Turner" <jamieturner@.fernhillsolutions.net> wrote in message
news:%23bKy5lXqFHA.712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I'm having a real problem with a simple query. The table in question has
> about 2 million entries with a clustered index and 2 normal indicies.
> This query:
> select * from uk_streets where search_thoroughfare like 'agsts drv%'
> works fine and fast but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets where search_thoroughfare like @.q
> takes about 20 times longer to execute. I figured it's to do with the
> query plan being used by the optimiser so I've added an index hint:
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like 'agsts drv%'
> is still very fast and uses the index but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like @.q
> ignores the hint and still takes ages (using the clustered index instead).
> I'm sure that I'm missing something but it's driving me bonkers! Can
> anyone shed any light on the problem please?
> Thanks in advance
> Jamie.
>
> PS: Here's the table and index structure:
> CREATE TABLE UK_Streets
> (
> locality_key int NOT NULL ,
> search_thoroughfare varchar (80) NOT NULL ,
> outcode varchar (4) NOT NULL ,
> sector char (1) NOT NULL ,
> thoroughfare_key int NOT NULL,
> thoroughfare_descriptor_key int NOT NULL
> )
> CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
> search_thoroughfare)
> CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
> CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
> search_thoroughfare)
>

Query optimiser bug?

Hi all,
I'm having a real problem with a simple query. The table in question has
about 2 million entries with a clustered index and 2 normal indicies.
This query:
select * from uk_streets where search_thoroughfare like 'agsts drv%'
works fine and fast but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets where search_thoroughfare like @.q
takes about 20 times longer to execute. I figured it's to do with the query
plan being used by the optimiser so I've added an index hint:
select * from uk_streets (index=uk_streets) where search_thoroughfare
like 'agsts drv%'
is still very fast and uses the index but...
declare @.q varchar(80)
set @.q='agsts drv%'
select * from uk_streets (index=uk_streets) where search_thoroughfare
like @.q
ignores the hint and still takes ages (using the clustered index instead).
I'm sure that I'm missing something but it's driving me bonkers! Can anyone
shed any light on the problem please?
Thanks in advance
Jamie.
PS: Here's the table and index structure:
CREATE TABLE UK_Streets
(
locality_key int NOT NULL ,
search_thoroughfare varchar (80) NOT NULL ,
outcode varchar (4) NOT NULL ,
sector char (1) NOT NULL ,
thoroughfare_key int NOT NULL,
thoroughfare_descriptor_key int NOT NULL
)
CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
search_thoroughfare)
CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
search_thoroughfare)
Try out with updating The table Statistics ,still if you get the Same
check out the fragmentation using Dbcc and if its bad rebuild the Index
and See
Thanks,
Saradhi
|||Hi Saradhi
Thanks for the message - I've run DBREINDEX and it's worked! Thank you so
much - I'd already rebuilt the stats which had no effect but that's
brilliant.
Thanks again!
Cheers
Jamie.
"saradhi" <saradhiY@.gmail.com> wrote in message
news:1124984188.908657.122330@.g43g2000cwa.googlegr oups.com...
> Try out with updating The table Statistics ,still if you get the Same
> check out the fragmentation using Dbcc and if its bad rebuild the Index
> and See
> Thanks,
> Saradhi
>
|||I am surprised that updating statistics made any difference at all..
The problem is around using variables in your where clause... When the query
is optimized, the optimizer can not see the value of the local variable, and
therefore is unable to use index stats to determine the best index. However
when the value is a literal , index stats can be used to determine the best
index, which can greatly improve performance.
This is not an issue with parameters which are passed in to a Stored proc
and used directly in a where clause... The optimizer CAN see and optimize
for this value - this feature is called Parameter sniffing.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jamie Turner" <jamieturner@.fernhillsolutions.net> wrote in message
news:%23bKy5lXqFHA.712@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I'm having a real problem with a simple query. The table in question has
> about 2 million entries with a clustered index and 2 normal indicies.
> This query:
> select * from uk_streets where search_thoroughfare like 'agsts drv%'
> works fine and fast but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets where search_thoroughfare like @.q
> takes about 20 times longer to execute. I figured it's to do with the
> query plan being used by the optimiser so I've added an index hint:
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like 'agsts drv%'
> is still very fast and uses the index but...
> declare @.q varchar(80)
> set @.q='agsts drv%'
> select * from uk_streets (index=uk_streets) where search_thoroughfare
> like @.q
> ignores the hint and still takes ages (using the clustered index instead).
> I'm sure that I'm missing something but it's driving me bonkers! Can
> anyone shed any light on the problem please?
> Thanks in advance
> Jamie.
>
> PS: Here's the table and index structure:
> CREATE TABLE UK_Streets
> (
> locality_key int NOT NULL ,
> search_thoroughfare varchar (80) NOT NULL ,
> outcode varchar (4) NOT NULL ,
> sector char (1) NOT NULL ,
> thoroughfare_key int NOT NULL,
> thoroughfare_descriptor_key int NOT NULL
> )
> CREATE CLUSTERED INDEX uk_streets_clustered ON UK_Streets(locality_key,
> search_thoroughfare)
> CREATE INDEX uk_streets ON UK_Streets(search_thoroughfare)
> CREATE INDEX uk_streets_outcode ON UK_Streets(outcode,
> search_thoroughfare)
>

Query not using index

Hi,

I have a table t1 with columns a,b,c,d,e,f,g,h,i,j,k,l

I have created a clustered index on a,b,d,e which forms the primary
key. I have created a covering index on all the columns of t1. There
are 1 million rows in this table.

My query chooses the TOP20 rows based on some filter conditions. When
I use an "ORDER BY 1", it uses the clustered index and I get the result
in 1 second, whereas it takes around 1minute 48seconds when I use an
"ORDER BY b or any other column". It is not using the covering / the
clustered index.

What is the best way to index this table so that it uses the index and
I get the result within the shortest possible time (just like that of
ORDER BY 1 which take hardly a second).

Thanks..

Sridhar(sridharg.rao@.gmail.com) writes:
> I have a table t1 with columns a,b,c,d,e,f,g,h,i,j,k,l
> I have created a clustered index on a,b,d,e which forms the primary
> key. I have created a covering index on all the columns of t1.

Which in practice is a second clustered index.

> My query chooses the TOP20 rows based on some filter conditions. When
> I use an "ORDER BY 1", it uses the clustered index and I get the result
> in 1 second,

Is that the number 1 or the column l as in "lily"?

> whereas it takes around 1minute 48seconds when I use an
> "ORDER BY b or any other column". It is not using the covering / the
> clustered index.
> What is the best way to index this table so that it uses the index and
> I get the result within the shortest possible time (just like that of
> ORDER BY 1 which take hardly a second).

You will need an index of which the first column is the column in the
ORDER BY clause.

If your queries on this table typically are "TOP 20 ORDER BY any column",
a non-clustered index on each column by a appears to be the best choice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> You will need an index of which the first column is the column in the
> ORDER BY clause.

Just to make sure that you understand why this is clear and why an
index on (a, b, c, d, e...) will not work...

If I ask you to find all of the people in a phone book who have a last
name that starts with "D" then you could easily do that. Now, how would
you find all of the people that have a last name where the fourth
letter is "d". You couldn't easily do that. Indexes work pretty much
the same way. If you want to find rows based on column "c" an index on
(a, b, c) isn't going to be nearly as useful as an index on just (c).

HTH,
-Tom.

Query not using clustered index

I am working with some software vendors with sql query performance on their application that we installed. On one of the tables there are four indexes, one is clustered. When the vendor restores a copy of our database and run a query against that table i
t uses the clustered index and completes in .02 seconds. When I run the same query on the same database it uses a different index than the clustered index and takes 1.7 seconds to run. Is there some way I can get this query to use the clustered index to
run faster? I have a maintinence plan that runs once a week to rebuild the indexes and update statistics using 50%. Any ideas would be appreciated.
When you say the same query on the same database are you talking the same
machine? If so what is different in the way you run the query than when he
does?
Andrew J. Kelly SQL MVP
"russgjones" <russgjones@.discussions.microsoft.com> wrote in message
news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> I am working with some software vendors with sql query performance on
their application that we installed. On one of the tables there are four
indexes, one is clustered. When the vendor restores a copy of our database
and run a query against that table it uses the clustered index and completes
in .02 seconds. When I run the same query on the same database it uses a
different index than the clustered index and takes 1.7 seconds to run. Is
there some way I can get this query to use the clustered index to run
faster? I have a maintinence plan that runs once a week to rebuild the
indexes and update statistics using 50%. Any ideas would be appreciated.
|||When you say the same query on the same database are you talking the same
machine? If so what is different in the way you run the query than when he
does?
Andrew J. Kelly SQL MVP
"russgjones" <russgjones@.discussions.microsoft.com> wrote in message
news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> I am working with some software vendors with sql query performance on
their application that we installed. On one of the tables there are four
indexes, one is clustered. When the vendor restores a copy of our database
and run a query against that table it uses the clustered index and completes
in .02 seconds. When I run the same query on the same database it uses a
different index than the clustered index and takes 1.7 seconds to run. Is
there some way I can get this query to use the clustered index to run
faster? I have a maintinence plan that runs once a week to rebuild the
indexes and update statistics using 50%. Any ideas would be appreciated.
|||It is on a different machine but the same database and indexes. We are both running the query in Query Analyzer.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>
|||It is on a differnet server, but it is the same database and indexes. We are both using query analyzer to run the query.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>
|||It is on a differnet server, but it is the same database and indexes. We are both using query analyzer to run the query.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>
|||Any time you restore a database you should run Update statistics to ensure
they are all set. I would run it the same way on both systems and then see
if the results are the same.
Andrew J. Kelly SQL MVP
"russgjones" <russgjones@.discussions.microsoft.com> wrote in message
news:242F4B00-1365-43C1-807D-882A72DC306E@.microsoft.com...
> It is on a differnet server, but it is the same database and indexes. We
are both using query analyzer to run the query.[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
same[vbcol=seagreen]
he[vbcol=seagreen]
four[vbcol=seagreen]
database[vbcol=seagreen]
completes[vbcol=seagreen]
a[vbcol=seagreen]
Is[vbcol=seagreen]
appreciated.[vbcol=seagreen]
|||It is on a different machine but the same database and indexes. We are both running the query in Query Analyzer.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>
|||It is on a differnet server, but it is the same database and indexes. We are both using query analyzer to run the query.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>
|||It is on a differnet server, but it is the same database and indexes. We are both using query analyzer to run the query.
"Andrew J. Kelly" wrote:

> When you say the same query on the same database are you talking the same
> machine? If so what is different in the way you run the query than when he
> does?
> --
> Andrew J. Kelly SQL MVP
>
> "russgjones" <russgjones@.discussions.microsoft.com> wrote in message
> news:5FF3899A-95F8-4F0E-A91F-F679FA1935F6@.microsoft.com...
> their application that we installed. On one of the tables there are four
> indexes, one is clustered. When the vendor restores a copy of our database
> and run a query against that table it uses the clustered index and completes
> in .02 seconds. When I run the same query on the same database it uses a
> different index than the clustered index and takes 1.7 seconds to run. Is
> there some way I can get this query to use the clustered index to run
> faster? I have a maintinence plan that runs once a week to rebuild the
> indexes and update statistics using 50%. Any ideas would be appreciated.
>
>