Query Tuning – Getting it Right the First Time

    By: Mrs. Janis Griffin on Jul 01, 2014

    This article provides the best methods for query tuning by utilizing Response Time Analysis (RTA) and SQL Diagramming techniques.

    Whether you are a developer or DBA, this article will outline a method for determining the best approach for tuning a query every time by utilizing Response Time Analysis (RTA) and SQL Diagramming techniques. Regardless of the complexity of the statement, this quick and systematic approach will lead you down the correct tuning path with no guessing. If you are a beginner or expert, this approach will save you countless hours tuning a query.

    Challenges of Tuning
    Before we start down the path of how to tune, let’s look at the many challenges of tuning that often leave the DBA and/or developer frustrated with their results. Let face it, SQL tuning is difficult. To do it correctly, you or the people on your team need to be very familiar with many different aspects of query. From a technical standpoint, you need to understand the execution plan, all the steps SAP ASE will take to execute the SQL, and how the data will be accessed. You also need to be familiar with SQL design concepts because sometimes tuning the SQL means rewriting it. When you are working with the end users, you need to understand how the application is used and why they are running certain queries. For example, why do they fill in that field, or what do they do with the information on that screen, etc... Understanding the purpose of the SQL/app will help you make better decisions down the road on how to tune it and will go a long way in putting attention to your tuning project. After all, many people are impatient, but SQL tuning takes time. That’s why working with other people is also about putting a face on your tuning project. Instead of the users saying something like “I’ve been complaining about this problem for weeks and the DBA team says they’re doing something, but no one knows what.”, they might say “I’ve been working with Jan from the DBA group and she definitely asks a lot of questions and seems to care about my experience.”

    Another huge challenge is that there are a large number of SQL statements in a database, so how do you know you’re working on the right one? I’ll talk about this in more detail, but this is also where the end users come in and can help you focus on the correct things. Often, they can show you the SQLs that affect them the most, or they know of a specific screen in an application that is slow. Instead of worrying about 100s or 1000s of SQLs running in the database, find the ones that affect the end user’s performance.

    Tuning is also difficult because all SQL statements are different. Just because you solved the last problem in 30 minutes by tuning a certain way, it doesn’t mean the next project is that easy or can be tuned the same way.

    Plus, some companies in general don’t care about performance. As long as the SQL gives the correct results, they don’t care how long it took. I believe some people just get used to “status quo” – “I always press this button the first thing in the morning and then go get coffee, because I know it takes an hour”. Bad performance becomes a way of life and sometimes people get mad when the app works efficiently as they have to do more.

    Finally, SQL tuning can be never ending. After tuning one SQL, another one will pop up. Also, as workloads and data change over time, there will always be a next performance problem. However, once you tune something and people notice, they’ll start coming out of the woodwork to get their app tuned as well which could be a good thing for your career.


    Login to read the article. Not a member? Create a free account!





    Copyright © 2014 ISUG-TECH. All Rights Reserved
    All material, files, logos and trademarks within this site are copyright their respective organizations

    Terms of Service - Privacy Policy - Contact the Help Desk