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.
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
This question is way too broad for a single EE question, but here's a couple of thoughts...
I HIGHLY recommend brining in an experienced SQL developer to review your new SQL database and make performance improvements.  Being an Access guy doing this in SQL is way out of your league, especially since the migration assistant is going to miss a lot of things such as modeling improvements (i.e. int should really be tinyint to use less memory), indexing opportunities, design flaws, varchar vs. char, etc.) This is not a criticism, I was once there myself.
If it helps I have an article called Migrating your Access Queries to SQL Server Transact-SQL to help with syntax.  I have ten other SQL Server-related articles published by EE, and there are many truly excellent experts that also have written great SQL articles.
SQL views are the same as Access queries but without the ability to pass parameters such as [Enter a date here].  For that you'll need to manually convert your SQL view to a Stored Procedure, which also means that in the Access front-end you'll be writing VBA code to call it.
Stored Procedures have much more functionality than Access query(ies), which results in more efficient code, so I highly recommend going down this road.
The Access Linked Table Manager can also link SQL Server Views and treats them just like tables.
0
mlcktmguyAuthor Commented:
JIm, Thanks for the input.  The article you wrote is very helpful.  I realize there is no specific answer to my questions, just looking for some input from people that have been thru this before.  I'll split the points among helpful responses.

The app uses VB (ADO) to work with the many current Access tables so transitioning to SQL may be smoother.
0
PatHartmanCommented:
The biggest difference between developing Access apps linked to Jet/ACE and those linked to SQL Server or other RDBMS is how you use forms.

Access is very closely tied to the Jet/ACE database engines and many people don't even know the difference so they call everything "Access" which is technically incorrect.  When people first develop Access apps the simplest solution seems to be to bind forms directly to tables or sometimes to queries with no selection criteria.  Jet/ACE handle this just fine but as you are finding out, it is extremely inefficient when the BE is something else.  Think about how these forms work.  Access opens the form as soon as it has sufficient data to populate the form and then behind the scenes retrieves a keyset for all the rest of the records.  The user then uses built in filters to drill down to the records he wants to see.  When the data is coming from SQL Server, Access still opens the form pretty quickly but it then has to bring down the entire recordset from the server and it does it a few records at a time.  This can take quite some time if the recordset is large and makes the form sluggish.  Once all the data has been retrieved from the server, filtering is done locally.  This type of process defeats the purpose of using a server based BE since the server is not actually doing anything for you.  To get the most efficiency out of your forms, bind them to queries with selection criteria and limit the number of rows returned to the bare minimum.  This way, the server does the work of searching and filtering and you eliminate all the network traffic required to schlep all that data around.  All of my main forms are bound to queries that include criteria that references combos or text boxes on forms.  Occasionally the users need to do more global searching.  In those cases, I use search forms that bring down a set of records and the user then clicks on one to open the edit form.  So, in no case does an editable form ever open to more than a single record.

I frequently create views to optimize certain commonly run queries that either search for types of data (open orders only) or join several tables that are always used together or both.

Although pass-through queries are always at least slightly faster, there is no need to bind your forms to them except in rare instances.  Doing that would render the forms not updateable so unless you have time and absolutely need to, I would not take on the task of writing crud code and making my forms essentially unbound for update purposes.

Access makes every effort to pass-through every query.  You can defeat this by using VBA and UDF functions in certain clauses among other things.  So, do some reading on optimizing Access for client/server to help develop good techniques.

The place where I almost always use pass-through queries is for bulk deletes/updates.  These queries take much longer as straight Access queries because of the transaction processing overhead Access adds.  Have you ever noticed that Access always asks you if you want to apply the updates?  The way it does that is to wrap them in a transaction that can be rolled back if you say no.  On one of my projects, I work with tables that contain hundreds of thousands and some even millions of rows.  When I run updates using those tables, Access doesn't ask me, it tells me up front that there isn't enough memory to back out the change and then applies the update directly if I say OK.

On very rare occasions, I have used stored procedures for batch processing or complicated reporting but I don't start there.

Start by fixing the forms and removing any VBA or UDF functions from your queries EXCEPT in the SELECT clause. Then make sure that when you ran the upsizing wizard all your indexes got transferred.  You need to be especially certain that indexes were created for ALL foreign keys and all commonly used search fields.

If that doesn't speed up the app enough, try views (make sure they include the PK of EVERY table in the join).  When you link to the view, Access will popup a dialog asking you to pick a unique identifier.  Make sure you pick ALL the PKs in the view so that each row has a unique identifier.  This will make the view updateable.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Eric ShermanAccountant/DeveloperCommented:
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
0
PatHartmanCommented:
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.
0
Eric ShermanAccountant/DeveloperCommented:
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
0
mlcktmguyAuthor Commented:
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:

http://www.experts-exchange.com/questions/28696816/Using-SQL-Stored-Procedure-From-Access-VB-Code.html
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.