[AccessD] Number vs text data type

Stuart,

You commented...  

"You are carrying out a single indexed lookup on an open table plus
a calculation using 5 functions (addition, Abs() CLng(), negation, 
NoMatch)

Which do *you*  think takes longer (ie has most effect on your 
results)?"


Why do *you* think that I originally said (complete with all the typos)...

"There is also a fair bit or arithmetic going on between each search.
 
 I goes without say (so why am I saying it?) that whne yo take out the
 indexes on the fields the search times go though the roof (for the Long
 field as well as the string one)."

In fact without the indexes on the fields the search proceeds with miserable
slowness as the entire table has to be scanned one record at a time to
locate (or not) a matching record. 

So in answer to your question, the point is not which of the various math,
string manipulation, Boolean operations and index look-ups take the longest,
or what impact the 1/18th of a second resolution of Timer has on the result.
Rather the point is that we do all of these data manipulations every time we
need to look up some data in any database. You cannot make the omelet
without breaking an egg.

My simple demo code looks up data in half a million records and it plainly
demonstrates that with an index it's quite fast, and without an index it's
slow as a cow swimming in a treacle-filled pool. So where does that make the
results meaningless? Now if you want to argue about whether .findfirst takes
0.05 seconds, or 0.04 or 0.0001 seconds then of course my code is not going
to answer the question. It was about index versus non-indexed searches,
that's all.

Lambert

> -----Original Message-----
> From:	Stuart McLachlan [SMTP:stuart at lexacorp.com.pg]
> Sent:	Wednesday, October 15, 2003 6:16 PM
> To:	Access Developers discussion and problem solving
> Subject:	RE: [AccessD] Number vs text data type
> 
> On 15 Oct 2003 at 14:46, Heenan, Lambert wrote:
> >             NumericSearchStart = Timer
> >             .FindFirst sCriteria
> >             intHits = intHits + Abs(CLng(Not .NoMatch))
> >             NumericSearchEnd = Timer
> ...
> >             TextSearchStart = Timer
> >             .FindFirst sCriteria
> >             strHits = strHits + Abs(CLng(Not .NoMatch))
> >             TextSearchEnd = Timer
> ....
> > 
> > And here are the results of a few runs, last to first...
> > 
> Average find times
> Numbers:0.0020859370 Hits:0
> Strings:0.0025468750 Hits:0
> 
> The results are meaningless :-(
> Timer is only accurate to about 1/18th of a second ie to .0556 
> seconds. Any test which takes less than that will either return 0 or 
> .0556.
> 
> You are carrying out a single indexed lookup on an open table plus
> a calculation using 5 functions (addition, Abs() CLng(), negation, 
> NoMatch)
> 
> Which do *you*  think takes longer (ie has most effect on your 
> results)?
> 
>  
> -- 
> Lexacorp Ltd
> http://www.lexacorp.com.pg
> Information Technology Consultancy, Software Development,System 
> Support.
> 
> 
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com