Access to SQL Server Migration - is there any need to migrate queries? (1 Viewer)

[jacko6]

Let's say we have a typical Access application setup - Access front end on PCs and back end on a network file server. Assume the app has 100 queries used on various forms (as the form recordset, for listboxes, combos etc). Queries are also used as recordsets for reports.

When migrating an Access backend to SQL Server the recommendation is to migrate queries too (where possible). I appreciate there may be performance gains in doing so, but are there actually any performance issues with not migrating queries? Without migrating queries would performance be any worse than with the original Access backend?

 

[Minty]

It depends.

Certain queries will perform very well with the new back-end data source. Some will possibly be awful.

A lot will depend on how the query is constructed, and other factors such as local vba functions that aren't native to SQL Server.
I'm afraid it's very much a question of suck it a see.

You certainly don't have to convert them to views, but you might want to convert some of them.
As a very vague rule of thumb, the more joins you have in a query the more likely you are going to want to convert them.

 

[pbaldy]

Agreed; I generally leave queries in Access until performance dictates otherwise. Then depending on need/situation/mood convert to pass through query, stored procedure, table-valued function, etc.

 

[Pat Hartman]

is there any need to migrate queries?​

No. Some of the queries might need to be modified if they are using UDF or VBA functions that do not have direct translations to T-SQL functions. Some of the queries might need to be converted to Pass-through queries. In very rare situations, most likely for reports, you might need a stored procedure. Sometimes you will find that views will speed up certain types of joins.

In general,
1. All queries should select the minimum number of columns and rows to satisfy the immediate user request.
2. Forms should never be bound to tables or to naked queries (queries without a WHERE clause)
3. Access makes every effort to "pass through" all queries. YOU can prevent that from happening though if you don't understand how UDF and VBA queries impact the query.
4. Make sure that indexes are appropriate. REMEMBER, if you used RI to define relationships, Access created a hidden index on each of the FK fields so don't duplicate that index. Over indexing can be as bad as under indexing. One affects one type of action, the other affects a different type of action.

Those are the basics.

Start with not changing any query. Look for slowness and work from there. You will almost certainly have to make modifications to your forms if they use filters rather than queries with criteria to limit the number of rows returned.

 

[GPGeorge]

You might invest some time watching this video, which covers a lot of the issues around migrating tables from Access to SQL Server, or to another server-based database.

Long story short, as Pat already pointed out, there is a lot to handle in the first place, so migrating queries, just for the sake of migrating them, is probably far down the list of things to do. If, after the migration, you encounter performance problems, that would be the time to judiciously start looking at certain queries as candidates for conversion to pass-thrus, views or stored procedures.

 

[jacko6]

Hi George, I'm not seeing a link to the video you refer to.

 

[kentgorrell]

@Jackoo6 - this is the link to Geroge's post with a link to the video
Migrating tables to SQL Comparison