Henry Simpson
hsimpson88 at hotmail.com
Mon Sep 15 12:56:20 CDT 2003
Charlotte:
My objection is demonstrated by the fact that using three quite small tables
in a cross product result of 11 million records took 37 seconds to return
that empty recordset of exactly zero records against a local database. That
suggests to me that there is a great deal of traffic or processing going on
to determine that no records should be returned. As Arthur says, such a
query cannot take advantage of any indexes which appears to mean that the FE
is retrieving a great deal of data, whether or not there is an index, prior
to testing the data against this condition. Although I haven't tested
against a single large table or query data source for a form, the cross
product query demonstrates that some significant traffic or processing is
involved with the 'Where False' clause.
If I need an empty recordset I find it convenient to open it in add mode
(dbAppendOnly) and DataEntry mode for a bound form unless of course such a
mode is unsupported. If I want to ensure that absolutely no LAN traffic is
incurred I find it handy to use a Union query against a local system table
and union in empty strings as the field values and bind to that. Another
alternative I have used is to bind a form at runtime, setting the
ControlSource property if and when required. Most commonly however, I use
an incrementing numeric PK as the first field and set a condition on that
indexed field of -1 and I suffer however much traffic that takes. I know
I've mixed Recordset and form DataSources in my approaches here but these,
and reports, are the most likely places where I would want to efficiently
return a zero records recordset.
I would be happy to try the '1 = 2' to see if it improves on the 37 seconds
when I get home tonight. I can also try this out on an NT LAN on Friday to
see how long is required.
Please note that I attempted to change the subject line for this issue but
we are now back to the original topic where the matter of returning 0
records is really a non issue.
Hen
>From: "Charlotte Foust" <cfoust at infostatsystems.com>
>Reply-To: Access Developers discussion and problem
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem
>solving"<accessd at databaseadvisors.com>
>Subject: RE: [AccessD] listbox with rowsource type (ala MS)
>Date: Mon, 15 Sep 2003 08:03:18 -0700
>
>Hen,
>
>Something like SELECT * FROM MyTable WHERE 1=2 returns an empty
>recordset immediately in my experience. I have used it for years to
>force forms to load right away with no records. This is the same as
>WHERE False, although I prefer the explicit syntax. Exactly what is it
>that you feel is wrong with that approach? It certainly works and is
>the only reliable method I've found for quickly loading a form with an
>empty recordset.
>
>Charlotte Foust
>
>
>-----Original Message-----
>From: Henry Simpson [mailto:hsimpson88 at hotmail.com]
>Sent: Monday, September 15, 2003 6:03 AM
>To: accessd at databaseadvisors.com
>Subject: Re: [AccessD] listbox with rowsource type (ala MS)
>
>
>Gustav:
>
>One of the things I was trying to say was that using a field list row
>source
>does not submit the query SQL to the BE for retreival so there is no
>difference whether or not a where clause is present. Another point is
>that
>it is only necessary to add an impossible restraint to a single table if
>the
>SQL is submitted because Jet should process that index first and finding
>
>nothing to join on, disregard the other table indexes. I'm not
>satisfied
>that using three 'Where Null' restraints wouldn't cause all three
>indexes to
>be pulled before the FE started processing the joing.
>
>My main point was that I have seen the 'Where False' construct advocated
>
>several times in the past on this list and that had led me to believe
>that
>someone here had at some time read or determined that the FE SQL parser
>had
>enough intelligence to determine that it was unnecessary to submit the
>request for data to a BE file when this condition was encountered.
>Otherwise, how does it make sense? If there are records with 0 in some
>field, will it return those? If there are yes/no fields with false
>records, would it return those? Apparently not, so what does it
>actually do
>with this magical condition? Let's just put an end to promoting 'Where
>False' as a means to retreiving a limited recordset.
>
>Hen
>
> >From: Gustav Brock <gustav at cactus.dk>
> >Reply-To: Access Developers discussion and problem
> >solving<accessd at databaseadvisors.com>
> >To: Access Developers discussion and problem
> >solving<accessd at databaseadvisors.com>
> >CC: Sad Der <accessd666 at yahoo.com>
> >Subject: Re: [AccessD] listbox with rowsource type (ala MS)
> >Date: Mon, 15 Sep 2003 09:17:46 +0200
> >
> >Hi Sander
> >
> >Henry is right (Subject: Where False, was listbox with rowsource type).
> >I did a test but with small tables only.
> >
> >However, assuming each table has a normal autonumber primary key,
> >modify the query like this:
> >
> > SELECT
> > tblOne.*,
> > tblTwo.*,
> > tblThree.*
> > FROM
> > tblOne,
> > tblTwo,
> > tblThree
> > WHERE
> > tblOne.ID Is Null And
> > tblTwo.ID Is Null And
> > tblThree.ID Is Null;
> >
> >That should reveal the field names in a split second.
> >
> >/gustav
> >
> >
> > > Hi Sander
> >
> > > Maybe I'm missing something, but why all this trouble to get a field
>
> > > list?
> >
> > > 1. Create a select query like this:
> >
> > > SELECT
> > > tblOne.*,
> > > tblTwo.*,
> > > tblThree.*
> > > FROM
> > > tblOne,
> > > tblTwo,
> > > tblThree
> > > WHERE
> > > False;
> >
> > > and save it as, say, qdyFieldList.
> > > This will retrieve all fields from the tables but no records.
> >
> > > 2. In your form, adjust the combobox to have FieldList as
> > > RowSourceType and specify qdyFieldList as the RowSource.
> >
> > > This should load in a fraction of a section.
> >
> > > /gustav
> >
> >
> > >>>i've got a problem. I'm using a tab control. On all
> > >>>tabs i've got listboxes wich are filled with field
> > >>>names of a couple of tables. These listboxes are
> > >>>filled using the rowsource type thingy. This seems to
> > >>>be the original MS-way-of-programming.
> > >>>
> > >>>Below I pasted the code. In the property RowSourceType
> > >>>of the listbox I fill in the function name.
> > >>>
> > >>>When I programmed it it worked fine. The client is on
> > >>>the desktop of the user and the BE is on our test
> > >>>network. It took about 4 seconds to load each
> > >>>tab-page.
> > >>>
> > >>>Now it's in production (on another network) and if a switch between
>
> > >>>tab pages now takes almost 2 MINUTES!!!
> > >>>
> > >>>Any ideas how this is possible??
> > >>>
> > >>>tia
> > >>>
> > >>>Sander
> >
> > >>>Private Function ListMeetVelden(fld As Control, id As Variant, row
> > >>>As Variant, col As Variant, code As
> > >>>Variant) As Variant
> > >>>
> > >>> Static strVelden() As String
> > >>> Static lngRows As Long
> > >>> Dim varRetVal As Variant
> > >>> Dim oMeetTable As DAO.Recordset
> > >>> Dim i As Integer
> > >>>
> > >>> On Error GoTo ListMeetVelden_Error
> > >>>
> > >>> Select Case code
> > >>> Case acLBInitialize
> > >>> ReDim strVelden(0)
> > >>> 'fill array with fields.
> > >>> Set oMeetTable = CurrentDb().OpenRecordset(Name:="tblxxxx",
> > >>>Type:=dbOpenSnapshot, Options:=dbOpenForwardOnly)
> > >>> With oMeetTable
> > >>> For i = 0 To .Fields.Count - 1
> > >>> strVelden(UBound(strFields)) = "C_" & .Fields(i).Name
> > >>> ReDim Preserve strVelden(0 To
> > >>>UBound(strVelden) + 1)
> > >>> Next i
> > >>> End With
> > >>>
> > >>> Set oMeetTable = Nothing
> > >>> 'fill array with fields.
> > >>> Set oMeetTable = CurrentDb().OpenRecordset(Name:="tblxxx",
> > >>>Type:=dbOpenSnapshot, Options:=dbOpenForwardOnly)
> > >>> With oMeetTable
> > >>> For i = 0 To .Fields.Count - 1
> > >>> strVelden(UBound(strFields)) = "D_" & .Fields(i).Name
> > >>> ReDim Preserve strVelden(0 To
> > >>>UBound(strFields) + 1)
> > >>> Next i
> > >>> End With
> > >>>
> > >>> Set oMeetTable = Nothing
> > >>> 'fill array with fields.
> > >>> Set oMeetTable = CurrentDb().OpenRecordset(Name:="tblxxx",
> > >>>Type:=dbOpenSnapshot, Options:=dbOpenForwardOnly)
> > >>> With oMeetTable
> > >>> For i = 0 To .Fields.Count - 1
> > >>> strVelden(UBound(strVelden)) = "E_" & .Fields(i).Name
> > >>> ReDim Preserve strVelden(0 To
> > >>>UBound(strVelden) + 1)
> > >>> Next i
> > >>> End With
> > >>>
> > >>> ReDim Preserve strVelden(0 To UBound(strVelden)
> > >>>- 1)
> > >>>
> > >>> lngRows = UBound(strVelden) + 1 ' array is
> > >>>0-based
> > >>> varRetVal = lngRows
> > >>> Case acLBOpen
> > >>> varRetVal = Timer 'Unique ID for control
> > >>> Case acLBGetRowCount
> > >>> varRetVal = lngRows
> > >>> Case acLBGetColumnCount
> > >>> varRetVal = 1
> > >>> Case acLBGetColumnWidth
> > >>> varRetVal = -1 'Default of -1 uses default
> > >>>column width
> > >>> Case acLBGetValue
> > >>> varRetVal = strVelden(row)
> > >>> Case acLBEnd
> > >>> Erase strVelden
> > >>> End Select
> > >>>
> > >>> ListMeetVelden = varRetVal
> > >>>
> > >>>ListMeetVelden_Exit:
> > >>> Set oMeetTable = Nothing
> > >>> Exit Function
> > >>>ListMeetVelden_Error:
> > >>> Set oMeetTable = Nothing
> > >>> Call g_oGenErr.Throw("xxx.frmxxx", "tblxxxx")
> > >>>End Function
>
>_________________________________________________________________
>Tired of spam? Get advanced junk mail protection with MSN 8.
>http://join.msn.com/?page=features/junkmail
>
>_______________________________________________
>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
_________________________________________________________________
Need more e-mail storage? Get 10MB with Hotmail Extra Storage.
http://join.msn.com/?PAGE=features/es