Better practice - Outer Apply vs Sub

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)

Better practice - Outer Apply vs Sub

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-16 : 00:54:18

The following 2 queries provide very similiar statistics, but slightly differant execution plans. Which of the two queries is generally a better practice?



Declare  @mytable table(xval int, Gcode varchar(2), dord int)
insert into @mytable(gcode,dord)
select 'a',100 union all
select 'a',200 union all
select 'a',300 union all
select 'b',400 union all
select 'b',500 union all
select 'c',600 union all
select 'd',700 Union all
select 'a',800 Union all
select 'a',900

--query 1
UPDATE t
SET t.xval= cnt.[Count] + 1
FROm @mYTABLE t
OUTER APPLY (SELECT TOP 1 dord
             FROM @Mytable
             WHERE dord <t.dord
             AND gcode <> t.gcode
             ORDER BY dord DESC)prev
OUTER APPLY (SELECT COUNT(DISTINCT gcode)AS Count
             FROM @Mytable
             WHERE dord<t.dord
             AND (gcode <>t.gcode OR dord<=prev.dord)) cnt

select * from @Mytable

--Query 2
UPDATE t
SET t.xval= (SELECT COUNT(DISTINCT gcode) AS Count
             FROM @Mytable
             WHERE dord<t.dord
             AND (gcode <>t.gcode OR dord<=(SELECT TOP 1 dord
             FROM @Mytable
             WHERE dord <t.dord
             AND gcode <> t.gcode
             ORDER BY dord DESC))) + 1
FROm @mYTABLE t

select * from @Mytable

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 01:01:35

Results from Profiler

Query   CPU   Reads    Writes    Duration
Query1  15     197      2         24
Query2  15     200      2         28

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-16 : 02:09:43

On a large sample set, this outperforms almost all other suggestions.

Declare  @mytable table(xval int, Gcode varchar(2), dord int, primary key (dord))

insert into @mytable(gcode,dord)
select 'a',100 union all
select 'a',200 union all
select 'a',300 union all
select 'b',400 union all
select 'b',500 union all
select 'c',600 union all
select 'd',700 Union all
select 'a',800 Union all
select 'a',900

declare @gcode varchar(2), @xval int

select top 1	@xval = 1,
		@gcode = gcode
from		@mytable
order by	dord

update	@mytable
set	@xval = xval = case when gcode <> @gcode then @xval + 1 else @xval end,
	@gcode = gcode

select	*
from	@mytable

Result from Profiler


CPU	Reads	Writes	Duration
  0	   51	    0	       1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources