Performance questions 1

[ghalewood]

Aplogies if these have already been answered, but as you know the search facility is down at the moment

1. Does a select statement that is populating a recordset run faster when only those fields required are named than when * is coded

2. When you run a select statement where only 1 record is on the table does it run faster when TOP 1 is coded. i.e.
is
("SELECT TOP 1 * FROM [competitor_sites] _ WHERE [site_id] = " & rs18roadside!site_id & "&quot

faster than

("SELECT * FROM [competitor_sites] _ WHERE [site_id] = " & rs18roadside!site_id & "&quot

 

[JeremyNYC]

1) Yes.

2) I don't know. Test it. Make a sub that reads the time, makes a recordset to get that value, stores it to a variable, loops through that process 500 times, reads the time, and spits out the time difference. Do it first with one SQL statement and then with the other. Then you'll know. And with any luck, you'll post the results back here, and then we'll know.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[ghalewood]

Should have thought about this myself.

Using the following code I ran each test 3 times, the table used has approx 16000 records in it the site_id is keyed and store is text, result are :-

select top 1 * from = 25/26/25 seconds
select * from = 20/21/20 seconds
select top 1 [store] from = 25/25/25 seconds
select [store] from = 17/19/17 seconds

and without the progress bar for the fastest

select [store] from = 17/17/17 (I would have thought a progress bar would have had more of an overhead)

Sub checktimings()

Dim rsSite As Recordset

Dim myDb As Database
Dim intCount As Integer
Dim dteDiff As Date
Dim dteStarttime As Date
Dim dteEndtime As Date
Dim strProgress As String

Set myDb = CurrentDb()
intCount = 0

strProgress = SysCmd(acSysCmdInitMeter, "Processing Nearest Stores", 15000)
dteStarttime = Now
Do While intCount < 15000
strProgress = SysCmd(acSysCmdUpdateMeter, intCount)

Set rsSite = myDb.OpenRecordset(&quot;SELECT [store] FROM [competitor_sites] _ WHERE [site_id] = 8644 &quot

Set rsSite = Nothing

intCount = intCount + 1
Loop
dteEndtime = Now

dteDiff = dteEndtime - dteStarttime

End Sub