select @@version =
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0
(Build 6002: Service Pack 2)
------------------------
-- Create test tables --
------------------------
use tempdb
go
set nocount on
go
if object_id('tblPrimary') is not null drop table dbo.tblPrimary
go
if object_id('tblSecondary') is not null drop table dbo.tblSecondary
go
create table dbo.tblPrimary (RecID int identity(1,1)
constraint PK_tblPri primary key clustered,
EntityID int,
Acct char(3))
go
create table dbo.tblSecondary (RecID int identity(1,1)
constraint PK_tblSec primary key clustered,
AdminDate datetime)
go
--------------------------
-- Populate test tables --
--------------------------
declare @Acct char(3), @Counter int; set @Counter = 1
while @Counter <= 100000
begin
select @Acct = case when datepart(ms,getdate()) % 2 = 0 then 'AAA' else 'BBB' end
insert into dbo.tblPrimary (EntityID, Acct) values (@Counter%5000, @Acct)
insert into dbo.tblSecondary(AdminDate)
select cast(cast( getdate() as int)-((datepart(s,getdate())% 5)+5) *
rand(cast(cast(newid()as binary(8))as int))as datetime) AdminDate
if @Counter % 500 = 0 print 'loop ' + cast(@counter as varchar(7))
set @Counter = @Counter + 1
end
create index IDX_tblPrimary_EntityID
on dbo.tblPrimary (EntityID)
go
create index IDX_tblSecondary_AdminDate
on dbo.tblSecondary(AdminDate desc)
go
-------------------------
-- Create Views, Sproc --
-------------------------
-----------------------------------------------
-- The COTS-supplied view; uses a Ranking --
-- function with ORDER BY clause. Works fine --
-- with explicit values, not so much with --
-- variables --
-----------------------------------------------
if object_id('vwMostRecentDate_ByAcct') is not null
drop view dbo.vwMostRecentDate_ByAcct
go
create view dbo.vwMostRecentDate_ByAcct
as
select
RecID,
AdminDate,
EntityID,
Acct
FROM (select p.RecID, s.AdminDate, p.Acct, p.EntityID,
ROW_NUMBER() over (partition by EntityID, Acct
Order by AdminDate desc) as RowNum
FROM dbo.tblPrimary p with(nolock)
inner join dbo.tblSecondary s with(nolock) on s.RecID = p.RecID
) t where t.RowNum = 1
go
---------------------------------------------------
-- Re-worked view; plays nice with sp_executesql --
-- Works fine however the data are pulled --
---------------------------------------------------
if object_id('vwMostRecentDate_ByAcct_WorkAround') is not null
drop view dbo.vwMostRecentDate_ByAcct_WorkAround
go
create view dbo.vwMostRecentDate_ByAcct_WorkAround
as
select
p.RecID,
s.AdminDate,
p.EntityID,
p.Acct
FROM dbo.tblPrimary p with(nolock)
inner join (select p.EntityID, p.Acct, max(s.AdminDate) AdminDate
FROM dbo.tblPrimary p with(nolock)
inner join dbo.tblSecondary s with(nolock) on s.RecID = p.RecID
group by p.EntityID, p.Acct
) x on x.EntityID = p.EntityID and x.Acct = p.Acct
inner join dbo.tblSecondary s on s.RecID = p.RecID
and s.AdminDate = x.AdminDate
go
------------------------------------------
-- Using a stored proc, with Row_Number --
-- This is just academic, since the app --
-- doesn't use sprocs --
------------------------------------------
if object_id('spMostRecentDate_ByAcct') is not null
drop proc dbo.spMostRecentDate_ByAcct
go
create proc dbo.spMostRecentDate_ByAcct (@EntityID int, @Acct char(3))
as
select
RecID,
AdminDate,
EntityID,
Acct
FROM (select p.RecID, s.AdminDate, p.Acct, p.EntityID,
ROW_NUMBER() over (partition by EntityID, Acct
Order by AdminDate desc) as RowNum
FROM dbo.tblPrimary p with(nolock)
inner join dbo.tblSecondary s with(nolock) on s.RecID = p.RecID
where p.EntityID = @EntityID and p.Acct = @Acct
) t where t.RowNum = 1
go
---------------
-- Run tests --
---------------
set statistics io on
set statistics time on
---------------------------
-- INDEX SEEKS = FAST!!! --
---------------------------
--Stored proc, using Row_Number --
exec dbo.spMostRecentDate_ByAcct @EntityID =6, @Acct='aaa'
--Not using Row_number, with parameters
declare @EntityID int, @Acct char(3); select @EntityID = 6, @Acct = 'AAA'
select *, 'Fast, no Row_Number, w/variables' [TestCase]
from dbo.vwMostRecentDate_ByAcct_WorkAround
where entityid = @EntityID and Acct = @Acct
--Using Row_Number
select *, 'Fast, uses Row_Number, explicit values' [TestCase]
from dbo.vwMostRecentDate_ByAcct where entityid = 6 and Acct = 'aaa'
--Not using Row_Number
select *, 'Fast, no Row_Number, explicit values' [TestCase]
from dbo.vwMostRecentDate_ByAcct_WorkAround where entityid = 6 and Acct = 'aaa'
--Not using Row_number, using sp_executesql
exec sp_executesql N'
SELECT *, ''Fast, no Row_number, sp_executesql'' [TestCase]
from dbo.vwMostRecentDate_ByAcct_WorkAround
where entityid = @EntityID and Acct = @Acct',
N'@EntityID int, @Acct nchar(3)',
@EntityID = 6, @Acct=N'AAA'
go
--------------------------------
-- TABLE SCAN, SORT = SLOW!!! --
--------------------------------
--Using Row_Number, with Parameters
declare @EntityID int, @Acct char(3); select @EntityID = 6, @Acct = 'AAA'
select *, 'Slow, uses Row_Number, w/variables' [TestCase]
from dbo.vwMostRecentDate_ByAcct
where entityid = @EntityID and Acct = @Acct
--Using Row_Number, using sp_executesql
exec sp_executesql N'
SELECT *, ''Slow, uses Row_number, sp_executesql'' [TestCase]
from dbo.vwMostRecentDate_ByAcct
where entityid = @EntityID and Acct = @Acct',
N'@EntityID int, @Acct nchar(3)',
@EntityID = 6, @Acct=N'AAA'
