Performance Issues, Access Front End, SQL Backend

[BlackOnBlack]

I've got Access Tables that have reached the 2GB limit and I want to do a SQL backend. I've exported my Access table to SQL and linked it up. However, it take 20 times longer to run a query that populates or updates data in SQL than in Access. Anyone have suggestions as to how I can improve this???

 

[shadow9449]

I've got Access Tables that have reached the 2GB limit and I want to do a SQL backend. I've exported my Access table to SQL and linked it up. However, it take 20 times longer to run a query that populates or updates data in SQL than in Access. Anyone have suggestions as to how I can improve this???

Did you replace your more complicated queries (especially with joins on big tables) with stored procedures? Did you make sure that your forms are unbound, or based on queries only displaying the relevant data?

If not, then you won't be improving the performance, as the data still has to be trasferred to the client each time. The savings using SQL Server come from having the server doing the work and passing the filtered results to the client.

SHADOW

 

[SQL_Hell]

Additionally....

Did you go through the upsizing process?

Where are the front ends being run from?

Are you still using the access queries? or do you now have views and sps?

 

[BlackOnBlack]

I went through the upsizing process for the tables. The front ends are being run in Access. Would views and stored procedures improve this? I know access really well, but know little about SQL. Perhaps I should learn.

 

[SQL_Hell]

Hi there,

I would definitely upgrade to views and sp's, I also recomend getting a book called "access projects with microsoft sql server". This will help alot.

I most cases when you run an access query against SQL server the entire contents of the table aer sent to the client and the query does the filtering at the clients end, this is absolutely awful for performance.

Is the front end running from the client? I.E. is there a copy of the front end of each users machine?