Tuesday, March 20, 2012

query performance

The folling query works but is it the most efficient way to write it from a
performance aspect?
The parameter can be set with the values (0,1 and %) these can be changed if
needed for the final query version.
The Detail.Automated database field is a datatype of bit
Should I be writing this to avoid the LIKE keyword?
-- only used for testing
DECLARE @.Auto AS char (1)
SET @.Auto = '%'
-- end test
SELECT DISTINCT Status.ID,
Info.ID,
Info.Text,
Detail.Automated,
Status.Status
FROM Status INNER JOIN Info
ON Status.ID = Info.ID
INNER JOIN Detail
ON Info.ID = Detail.ID
WHERE (Status.ID = 4)
AND (Detail.Automated LIKE @.AutoJim Abel (JimAbel@.discussions.microsoft.com) writes:

> The folling query works but is it the most efficient way to write it
> from a performance aspect? The parameter can be set with the values (0,1
> and %) these can be changed if needed for the final query version.
> The Detail.Automated database field is a datatype of bit
> Should I be writing this to avoid the LIKE keyword?
Using LIKE with bit looks quite strange. I would rather write:
AND (Detail.Automated = @.Auto OR @.Auto IS NULL)
And of course @.Auto would be declared as bit.
Whether the query you have is the best from the point of view of
performance is impossible to say, as this requires knowledge about
the tables, indexes, and the amount and distribution of the data in
the tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

No comments:

Post a Comment