efficiency of query approaches

Question

efficiency of query approaches

johnsql-193053

SSCommitted

Points: 1657
More actions
March 20, 2011 at 10:07 am

#247125

Hi
I use the 4 following query approaches to get the same results. I'd like to know which is the best efficient one. "efficient" means the least running time.
Thank you in advance.
create table #g (GroupID int)
create table #t(SKU varchar(50) unique, GroupID int)
insert #g (GroupID)
select 20003 union all
select 10092 union all
select 30005
insert #t (GroupID , SKU )
select 20003, 'S200031' union all
select 20003, 'M200032' union all
select 20003, 'T200033' union all
select 10092, 'A100921' union all
select 10092, 'B100922' union all
select 30005, 'G300051'
--select * from #g
--select * from #t
-- Subquery
select t.*, SKUMain = (select SKU from #t t2 where t2.GroupID=t.GroupID and SKU like '%1')
from #t t
inner join #g g on g.GroupID = t.GroupID
-- CTE
; with tg (SKUMain, GroupID)
AS
(
select SKU as SKUMain, GroupID
from #t t2 where SKU like '%1'
)
select t.*, tg.SKUMain
from #t t
inner join tg on tg.GroupID = t.GroupID
-- derived table
select t.*, tg.SKUMain
from #t t
inner join
(
select SKU as SKUMain, GroupID
from #t t2
where SKU like '%1'
) tg on tg.GroupID = t.GroupID
-- CROSS APPLY
select t.*, tg.SKUMain
from #t t
cross apply
(
select SKU as SKUMain, GroupID
from #t t2
where SKU like '%1' and t2.GroupID = t.GroupID
) tg
drop table #g
drop table #t

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

What does your testing show? Have you looked at the execution plans, duration and IO statistics of each?

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

johnsql-193053 SSCommitted Points: 1657 More actions · Answer 2

GilaMonster (3/20/2011)
What does your testing show? Have you looked at the execution plans, duration and IO statistics of each?

The 4 queries return the same result like:

SKUGroupIDSKUMain

S20003120003S200031

M20003220003S200031

T20003320003S200031

A10092110092A100921

B10092210092A100921

G30005130005G300051

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

You haven't really answered the question - please look at it again. There is an article here which may give you some useful information about query analysis.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

johnsql-193053 (3/20/2011)
GilaMonster (3/20/2011)
What does your testing show? Have you looked at the execution plans, duration and IO statistics of each?
The 4 queries return the same result like:
SKUGroupIDSKUMain
S20003120003S200031
M20003220003S200031
T20003320003S200031
A10092110092A100921
B10092210092A100921
G30005130005G300051

You asked which is the most effecient insofar as run time. It seems like a simple enough task for you to run. SSMS provides a run duration clock in the lower right corner.

--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)

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

johnsql-193053 (3/20/2011)
GilaMonster (3/20/2011)
What does your testing show? Have you looked at the execution plans, duration and IO statistics of each?
The 4 queries return the same result like:
SKUGroupIDSKUMain
S20003120003S200031
M20003220003S200031
T20003320003S200031
A10092110092A100921
B10092210092A100921
G30005130005G300051

Yes, they will.

You asked which perform best. The best way to answer that question it to test the queries out, on large enough data sets that any differences show up. Management Studio's query timer is not all that accurate, so rather use STATISTICS TIME, along with STATISTICS IO to see. Also have a look at the execution plan of each query.

If you don't understand why a query performs better or worse than another, then ask, but just to see which is which, the absolute best way is to test them out and see.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Your question is incomplete. The key piece of missing information is "how many rows of data in each table"? What is spiffy fast for 10 rows in one table and 1M rows in the other maybe an absolute DOG when it is 1M rows by 10 rows. Or 100M rows by 10M rows. Query plan performance is HUGELY dependent on the number of rows processed.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service