Passthrough vs Local Know your Queries! (1 Viewer)

I am transferring a database to SQL for both performance and scale-ability issues.

We are retaining the Access User Front-Ends due to the familiarity that the User has.

The Front ends make use of (local) Queries based on the underlying (SQL) data.

I am trying to establish what is the best method of replicating these queries?

Better to keep the Front end local queries or somehow call a view in the SQL Server? or would a formulated pass-through query bee better.

Your thoughts are much appreciated

This thread has some very similar questions and answers - note the bits about UDF's and VBA specific functions.
https://www.access-programmers.co.uk/forums/showthread.php?t=301830

it depends - well designed tables/relationships combined with well written queries should be as fast in SQL Server as in Access, subject to network performance issues. So other than any changes to table/field names you would have your linked tables linked to sql server tables rather than access tables and queries would therefore remain the same. Depending on your version of access you can use the upsizing wizard.

Things like SQL Server using % while Access uses * for Like criteria will be converted automatically by the ODBC driver

SQL Server does have a wider language range so there may be things that will run better rewritten as a passthrough query. SQL Server does not support Crosstab queries, so these would need to be rewritten

performance is usually the last reason for moving to sql server, it can be slower due to the server being shared across many apps for example. See this link (post #1) about things to consider - if you haven't addressed them before moving to sql server, you will almost certainly need to address them as part of the move.

https://www.access-programmers.co.uk/forums/showthread.php?t=291269&highlight=indexes

Don't start with the assumption that you have to rewrite the queries as pass-through. Pass through queries are not updateable so if you use them for bound forms, you would have to rewrite all the code in the form to not use events and to use your own methods to control when/what to update.

You will find during your testing that there are choke points. Start with those and see if you can figure out what is causing the slowness. Usually it will be that you have something in the query that cannot be directly converted to T-SQL and so rather than passing the query through to the server for processing, the ODBC driver is modifying the query to return large amounts of data and finishing the processing locally.

Some things are just automatic though. For example, bulk deletes should always be done with pass-through queries UNLESS you really want the option to back out the delete that Access offers? Using a Truncate Table in a pass-through eliminates the logging which saves a huge amount of overhead. Changing bulk deletes to pass-through queries just changes the SQL. It doesn't change any of the surrounding logic as would happen if you changed a form to be bound to a pass-through query.

I am much obliged to you all for your comments and thoughts. More Homework me thinks !
Thanks Guys