How to Optimize SQL Queries Part I

How to Optimize SQL Queries Part I

· JumpStart with the DB Schema and see if it makes sense
Most often, Databases have bad designs and are not normalized. This can greatly affect the speed of your Database. As a general case, learn the 3 Normal Forms and apply them at all times. The normal forms above 3rd Normal Form are often called de-normalization forms but what this really means is that they break some rules to make the Database faster.
What I suggest is to stick to the 3rd normal form except if you are a DBA (which means you know subsequent forms and know what you’re doing). Normalization after the 3rd NF is often done at a later time, not during design.

· Ask only what you need

o As a thumb rule never use Never use “Select *” — Specify only the fields you need; it will be faster and will use less bandwidth.
o Joins are expensive in terms of time.
o Make sure that you use all the keys that relate the two tables together
o always try to join on indexed fields.

· a rule of thumb, if your query is longer than a second, you can probably optimize it.
o Start with the Queries that are most often used as well as the Queries that take the most time to execute.

· Indexes
o add indexes on fields you often use for filtering in the Where Clauses.
o Be careful with adding indexes because they need to be maintained by the database. If you do many updates on that field, maintaining indexes might take more time than it saves.
o Try innovating In the Internet world, read-only tables are very common. When a table is read-only, you can add indexes with less negative impact because indexes don’t need to be maintained (or only rarely need maintenance).
· Use SPz
o Calculations in code (VB, Java, C++, …) are not as fast as SP in most cases
o You should do as much as possible in the Query or Stored Procedure. Going back and forth(Programming lang ) is plain stupid
o Sometimes, your Stored Procedure will be better off creating a temporary table, inserting data in it and returning it than going back and forth 10,000 times. You might have a slower query that saves time on a greater number of records or that saves bandwidth.

· Joins
Damn good example
We have two tables — Products (ProductID, DescID, Price) and Description(DescID, LanguageID, Text). There are 100,000 Products and unfortunately we need them all.
There are 100 languages (LangID = 1 = English). We only want the English descriptions for the products.
We are expecting 100 000 Products (ProductName, Price).
First try:
Select D.Text As ProductName, P.Price
From Products P INNER JOIN Description D On P.DescID = D.DescID
Where D.LangID = 1
That works but it will be really slow because your DB needs to match 100,000 records with 10,000,000 records and then filter that Where LangID = 1.
The solution is to filter On LangID = 1 before joining the tables.
Corrected:
Select D.Text As ProductName, P.Price
From (Select DescID, Text From Description Where D.LangID = 1) D
INNER JOIN Products P On D.DescID = P.DescID

Now, that will be much faster. You should also make that query a Stored Procedure to make it faster.

Advertisements

1 Comment »

RSS feed for comments on this post. TrackBack URI

  1. Good Work guys keep it up !


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: