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
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.