Arthur Fuller
artful at rogers.com
Sun Sep 14 17:33:18 CDT 2003
Where False by definition cannot take advantage of any indexes. Your
approach which directs the focus to the PKs in question is vastly superior.
This should not be surprising. I think that the PK tests will scale to
millions of rows.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Henry Simpson
Sent: Sunday, September 14, 2003 11:42 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Where False, was listbox with rowsource type
I had a look at Gustav's suggestion for filling a listbox with the field
names from three different tables to test a few of the assumptions and to do
some timing tests.
I chose 3 linked tables, all located on a local drive
tbl1: 33 fields and 1640 records
tbl2: 7 fields and 451 records
tbl3: 5 fields and 15 records
I created a cross product query:
Select tbl1.*, tbl2.*, tbl3.*
When opened in datasheet view, it appeared in under a second. When I hit
the new record button, it took over a minute for the datasheet to display
the new record line and the row count showed 11,094,600 records which equals
1640 * 451 * 15 as expected.
I then modified the query to add the 'Where False' condition. When I opened
the query in datasheet view, the wait was 37 seconds. I then timed the no
condition cross product and the time was 87 seconds.
The next modification was to add a condtion that the autonumber ID of tbl1
(the largest) be equal to -1. I had to create a test module that used
timeGetTime to determine that this took less than 10 milliseconds.
Another useful modification was to join the tables on similar type fields
with different data on indexed fields. For example, joining the LastName
field of tbl1 to the Address field of tbl2 to the ZipCode field of tbl3 also
returned no records in under 10 milliseconds. I quit testing at this point
but it was my assumption that setting an impossible condition on a known
invalid numeric key on the table with the smallest number of rows would
result in the quickest return of no records.
Bottom line, 'Where False' seems not to take advantage of available indexes
and seems to require some kind of join processing otherwise there is no way
three quite tiny local tables would require 37 seconds to open a querydef.
Even the full cross product query opened in under a second though without
showing the (irrelevant) record count.
Getting back to the callback vs saved querydef fieldlist approaches. I
created two forms. Each had a single list and no other controls. The first
used a fast querydef (pk of the largest table = -1) and the second used a
callback function. I then wrote a test module as follows:
Public Declare Function timeGetTime Lib "Winmm" () As Long
Sub loadtimes()
Dim lngt As Long
lngt = timeGetTime
DoCmd.OpenForm ("QueryDefFieldList")
MsgBox timeGetTime - lngt
lngt = timeGetTime
DoCmd.OpenForm ("CallBack")
MsgBox timeGetTime - lngt
End Sub
The callback function and list fill procedure using a module level string
array was in the code behind the CallBack version of the form as shown
below:
Dim strVelden() As String
Sub setData()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim Ar
Dim lngI As Long
Dim lngJ As Long
Dim ArStr() As String
Ar = Array("tb1", "tbl2", "tbl3")
Set db = CurrentDb
For lngI = 0 To UBound(Ar)
Set tdf = db(Ar(lngI))
ReDim Preserve strVelden(lngJ + tdf.Fields.Count - 1)
For Each fld In tdf.Fields
strVelden(lngJ) = fld.Name
lngJ = lngJ + 1
Next
Next
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub
Private Function ListMeetVelden(fld As Control, ID As Variant, row As
Variant, col As Variant, code As Variant) As Variant
Dim varRetVal As Variant
Select Case code
Case acLBInitialize
setData
varRetVal = 1
Case acLBOpen
varRetVal = Timer 'Unique ID for control
Case acLBGetRowCount
varRetVal = UBound(strVelden) + 1
Case acLBGetColumnCount
varRetVal = 1
Case acLBGetColumnWidth
varRetVal = -1 'Default of -1 uses default Column Width
Case acLBGetValue
varRetVal = strVelden(row)
Case acLBEnd
End Select
ListMeetVelden = varRetVal
End Function
I then ran the test procedure a few times and it showed that it made little
or no difference whether the saved querydef had conditions on it or not, it
only pulled the field names from the querydef (which was created as a local
querydef in the FE). I had expected that the fieldlist method would be
faster than the callback but on initial load, the callback consistently
loaded twice as fast as the fieldlist. Reloading, the callback remained
twice as fast, ranging from 1.2 times to 4.7 times as fast. Times for both
methods ranged from 12 to 90 milliseconds and the slowest load I have for an
initial load using a field list was 150 milliseconds.
A few closing notes:
The test code I wrote does not have proper error handling but does allow the
error on the list filler to return the specific table name on which an error
occurs.
The test code does not take into account array dimensions should a tabledef
have no fields but this is not production code and this can be readily
accomodated.
Using an array allows your code to sort field names and it allows you to
concatenate the table name as a prefix when the array is written. To do
this with a field list you would have to specify all the names in order in
the querydef and it could not automatically take account of revisions in the
table design.
If you write a proper error handler and you use an invalid table name, or
your table names change, the resulting error using a querydef field list is
an Access UI error whereas the callback version error is easily trapped,
logged and handled.
Using a saved querydef is another object in the database window that adds to
the number of objects that need to be refreshed when currentdb is called
explictly (or implicitly by Access).
Using a saved querydef adds another object that someone maintaining the
database down the road is going to wonder about where or whether it is
required whereas the callback code is obvious to anyone competent in their
use.
The original code erased the array in the Case acLBEnd and this appears to
free up some resources but Access requires continued access to the list data
when the list is scrolled. The array cannot be erased until the form
unloads or closes.
It is possible to use Databases(0)(0), a global database variable or a
function that returns a static pointer to a database to improve on the
callback performance when used in a LAN environment and this optimization is
neither available nor necessary using a fieldlist approach, but it might
make the performance advantage even greater in such an environment and
should certainly make it no less.
Although Gustav's suggestion is a nice simple approach that I hadn't
considered and it is quite fast, it is neither quite as fast nor as flexible
as using a callback. Another approach someone may want to try is parsing
the msysQueries table but I believe that using DAO with a callback remains
one of the best approaches.
Hen
_________________________________________________________________
Send and receive larger attachments with Hotmail Extra Storage.
http://join.msn.com/?PAGE=features/es
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003