Query Execution Plan

Fig 1

Fig 2

Often in database programming, we face the issue of query optimization. Though the Database Management System (DBMS) has one component known as "Query Optimizer" which takes care of optimization but programmer himself needs to improve the query to get the results efficiently.
While query optimization, there should be some benchmark / indicator to show the efficiency of the query which can help the programmer to decide which part to look on for optimization.

SQL Server 2000 has one such feature know as "Query Execution Plan".
One more terminology needs to be explained before going into that.

Cost of query - It figures how much CPU and I/O the query utilises. An optimized query should take less cost. Using this factor queries are compared for efficiency.

Query Execution Plan -
It means the way the SQL Server runs our query, how it optimizes and what are the costs for different actions of a query.

One very simple example -

There are two tables -Test1 and Test2 with 2 columns id and name each.
Selection the ids and name from these 2 table using following query -

Select T1.id, T2.Name
from test1 T1, test2 T2
where
T1.id = T2.id

Using the Query execution Plan we can do analysis of the costs occurred for the actions inside the query -

Check Fig 1.
It says -

1. Table scan 40 % cost - For scanning the tables T1 and T2, it costs 40 % of the entire query.

2. Hash Matching - For matching the fetched records as per the where clause it takes
19 %.

3. Select 0 % - Finally selecting the records.

(Where does the remaining 1 % go? )


Lets add something to the query - Say sorting -

Select T1.id, T2.Name
from test1 T1, test2 T2
where
T1.id = T2.id
order by T1.id

Check Fig 2.
Now the result has -

Scanning - 36 % each

Hash matching - 17 %

Sorting - This is new and because of the order by clause. Takes 11 %.

Finally select action.

(Its correct as 100 % total appears)

That’s all for this from me. Now your suggestion and advice.


Bye.
Avaneesh S. Tiwari
26 June '08