John Colby
jcolby at colbyconsulting.com
Fri Aug 15 08:30:11 CDT 2003
Martin's answer was to run showplan. I have done so.
aaaaqtestRushmorex is a query that does exactly the kind of append the list
member asked about -
Name: Lastname & ", " & FirstName - sorted on that field
SELECT tblClaimant.CLMT_LastName, tblClaimant.CLMT_FirstName,
[CLMT_LastName] & ", " & [CLMT_FirstName] AS Name
FROM tblClaimant
ORDER BY tblClaimant.CLMT_LastName DESC , [CLMT_LastName] & ", " &
[CLMT_FirstName] DESC;
aaaaqtestRushmore does the same thing but sorted on just the last name field
SELECT tblClaimant.CLMT_LastName, tblClaimant.CLMT_FirstName,
[CLMT_LastName] & ", " & [CLMT_FirstName] AS Name
FROM tblClaimant
ORDER BY tblClaimant.CLMT_LastName, tblClaimant.CLMT_FirstName DESC;
--- aaaaqtestRushmorex ---
- Inputs to Query -
Table 'tblClaimant'
Database 'X:\DISNEW\DISCO_BE.mdb'
- End inputs to Query -
01) Sort table 'tblClaimant'
--- aaaaqtestRushmore ---
- Inputs to Query -
Table 'tblClaimant'
Database 'X:\DISNEW\DISCO_BE.mdb'
Using index 'CLMT_LastName'
Having Indexes:
CLMT_LastName 13288 entries, 46 pages, 8145 values
which has 1 column, fixed
CLMT_FirstName 13288 entries, 44 pages, 2831 values
which has 1 column, fixed
- End inputs to Query -
01) Scan table 'tblClaimant'
Database 'X:\DISNEW\DISCO_BE.mdb'
Using index 'CLMT_LastName'
As you can see, the query sorted on an actual field says "Using index
'CLMT_LastName'"
The query sorted on the appended string field says only "01) Sort table
'tblClaimant'"
Interestingly (to me), these are identical queries, i.e. I pulled out the
first name and last name fields, then created the appended "Name" alias
field. Sorted on the Name field and saved as aaaaqtestRushmorex. Removed
that index and applied a sort to CLMT_LastName, saved that query as
aaaaqtestRushmore. Note that even though the query is identical except for
where the sort is applied, the one with the sort on the Name field (appended
field) never even mentions the "having indexes" information.
Even worse, notice that the original "x" query had an "order by" first on
the last name, then on the appended string. Jet still ignored the index on
the last name field. The presence of the order by on a field that could not
be optimized (the append) caused jet to "give up" on using any indexes.
Just to cover all bases, I went back in and removed that order by on the
last name so that the X query had ONLY an order by on the appended field.
The showplan text was identical, i.e. no usage of indexes.
John W. Colby
www.colbyconsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman
Sent: Friday, August 15, 2003 9:01 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields
John,
I never said that I believed it or hoped it to be true. I simply said I
don't know the answer. And I didn't say you were wrong either. I just
asked how you knew for sure that it was the case other then observation.
I'm glad Martin has the ability to pass this along (hopefully to Kevin)
and get an answer that is definite. Then we will know for sure.
Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Thursday, August 14, 2003 2:43 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields
Jim,
Since no one (I know) knows the internals of JET, I am not going to argue
with you on that account. In fact, I am not going to argue with you at all.
If you wish to believe (or hope, or pray) that Jet can magically parse a
bunch of stuff used to create an appended string back into it's components,
and then use that information to decide whether or not to attempt to apply
Rushmore to whatever pieces it can determine comes from table fields....
uh...
Can I state it as a fact. Of course not.
What I can say is that I have a very good class for timing stuff down to
milli-second increments. Anyone who wishes to may use that class to
empirically time such queries. I will be willing to bet good money, up to a
month's wages (I'm "unemployed" of course) against an equal number of
doughnuts that you will find, 100 times out of 100 times, that attempting to
apply a sort to such a field will, each and every time, be an order of
magnitude slower than using the component fields yourself to perform the
sort.
And of course, having done that, one still can't state as FACT that Jet
isn't attempting to use Rushmore to perform the sort on the appended string
field.
And yes, I have read it in various places over the years. I have long ago
lost the ability to recall where I read every piece of information I have
read.
So let's not start any myths here. I do not know for a fact that Jet
doesn't use Rushmore (or anything else) to attempt to optimize such queries.
I simply have the sense to look at what would be required, the silliness of
the concept, and the results I have seen in my personal experience, and
state that I know, without a shadow of a doubt, that Access makes no attempt
to apply such methods to a sort of an appended string.
John W. Colby
www.colbyconsulting.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman
Sent: Thursday, August 14, 2003 1:12 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields
John,
<<Nope, I cannot give you and sources and will not spend my time looking.
Go
look in any book on Access queries, sorting and indexing and I'm sure you'll
find your own.>>
That's why I'm asking. I have pretty much read everything there is to
read about JET over the past 10 years, and I've never seen anything on this.
<<[Fielda] & Random() & [Fieldb] & ascii(right$("John Colby",1))
How in the world is any index on FieldA and FieldB going to help you sort
that expression into logical order?>>
Rushmore works by making merges/intersections between indexes. JET
queries as we all know are very aware of the underlying tables. This has
been a key feature of Access/JET since day 1. JET may be smart enough to
figure out that the logical order is based on two fields it already has
indexes for. Then again it may not be.
<<How in the world is Access going to determine whether to even try?>>
It knows the field names and what tables they come from.
<<The resulting string expression can be ANYTHING!!!>>
Yes, but that wasn't the example Mark gave.
<<Access does NOT use the indexes on the fields used to build up an appended
string. If you apply a sort order to such a string, Access will do the sort
on the resulting string, on-the-fly, and it will be PAINFULLY slow!!!>>
That seems to be the case, but do you really know for sure? I don't
believe you do. I'm not saying your wrong, but I don't think you can state
it as a fact, unless you have seen docs on JET's internal workings. That's
why I asked you to cite a source.
It's just like the myth that got started about domain functions always
being the slowest of methods. This is not the case as in some situations, a
domain function can actually beat every other method. That floated around
for several years until someone sat down and tested it and found it not to
be true.
Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Colby
Sent: Thursday, August 14, 2003 10:09 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Indexes on text fields
<<snip>>
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com