Performance penalty for LIKE when I really mean Equal?

19 Aug 2005 4:14 PM

BBM

Hi,

I have a situation where I can retrieve either all the rows in a table or
just some of them based on a "type" field in the row. The type of the rows
to select is passed as a parm to the method that retrieves the rows. If the
calling program wants the whole table it passes String.Empty as the type.

I was thinking of having one parameterized SQL statement like this...

SELECT * FROM T1 WHERE T1.TYPE LIKE @Type

I'll append '%' to whatever "type" is passed the method. So if the calling
routine wants all records, LIKE '%' should return all rows. If the User
passes 'OT' (assuming a two character type code) I should still get all the
'OT' rows from the passed parm of
'OT%'.

Will I suffer a performance penalty by doing this, or is the query optimizer
smart enough to figure out that for a two character field, LIKE 'OT%' is the
equivalent of = 'OT'?

Thanks.

BBM

19 Aug 2005 4:34 PM

ML

For prefix criteria (i.e. where the wildcard character is not used at the
beginning of the search argument), the query optimizer can take advantage of
indexes if the column used in the LIKE expression is indexed.

ML

19 Aug 2005 7:10 PM

JT

If @Type is NULL, then the following will return all rows without evaluating
the like comparison:

SELECT * FROM T1 WHERE (@Type Is Null) or (T1.TYPE LIKE @Type)

Show quote

"BBM" <b**@bbmcompany.com> wrote in message
news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@microsoft.com...
> Hi,
>
> I have a situation where I can retrieve either all the rows in a table or
> just some of them based on a "type" field in the row. The type of the
> rows
> to select is passed as a parm to the method that retrieves the rows. If
> the
> calling program wants the whole table it passes String.Empty as the type.
>
> I was thinking of having one parameterized SQL statement like this...
>
> SELECT * FROM T1 WHERE T1.TYPE LIKE @Type
>
> I'll append '%' to whatever "type" is passed the method. So if the
> calling
> routine wants all records, LIKE '%' should return all rows. If the User
> passes 'OT' (assuming a two character type code) I should still get all
> the
> 'OT' rows from the passed parm of
> 'OT%'.
>
> Will I suffer a performance penalty by doing this, or is the query
> optimizer
> smart enough to figure out that for a two character field, LIKE 'OT%' is
> the
> equivalent of = 'OT'?
>
> Thanks.
>
> BBM

23 Aug 2005 2:58 PM

BBM

Thanks for the tip. I'll try this.

Show quote

"JT" wrote:

> If @Type is NULL, then the following will return all rows without evaluating
> the like comparison:
>
> SELECT * FROM T1 WHERE (@Type Is Null) or (T1.TYPE LIKE @Type)
>
>
>
> "BBM" <b**@bbmcompany.com> wrote in message
> news:E8437BCA-E0D4-4D1D-9A98-5F62A95938BE@microsoft.com...
> > Hi,
> >
> > I have a situation where I can retrieve either all the rows in a table or
> > just some of them based on a "type" field in the row. The type of the
> > rows
> > to select is passed as a parm to the method that retrieves the rows. If
> > the
> > calling program wants the whole table it passes String.Empty as the type.
> >
> > I was thinking of having one parameterized SQL statement like this...
> >
> > SELECT * FROM T1 WHERE T1.TYPE LIKE @Type
> >
> > I'll append '%' to whatever "type" is passed the method. So if the
> > calling
> > routine wants all records, LIKE '%' should return all rows. If the User
> > passes 'OT' (assuming a two character type code) I should still get all
> > the
> > 'OT' rows from the passed parm of
> > 'OT%'.
> >
> > Will I suffer a performance penalty by doing this, or is the query
> > optimizer
> > smart enough to figure out that for a two character field, LIKE 'OT%' is
> > the
> > equivalent of = 'OT'?
> >
> > Thanks.
> >
> > BBM
>
>
>