For a database developer it is a skill to write a most efficient tuned query.
While query writing we generally come across some like I have written two different queries for same requirement which one is better? Join is better or sub query? When temporary will perform better and when table variable etc. Answer is very simple compare write all possible way which you can write and compare among them. Which one enjoy least that query is better among other.
While query writing we generally come across some like I have written two different queries for same requirement which one is better? Join is better or sub query? When temporary will perform better and when table variable etc. Answer is very simple compare write all possible way which you can write and compare among them. Which one enjoy least that query is better among other.
How to
compare the queries? How to know cost of the queries?
Sql server provides some counter on the basis of
which queries can be compared. They are
logical read, CPU time, IO cost etc.
To get the logical of query first enable:
SET STATISTICS IO ON
Then execute your query. In the messages tab you
will find total logical read. As shown below:
![]() |
Logical Read SQL SERVER |
A query which has less logical read is better than
query with large logical read. Best way to compare the queries is using
execution plan. For example, I want to know, which query will perform better
query 1 or query 2?
--Query 1
SELECT U.ntUserID
,MAX(U.vcUserName) AS vcUserName
,COUNT(*)
ntUserContact
FROM tblUser U
INNER JOIN
tblUserContact UC ON U.ntUserID
= UC.ntUserID
GROUP BY U.ntUserID
--Query 2
SELECT U.ntUserID
,U.vcUserName
,(
SELECT
COUNT(*)
FROM
tblUserContact UC
WHERE
U.ntUserID = UC.ntUserID
)
ntUserContact
FROM tblUser U
Step 1: Enable the actual execution plan. (To enable
it use window shortcut Ctrl + M or click on to Query -> Include Actual
Execution Plan.)
![]() |
ActualExecution Plan |
Step 2:
Execute both the queries in same query page.
![]() |
Compare Query Cost Sql Server |
Step 3:
Click on execution plan tab. At the top of both of the execution plan you will
find cost of the query relative to batch:
![]() |
Compare Execution Plan |
So cost of
query relative to batch is 59% and query 2 is 41%. It mean query 2 is better than query 1 by
(59-41) * 100/59 % = 30%.
What are measures causes of badly or inefficient written
transact sql queries?
May be
developer are not familiar with all the weapons which are provided in
Transact-SQL. Some of the developers are familiar with very less numbers
clauses like SELECT, WHERE, FROM, TOP, GROUP BY, ORDER BY, HAVING, INTO etc.
and they want to full fill the entire requirement of application by using only those
operators. But they are not familiar
with clauses like OUTPUT, CROSS APPLY, PIVOT, FOR XML, MERGE etc. due to which
they are not able to use appropriate clause while query writing which leads
inefficient query writing.
May they
are not familiar with how a query is processed and cost of each physical
operators etc.
Bad database schema design: Developer has not any alternatives and they
are force to write bad script instead of correcting the schema design.
Here we are
going to discuss about how to write an efficient transact sql script. How a
query is processed. While query writing what should be considered and what
should be with help of some very common real world examples.
No comments:
Post a Comment