SQL Performance and Queries Optimization

 

 

A bit of history: way back in 2000, picking up a little extra cash, I developed a Microsoft Access database for analytical reporting Asbestos and Lead testing Laboratory. I continued to develop and build on that Access Database for the next 6 years, I then took a hiatis and went in to the Peace Corps as a volunteer in Eastern Europe for the next 3+ years. On my way home the company contacted me as they heard I was returning and they wanted me to do a revamp of it. Just returning and having limited funds, I was over joyed to do work on it. With that done, it has served them for almost 5 years, but data has grown, and it has been no secret to any of us that we needed to look at a more robust backend then Microsoft Access. Don't get me wrong, for small data projects Microsoft Access has been one of the best things to come out of Microsoft but the time had come for a stronger data engine. 

Having Incorporated a business in December 2013, and starting on the business development, this is being our for first official development project and the crown jewel of 2014 for us. While the client has opted to continue to use Microsoft Access as the front end, we have moved all data and reports to SQL and SRSS. While initial performance was showing to be a marked improvement over a a Access backend and Access reports but as we developed the back end and build the queries and views we

 started to see some very serious degradation in performance. Luckily, Microsoft has a tool that comes with most versions of SQL 2012 (Not Express) called Database Tuning Advisor. This tool was a life saver. 

How I used it:

Since most of my SRSS reports run off of nested views that perform all the complex calculations needed for the database I simply right clicked the view, and scripted it out to a Select statement.

Next I selected ONLY the Select statement and right clicked on it and Click on Analyze Query in Database Engine Tuning Advisor. This will open up the advisor for you.

 

 When the Database Engine Tuning Advisor opens, it will have Query selected, with the database the query is being run in. you simply click Start Analysis. 

 

 

It will look at all the related tables and how the query functions and make recommendations on what should be done. Review the recommendations, you can check and uncheck what recommendations you would like to have applied. It will also tell you what performance increase you are likely to see from the recommendations.
 

to initiate the selected recomendations, select Actions/Apply Recommendations.
 
An example of this is a report that would take a whopping 45 seconds to run before running this. Using the query it used in the SRSS Designer, I analyzed it and after applying the recommendations it only took 5 seconds to run. To give you some perspective, this report could take up to 40 seconds to run (or more on a slow computer) in access.
 

This website and its content is copyright of ITHierarchy Inc - © ITHierarchy Inc 2013-2015. All rights reserved.

Any redistribution or reproduction of part or all of the contents in any form is prohibited other than the following:

  • you may print or download to a local hard disk extracts for your personal and non-commercial use only
  • you may copy the content to individual third parties for their personal use, but only if you acknowledge the website as the source of the material

You may not, except with our express written permission, distribute or commercially exploit the content. Nor may you transmit it or store it in any other website or other form of electronic retrieval system.

 

;