Could I get some query theory clarification?

[Dennis Snelgrove]

This is going to be lengthy.



I've got an event-log database. The main screen has the records showing
events as they occur. Much of our work is in the concept of "First Event A
occurs, then at some point a little later Event B occurs, then later Event
C, and then later again Event D." I found it useful and much more efficient
to put a shortcut button on the subform where if the button isn't blank,
clicking it will auto enter the next Event along with a number of related
information from that specific record. I put a user-defined calculation into
the subform's Recordsource wherein I pass the current record's Primary key
(an autonumber) and it looks to see if any subsequent Events have occurred.
If they have, then this record's button is blanked out since the following
Event has already occurred. If the following Event hasn't occurred, the
button's caption is set appropriately.



Now for the part I need clarified. Below is the user-defined function. Up to
now, the query I've run have taken at most 3-5 seconds, which for our
purposes is acceptable. Now I'm building an almost identical query where I'm
applying criteria to this function. The run-time for this query has gone
from 5 seconds to about 2 minutes. I removed the criteria for just this
calculated field, and the runtime dropped back to 5 seconds. I've also tried
removing just this one criterion and then making a new query which uses this
query as the sole source, and applying the criteria in there. No
difference - it still went back up to 2 minutes again. I figure that the
reason for the large delay is the fact that the PC is doing large amounts of
creating Recordsets and doing a lot of DCounts, as per the u.d.f. What I don
't' understand is why the runtime is becoming so large when criteria is put
against that calculation, versus when it's just another field.



Anyone willing to discuss this with me, or even better yet, make a
suggestion or offer insights, I'd be most grateful.



Thanks.



----------------------------------------------------------------------------
---



Public Function ActionLabel(CallID As Long) As String

' This function will take the Primary Key value for an entry, and return the
' proper caption for the "Make In or Out entry" textbox on the Call-out
detail
' subform
'
' If the "Make In entry" textbox is clicked, the program will auto-create a
' new entry for that person with the current time, the Action field will be
' set to "In" and all other fields copied forward from the "Confirmed" entry
'
' If the "Make Out entry" textbox is clicked, the program will auto-create a
' new entry for that person with the current time, the Action field will be
' set to "Out" and all other fields copied forward from the "In" entry
'
' The flow of the call-out program is as follows:
' - call a person to come in
' - if that person confirms, put "Confirmed" in the Comments field. This
' will cause the "Make In entry" label to appear in that entry
' - when that employee books in, the Dispatcher simply clicks the "Make
' In entry" and a new entry is auto-created for that employee with
' an Action of "In" and the current time. The "Make In entry" button
' on the confirmation entry will disappear because the "In" now entry
exists
' - when that employee books out, the Dispatcher simply clicks the "Make
Out
' entry" button and a new entry is auto-created for that employee with
' an Action of "Out" and the current time. The "Make Out entry" button
' will disappear because the "Out" entry now exists

Dim rs As DAO.Recordset
Dim strSQLCondition As String
Dim strSQLCondition1 As String

' Get all fields for the current entry
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblCallOutDetail WHERE
[CallID]=" & CallID)

' The first condition is used to check if there are any "In" entries for
this employee
' that fall after this entry. If not, then the ActionLabel is "Make In
Entry"
strSQLCondition = "(([Action]='In') And ([CallTime]>=#" & _
Nz(rs("CallTime"), rs("TimeEntered")) & _
"#) And ([NamesID]=" & _
rs("NamesID") & "))"

' The second condition is used to check if there are any "Out" entries for
this employee
' that fall after this entry. If not, then the ActionLabel is "Make Out
Entry"
strSQLCondition1 = "(([Action]='Out') And ([CallTime]>=#" & _
Nz(rs("CallTime"), rs("TimeEntered")) & "#) And
([NamesID]=" & _
rs("NamesID") & "))"

' Give a "Make Out entry" on one of two conditions - Action is "In" and:
' - the CallTime is not Null
' - the CallTime is Null and the Comments is "Shift Driver"
If rs("Action") = "In" Then
If (Not IsNull(rs("CallTime"))) Or ((IsNull(rs("CallTime")) _
And (rs("Comments") = "Shift Driver"))) Then
If DCount("*", "tblCallOutDetail", strSQLCondition1) = 0 Then
ActionLabel = "Make Out entry"
End If
End If
Else
If rs("Comments") = "Confirmed" Then
If DCount("*", "tblCallOutDetail", strSQLCondition) = 0 Then
ActionLabel = "Make In entry"
Else
ActionLabel = ""
End If
Else
ActionLabel = ""
End If
End If

Set rs = Nothing
End Function

 

[Dirk Goldgar]

Dennis -

If you apply a query criterion to a calculated field, that field must be
evaluated for every candidate record. If you don't apply a criterion to
that field, then Access won't evaluate the field until after the records
have been selected. In fact, for a calculated field that is the result
of a user-defined function, Access may even go so far as to delay the
evaluation of the field until it is actually referred to or displayed.

I haven't tried to figure out all the logic in your function, but I
wonder if it can be rewritten more efficiently or, better yet, have the
whole calculation be expressed as SQL subqueries in the form's
recordsource query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

This is going to be lengthy.



I've got an event-log database. The main screen has the records
showing events as they occur. Much of our work is in the concept of
"First Event A occurs, then at some point a little later Event B
occurs, then later Event C, and then later again Event D." I found it
useful and much more efficient to put a shortcut button on the
subform where if the button isn't blank, clicking it will auto enter
the next Event along with a number of related information from that
specific record. I put a user-defined calculation into the subform's
Recordsource wherein I pass the current record's Primary key (an
autonumber) and it looks to see if any subsequent Events have
occurred. If they have, then this record's button is blanked out
since the following Event has already occurred. If the following
Event hasn't occurred, the button's caption is set appropriately.



Now for the part I need clarified. Below is the user-defined
function. Up to now, the query I've run have taken at most 3-5
seconds, which for our purposes is acceptable. Now I'm building an
almost identical query where I'm applying criteria to this function.
The run-time for this query has gone from 5 seconds to about 2
minutes. I removed the criteria for just this calculated field, and
the runtime dropped back to 5 seconds. I've also tried removing just
this one criterion and then making a new query which uses this query
as the sole source, and applying the criteria in there. No
difference - it still went back up to 2 minutes again. I figure that
the reason for the large delay is the fact that the PC is doing large
amounts of creating Recordsets and doing a lot of DCounts, as per the
u.d.f. What I don 't' understand is why the runtime is becoming so
large when criteria is put against that calculation, versus when it's
just another field.



Anyone willing to discuss this with me, or even better yet, make a
suggestion or offer insights, I'd be most grateful.



Thanks.



---------------------------------------------------------------------- ------
---



Public Function ActionLabel(CallID As Long) As String

' This function will take the Primary Key value for an entry, and
return the ' proper caption for the "Make In or Out entry" textbox on
the Call-out detail
' subform
'
' If the "Make In entry" textbox is clicked, the program will
auto-create a ' new entry for that person with the current time, the
Action field will be ' set to "In" and all other fields copied
forward from the "Confirmed" entry '
' If the "Make Out entry" textbox is clicked, the program will
auto-create a ' new entry for that person with the current time, the
Action field will be ' set to "Out" and all other fields copied
forward from the "In" entry '
' The flow of the call-out program is as follows:
' - call a person to come in
' - if that person confirms, put "Confirmed" in the Comments
field. This ' will cause the "Make In entry" label to appear in
that entry ' - when that employee books in, the Dispatcher simply
clicks the "Make ' In entry" and a new entry is auto-created
for that employee with ' an Action of "In" and the current
time. The "Make In entry" button ' on the confirmation entry
will disappear because the "In" now entry exists
' - when that employee books out, the Dispatcher simply clicks
the "Make Out
' entry" button and a new entry is auto-created for that
employee with ' an Action of "Out" and the current time. The
"Make Out entry" button ' will disappear because the "Out"
entry now exists

Dim rs As DAO.Recordset
Dim strSQLCondition As String
Dim strSQLCondition1 As String

' Get all fields for the current entry
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblCallOutDetail WHERE
[CallID]=" & CallID)

' The first condition is used to check if there are any "In" entries
for this employee
' that fall after this entry. If not, then the ActionLabel is "Make In
Entry"
strSQLCondition = "(([Action]='In') And ([CallTime]>=#" & _
Nz(rs("CallTime"), rs("TimeEntered")) & _
"#) And ([NamesID]=" & _
rs("NamesID") & "))"

' The second condition is used to check if there are any "Out"
entries for this employee
' that fall after this entry. If not, then the ActionLabel is "Make
Out Entry"
strSQLCondition1 = "(([Action]='Out') And ([CallTime]>=#" & _
Nz(rs("CallTime"), rs("TimeEntered")) & "#) And
([NamesID]=" & _
rs("NamesID") & "))"

' Give a "Make Out entry" on one of two conditions - Action is "In"
and: ' - the CallTime is not Null
' - the CallTime is Null and the Comments is "Shift Driver"
If rs("Action") = "In" Then
If (Not IsNull(rs("CallTime"))) Or ((IsNull(rs("CallTime")) _
And (rs("Comments") = "Shift Driver"))) Then
If DCount("*", "tblCallOutDetail", strSQLCondition1) = 0 Then
ActionLabel = "Make Out entry"
End If
End If
Else
If rs("Comments") = "Confirmed" Then
If DCount("*", "tblCallOutDetail", strSQLCondition) = 0 Then
ActionLabel = "Make In entry"
Else
ActionLabel = ""
End If
Else
ActionLabel = ""
End If
End If

Set rs = Nothing
End Function

 

[Dennis Snelgrove]

Dirk,

Okay, but maybe I'm confused by something here. Let me explain it this way:

I've got a table (let's call it tblA) with 3,500 records in it. I've got to
do some filtering, so I create a query (call it qryInitialFilter) that will
filter out the 3,500 records down to about 30. Then I've got a second query
(qryFinal) which is the one that actually applies the calculated field. This
takes approximately 2 minutes. I then backed up the back-end, deleted all
but 100 records (which still left all 30 of the required records) and ran
qryFinal. It took maybe 5 or 6 seconds.

I was under the inpression that qryInitialFilter would trim out the 3,470
records, and there would only be a 30 record recordset going into qryFinal,
where the filtering by the calculated field would be performed. Am I wrong
about this? Does qryFinal force the calculation of *all* 3,500 records for
the filter, even though it's not technically applied until the 30 record
stage?

Thanks for your time and patience...

Dennis -

If you apply a query criterion to a calculated field, that field must be
evaluated for every candidate record. If you don't apply a criterion to
that field, then Access won't evaluate the field until after the records
have been selected. In fact, for a calculated field that is the result
of a user-defined function, Access may even go so far as to delay the
evaluation of the field until it is actually referred to or displayed.

I haven't tried to figure out all the logic in your function, but I
wonder if it can be rewritten more efficiently or, better yet, have the
whole calculation be expressed as SQL subqueries in the form's
recordsource query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

This is going to be lengthy.



I've got an event-log database. The main screen has the records
showing events as they occur. Much of our work is in the concept of
"First Event A occurs, then at some point a little later Event B
occurs, then later Event C, and then later again Event D." I found it
useful and much more efficient to put a shortcut button on the
subform where if the button isn't blank, clicking it will auto enter
the next Event along with a number of related information from that
specific record. I put a user-defined calculation into the subform's
Recordsource wherein I pass the current record's Primary key (an
autonumber) and it looks to see if any subsequent Events have
occurred. If they have, then this record's button is blanked out
since the following Event has already occurred. If the following
Event hasn't occurred, the button's caption is set appropriately.



Now for the part I need clarified. Below is the user-defined
function. Up to now, the query I've run have taken at most 3-5
seconds, which for our purposes is acceptable. Now I'm building an
almost identical query where I'm applying criteria to this function.
The run-time for this query has gone from 5 seconds to about 2
minutes. I removed the criteria for just this calculated field, and
the runtime dropped back to 5 seconds. I've also tried removing just
this one criterion and then making a new query which uses this query
as the sole source, and applying the criteria in there. No
difference - it still went back up to 2 minutes again. I figure that
the reason for the large delay is the fact that the PC is doing large
amounts of creating Recordsets and doing a lot of DCounts, as per the
u.d.f. What I don 't' understand is why the runtime is becoming so
large when criteria is put against that calculation, versus when it's
just another field.



Anyone willing to discuss this with me, or even better yet, make a
suggestion or offer insights, I'd be most grateful.



Thanks.



---------------------------------------------------------------------- ------
---



Public Function ActionLabel(CallID As Long) As String

' This function will take the Primary Key value for an entry, and
return the ' proper caption for the "Make In or Out entry" textbox on
the Call-out detail
' subform
'
' If the "Make In entry" textbox is clicked, the program will
auto-create a ' new entry for that person with the current time, the
Action field will be ' set to "In" and all other fields copied
forward from the "Confirmed" entry '
' If the "Make Out entry" textbox is clicked, the program will
auto-create a ' new entry for that person with the current time, the
Action field will be ' set to "Out" and all other fields copied
forward from the "In" entry '
' The flow of the call-out program is as follows:
' - call a person to come in
' - if that person confirms, put "Confirmed" in the Comments
field. This ' will cause the "Make In entry" label to appear in
that entry ' - when that employee books in, the Dispatcher simply
clicks the "Make ' In entry" and a new entry is auto-created
for that employee with ' an Action of "In" and the current
time. The "Make In entry" button ' on the confirmation entry
will disappear because the "In" now entry exists
' - when that employee books out, the Dispatcher simply clicks
the "Make Out
' entry" button and a new entry is auto-created for that
employee with ' an Action of "Out" and the current time. The
"Make Out entry" button ' will disappear because the "Out"
entry now exists

Dim rs As DAO.Recordset
Dim strSQLCondition As String
Dim strSQLCondition1 As String

' Get all fields for the current entry
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblCallOutDetail WHERE
[CallID]=" & CallID)

' The first condition is used to check if there are any "In" entries
for this employee
' that fall after this entry. If not, then the ActionLabel is "Make In
Entry"
strSQLCondition = "(([Action]='In') And ([CallTime]>=#" & _
Nz(rs("CallTime"), rs("TimeEntered")) & _
"#) And ([NamesID]=" & _
rs("NamesID") & "))"

' The second condition is used to check if there are any "Out"
entries for this employee
' that fall after this entry. If not, then the ActionLabel is "Make
Out Entry"
strSQLCondition1 = "(([Action]='Out') And ([CallTime]>=#" & _
Nz(rs("CallTime"), rs("TimeEntered")) & "#) And
([NamesID]=" & _
rs("NamesID") & "))"

' Give a "Make Out entry" on one of two conditions - Action is "In"
and: ' - the CallTime is not Null
' - the CallTime is Null and the Comments is "Shift Driver"
If rs("Action") = "In" Then
If (Not IsNull(rs("CallTime"))) Or ((IsNull(rs("CallTime")) _
And (rs("Comments") = "Shift Driver"))) Then
If DCount("*", "tblCallOutDetail", strSQLCondition1) = 0 Then
ActionLabel = "Make Out entry"
End If
End If
Else
If rs("Comments") = "Confirmed" Then
If DCount("*", "tblCallOutDetail", strSQLCondition) = 0 Then
ActionLabel = "Make In entry"
Else
ActionLabel = ""
End If
Else
ActionLabel = ""
End If
End If

Set rs = Nothing
End Function

 

[Dirk Goldgar]

Dirk,

Okay, but maybe I'm confused by something here. Let me explain it
this way:

I've got a table (let's call it tblA) with 3,500 records in it. I've
got to do some filtering, so I create a query (call it
qryInitialFilter) that will filter out the 3,500 records down to
about 30. Then I've got a second query (qryFinal) which is the one
that actually applies the calculated field. This takes approximately
2 minutes. I then backed up the back-end, deleted all but 100 records
(which still left all 30 of the required records) and ran qryFinal.
It took maybe 5 or 6 seconds.

I was under the inpression that qryInitialFilter would trim out the
3,470 records, and there would only be a 30 record recordset going
into qryFinal, where the filtering by the calculated field would be
performed. Am I wrong about this? Does qryFinal force the calculation
of *all* 3,500 records for the filter, even though it's not
technically applied until the 30 record stage?

Thanks for your time and patience...

I've struggled with this sort of problem on occasion myself. I can't
say for sure what is happening in your case, but I'm pretty sure that
when you base queries on queries, it is not necessarily the case that
the lower-order queries are executed before the higher-order queries. I
believe that Jet analyzes the whole query structure and makes its own
plan for how to come up with the requested results. This plan may
change the order of operations from the way they were originally
specified to the way that Jet "thinks" will be most efficient. If Jet's
guesses are wrong, then you can end up with wildly inefficient queries.

My guess is that in your case, Jet has chosen to apply the criterion
that involves your function first. There is very little you can do
about this, although sometimes you can change your query around in a way
that will affect the execution plan. If you really *have* to use this
function in the query, you might resort to running the initial filtering
step as a make-table query or an append query into a work table, and
then run your final query on that table. I hate to recommend that,
though, because that sort of thing often leads to bloating of the
database.

A query that takes 5 or 6 records to process 100 records is definitely
one that deserves attention and an attempt at a rewrite. As I said
before, it wouldn't surprise me at all if your UDF could be dispensed
with altogether, with the equivalent criteria being applied via
subqueries in the main query's SQL. That might well speed things up a
lot.