Prev: Simple Append Query not Working. I do not have a clue why.
Next: Insufficient Memory after XP memory upgrade.
From: Luting on 23 Jun 2008 15:42
Hello everyone,

I wonder is there any pre defined sequence in Access when it executes
queries and views.
For example, is there any difference in efficiency of the following
two queries?
1. SELECT * FROM table1, table2
WHERE table1.date=table2.date
AND table1.name like "%A%"


2. SELECT * FROM table1, table2
WHERE table1.name like "%A%"
AND table1.date=table2.date


table1 is a huge table with millions of records. So if Access can
filter it with "table1.name like "%A%"" first, the join part will be
greatly speeded up.

If both the queries are the same to Access, is there any other ways I
can tell Access to execute using one plan instead of another?
From: Bob Barrows [MVP] on 23 Jun 2008 15:56
Luting wrote:
> Hello everyone,
>
> I wonder is there any pre defined sequence in Access when it executes
> queries and views.
> For example, is there any difference in efficiency of the following
> two queries?
> 1. SELECT * FROM table1, table2
> WHERE table1.date=table2.date
> AND table1.name like "%A%"
>
>
> 2. SELECT * FROM table1, table2
> WHERE table1.name like "%A%"
> AND table1.date=table2.date

Do you really have fields named "name" and "date"?? Bad mistake, if so!
Do not used reserved keywords for table and field names - using "date" as a
field name can especially have serious consequences.

See here for a list of reserved keywords
http://www.aspfaq.com/show.asp?id=2080

>
>
> table1 is a huge table with millions of records. So if Access can
> filter it with "table1.name like "%A%"" first, the join part will be
> greatly speeded up.
>
> If both the queries are the same to Access, is there any other ways I
> can tell Access to execute using one plan instead of another?

No, they are the same query. I suspect you will need to filter the records
yourself in a subquery or saved query before the join. Jet (in fact most
database engines) assembles the data source first, from the sources listed
in the FROM clause, before applying any filter criteria.
So, you can either create a saved query, call it FilteredTable1 with the sql

select * from table2 where [name] like "%A%"

Then join table1 to the saved query (I prefer not to use the obsolete join
syntax you are using - and hopefully you plan to specify the fields to be
returned by the query instead of using selstar):

SELECT * FROM FilteredTable1 As q join table2
ON q.date=table2.date


Or you can use a subquery:
:
SELECT * FROM
(select * from table2 where [name] like "%A%") As q
join table2 ON q.date=table2.date



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


From: Luting on 23 Jun 2008 16:12
> Do you really have fields named "name" and "date"?? Bad mistake, if so!
> Do not used reserved keywords for table and field names - using "date" as a
> field name can especially have serious consequences.
>
> See here for a list of reserved keywordshttp://www.aspfaq.com/show.asp?id=2080

Hi Bob,

No, "name" and "date" are not the real names. I just tried to use
simple names in the example.
But thank you for your alert. I didn't know they were bad names.

And thank you very much for the answer.
From: Klatuu on 23 Jun 2008 16:28
As to efficiencies, either way you write it will be fine. After you have
created a query and when you save it, Jet uses a query optimizing system
known as Rushmore. It analyses the query requirements and determines the
most efficient way to retrieve the requested records.
--
Dave Hargis, Microsoft Access MVP


"Luting" wrote:

> > Do you really have fields named "name" and "date"?? Bad mistake, if so!
> > Do not used reserved keywords for table and field names - using "date" as a
> > field name can especially have serious consequences.
> >
> > See here for a list of reserved keywordshttp://www.aspfaq.com/show.asp?id=2080
>
> Hi Bob,
>
> No, "name" and "date" are not the real names. I just tried to use
> simple names in the example.
> But thank you for your alert. I didn't know they were bad names.
>
> And thank you very much for the answer.
>
From: Bob Barrows [MVP] on 23 Jun 2008 16:42
Does that include doing filtration before assembling the data source? That's
contrary to the way I was taught most database engines worked. I would be
very interested in seeing this confirmed. Unfortunately, I don't have time
to test it for myself.

Luting, can you test both methods and let us know if there is a difference?
I'd be happy to be proven wrong on this.
Oh! And there is a registry setting to allow you to see the execution plan
that Jet will use for your query. Looking at the plans should tell us more
than timing results will.

http://articles.techrepublic.com.com/5100-10878_11-5064388.html


Klatuu wrote:
> As to efficiencies, either way you write it will be fine. After you
> have created a query and when you save it, Jet uses a query
> optimizing system
> known as Rushmore. It analyses the query requirements and determines
> the
> most efficient way to retrieve the requested records.
>
>>> Do you really have fields named "name" and "date"?? Bad mistake, if
>>> so!
>>> Do not used reserved keywords for table and field names - using
>>> "date" as a field name can especially have serious consequences.
>>>
>>> See here for a list of reserved
>>> keywordshttp://www.aspfaq.com/show.asp?id=2080
>>
>> Hi Bob,
>>
>> No, "name" and "date" are not the real names. I just tried to use
>> simple names in the example.
>> But thank you for your alert. I didn't know they were bad names.
>>
>> And thank you very much for the answer.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 |  Next  |  Last
Pages: 1 2
Prev: Simple Append Query not Working. I do not have a clue why.
Next: Insufficient Memory after XP memory upgrade.