Query performance

Hi,
I have some questions regarding query performances:
Here is a commented set of queries against a Table called ADDRESS.
I am using a daft sub query to highlight a point.
Any help much appreciated!
Tris
sp_helpindex Address
/*
index_name index_description
index_keys
----
--
ADDRESS.COUNTY nonclustered located on PRIMARY
County
ADDRESS.POSTCODE nonclustered located on PRIMARY
PostCode
ADDRESS.TOWN nonclustered located on PRIMARY
Town
*/
-- Using no Variable, executes in 0 seconds
select * from address where postcode in
(Select postCode from Address where postcode like 'KT22 7E%')
-- Using a variable ruins index usage and executes in about 15 seconds.
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address where (postcode in
(Select postCode from Address where postcode like @.PC))
-- Adding an index hint goes back to 0 seconds
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
in
(Select postCode from Address where postcode like @.PC))
-- Adding an OR ruins it completely running at about 1 minute!
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
in
(Select postCode from Address where postcode like @.PC) or @.PC = '')
-- WHY?Hi
Check out statistics on the column. Create a SP which accepts this value as
a parameter and see what is going on.
<Tris.Phillips@.gmail.com> wrote in message
news:1139834636.524975.39380@.g14g2000cwa.googlegroups.com...
> Hi,
> I have some questions regarding query performances:
> Here is a commented set of queries against a Table called ADDRESS.
> I am using a daft sub query to highlight a point.
> Any help much appreciated!
> Tris
>
> sp_helpindex Address
> /*
> index_name index_description
> index_keys
>
> ----
--
> ADDRESS.COUNTY nonclustered located on PRIMARY
> County
> ADDRESS.POSTCODE nonclustered located on PRIMARY
> PostCode
> ADDRESS.TOWN nonclustered located on PRIMARY
> Town
> */
> -- Using no Variable, executes in 0 seconds
> select * from address where postcode in
> (Select postCode from Address where postcode like 'KT22 7E%')
> -- Using a variable ruins index usage and executes in about 15 seconds.
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address where (postcode in
> (Select postCode from Address where postcode like @.PC))
> -- Adding an index hint goes back to 0 seconds
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
> in
> (Select postCode from Address where postcode like @.PC))
> -- Adding an OR ruins it completely running at about 1 minute!
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
> in
> (Select postCode from Address where postcode like @.PC) or @.PC = '')
>
> -- WHY?
>|||Im sorry, Im not sure what you mean?
The crux of the question is : why does adding the OR make the query so
slow?
Thanks.
Tris|||Take a look at all execution plans and compare them
Try to avoid using '*' in your queries it may hurt your performance
<Tris.Phillips@.gmail.com> wrote in message
news:1139835301.074781.95290@.g44g2000cwa.googlegroups.com...
> Im sorry, Im not sure what you mean?
> The crux of the question is : why does adding the OR make the query so
> slow?
> Thanks.
> Tris
>|||It would appear that replacing the OR with an AND and reversing the
logic fixes the issue.
-- Changing an OR to an AND (and reversing logic) fixes it!
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address with (index = [ADDRESS.POSTCODE]) where ((@.PC is
not null) AND (postcode in
(Select postCode from Address where postcode like @.PC)))
VERY STRANGE?|||The two where clauses (and vs. or) are not equal.
The first one (using or) is selecting all record in the list of postcodes,
PLUS all records where the postcode variable is an empty string.
The second (using and) is selecting all records in the list of postcodes,
ONLY if the postcode variable is not null.
where
postcode in (Select postcode from Address where postcode like @.PC)
or @.PC = ''
where
@.PC is not null
AND postcode in (Select postcode from Address where postcode like @.PC)
@.PC = ''
and
@.PC is not null
are completely different comparisons. They are neither similar nor
opposite, so you really can't compare how one behaves vs. the other.
Also, in each of those comparisons you are comparing a variable to a value,
not a field to a value. It will not behave the same way if you substitute
an actual field in place of the variable, so your tests are probably not
testing what you want.
Regarding your original question, you should expect OR to slow down queries
as it requires the query engine to look at more rows in order to make a
decision. Granted, OR will not always slow things down, but used the wrong
way it can bring things to a grinding halt.
In the case of your query, I would guess that SQL server is using an index
without the OR and retrieving just the rows where postal code is a match.
Once you add the OR it is checking every single row, since the index is no
longer a valid way of determining which rows meet the criteria. The in
clause allows the index to be used, but the [or @.PC = ''] is not even going
against your table, so the index cannot be used against it. Basically, the
optimizer is ignoring the index because it would need to verify every row
against the [or @.PC = ''], so attempting to limit the rows returned would be
pointless.
Logically, we know that [or @.PC = ''] = [or 'KT22 7E%'=''] = [false] and
should be removed, but SQL server seems to be looking more at the [OR ...]
than the fact that it evaluates to false every time.
If you use OR and both sides of the OR are using columns from the same index
then I would expect SQL Server to still process it efficiently. If you use
OR and both sides of the OR are going against the same table, but different
indexes, then I would not expect indexes to be used, unless there was
another criteria (join perhaps) which was not affected by your OR. If,
however, you use OR against two different tables, you can expect SQL Server
to return every single row regardless of whether it matches your criteria,
and only perform the final filter at the end, and your performance will be
very inefficient.
Try checking out some of these sites for more information on performance
tuning...
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
http://www.sql-server-performance.com/
http://www.sql-server-performance.com/q&a132.asp
<Tris.Phillips@.gmail.com> wrote in message
news:1139841641.324707.140870@.f14g2000cwb.googlegroups.com...
> It would appear that replacing the OR with an AND and reversing the
> logic fixes the issue.
> -- Changing an OR to an AND (and reversing logic) fixes it!
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address with (index = [ADDRESS.POSTCODE]) where ((@.PC is
> not null) AND (postcode in
> (Select postCode from Address where postcode like @.PC)))
> VERY STRANGE?
>