Pass Through to SQL Server Optimization Questions II

[tcb]

This is a test in running a pass through to SQL Server query vs the
same query run to linked SQL Server tables.

The pass through query runs much faster but loops through the
recordset much slower. What accounts for this?

The results of my test (Function DoTimer below) are here. I realize
that the results gleaned from the recordset loop could be done in its
own query, but the point of this is just to test.

5. q_PassThrough: 3.75 Total Time: 19.3125
Rows: 58532 - SalesAllTotal: 8121654735.25297
Rows: 58532 - Looping through recordset cumulative total time:
4.859375 seconds.
q_PassThrough - AVG Time: 3.8625 - AVG Loop through recordset time:
0.971875

Access/Jet:

5. q_Access: 31.9375 Total Time: 160.7344
Rows: 58532 - SalesAllTotal: 8121654735.25297
Rows: 58532 - Looping through recordset cumulative total time: 0.625
seconds.
q_Access - AVG Time: 32.14687 - AVG Loop through recordset time: 0.125


The SQL for both the Q_PassThrough and the Q_Access query is:

SELECT tSales.WDate, tVendors.VendorName, tDepartments.DeptDescrip,
Sum(tSales.Sales_All) AS SumOfSales_All
FROM (tSales LEFT JOIN tVendors ON tSales.Vendor=tVendors.VendorID)
LEFT JOIN tDepartments ON tSales.Dept=tDepartments.DeptID
GROUP BY tSales.WDate, tVendors.VendorName, tDepartments.DeptDescrip
ORDER BY tSales.WDate, tVendors.VendorName;


Function doTimer()

Dim sngStart As Single 'start time
Dim sngEnd As Single 'end time
Dim sngElapsed As Single 'elapsed time
Dim sngElapsedTotal As Single 'total elapsed time
Dim sngAVGtime As Single 'average time
Dim sngStart2 As Single 'start time
Dim sngEnd2 As Single 'end time
Dim sngElapsed2 As Single 'elapsed time
Dim sngElapsedTotal2 As Single 'total elapsed time
Dim sngAVGtime2 As Single 'average time

Dim d_SalesAll As Double
Dim d_SalesAllTotal As Double

Dim qName As String
'qName = "q_PassThrough"
qName = "q_Access"

DoCmd.Hourglass True

Dim X, Y, YT As Double

For X = 1 To 5 ' Set up repetitions.

sngStart = Timer ' Get start time.

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset(qName)

sngStart2 = Timer

Do Until rs.EOF

Y = 1
d_SalesAll = rs!SumOfSales_All
rs.MoveNext
d_SalesAllTotal = d_SalesAllTotal + d_SalesAll
YT = YT + 1

Loop

sngEnd2 = Timer
sngElapsed2 = Format(sngEnd2 - sngStart2) ' Elapsed time
sngElapsedTotal2 = sngElapsedTotal2 + sngElapsed2

rs.Close

sngEnd = Timer ' Get end time.

sngElapsed = Format(sngEnd - sngStart) ' Elapsed time

sngElapsedTotal = sngElapsedTotal + sngElapsed

Debug.Print X & ". " & qName & ": " & sngElapsed & " Total Time: "
& sngElapsedTotal 'total elapsed time
Debug.Print "Rows: " & YT & " - SalesAllTotal: " & d_SalesAllTotal
Debug.Print "Rows: " & YT & " - Looping through recordset
cumulative total time: " & sngElapsedTotal2 & " seconds."

d_SalesAll = 0
d_SalesAllTotal = 0
Y = 0
YT = 0

Next X

X = X - 1

sngAVGtime = sngElapsedTotal / X
sngAVGtime2 = sngElapsedTotal2 / X

Debug.Print qName & " - AVG Time: " & sngAVGtime & " AVG Loop
through recordset time: " & sngAVGtime2

DoCmd.Hourglass False

End Function

 

[Lance]

Just a guess..

But passthroughs are run on the server while ODBC linked tables are pulled
into memory when queries are run against them. Once they're in the client
machine's memory they'd be faster than querying the server again and again.