Query Performance

[alex]

Query Performance

Hello,

Using Access ’03…

For those who know/understand how queries are processed…

Is there a performance difference between these two queries?

E.g.,

1.
Select *
From myTable
Where
FirstName = “*”
And
LastName = “Doe”

2.
Select *
From myTable
Where
LastName = “Doe”

I’m building a query in code and am wondering if I should keep the
asterisks or remove them when they stand alone like example 1.

Does it matter if I’m using Jet, Sql Server, or ORACLE with an Access
fe?

Thanks,
alex

 

[John W. Vinson]

Query Performance

Hello,

Using Access ’03…

For those who know/understand how queries are processed…

Is there a performance difference between these two queries?

E.g.,

1.
Select *
From myTable
Where
FirstName = “*”
And
LastName = “Doe”

This will of course return only those records for which the FirstName field is
a single literal asterisk character; did you perhaps mean

FirstName LIKE "*"

instead?

2.
Select *
From myTable
Where
LastName = “Doe”

I’m building a query in code and am wondering if I should keep the
asterisks or remove them when they stand alone like example 1.

It's always going to be slower to search two fields rather than one. Depending
on the indexes and the optimizer's plan, it may be a trivial difference; but
if you don't need to search a field just leave it out of your query.

Does it matter if I’m using Jet, Sql Server, or ORACLE with an Access
fe?

Yes and no of course... <g> Different backends will have different
optimizations, different execution plans and different results. Again, the
differences may be trivial (they would in this very simple example).

 

[alex]

This will of course return only those records for which the FirstName field is
a single literal asterisk character; did you perhaps mean

FirstName LIKE "*"

instead?



It's always going to be slower to search two fields rather than one. Depending
on the indexes and the optimizer's plan, it may be a trivial difference; but
if you don't need to search a field just leave it out of your query.


Yes and no of course... <g> Different backends will have different
optimizations, different execution plans and different results. Again, the
differences may be trivial (they would in this very simple example).
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

John,
Yes, I meant like "*".
Thanks for the response.

 

[John Spencer]

One other difference between the two queries, is that the first will not
return any records where FirstName is a null while the second will return
nulls in the FirstName field.

FirstName is Null and LastName is "Doe"
First query will not return the record.
Second query will return the record.

As John noted the first query will probably be slower since you are searching
two fields instead of just one field. The difference in speed will probably
be imperceptible to humans - especially if the fields are indexed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[alex]

One other difference between the two queries, is that the first will not
return any records where FirstName is a null while the second will return
nulls in the FirstName field.

FirstName is Null and LastName is "Doe"
First query will not return the record.
Second query will return the record.

As John noted the first query will probably be slower since you are searching
two fields instead of just one field.  The difference in speed will probably
be imperceptible to humans - especially if the fields are indexed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Thanks John, I appreciate the comment.