Question Pass-through query vs stored procedure vs SQL view

[MS1234]

Hi, I've been programming in Access for umpteen years but have never looked at pass-through queries before. I want to see if it will optimize my db performance in any way.

I have multiple SQL tables/views linked via ODBC to my front end Access db.

As a typical example:

SQL view written to pull data from multiple SQL tables.
Link SQL view via ODBC to Access db.
Query written in Access using the linked SQL view, with additional manipulation on the Access query to restrict records returned (eg Vendor = forms!Vendors.VendorID).

This is how I have the majority of my Access queries - pulling a SQL view then restricting data output based on values selected on forms.

So my question is - would Pass-Through queries be a better option for these, and if so how would it deal with the form based parameters.

Alternatively would stored procedures be a better bet for me? I've never used these either but I would imagine you can write the stored procedure, pass the parameter to it from access (eg Vendor = forms!Vendors.VendorID), the stored procedure returns the relevant data as the result in Access.

Thanks

 

[Pat Hartman]

Access attempts to "pass through" every query so although pass-through queries are slightly more efficient because Jet/ACE doesn't have to process them first, the data retrieval should be pretty much the same.

Make sure you are not using VBA and UDF functions anywhere in the queries except for the Select Clause. Having these functions in the select clause isn't a problem because Access can send the rest of the query to the server and just apply the functions to the resultset which should be very small.

Make sure your SQL server database is optimized and indexed. Periodically compact the FE so that Access picks up the latest statistics for the BE.

Make sure that all your forms are bound to queries and those queries return only the rows and columns needed. Old style Access apps link to tables and rely on filters to get the user to the data they want to see. Properly constructed client/server apps don't download entire tables to be filtered locally, they use queries with criteria to bring down only what the user wants to see "now".

I use pass-through queries for bulk deletes and some bulk updates. I also sometimes use a sp for a complex report. The heart of the app and all the forms are bound to linked tables or views.

Binding forms to pass-through queries will render them not-updateable and that opens a whole new can of worms.

 

[BigHappyDaddy]

Access attempts to "pass through" every query so although pass-through queries are slightly more efficient because Jet/ACE doesn't have to process them first, the data retrieval should be pretty much the same.

I was surprised to read this only because in my previous workplace, pass through select queries were MUCH faster than the same query in Jet/ACE using linked tables. Especially on larger tables (100,000+ records per table).

Am I misunderstanding something when comparing pass through queries versus "local" queries on licked tables???

I created pass through queries for either SQL Server or AS400 databases.

 

[Pat Hartman]

Typically when working with bound forms, your recordsets would never be 100,000 rows. I also mentioned that Pass-through queries are generally better for bulk deletes and bulk updates. The reason that bulk updates tend to be slow in Access is because Access always gives you the option to change your mind so it does a lot of work with transactions to provide that capability. pass-through queries just do the update, no second chances.

So, for what the OP was talking about changing, Pass through queries won't add any appreciable speed and they will make your forms not updateable.