Query Performance

  • Thread starter Thread starter alex
  • Start date Start date
A

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
 
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”
Click to expand...

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.
Click to expand...

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?
Click to expand...

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).
 
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 -
Click to expand...

John,
Yes, I meant like "*".
Thanks for the response.
 
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
 
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
Click to expand...

Thanks John, I appreciate the comment.
 
Back
Top