Which one is faster and best

Question

Which one is faster and best

thava

SSCrazy

Points: 2275
More actions
April 24, 2010 at 7:58 am

#223918

you people are rocking here!
Recently i have some query which pickup the same result set :unsure: :unsure: :unsure:
i want to know which one is the best one and why
SELECT * FROM salhdr h,saldet d WHERE h.SalId =d.SalId AND d.ItemId=1425
SELECT * FROM SalHdr h INNER JOIN saldet d ON d.SalId = h.SalId WHERE d.ItemId=1425
SELECT * FROM SalHdr h,(SELECT * FROM SalDet d WHERE d.ItemId =1425)x WHERE x.SalId=h.SalId
SELECT * FROM SalHdr h INNER JOIN(SELECT * FROM SalDet d WHERE d.ItemId =1425)x ON x.SalId=h.SalId
where salhdr is the parent table with primary key salid and the child table saldet has no primary key but it is related with salhdr (i.e) salid is the foreign key
i am confused here please some one explain a brief please
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why don't you run them and see which is fastest? If you find one is faster/slower and you don't know why, post the execution plan and someone's sure to have an explanation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

thava SSCrazy Points: 2275 More actions · Answer 2

that's great immediate reply,

i have attached it

i execute this 4 statement,

i didn't know which one is better

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

thava (4/24/2010)
i didn't know which one is better

Which one ran faster? Which one used hte least CPU? Which did the least IOs? Use either SQL profiler or STATISTICS IO and STATISTICS TIME to get those stats.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

thava SSCrazy Points: 2275 More actions · Answer 4

ok, now using the sql profiler i found that the first query is faster and the loas t one is lettle bit of faster that the first one so the order is

4

1

2

3

but now when i run the query

again

it shows me some other order

4

3

2

1

now could you please tell me the reason

why this happen

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Other stuff running on the server, network delays, etc. You haven't given any numbers, but if the differences are small, then they can be ignored. Generally one would run queries multiple times, average the results and see if the differences are significant or not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vidya_pande SSCertifiable Points: 5275 More actions · Answer 6

run

DBCC FREEPROCCACHE

and

DBCC DROPCLEANBUFFERS

while running the queries again to test the performance. This will make sure you are comparing perfoamnce with correct values

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 7

vidya_pande (4/24/2010)
run
DBCC FREEPROCCACHE
and
DBCC DROPCLEANBUFFERS
while running the queries again to test the performance. This will make sure you are comparing perfoamnce with correct values

I'm not sure I'd do either on a production server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

thava (4/24/2010)
you people are rocking here!
Recently i have some query which pickup the same result set :unsure: :unsure: :unsure:
i want to know which one is the best one and why
SELECT * FROM salhdr h,saldet d WHERE h.SalId =d.SalId AND d.ItemId=1425
SELECT * FROM SalHdr h INNER JOIN saldet d ON d.SalId = h.SalId WHERE d.ItemId=1425
SELECT * FROM SalHdr h,(SELECT * FROM SalDet d WHERE d.ItemId =1425)x WHERE x.SalId=h.SalId
SELECT * FROM SalHdr h INNER JOIN(SELECT * FROM SalDet d WHERE d.ItemId =1425)x ON x.SalId=h.SalId
where salhdr is the parent table with primary key salid and the child table saldet has no primary key but it is related with salhdr (i.e) salid is the foreign key
i am confused here please some one explain a brief please

i would guess you are playing with small data thats y results are almost same. additionally you can take it other way , what bad you find in the query like scan/lookup , table spool etc. these itmes can give you idea

OR

you can try

SET STATISTICS IO ON

YOu queries

SET STATISTICS IO OFF

this can also give some idea.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)