Simple • Straightforward

So easy a Caveman
Programmer can do it.

›› Let's Get Started

Why Optimize?

How Data is Stored

Returning data is one thing, but returning it efficiently is quite another. I hope to impart on you that writing code and procedures that return the correct data is only half the battle. When first writing the queries, the database you write them against is usually small and will always return results quickly. Later on down the road this is a different story. What once returned in less than a second, can later take minutes or even hours. The most important question you need to ask yourself is, how big will this database get?

SQL data is typically stored on a local hard for the server, or possibly on a SAN. When thinking about how data gets returned, it is import to know exactly what goes on with your hard drive.

Hard Drive

There are 2 important things you need to know about how hard drives store data. One is that is stored on platters with data placed on those platters similar to an old record, it goes around starting from the center and working its way out in larger and larger circles. The second piece is that there are multiple platters that the arm needs to search through to find the data it needs. The platters will spin around in circles so the arm can read the data off of those platters.



And this means...

  1. You can only store so much data on the hard drive before it maxes out.
  2. When you have to return large amounts of data for calculations, those hard drives are going to have to spin the platters as fast as they can to get the data set you need. The more they have to spin, the longer it will take to get your data set returned.


So When to Optimize?

Well, it depends. If you have a database that is small and has no large tables to pull from… then there really is not a point. But what is small? I would say if any table has over 10,000 rows, then you might need to take a look at getting things time run faster.

On the other hand, if you know that the database is going to get large (large is 500+ MB), then you should prepare for the future. I regularly work on databases that start off under 200MB, but over the years will grow to 30GB-50GB.

So remember those rules - 10,000 rows or 500+ MB.

Now that lets get into some of our tools.





Comments

Name:
Title:   
Commenting Currently Disabled


Jason
1/26/2013 10:55:59 AM
Re: Optimization


Hi Rajan, I am not sure I understand the question. When you mentioned DDL and DML, are you meaning: - DDL: CREATE, ALTER, DROP - DML: SELECT, INSERT, UPDATE, DELETE If you are, then all the same rules apply that I have listed on the site. If you have some specific questions, definitely feel free to email me at contact@sqloptimizationsschool.com.

1/25/2013 6:38:38 AM



Rajan
1/25/2013 6:38:35 AM
optimization


sir please add how to optimize if i am doing more than one DDL/DML through Template table
Wilma
8/4/2011 10:08:33 AM



I had no idea how to approach this before-now I'm lecokd and loaded.
Jason
7/16/2011 7:10:56 PM
Button Added


Makes sense. I added the button navigation.
Arto Ahlstedt
7/13/2011 3:35:01 PM
Query Engine (peer-nominated)


On page http://www.sqloptimizationsschool.com/Pages/Basic%20Concepts/Why%20Optimize.aspx I expected to see a link named "Next" or even better "Next - Execution plans".
Jason
6/20/2011 1:02:55 PM
Please let me know...


Definitely let me know if there are things you would like added or changed. I will review your comments and add in the changes.