How to identify Effective Query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to identify Effective Query?

Author

Topic

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-12-06 : 05:57:04

I run the execution plan of two SELECT Queries. It shows like as below

which one is effective query?

Sample1 :
Query 1: Query Cost (relative to the batch): 49%
Query 2: Query Cost (relative to the batch): 51%

Sample 2:
Query 1: Query Cost (relative to the batch): 40%
Query 2: Query Cost (relative to the batch): 60%

====================================================
you realize you've made a mistake, take immediate steps to correct it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-06 : 08:42:35

All the query cost adds upto 100% at last. Show us everything so we can analyze.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-12-06 : 09:52:17

SELECT * FROM Table1 WHERE T1_ID IN
(SELECT T2_ID FROM Table2 WHERE T2_Identifier IN (SELECT T3_Identifier FROM Table3))

SELECT Table1.* FROM Table1 WHERE T1_ID IN
(SELECT Table2.T2_ID FROM Table2, Table3 WHERE Table2.T2_Identifier = Table3.T3_Identifier)

SELECT Table1.* FROM Table1, Table2, Table3 WHERE Table1.T1_ID = Table2.T2_ID
AND Table2.T2_Identifier = Table3.T3_Identifier

Which query is effective? I have index for T1_ID, T2_ID, T2_Identifier, T3_Identifier.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-06 : 10:02:55

I would write like this:

SELECT T1.* FROM Table1 T1
inner join Table2 T2
on T1.T1_ID = T2.T2_ID
inner join Table3 T3
on T2.T2_Identifier = T3.T3_Identifier

Show the execution plan .

gvphubli
Yak Posting Veteran

54 Posts

Posted - 2008-12-06 : 22:03:25

never use sub-query, always try to use the joins as demo'ed above

TechnologyYogi
http://gvphubli.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 01:02:15

quote:
Originally posted by karthickbabu

SELECT * FROM Table1 WHERE T1_ID IN
(SELECT T2_ID FROM Table2 WHERE T2_Identifier IN (SELECT T3_Identifier FROM Table3))

SELECT Table1.* FROM Table1 WHERE T1_ID IN
(SELECT Table2.T2_ID FROM Table2, Table3 WHERE Table2.T2_Identifier = Table3.T3_Identifier)

SELECT Table1.* FROM Table1, Table2, Table3 WHERE Table1.T1_ID = Table2.T2_ID
AND Table2.T2_Identifier = Table3.T3_Identifier

Which query is effective? I have index for T1_ID, T2_ID, T2_Identifier, T3_Identifier.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

i think third query will be better. Also avoid using old join syntax and learn using ANSI syntax as old syntax wont be supported in future versions of sql server. so use new equivalent query as suggested by Sodeep

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-07 : 02:03:00

The three queries will not return same result if there is a duplicate value in either t1_id or t2_id column.

E 12°55'05.63"
N 56°04'39.26"

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-12-07 : 05:26:02

That is what I was going to say. It *might* work, but may not. Mostly with this simple stuff, if you can write the same thing in multiple ways then it will generally resolve to the same plan.

gvphubli - what you say is not true. You have to get the data right at least!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-07 : 11:58:08

quote:
Originally posted by LoztInSpace

That is what I was going to say. It *might* work, but may not. Mostly with this simple stuff, if you can write the same thing in multiple ways then it will generally resolve to the same plan.

In my experience ,I would say Subqueries and Correlated sub-queries are bad when you are dealing with large number of records.
gvphubli - what you say is not true. You have to get the data right at least!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-12-07 : 22:02:49

Sodeep - It doesn't matter how 'good' or 'bad' you think certain elements of SQL may be, every construct is there for a reason and they uniquely solve a particular data problem. My point is that you cannot just throw in a substitute approach to a problem and hope it is going to work!
The join above is a great example of that. If the FK has a unique, non-null constraint then a join will work ,and becuase it it known, the optimiser may well be able to implement the query as that. If there are no such constraints then it is a completely different query and the query plan must obey the requirements as stated (as should we as advisors).
See also "if IN is too slow use EXISTS (or vice verca)" myth - they are not the same and are not always interchangable.

Granted, the multiple solutions line does get a bit blurry with the new windowed functions.

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-12-08 : 01:40:01

Thanks for your comments, I follow the third query but using ANSI Syntax.

====================================================
you realize you've made a mistake, take immediate steps to correct it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources