[AccessD] Indexes on text fields

Someone send the origonal question and I will pass it on to someone whoh
knows.

Martin


----- Original Message ----- 
From: "John Colby" <jcolby at colbyconsulting.com>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Thursday, August 14, 2003 11:43 AM
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
>