Horkay Blog
The postings on this site are my own and do not represent my Employer's positions, advice or strategies.
Thursday, March 29, 2012

I'm slow in applying the new SQL Server functions.

Recently I ran into a request where I needed to pull the newest record for a related table several joins deep.  I finally decided to dig into and use the new rank function to do it.  It resulted in a much better query plan with many fewer reads.

Here is a simple example of using the rank function vs. using a correlated sub-query with the max() function.  In this simple example there is no cost savings either way that I could find, but real world scenarios are never this simple and you may find like I did that it is time to look at the new functions.  Example below:

use tempdb

go

Create Table ORDERS (ORDERNUMBER int identity(1,1) primary key,REFERENCE int not null,SomeBSColumns varchar(200))

go

insert into ORDERS (REFERENCE,SomeBSColumns) values (1,'order 1'),(2,'order 2'),(3,'order 3'),(4,'order 4')

go

Create Table PhoneData (PhoneKey int identity(1,1) primary key, OrderNumber int not null,NotesXML varchar(1000),TimeTag1 datetime)

go

Create nonclustered index ix_phoneData on PhoneData (orderNumber)

go

insert into PhoneData (OrderNumber,NotesXML,TimeTag1) values

(1,'Not Received','02/20/2012 16:00:00.000')

,(1,'still not received','02/22/2012 16:00:00.000')

,(1,'loud complaint','02/23/2012 16:00:00.000')

,(1,'finally received','02/24/2012 16:00:00.000')

,(3,'order 3 received, but broken','02/26/2012 16:00:00.000')

,(3,'another replacement sent','02/27/2012 16:00:00.000')

,(4,'finally received','02/22/2012 16:00:00.000')

go

-----------------

set statistics io on

go

-- Rank()

SELECT x.* FROM

( SELECT distinct o.ORDERNUMBER,o.REFERENCE, o.SomeBSColumns,

p.NotesXML,p.timetag1,

RANK() over (partition by o.ORDERNUMBER order by p.Timetag1 DESC) as 'RANKING'

FROM ORDERS o

LEFT OUTER JOIN PhoneData p ON o.ORDERNUMBER = p.ORDERNUMBER

) x

WHERE x.RANKING = 1

ORDER BY x.OrderNumber

-- old way

------

Select FIQ.*,p.NotesXML

From (

select orders.ORDERNUMBER,orders.REFERENCE, orders.SomeBSColumns,

( select MAX(TimeTag1) as TimeTag1 From PhoneData pIQ -- Alias - PhoneData Inner Query

where (ORDERS.ORDERNUMBER = pIQ.OrderNumber)

)

as TimeTag1

From ORDERS

) FIQ -- Alias = First-Inner-Query

Left Outer Join PhoneData p on

FIQ.ORDERNUMBER = p.OrderNumber

And p.Timetag1 = FIQ.TimeTag1

Thursday, March 29, 2012 11:52:57 AM (Central Standard Time, UTC-06:00) |  | SQL Server#
Search
Popular Posts
Unpatched Vulnerabiltiy discovered ...
Spring Fornicator brewed...
SQL Agent will not start when a use...
SQL Instance will not fail back to ...
Hash Join vs. Merge Join
Recent Posts
Archive
May, 2019 (1)
April, 2019 (1)
March, 2019 (1)
February, 2019 (1)
January, 2019 (1)
December, 2018 (1)
November, 2018 (3)
October, 2018 (1)
June, 2018 (1)
May, 2018 (3)
April, 2018 (2)
February, 2018 (3)
January, 2018 (3)
November, 2017 (1)
October, 2017 (1)
August, 2017 (1)
June, 2017 (2)
May, 2017 (2)
April, 2017 (2)
March, 2017 (1)
February, 2017 (1)
December, 2016 (2)
October, 2016 (2)
September, 2016 (1)
August, 2016 (1)
July, 2016 (1)
March, 2016 (2)
February, 2016 (3)
December, 2015 (4)
November, 2015 (6)
September, 2015 (1)
August, 2015 (2)
July, 2015 (1)
March, 2015 (2)
January, 2015 (1)
December, 2014 (3)
November, 2014 (1)
July, 2014 (2)
June, 2014 (2)
May, 2014 (3)
April, 2014 (3)
March, 2014 (1)
December, 2013 (1)
October, 2013 (1)
August, 2013 (1)
July, 2013 (1)
June, 2013 (2)
May, 2013 (1)
March, 2013 (3)
February, 2013 (3)
January, 2013 (1)
December, 2012 (3)
November, 2012 (1)
October, 2012 (1)
September, 2012 (1)
August, 2012 (1)
July, 2012 (4)
June, 2012 (3)
April, 2012 (1)
March, 2012 (3)
February, 2012 (3)
January, 2012 (4)
December, 2011 (3)
October, 2011 (2)
September, 2011 (2)
August, 2011 (8)
July, 2011 (4)
June, 2011 (3)
May, 2011 (3)
April, 2011 (1)
March, 2011 (2)
February, 2011 (3)
January, 2011 (1)
September, 2010 (1)
August, 2010 (2)
May, 2010 (2)
April, 2010 (3)
March, 2010 (1)
February, 2010 (4)
January, 2010 (1)
December, 2009 (3)
November, 2009 (2)
October, 2009 (2)
September, 2009 (5)
August, 2009 (4)
July, 2009 (8)
June, 2009 (2)
May, 2009 (3)
April, 2009 (9)
March, 2009 (6)
February, 2009 (3)
January, 2009 (8)
December, 2008 (8)
November, 2008 (4)
October, 2008 (14)
September, 2008 (10)
August, 2008 (7)
July, 2008 (7)
June, 2008 (11)
May, 2008 (14)
April, 2008 (12)
March, 2008 (17)
February, 2008 (10)
January, 2008 (13)
December, 2007 (7)
November, 2007 (8)
Links
Categories
Admin Login
Sign In
Blogroll