[AccessD] Speed Problems when looping through records or the terrors of deduping

Gustav Brock Gustav at cactus.dk
Mon Sep 15 09:56:30 CDT 2008


Hi Kevin and Mark

This reminds me of the array speed test we did in Oct. 2005 (look up subject: "Array faster in DLL?") where I found out that moving a trivial routine like this to an external dll made with a simple tool like FreeBasic, speed could be increased by a factor more than 10. 

However, Shamil made the same in C++ and this was _way_ faster than both the VBA and the Basic implementations:

<quote>
.. speed improvement compared to VBA:

  VBA: 1:1
  PowerBasic dll: 10:1
  FreeBASIC dll: 15.5:1
  C++ dll: 170:1
</quote>


/gustav

>>> markamatte at hotmail.com 15-09-2008 16:04 >>>


I ran a small test with about 2700 records...I called the function from a query and compared all names to my name...( JaroDistance([bo_name],"Mark Matte"))...for testing...this took out the cartersian join, or any join that may slow this down....and againts 2700 records it took 7-8 seconds.  

I believe Rocky hit on it earlier...it does not appear that your time is lost in getting the records, or joining them...its running them through the function.  

Your test:  18000 records= 45 seconds per
My test:  2700 records= 7 second per
Rationalization: 7*2700records=18900...7*7seconds=49

...its the same amount of time...bottom line is it doesn't matter, at this point, how you pull the records together to compare them...it just takes that long to compare.

If the speed is to be improved, I agree the 2 best approaches would be 1...work on the speed of the function...not the recordset/query.  2...call the function less times.

Good Luck,

Mark A. Matte






More information about the AccessD mailing list