Which method is fastest? Ronald 12/20/2008 7:51:00 PM Hi. To select/find data in a table, what will be the fastest method: 1. By query: "SELECT * FROM Table WHERE Field1=""" & Me!txtField1 & """ AND """ Field2=""" & Me!txtField2 & """;" And loop through the records found (if any). Or 2. Select the table in whole. And .FindFirst "Field1=""" & Me!txtField1 & """ AND """ Field2=""" & Me!txtField2 & """" and loop using .FindNext I guess method 2 is the fastest. Is that correct? Thanks, Ronald.

Re: Which method is fastest? "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 12/20/2008 8:20:00 PM Have you tried running both and seeing which is faster? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Ronald" <wit[ at ]yahoo> wrote in message news:686056D9-A9CB-4F4F-9880-EA3F57438155[ at ]microsoft.com... [Quoted Text] > Hi. > > To select/find data in a table, what will be the fastest method: > 1. By query: "SELECT * FROM Table WHERE Field1=""" & Me!txtField1 & """ > AND > """ Field2=""" & Me!txtField2 & """;" > And loop through the records found (if any). > > Or > 2. Select the table in whole. > And .FindFirst "Field1=""" & Me!txtField1 & """ AND """ Field2=""" & > Me!txtField2 & """" and loop using .FindNext > > I guess method 2 is the fastest. Is that correct? > > Thanks, > > Ronald.

Re: Which method is fastest? John W. Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 12/20/2008 8:22:58 PM On Sat, 20 Dec 2008 11:51:00 -0800, Ronald <wit[ at ]yahoo> wrote: [Quoted Text] >Hi. > >To select/find data in a table, what will be the fastest method: >1. By query: "SELECT * FROM Table WHERE Field1=""" & Me!txtField1 & """ AND >""" Field2=""" & Me!txtField2 & """;" >And loop through the records found (if any). > >Or >2. Select the table in whole. >And .FindFirst "Field1=""" & Me!txtField1 & """ AND """ Field2=""" & >Me!txtField2 & """" and loop using .FindNext > >I guess method 2 is the fastest. Is that correct? I would expect method 1 to be much faster (if there are indexes on Field1 and Field2, which would help both methods). The first method will return a recordset containing only the hits, rather than the whole table. -- John W. Vinson [MVP]

Re: Which method is fastest? Ronald 12/21/2008 12:30:05 PM Hi Douglas. I did now. See my reply to John. Ronald. "Douglas J. Steele" wrote: [Quoted Text] > Have you tried running both and seeing which is faster? > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > "Ronald" <wit[ at ]yahoo> wrote in message > news:686056D9-A9CB-4F4F-9880-EA3F57438155[ at ]microsoft.com... > > Hi. > > > > To select/find data in a table, what will be the fastest method: > > 1. By query: "SELECT * FROM Table WHERE Field1=""" & Me!txtField1 & """ > > AND > > """ Field2=""" & Me!txtField2 & """;" > > And loop through the records found (if any). > > > > Or > > 2. Select the table in whole. > > And .FindFirst "Field1=""" & Me!txtField1 & """ AND """ Field2=""" & > > Me!txtField2 & """" and loop using .FindNext > > > > I guess method 2 is the fastest. Is that correct? > > > > Thanks, > > > > Ronald. > > >

Re: Which method is fastest? Ronald 12/21/2008 12:40:01 PM Hi John. You are right! First I ran the code as is. No time difference. Then I let it run 1000 times and that gave a big difference. The table has a little more than 25000 entries and both search fields are indexed. This is the code: Debug.Print Time For intCount2 = 1 To 1000 Set rstTelefoonnummers = .OpenRecordset("SELECT * FROM [Telefoonnummers] WHERE [Netnummer]=""" & Me![cboNetnummer] & """ AND [Abonneenummer]=""" & Me![txtAbonneenummer] & """;", dbOpenDynaset) With [rstTelefoonnummers] intCount1 = 0 If (.EOF = False) Then .MoveFirst Do While (.EOF = False) ReDim Preserve typAdressen(intCount1 + 1) typAdressen(intCount1).lngAdresID = ![AdresID] typAdressen(intCount1).datLaatsteMaalGebeld = ![LaatsteMaalGebeld] intCount1 = intCount1 + 1 .MoveNext Loop End If .Close End With Set rstTelefoonnummers = Nothing Next intCount2 Debug.Print Time Debug.Print Time For intCount2 = 1 To 1000 Set rstTelefoonnummers = .OpenRecordset("Telefoonnummers", dbOpenDynaset) With [rstTelefoonnummers] intCount1 = 0 .FindFirst "[Netnummer]=""" & Me![cboNetnummer] & """ AND [Abonneenummer]=""" & Me![txtAbonneenummer] & """" If (.NoMatch = False) Then Do While (.NoMatch = False) ReDim Preserve typAdressen(intCount1 + 1) typAdressen(intCount1).lngAdresID = ![AdresID] typAdressen(intCount1).datLaatsteMaalGebeld = ![LaatsteMaalGebeld] intCount1 = intCount1 + 1 .FindNext "[Netnummer]=""" & Me![cboNetnummer] & """ AND [Abonneenummer]=""" & Me![txtAbonneenummer] & """" Loop End If .Close End With Set rstTelefoonnummers = Nothing Next intCount2 Debug.Print Time And these are the results: 13:16:44 13:16:45 13:16:45 13:18:20 Ronald. "John W. Vinson" wrote: [Quoted Text] > On Sat, 20 Dec 2008 11:51:00 -0800, Ronald <wit[ at ]yahoo> wrote: > > >Hi. > > > >To select/find data in a table, what will be the fastest method: > >1. By query: "SELECT * FROM Table WHERE Field1=""" & Me!txtField1 & """ AND > >""" Field2=""" & Me!txtField2 & """;" > >And loop through the records found (if any). > > > >Or > >2. Select the table in whole. > >And .FindFirst "Field1=""" & Me!txtField1 & """ AND """ Field2=""" & > >Me!txtField2 & """" and loop using .FindNext > > > >I guess method 2 is the fastest. Is that correct? > > I would expect method 1 to be much faster (if there are indexes on Field1 and > Field2, which would help both methods). The first method will return a > recordset containing only the hits, rather than the whole table. > -- > > John W. Vinson [MVP] >

Re: Which method is fastest? "Jeff Boyce" <JeffBoyce_IF[ at ]msn.com-DISCARD_HYPHEN_TO_END> 12/21/2008 2:56:16 PM Ronald You are asking a "how" question ... Both of the approaches you offer mention looping through a recordset. If you'll describe a bit more about what having the "looped-through recordset" will allow you to do, folks here may be able to offer even more efficient approaches. For example, Access queries (set-oriented) can provide a much more efficient way to process records from a larger recordset than using code to step through (loop) the set. Good luck! -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Ronald" <wit[ at ]yahoo> wrote in message news:686056D9-A9CB-4F4F-9880-EA3F57438155[ at ]microsoft.com... [Quoted Text] > Hi. > > To select/find data in a table, what will be the fastest method: > 1. By query: "SELECT * FROM Table WHERE Field1=""" & Me!txtField1 & """ AND > """ Field2=""" & Me!txtField2 & """;" > And loop through the records found (if any). > > Or > 2. Select the table in whole. > And .FindFirst "Field1=""" & Me!txtField1 & """ AND """ Field2=""" & > Me!txtField2 & """" and loop using .FindNext > > I guess method 2 is the fastest. Is that correct? > > Thanks, > > Ronald.

Re: Which method is fastest? Ronald 12/21/2008 5:06:01 PM Thank you, Jeff, I'll keep it in mind for next time. For now, you can see the exact code I use in my reply to John. "Jeff Boyce" wrote: [Quoted Text] > Ronald > > You are asking a "how" question ... > > Both of the approaches you offer mention looping through a recordset. > > If you'll describe a bit more about what having the "looped-through > recordset" will allow you to do, folks here may be able to offer even more > efficient approaches. For example, Access queries (set-oriented) can > provide a much more efficient way to process records from a larger recordset > than using code to step through (loop) the set. > > Good luck! > > -- > Regards > > Jeff Boyce > www.InformationFutures.net > > Microsoft Office/Access MVP > http://mvp.support.microsoft.com/ > > Microsoft IT Academy Program Mentor > http://microsoftitacademy.com/ > > "Ronald" <wit[ at ]yahoo> wrote in message > news:686056D9-A9CB-4F4F-9880-EA3F57438155[ at ]microsoft.com... > > Hi. > > > > To select/find data in a table, what will be the fastest method: > > 1. By query: "SELECT * FROM Table WHERE Field1=""" & Me!txtField1 & """ > AND > > """ Field2=""" & Me!txtField2 & """;" > > And loop through the records found (if any). > > > > Or > > 2. Select the table in whole. > > And .FindFirst "Field1=""" & Me!txtField1 & """ AND """ Field2=""" & > > Me!txtField2 & """" and loop using .FindNext > > > > I guess method 2 is the fastest. Is that correct? > > > > Thanks, > > > > Ronald. > >