Wednesday, July 7, 2010

Performance tuning #1

I have taken a long break from updating this blog.
To appease my mind, I am going to update a series of posts for Performance tuning, as it is going on in my project work.
 
When it comes to performance tuning, we cannot stick to a certain set of rules. It varies based on the data you are dealing with.
Although, we can create a baseline and address issues based on scenarios we face on a day to day basis.
 
Feel free to comment and ask questions. I will try and address them as soon as I can.
 
  1. Utilizing Teradata’s Parallel Architecture:
If you understand what happens in the background, you will be able to make your query work its best. So, try and run explain plan on your query before executing it and see how the PE(Parsing Engine) has planned to execute it.
Understand the Key-words in Explain plan. I will have to write a more detailed post on this topic. But for now, let us go on with the highlights
  1. Understanding Resource consumption:
Resource that you consume can be directly related to dollars. Be aware and frugal about the resources you use.
Following are the factors you need to know and check from time to time:
  1. CPU consumption
  2. Parallel Efficiency / Hot amp percentage
  3. Spool usage
  1. Help the Parser:
Since the architecture has been made to be intelligent, we have to give it some respect J. You can help the parser understand data you are dealing with, by collecting statistics. But you need to be careful when you do so, due to 2 reasons:
  1. Incorrect stats are worse than not collecting stats, so make sure your stats are not stale(old)
  2. If your dataset changes rapidly in your table, and suppose you are dealing with a lot of data, then collecting stats itself might be resource consuming. So, based on how frequently your table will be accessed, you will have to make the call
  1. Since same SQL can be written in different ways, you will have to know which method is better than which. For eg, creating Volatile table vs Global temp table vs working table. You cannot directly point out which is the best, But I can touch base on the pros and cons and comparison for them.
  2. Take a step back and look at the whole process. Consider how much data you need to keep, how critical is it for your business to get the data soon, how frequently do you need to run your SQL. Most of the times, the ‘big picture’ will give you a lot of answers