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)
>