query performance

4 Feb 2006 8:23 PM

Jim Abel

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 @Auto

4 Feb 2006 2:41 PM

Rajesh

Just check with this

Initialize the parameter to NULL

SET @Auto = 0

Detail.Automated = ISNULL(@Auto ,Detail.Automated)

NOTE : Constraint Detail.Automated is not null

Detail.Automated = ISNULL(@Auto ,Detail.Automated) and Detail.Automated IS
NULL

NOTE : Constraint Detail.Automated can have NULL values.

and Also check whether any index on the search arguments in the where caluse.

HTH

Rajesh Peddireddy

Show quoteHide quote

"Jim Abel" wrote:

> 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 @Auto

4 Feb 2006 6:21 PM

Rajesh

check also indexes on the join fields

HTH
Rajesh Peddireddy

Show quoteHide quote

"Rajesh" wrote:

> Just check with this
>
> Initialize the parameter to NULL
>
> SET @Auto = 0
>
> Detail.Automated = ISNULL(@Auto ,Detail.Automated)
>
> NOTE : Constraint Detail.Automated is not null
>
> Detail.Automated = ISNULL(@Auto ,Detail.Automated) and Detail.Automated IS
> NULL
>
> NOTE : Constraint Detail.Automated can have NULL values.
>
> and Also check whether any index on the search arguments in the where caluse.
>
> HTH
>
> Rajesh Peddireddy
>
> "Jim Abel" wrote:
>
> > 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 @Auto

5 Feb 2006 12:11 PM

Erland Sommarskog

Jim Abel (JimA***@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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thread options