To Sort or not To Sort (Speeding up Queries)

[Lil'' Rascal]

Hello all,

I had a wonderment this morning whilst designing a query.....

Case) You have 6 fields you're selecting from a table:

tbltest.field1, tblTest.field2, tblTest.field3, tblTest.field4,
tblTest.field5, tblTest.field6

Example)

Private Sub cmdButton1_Click()

Dim strSQL AS String
Dim rs AS DAO.Recordset

strSQL= "SELECT tblTest.field1, tblTest.field3, tblTest.field5, " & _
"tblTest.field6, tblTest.field2, tblTest.field4 " & _
"FROM tblTest;"

Set rs = CurrentDb.OpenRecordset (strSQL, dbOpenDynaset)

**** whatever the code may be****

Set rs = Nothing

End Sub

Q.) If, while SELECTing these fields in your SQL query for a Recordset does
the order in which you arrange the fields in the query language effect the
overall speed of the query? Let's say that there were 20 fields you were
SELECTing from. Would it make a difference then?

Thanks,
Lil' Rascal

 

[John Spencer]

The order of the fields in the SELECT clause has nothing to do with the speed
of query execution.

The number of fields returned may effect the speed of getting the results
since more data has to be sent if you are returning many fields in a row
versus returning one field in a row. However, even there unless you are
returning a massive number of rows the difference in speed would be close to
unmeasurable.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Lil'''''''' Rascal]

Thanks John!

I didn't think it would, but you never know till you ask.

Thanks again,
Lil' Rascal