Performance tuning of an Access front-end with a SQL Server back-end?

[PNC]

Hi,

I have received a new assignment to:
- Speed up the queries run by Access to the SQL Server database
- Minimize the lock contention for about 35 users
- Minimize the network traffic, b/c when access runs certain queries it downloads the entire table

Any suggestions to solve this problem?
Should I build more indexes?
Should I use views built directly on SQL Server?
Should I use Crystal reports for the reports and VB or ASP for the forms?

 

[SQLSister]

First, switch your data access to using stored procedures not views. Stored procedures are more efficinet in general. They can also accept input varaibles which views cannot.

Are you accessing using DAO ro ADO? ADO is more efficient.

Most query inefficencies are caused by either poor indexing or inefficient code. In addition, if you are using Acess queries right now, just changing them to stored procedures should spped things up considerably as the queries not longer have to be processed by both Acces and SQL Server and there is no transalation between the differnt flavors of SQL.

Indexes should be on fields which are used in joins and fields which are used in where clauses.

Do you really need the whole table inthose queries currently getting them? A where clause will improve performance.

Never use Select * in your queries, only retun the minimum amount of data you need for the particular function.

Lots of things to do to improve efficieny. I suggest you get a book on Performance Tuning as this is a quite complex subject.

Questions about posting. See faq183-874

 

[pweegar]

If, as you implied, you were thinking about going to either VB or asp to build your forms, why not take it one more step and eliminate the access tables completely? Use SQL's DTS fucntionality to transform your access data into sql tables. Why? First off you would eliminate downloading the entire access table. 2) You can optimize your quries. SQL Server has a query analyzer that helps with the optimization. 3) You woun't need to use pass thru queries between Access nad SQL. 4) You can build views for your data so that the users see only what they need to see. 5)SQL server is better suited for performance with the number of users you have.

You will also want to consider how much growth you expect. SQL is built to handle large amounts of data, where Access can, but as the tables grow, you will see a further reduction in network performance and Access does have a limit as to it's size.

Where I work, I have one database that is approx. 7.5 Gig. Wouldn't be able to do this with Access. One table alone has just over 1 million rows!

Just a few things to think about.

 

[PNC]

Thanks.

 

[oharab]

Just for the record, Access doesn't always download all the records every time. It's a bit more sophisticated than that!
If indexes are available it will use them to work out which rows it needs.

Where I work, I have one database that is approx. 7.5 Gig. Wouldn't be able to do this with Access. One table alone has just over 1 million rows!

correct, access couldn't store all this data, but, as PNC has done, it could easily link to the SQL server and view it or manipulate it or report on it.
I'm guessing, though you don't say, you are using linked tables in Access and building your queries in access on them. In the long term, Stored Procedures are the way to go, as SQLSister suggests, but a short term, quick win way would be to re-write the queries in SQLServer SQL (which isn't too far from Access SQL) and use pass-through queries.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a