SQL Server backend, improved perfomance strategies
I am a long time user of Access, since version 2.0. Up to this point all of the back end DB’s were also in Access. I now have a client growing beyond the size limitations of an Access backend. We are moving them to SQL Server. I have never used SQL Server before.
Initially I converted the Access backend to SQL using the migration assistant. I linked the SQL tables into the front end DB and the application ran. However, it is unacceptably slow.
I have already posted SQL related questions on EE and gone thru several SQL books and tutorials. Three techniques stood out to me as having a performance benefit. They are:
Converting Access queries to ‘pass thru’ queries.
Creating SQL Views
Stored Procedures
I understand how to create these items and have done so in ‘play’ mode. I’m really looking for insight from experienced users on what strategy improved performance the most, so I know where to concentrate my time.
For example: For an existing query, what would yield better response? Converting an existing query to a ‘pass thru’ query or replacing the query with a SQL view. I realize both options might not be available depending on the make up the query but for the sake of this question, assume both ‘pass thru’ conversion or converting the entire query to a view is possible. Which would execute more quickly when the app is running?
In what situations is creating a stored procedure the best solution?
I realize there is no one correct answer to my questions, I’m just looking for some direction on making decisions to improve the performance/response of this application, running with a SQL backend.
I am also operating in the real world so I don’t have unlimited time to make this transition. The fewer objects and/or process I have the less I have to totally re-create, the better. But I also want to do it right. I have another new development SQL project coming up soon and would like to know the best techniques.
I'm sure I'll have many more 'using SQL in Access' questions posted in the near future. Any help would be appreciated.
Initially I converted the Access backend to SQL using the migration assistant. I linked the SQL tables into the front end DB and the application ran. However, it is unacceptably slow.
I have already posted SQL related questions on EE and gone thru several SQL books and tutorials. Three techniques stood out to me as having a performance benefit. They are:
Converting Access queries to ‘pass thru’ queries.
Creating SQL Views
Stored Procedures
I understand how to create these items and have done so in ‘play’ mode. I’m really looking for insight from experienced users on what strategy improved performance the most, so I know where to concentrate my time.
For example: For an existing query, what would yield better response? Converting an existing query to a ‘pass thru’ query or replacing the query with a SQL view. I realize both options might not be available depending on the make up the query but for the sake of this question, assume both ‘pass thru’ conversion or converting the entire query to a view is possible. Which would execute more quickly when the app is running?
In what situations is creating a stored procedure the best solution?
I realize there is no one correct answer to my questions, I’m just looking for some direction on making decisions to improve the performance/response of this application, running with a SQL backend.
I am also operating in the real world so I don’t have unlimited time to make this transition. The fewer objects and/or process I have the less I have to totally re-create, the better. But I also want to do it right. I have another new development SQL project coming up soon and would like to know the best techniques.
I'm sure I'll have many more 'using SQL in Access' questions posted in the near future. Any help would be appreciated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have a 15 year old CRM system used by 12 concurrent users all day. Like yours, in about the fifth year they out grew the limitations of an Access back end db. I migrated the server tables to MySQL, changed the queries to Pass-Thru and created a few Views on MySQL. Worked perfectly ... and is still working today. In my experience with performance ... it's all in how your queries are structured to request information from your server. Also, I had much improvement using ADODB Recordsets where appropriate.
ET
ET
etscherman,
Sounds like you didn't start with the typical Access app with bound forms since you can't simply swap access queries for pass-through queries because that would make the forms not updateable.
Sounds like you didn't start with the typical Access app with bound forms since you can't simply swap access queries for pass-through queries because that would make the forms not updateable.
PatHartman ...
I did a lot of re-tooling in the F/E to accommodate performance since that was the main concern. The Data Entry and Inquiry Form Objects were not the main problem and still worked with minor tweaking like opening with the current year transactions only. Then allow the user to select previous years information to be displayed. This is a heavy reporting database with lots of history calculations ... That's where the Pass-Thru queries were used the most and it works very efficiently ... not waiting a long time for reports to generate and pop up to the screen.
ET
I did a lot of re-tooling in the F/E to accommodate performance since that was the main concern. The Data Entry and Inquiry Form Objects were not the main problem and still worked with minor tweaking like opening with the current year transactions only. Then allow the user to select previous years information to be displayed. This is a heavy reporting database with lots of history calculations ... That's where the Pass-Thru queries were used the most and it works very efficiently ... not waiting a long time for reports to generate and pop up to the screen.
ET
ASKER
Thanks for the input and suggestions. I'm sure I'll be posting many more SQL/Access questions, hopefully you can help out on those as well. I have another one posted asking for the syntax to call a stored procedure from Access. I've gotten some general feedback but not exactly what I was looking for yet. Perhaps you can assist on that questions also. This is the address:
https://www.experts-exchange.com/questions/28696816/Using-SQL-Stored-Procedure-From-Access-VB-Code.html
https://www.experts-exchange.com/questions/28696816/Using-SQL-Stored-Procedure-From-Access-VB-Code.html
I know the question is closed, but I'll throw in this:
We do this type of migration work for clients, and I've distilled what we've learned into a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases. It's called "Best of Both Worlds" at our free J Street Downloads Page:
http://www.JStreetTech.com/downloads
It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.
Cheers,
Armen Stein, Access MVP
J Street Technology
We do this type of migration work for clients, and I've distilled what we've learned into a PowerPoint presentation on techniques for using Access as a client-server front-end to SQL Server databases. It's called "Best of Both Worlds" at our free J Street Downloads Page:
http://www.JStreetTech.com/downloads
It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.
Cheers,
Armen Stein, Access MVP
J Street Technology
ASKER
The app uses VB (ADO) to work with the many current Access tables so transitioning to SQL may be smoother.