Pass Through to SQL Server Optimization Questions

[tcb]

On an autoexec function several recordsets are opened. Some are
queries to local tables, some are to sql server tables.

I have been trying to optimize performance but I haven't seen much
difference in speed between queries run directly against linked SQL
Server tables vs pass through queries. Here is my code for creating
the pass throughs (I have another function OpenRecordSetLocal that
handles queries to tables within the frontend. The ODBC connection
string resides in the query Q_PassThrough.

Am I missing something key? Is there a better way to do this?
Thanks.

Function OpenRecordset(sql As String)

On Error Resume Next

Dim DB As Database, rs As DAO.Recordset
Set DB = CurrentDb()

CurrentDb.QueryDefs("Q_PassThrough").sql = sql

Set rs = DB.OpenRecordset("Q_PassThrough")

Set OpenRecordset = rs

Exit Function

Err_OpenRecordset:
MsgBox Error(Err), vbInformation, "DATA ERROR"
Debug.Print "Data Error"
Exit Function

End Function

 

[Guest]

Firstly, there is no general reason to expect SQL Server to be faster.
On the same hardware, you still have to lookup the data, and load
if from disk - you can't get away from that.

Secondly, SQL Server is designed to handle heavy loads from
multiple connections in a resource constricted environment better
than Access/Jet -- you haven't tested that yet :~)

Thirdly, ODBC defines a restricted set of standard SQL. Any
application that restricts itself to that restricted open standard
should be able to talk to any ODBC driver. If you use Pass-Through
queries instead of ODBC linked tables, you can bypass that restriction.

In particular, ODBC had a restriction of only one Left or Right join
per query. You can have multiple symmetrical inner joins, but only one
Left or Right join. This means that for a complex query, Access/Jet
has to implement most of your Left/Right joins locally when using
linked tables.

There are several important restrictions like that. So the performance
difference you see may depend on the SQL of the query you are
comparing.

Finally, you haven't shown how you are using the recordset. It
may be that all of your performance is swamped by some other
slow process, for example, connecting and disconnecting.

(david)

PS, I see that you have both an Error Handler, and ON ERROR
RESUME NEXT. No point in having both. Also, Error(Err) is
not a good way to get the error string - better to use Err.description.
Also, Err only gets the top error -- better to loop the dbEngine.errors
collection when using linked tables.

 

[tcb]

Thanks for your thoughtful response, things for me to consider this
morning.