Access 2010: UDF in calulated query field runs painfully slow over network

I have a UDF in a calculated query field that returns the names of one or more people connected to each record. It runs great in my laptop but when I run it over our network (front-end on my PC and back-end on server) the form can take up to 12 seconds to open. If I remove the calculated field from the query (troubleshooting) the form opens immediately. Any idea how I can speed this up or do this a different way?

I have attached a snapshot of the query. Here is the function:

Function GuestNames(lngRoomID As Long) As String
 
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  Dim strGuestNames As String

  Set dbs = CurrentDb()

  strSQL = "SELECT tblCustomers.LastName " _
          & "FROM (tblCustomers INNER JOIN tblTourBookings " _
          & "ON tblCustomers.CustomerID = tblTourBookings.CustomerID) " _
          & "INNER JOIN tblCustomerRooms " _
          & "ON tblTourBookings.TourBookingID = tblCustomerRooms.TourBookingID " _
          & "WHERE tblCustomerRooms.RoomID = " & lngRoomID & " " _
          & "ORDER BY tblCustomers.LastName;"

  Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

  If Not rst.EOF Then
    rst.MoveFirst
    Do While Not rst.EOF
      strGuestNames = strGuestNames & rst!LastName
      rst.MoveNext
      If Not rst.EOF Then
        strGuestNames = strGuestNames & ", "
      End If
    Loop
    GuestNames = strGuestNames
  Else
    GuestNames = "Enter Name(s)"
  End If
 
  rst.Close
  Set rst = Nothing
  dbs.Close

End Function
John CarrollAsked:
Who is Participating?
 
John CarrollAuthor Commented:
I have read about temp tables but for me it's kind of a workaround for certain deficiencies. In this case it would be to compensate for running a looping function over a network which is too slow to use.

My rule of thumb is when something appears to become overly complicated to accomplish, it can suggest that the design of the user interface should be re-evaluated.

Based on the discussion we have had in this thread I decided to go back and re-evaluate what information we really need to see on this form. After some discussions here at our office the point was made that showing last names of everyone booked in each room (in the calculated field) is not always beneficial. For instance, we could have a number of families with the same name (some related, some not) where we see Smith, Smith" on one line and Smith, Smith again on the next line so the only way for us to know who is who is to double click on a record to see the full names of the people booked in that room.

So it was decided to replace the calculated field with "Last Name, First Name" of each person per line item along with the rest of the room information. Then double-click as usual to open the booking detail form that shows who else (if anyone) is also in that room.

The total records in the datasheet will now show the total number of people booked in the hotel now instead of the total rooms booked but I can use some code to provide that info.

Long story short, this will eliminate the need for the calculated filed and function thus solving the speed issue.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You can try these for starters:

Function GuestNames(lngRoomID As Long) As String
 
     ' Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  Dim strGuestNames As String

    ' Set dbs = CurrentDb()

  strSQL = "SELECT tblCustomers.LastName " _
          & "FROM (tblCustomers INNER JOIN tblTourBookings " _
          & "ON tblCustomers.CustomerID = tblTourBookings.CustomerID) " _
          & "INNER JOIN tblCustomerRooms " _
          & "ON tblTourBookings.TourBookingID = tblCustomerRooms.TourBookingID " _
          & "WHERE tblCustomerRooms.RoomID = " & lngRoomID & " " _
          & "ORDER BY tblCustomers.LastName;"

  Set rst = CurrentDB.OpenRecordset(strSQL, dbOpenDynaset)

  If Not rst.EOF Then
    rst.MoveFirst
    Do While Not rst.EOF
      strGuestNames = strGuestNames & rst!LastName
      rst.MoveNext
      If Not rst.EOF Then
        strGuestNames = strGuestNames & ", "
      End If
    Loop
    GuestNames = strGuestNames
  Else
    GuestNames = "Enter Name(s)"
  End If
 
  rst.Close
     '  Set rst = Nothing  'Probably don't need this either
    '  dbs.Close ' don't do this ... you cannot close CurrentDB, because it's the instance of Access that is open. This line does nothing

End Function
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Next step is to Dim the object variables rst as a Global in the Declarations section of the module, so you are not Dimming each time the function is called
And probably the same for the two string variables  ... then just reset them to "" at the beginning of the Function.

This is a quick test to see if you get any improvement.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
John CarrollAuthor Commented:
I will try your suggestions on the network tomorrow and report back. Thanks.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
So assuming you have done what I just suggested with the Globals (or use TempVar if you are in A2010/A2013):

Function GuestNames(lngRoomID As Long) As String
  strSQL = "" ' Initialize ' actually you don't really need this either - since you set strSQL below anyway.
  strGuestNames ="" 'Initialize

  strSQL = "SELECT tblCustomers.LastName " _
          & "FROM (tblCustomers INNER JOIN tblTourBookings " _
          & "ON tblCustomers.CustomerID = tblTourBookings.CustomerID) " _
          & "INNER JOIN tblCustomerRooms " _
          & "ON tblTourBookings.TourBookingID = tblCustomerRooms.TourBookingID " _
          & "WHERE tblCustomerRooms.RoomID = " & lngRoomID & " " _
          & "ORDER BY tblCustomers.LastName;"

  Set rst = CurrentDB.OpenRecordset(strSQL, dbOpenDynaset)

  With rst  ' Use the With / End With to save time
  If Not .EOF Then
      .MoveFirst
    Do While Not .EOF
      strGuestNames = strGuestNames & rst!LastName
         .MoveNext
      If Not  .EOF Then
        strGuestNames = strGuestNames & ", "
      End If
    Loop
    GuestNames = strGuestNames
  Else
    GuestNames = "Enter Name(s)"
  End If
 
      .Close
  End With
End Function
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
TempVar >>> TempVars() .... better choice than Globals ... but for now ... try with Globals.

And pretty sure the Loop can be simplified

However even with these simplifications, you may not see a big change. Over a network compared to a laptop is an entirely different paradigm :-(

mx
0
 
Dale FyeCommented:
When you run it from your laptop, I assume you have a copy of the back-end on the laptop?

When you run it from the network, I assume that your desktop is hard-wired to your back-end, not running over WiFi?

The problem is that you are making individual trips to the server for each record in your queries recordset, and when you work across the network, you are competing with everyone else who is using the network, and you are pulling all of the records from your TourBookings table back across the network to your PC every time you do this.  I would seriously consider creating a temporary table identical to TourBookings and use that table in this query instead of making multiple trips across the network.  I've got an article on using temp tables (with a sample database and function which does all of the heavy lifting) here on EE; take a look.

  rst.MoveLast
  rst.MoveFirst
  GuestNames = "Enter Name(s)"
  Do While Not rst.EOF
      strGuestNames = strGuestNames & ", " & rst!LastName
      rst.MoveNext
  Loop
  if Len(strGuestNames) > 0 then GuestNames = Mid(strGuestNames,2)

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Do you really need a comma-separated list of all Guests in a Query? That would be odd.

If you're viewing this on a Form, then don't use that UDF, and instead include a Subform that would list the Guests associated with the RoomID. Same on a Report - use a SubReport.
0
 
Dale FyeCommented:
Good point, Scott.  

I was wondering why you would want a list of all guests, past and present who were ever booked into a particular room.
0
 
John CarrollAuthor Commented:
Yes when I run from my laptop I have both the front and back-end on my laptop and when on the network I am running the front-end on my laptop and back-end on the server.

As you can see attached, once you select the tour and the hotel the subform displays a list of all of the rooms booked in that hotel as a datasheet as well as the last name(s) of those in each room. Double-clicking on a room record opens another form showing more detail about the booking as well as allowing you to add or delete those staying in the room and ability to edit any other details.

It would not make sense to move the last names of those staying in a room to another subform (but I understand why one would suggest this).

I hope to try the suggestions of speeding up the function shortly.
Rooms.jpg
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" speeding up the function shortly.'
If improvement ... again, we can probably optimize that loop also.
But sure to try the Global gig ... for your test.
But I'm only give it 50/50 as helpful ....
0
 
John CarrollAuthor Commented:
By Global to you mean Public? I know you are trying to see if it helps the speed but isn't it dangerous to use Global/Public variables? I would think you would have to give them a unique name as a best practise.

BTW: The using the With statement (don't know why I didn't think of this) cut the time down from 12 sec to 9 sec so 25% improvement is not bad.

Using a temporary table in place of network calls would most certainly solve the speed issue but repeated creation and deleting of a table could cause the client to bloat. Have you experienced this?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"By Global to you mean Public?"
Yes ... exactly.  And sure ... a unique name like maybe.

"isn't it dangerous to use Global/Public variables?"
It's all relative. You *need* performance improvement. So, no need to Dim for *each* record call :-)
And as I noted ... if this works, we can convert to TempVars() ... much better than Global Variables

  Public gvstrSQL As String
  Public gvstrGuestNames As String
Or gvstrSQL<SomethingSpecificToThisFuncion>

But for the moment ... just make the existing ones Public - let's see what this does.  Encouraging about With / End With ...
0
 
Dale FyeCommented:
When you use the UpdateTempTable code in that article, one of the arguments is "InCurrentDB".  If you set this argument to False, then the temp table will be created in an external database and linked into your application, so you will not see any bloating.  Additionally, it is safe to set the CompactOnClose property of the database to True, which will reduce any possible bloating.

Make sure that when your application closes, you use the DeleteObject method to remove that temp table from the application, and then delete the actual temporary database.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Have you experienced this?"
Not for me. My database loader copies a FE 'master' down to local workstation, as well as all lookup tables ... and in some cases, active records from the BE into a local 'temp' (for lack of a better word). table. This happens a least 1x/day ... so front end bloating is a non issue.

"then the temp table will be created in an external database and linked into your application, "
But it seems that could result in some potential performance hit ... being linked and on the network ?
0
 
Dale FyeCommented:
Joe,

The subroutine in my article creates the temp table on the users PC, in the directory where the front end resides.  I've thought about that a lot lately, and am probably going to modify it so that it creates the table in the C:\Users\UserName\AppData\ folder.

Similar to your comment about downloading lookup tables from your network to the local PC.
0
 
John CarrollAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Frozone's comment #a40716222

for the following reason:

Since it was determined that the best speed increase I could expect with optimizing the current method, I decided to settle for looking at my data in another acceptable way in order to not have to use eliminate the function in question all together. This bring performance back to a workable level and probably a better solution in the long run.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Since it was determined that the best speed increase I could expect with optimizing the current method, "
However, this determination was made via the input from the three experts participating in this question. So I'm not understanding why you are Accepting your own comment - whereas w/o the input from us, you could not have reached this conclusion.

mx
0
 
John CarrollAuthor Commented:
I agree so what's your point? Based on the input from those who responded I came to the conclusion that it was not going to be possible to do what I needed to do at an acceptable speed. So the only solution I could come up with was to simplify the query and make do with a little less information on this screen. Have I done something wrong?
0
 
Dale FyeCommented:
I think Joe's point is that you could not have come to that conclusion without the help of the experts, so you should award points as appropriate to the expert comments which helped you reach your "solution".
0
 
John CarrollAuthor Commented:
I'm not sure how the points thing works but I have no problem with that. I just thought we were supposed to mark the final solution. So what do I use, the Accept Multiple solutions or is there another way to award points?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If it were me ... in this case as the OP, I would Accept that post as you did at the Accepted Answer, then divvy of Assists among the other participants - as appropriate.
0
 
Dale FyeCommented:
so, if you are new here, the first step is to go to your explanation of what you decided to do.  Click the "Select Multiple Solutions" hyperlink.  Then go to the expert comments that helped you to come to your solution and divide those points among the various comments by typing in the specific point values for each solution.

Welcome to EE!
0
 
John CarrollAuthor Commented:
After evaluating feedback from those who responded it was apparent that it was not going to be possible to speed up the function (which populates a calculated field in the subform query) enough to make for form usable. So I decided to eliminate the use of the calculated field with function and just return on name per record which speeds things up considerably.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.