Access using SQL tables slow.

[ThomH]

We have converted an Access Database with over 150 tables and 120 queries to SQL, with Access as the front end.
Reports (that contain 15 or more linked tables or queries) are now taking more than twice as long to run. A report that took 30 seconds with Access tables are now taking 10 minutes using the SQL tables.

Looking for any suggestions.

Thank you!

 

[donutman]

Let's see the query on the worst case scenario. Also try running the queries in QA to see how fast they are. Have you created the indexes...set the PKs?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

I experienced a similar thing when I converted my app to SQL Server (from Access). The thing that made a difference for me was to create indexes on the SQL tables.

You need to be a little careful determining which columns to put the indexes on because if you have too many indexes, it can hinder performance on inserts, updates, and deletes.

Of course, indexes may not be your problem, but it is worth looking in to.

 

[cmmrfrds]

If you are using ODBC linked tables in the Access app it will be slow since the data must be downloaded to the desktop to complete the joins. Do not link the tables in Access instead do pass-through queries so the work can be done on the server and only the resultset passed back to the app.

Please check the indexes it may speed up the query, but that will not solve the problem of using linked tables. Linked table queries are okay for small tables and few joins.