Re: Record source at runtime

"Arvin Meyer [MVP]" <a@xxxxx> wrote in
news:#F08lePDIHA.5856@xxxxxxxxxxxxxxxxxxxx:

If you have defined a primary key, always include that in your
query. to speed things up other indices are often added like last
name, first name, etc. When using those indexes, always use them
in the order they are created (check the table index list) Like:

Select PersonID, LastName, FirstName From tblPeople Where LastName
= "Meyer" And FirstName = "Arvin";

Notice, I've used the PK and the index in the order I created
them. That's the way most (and for sure both Access and
SQL-Server) use an index to look for data.

In the case of Access, It see's that the PK isn't in the Where
Clause, but is in the data requested, so it will return the PKs
from those rows that use "Meyer", then it will see that it needs
to go back and return the data requested in the 3 fields from
those rows.

The wording of your two answers puzzles me. You seem to me to be
saying that including the PK in the SELECT clause improves
performance somehow. I can't see how.

Jet uses clustered indexes for the primary key, and that means that
the table's data is written in PK order after a compact. That means
that the data pages have to retrieved via the PK no matter what you
select on. That is, you have to go through the PK no matter *what*
criteria you use, since the data pages are stored according to the
PK.

So, what would happen is this (oversimplied for clarity):

1. retrieve indexes for Lastname and Firstname.

2. those indexes will point to the PKs of the records that match.

3. request the data pages for those PKs.

It may be that the PK index has to be retrieved, but I doubt it. I'd
think it would be retrieved only when there are criteria on the PK.
It depends on whether the non-primary indexes store pointers to the
PK values or pointers to the data pages. I'm not sure what actually
happens. If it's the former, then the PK index would be required to
find the actual data pages.

On the other hand, since the PK index is clustered, it may be that
the pages function as the index (using an offset to find the PK
values). I'm not sure about this.

But I'm pretty certain that the SELECT clause comes into effect only
after the minimal number of indexes have been used to decide which
data pages need to be retrieved (or, which PK values will be used to
decide which data pages to retrieve).

SQL-Server does something similar, except since it's running on
the engine, it doesn't need to return the index first, it
processes that on the server and returns the data.

In both cases, had I written the query:

Select PersonID, FirstName, LastName From tblPeople Where
FirstName = "Arvin" And LastName = "Meyer";

It would still look for the index on LastName before it did on
FirstName, taken several milliseconds before it moved to the next
index. In the above case, it wouldn't be noticeable, but it sure
would if you were looking for John Smith in a couple of hundred
thousand records.

Are you sure about that? I'd think that the query optimizer would
fix that for you. That is, it's likely to use the least sparse index
first.

Have you checked whether SHOWPLAN gives you any indication on
exactly what happens in regard to the order of filtering by indexes
in the same table?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.

Relevant Pages

  • Re: Search Firstname field AND Lastname field?
    ... last name field in my query? ... The WHERE clause is a Boolean logical expression; ... be true in order to retrieve a record, or use the OR operator to ...
    (microsoft.public.access.gettingstarted)
  • Re: Sort Problem when Using Make-Table Query
    ... The only sure way to retrieve the Records in a certain order is to use the ... the safest way is to use the ORDER BY clause. ... "Make-Table Query" in ascending order on a Number field. ... As a work around I ran a query to sort the table. ...
    (microsoft.public.access.queries)
  • Re: ORACLE 8 sql query
    ... I have following query ... my question in the IN Clause above ROLE.role_abbr IN ... I need to retrieve values like ... Puget Sound Oracle Users Group ...
    (comp.databases.oracle.misc)
  • ORACLE 8 sql query
    ... I have following query ... my question in the IN Clause above ROLE.role_abbr IN ... I need to retrieve values like ...
    (comp.databases.oracle.misc)
  • Re: On ADSI and LDAP
    ... the problem is how can I retrieve the value for myuser using the ... would be more efficient to use ADO to query AD for the attributes values. ... For more on using ADO, ... Dim adoCommand, adoConnection, strBase, strFilter, strAttributes ...
    (microsoft.public.scripting.vbscript)
Quantcast