 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / September 2005Dlookup Vs Recordset and efficiency
I make a lot of use of the DLookup function to retrieve data back from the tables, but wondered at what point it is more efficient to use a recordset when retrieving many values back from the same table. e.g. if I needed only 1 item back from table A, I would have thought that DLookup would be more efficient than defining a recordset to get the data. If I needed two values back from the same table, would two DLookups still be more efficient that using a recordset. At what point would it make sense to use a recordset? Many Thanks Chris Hi, Chris. > At what point would it make sense to > use a recordset? At the point where you are willing to sacrifice a little bit of extra coding time in exchange for speed of execution. The Domain functions (DLookup, DCount, DMax, et cetera) will be less efficient than coding for a Recordset Object. (Unless one intentionally misuses the Recordset, such as addint JOIN's with many other unnecessary tables and returning all fields instead of just the necessary fields). The reason the Domain functions are less efficient is because there's some additional overhead involved with Access's automatic creation of a Recordset Ojbect. This additional overhead costs time and system resources. If this Domain function were to be processed within a loop that is executed numerous times, the extra overhead becomes very noticeable to the user. However, when used sparingly, there's only a little bit of difference in execution times that the average user often won't be able to positively identify as "This is so S-L-O-W!" But it is. They just don't recognize it. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. > I make a lot of use of the DLookup function to retrieve data back from the > tables, but wondered at what point it is more efficient to use a recordset [quoted text clipped - 8 lines] > Many Thanks > Chris I was using probably 10 or so DLookup()'s using the same criteria on the same table to get a single value. Converting to using a RS object was actually quite easy. In my case, I created a single SUB to load up global variables from the recordSet and then call it right before I need the values. As soon as the code finishes, I've got another SUB that is called that clears out the global variables. > Hi, Chris. > [quoted text clipped - 45 lines] >>Many Thanks >>Chris Hi Gunny Thanks for that. That is useful to know. I guess the key points I need to consider would be: 1. network traffic and 2. Frequency (if as you say running in a loop) > Hi, Chris. > [quoted text clipped - 45 lines] > > Many Thanks > > Chris You might also look at http://allenbrowne.com/ser-42.html Allen wrote a function called -> Extended DLookup() <- "Purpose: Faster and more flexible replacement for DLookup()" "Arguments: Same as DLookup, with additional Order By option." All the work has been done for you!!! Well, most of it :D  SignatureSteve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) > Hi, Chris. > [quoted text clipped - 45 lines] > > Many Thanks > > Chris I know that using DLookups() in a query is a BAD thing due to ONE DLookup() being done per record in the results. What are the effects if the DLookups are spun out to a function? In my DB, I have the following three tables tblTransports tblTransportGuests tblClients tblTransportGuests contains the lngTransportId and lngClientId for the transport and guest respectively. In tblTransportGuests the field ynPrimaryPassenger indicates that the passenger is the PrimePax. I'm now wondering if I should now mod the function below to use a RecordSet or not. Ideas? Function getPrimaryPassengerForTransport(lngTransportId As Long, strFormat As String) Dim lngClientID As Variant Dim strPassengerFirstName As Variant Dim strPassengerLastName As Variant Dim strWhereCriteria As String lngClientID = DLookup("lngClientID", "tblTransferGuests", "lngTransportId = " & lngTransportId & " AND ynPrimaryPassenger = True") If IsNull(lngClientID) = True Then getPrimaryPassengerForTransport = "-No Primary PAX-" Else strWhereCriteria = "lngClientId = " & lngClientID strPassengerFirstName = DLookup("txtClientFirstName", "tblClients", strWhereCriteria) strPassengerLastName = DLookup("txtClientLastName", "tblClients", strWhereCriteria) Select Case strFormat Case "FN", "FirstLast" getPrimaryPassengerForTransport = strPassengerFirstName & " " & strPassengerLastName Case "NF", "LastFirst" getPrimaryPassengerForTransport = strPassengerLastName & ", " & strPassengerFirstName Case Else getPrimaryPassengerForTransport = strPassengerFirstName & " " & strPassengerLastName End Select End If End Function > You might also look at > [quoted text clipped - 6 lines] > > All the work has been done for you!!! Well, most of it :D > I know that using DLookups() in a query is a BAD thing due to ONE > DLookup() being done per record in the results. What are the effects [quoted text clipped - 13 lines] > > Ideas? You are calling three DLookups. If you use two Recordsets that return one row each and use a single database object to retrieve all three values then you reduce the creation of database objects from three to one and the number of actual SQL statements run from three to two. Is that more efficient? Obviously it is. Will you notice the difference? I doubt it. Again the problem with Domain functions is not that apparent when you are using just a few of them. If I have more than a couple I will usually opt for another method, but there are no absolutes as to when the user will be able to tell the difference. SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com Thought so. I can drop it down to a single recordSet by using a JOIN in the statement. The issue that I was curious about was that a slightly unnoticeable delay can become noticable when your dealing with large number of records. (Feel free to send me a dollar a day.) >>I know that using DLookups() in a query is a BAD thing due to ONE >>DLookup() being done per record in the results. What are the effects [quoted text clipped - 24 lines] > usually opt for another method, but there are no absolutes as to when the > user will be able to tell the difference. > Thought so. I can drop it down to a single recordSet by using a JOIN > in the statement. The issue that I was curious about was that a > slightly unnoticeable delay can become noticable when your dealing > with large number of records. (Feel free to send me a dollar a day.) With proper indexing the number of records to be searched should not be a factor, only how many rows you need to retrieve and how many lookups it takes to do that. The join is a good idea though. SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com So it really wouldn't matter that much that I might have to do the DLookup on 1,000 records? >>Thought so. I can drop it down to a single recordSet by using a JOIN >>in the statement. The issue that I was curious about was that a [quoted text clipped - 6 lines] > > The join is a good idea though. > So it really wouldn't matter that much that I might have to do the > DLookup on 1,000 records? If you mean you need to use DLookup to retrieve one value from a table with 1000 records (a very small table actually) then yes, as long as the field used in the WHERE argument is indexed the number of records in the table matters little. You should expect instantaneous results even with a few hundred thousand rows in the table. SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.