Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011»»

The Numbers Table Expand / Collapse
Author
Message
Posted Friday, November 28, 2008 8:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
I'm not sure what's going on... I ran the code you modified and here's what I got...

===== Set based method with Date table =====

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 931 ms.
====================================================================================================
===== Method with RBAR looping function =====

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:
CPU time = 10531 ms, elapsed time = 11474 ms.

In other words, on my machine, your changes helped the RBAR solution a bit, but the set bsed solution still ate it up.

I also noticed that you changed my table reference from RefDate to RefDates which means you didn't use the same demo table as I which, of course, means another difference that I can't see. This is going to be a tough one to resolve.

It would be interesting to see what other folks get as a result from "our" test code.

Anyone care to assist?


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610378
Posted Friday, November 28, 2008 8:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:56 AM
Points: 338, Visits: 1,422
Joe Celko (11/28/2008)
Let me make an appeal to simple logic.

You build a look-up table once (and if you did a proper calendar table, you also benefit from other uses). It probably has a clustered index or key to take advantage of the sequential nature of time. It probably has other constraints to ensure data integrity.

You have a table-valued function that creates a new table every time, without an index or key. And certainly without constraints for the optimizer to use.

There are (n) sessions in the DB. The Calendar table is cached in main storage -- 20, 50 or even 100 years is small. The function is re-computed over and over.

So before you get to the query, the look-up table has won on both speed and integrity.


There is more than one way to skin a cat.
Post #610381
Posted Friday, November 28, 2008 8:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 7, 2014 4:27 AM
Points: 324, Visits: 2,212
I did some test myself and using the test tables Jeff Moden created. I made a function that is not RBAR and beats both compared solutions on my server bigtime. If others can confirm this on their systems, it will disprove the claim that it is RBAR that sped things up!

The Smileys in the code are a forum bug, and are actually ")" characters.


1. Add the following tally function to your Jeff's test suite:


-- Tally table function (24 bit, large enaugh for general purpose use)
--
create function dbo.tfnTally( @max int ) returns table
as
return
(
with
nQ( N ) as
(
select 0 union all -- 0
select 0 union all -- 1
select 0 union all -- 2
select 0 union all -- 3
select 0 union all -- 4
select 0 union all -- 5
select 0 union all -- 6
select 0 union all -- 7
select 0 union all -- 8
select 0 union all -- 9
select 0 union all -- 10
select 0 union all -- 11
select 0 union all -- 12
select 0 union all -- 13
select 0 union all -- 14
select 0 -- 15
)
select top ( isnull( @max, 0 ) )
row_number() over ( order by anchor.constant ) as n
from
( select 0 as constant ) as anchor
cross join nQ n1 -- 16 ( 4 bit)
cross join nQ n2 -- 256 ( 8 bit)
cross join nQ n3 -- 4096 (12 bit)
cross join nQ n4 -- 65536 (16 bit)
cross join nQ n5 -- 1048576 (20 bit)
cross join nQ n6 -- 16777216 (24 bit)
)
;
go


2. Add the following RBAR improvement (drop in replacement) to your Jeff's test suite:


CREATE FUNCTION [dbo].[GenRefDates_Indexed]   
(
@StartDate datetime,
@EndDate datetime
)
RETURNS @table TABLE (Date datetime not null primary key clustered with fillfactor = 100)
AS BEGIN

DECLARE @tmpDate datetime
SET @tmpDate = Convert(varchar, @StartDate, 112)
SET @EndDate = Convert(varchar, @EndDate, 112)

WHILE @tmpDate <= @EndDate
BEGIN
INSERT INTO @table VALUES (@tmpDate)
SET @tmpDate = DateAdd(dd, 1, @tmpDate)
END

RETURN
END


3. Add the following non RBAR function (drop in replacement) to your Jeff's test suite:


create function dbo.GenRefDates_noRBAR
(
@StartDate datetime
, @EndDate datetime
)
returns @table table
(
Date datetime not null
)
as begin

insert into @table( Date )
select
dateadd( dd, tally.N - 1, @StartDate )
from
dbo.tfnTally( datediff( dd, @StartDate, @EndDate ) + 1 ) as tally
;
return
end
go


4. Add the following non RBAR improved function (drop in replacement) to your Jeff's test suite:

create function dbo.GenRefDates_noRBAR_indexed
(
@StartDate datetime
, @EndDate datetime
)
returns @table table
(
Date datetime not null primary key clustered with fillfactor = 100
)
as begin

insert into @table( Date )
select
dateadd( dd, tally.N - 1, @StartDate )
from
dbo.tfnTally( datediff( dd, @StartDate, @EndDate ) + 1 ) as tally
;
return
end
go


Add the following tests at the end of the Jeff Moden's existing test script

--===== Method with RBAR looping function (indexed result this time)
PRINT '===== Method with RBAR looping function (indexed result this time) ====='
SET STATISTICS TIME ON
DECLARE @MonthYear DATETIME,
@MonthStart DATETIME,
@MonthEnd DATETIME,
@NextMonthStart DATETIME

SELECT @MonthYear = 'May 2008',
@MonthStart = @MonthYear,
@NextMonthStart = DATEADD(mm,1,@MonthStart),
@MonthEnd = @NextMonthStart-1

SELECT d.Date, e.SomeInt, SUM(e.SomeMoney) AS Total
FROM dbo.Event e
RIGHT OUTER JOIN
dbo.GenRefDates_Indexed(@MonthStart,@MonthEnd) d
ON e.SomeDate = d.Date

GROUP BY d.Date, e.SomeInt
ORDER BY d.Date, e.SomeInt
SET STATISTICS TIME OFF
GO
PRINT REPLICATE('=',100)
GO

--===== Method with temp table generation (non-indexed) using and numbers/tally function
PRINT '===== Method with NO-RBAR (non-indexed) function ====='
SET STATISTICS TIME ON
DECLARE @MonthYear DATETIME,
@MonthStart DATETIME,
@MonthEnd DATETIME,
@NextMonthStart DATETIME

SELECT @MonthYear = 'May 2008',
@MonthStart = @MonthYear,
@NextMonthStart = DATEADD(mm,1,@MonthStart),
@MonthEnd = @NextMonthStart-1

SELECT d.Date, e.SomeInt, SUM(e.SomeMoney) AS Total
FROM dbo.Event e
RIGHT OUTER JOIN
dbo.GenRefDates_noRBAR(@MonthStart,@MonthEnd) d
ON e.SomeDate = d.Date

GROUP BY d.Date, e.SomeInt
ORDER BY d.Date, e.SomeInt
SET STATISTICS TIME OFF
GO
PRINT REPLICATE('=',100)
GO

--===== Method with temp table generation (indexed) using and numbers/tally function
PRINT '===== Method with NO-RBAR (indexed) function ====='
SET STATISTICS TIME ON
DECLARE @MonthYear DATETIME,
@MonthStart DATETIME,
@MonthEnd DATETIME,
@NextMonthStart DATETIME

SELECT @MonthYear = 'May 2008',
@MonthStart = @MonthYear,
@NextMonthStart = DATEADD(mm,1,@MonthStart),
@MonthEnd = @NextMonthStart-1

SELECT d.Date, e.SomeInt, SUM(e.SomeMoney) AS Total
FROM dbo.Event e
RIGHT OUTER JOIN
dbo.GenRefDates_noRBAR_Indexed(@MonthStart,@MonthEnd) d
ON e.SomeDate = d.Date

GROUP BY d.Date, e.SomeInt
ORDER BY d.Date, e.SomeInt
SET STATISTICS TIME OFF
GO
PRINT REPLICATE('=',100)
GO

You will find that the RBAR version offers NO advantage over the NON-RBAR versions. You will also find that the indexed versions of both RBAR and NON-RBAR perform identical and MUCH faster then their non-indexed versions, take a good look at these functions. This proofs that it is not the RBAR property that is significant here, but indexing and query complexity is as an inline version as below performs worst.

Full inline TVF (performs worst on test code due to complexity of query)

create function dbo.GenRefDates_noRBAR_inline
(
@StartDate datetime
, @EndDate datetime
)
returns table
as
return
(
select
dateadd( dd, tally.N - 1, @StartDate ) as Date
from
dbo.tfnTally( datediff( dd, @StartDate, @EndDate ) + 1 ) as tally
)
;
go


And the accompaning test script addition:

--===== Method with temp table generation (inline) using and numbers/tally function
PRINT '===== Method with NO-RBAR (inline) function ====='
SET STATISTICS TIME ON
DECLARE @MonthYear DATETIME,
@MonthStart DATETIME,
@MonthEnd DATETIME,
@NextMonthStart DATETIME

SELECT @MonthYear = 'May 2008',
@MonthStart = @MonthYear,
@NextMonthStart = DATEADD(mm,1,@MonthStart),
@MonthEnd = @NextMonthStart-1

SELECT d.Date, e.SomeInt, SUM(e.SomeMoney) AS Total
FROM dbo.Event e
RIGHT OUTER JOIN
dbo.GenRefDates_noRBAR_Inline(@MonthStart,@MonthEnd) d
ON e.SomeDate = d.Date

GROUP BY d.Date, e.SomeInt
ORDER BY d.Date, e.SomeInt
SET STATISTICS TIME OFF
GO
PRINT REPLICATE('=',100)
GO

Post #610383
Posted Friday, November 28, 2008 8:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
Joe Celko (11/28/2008)
Let me make an appeal to simple logic.

You build a look-up table once (and if you did a proper calendar table, you also benefit from other uses). It probably has a clustered index or key to take advantage of the sequential nature of time. It probably has other constraints to ensure data integrity.

You have a table-valued function that creates a new table every time, without an index or key. And certainly without constraints for the optimizer to use.

There are (n) sessions in the DB. The Calendar table is cached in main storage -- 20, 50 or even 100 years is small. The function is re-computed over and over.

So before you get to the query, the look-up table has won on both speed and integrity.


That's a good explanation, Joe. Thanks.

The key here is jacRoberts claims and he swears that the function code he is running is faster and has even offered output showing his timings. Either he's a consumate "troll" trying to see how long he can get this thread to last or he actually has something going on with his system that produce the results he claims. If it's the later of the two, then I'm very interested in what the difference between his system and the rest of the world's might be because it would be incredibly useful.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610389
Posted Friday, November 28, 2008 8:22 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
jacRoberts,

I have SET NOCOUNT ON in the test code so I can't do the verification. Pardon me for asking stupid questions, but I have to check... Have you verified that both result sets are returning both identical rows and an identical number of rows?

There's something else going on here and the devil may be in the data...

Using the methods found in the link in my signature, would you post the table creation statements for the Event and RefDate tables that YOU have along with 10 rows for each. Be sure to include all indexes and keys on each, please. This is very interesting and I'm trying to get to the bottom of it because it could be a "fix" for other RBAR problems.

Also, it would be good to know more about the machine and the exact version of SQL Server that you're using to do this. Thanks.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610394
Posted Friday, November 28, 2008 8:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
peter (11/28/2008)
Considdering that for me, just writing a post (let alone a focused one) takes a lot of time and then reading all the work you have put into your argument makes me feel "a bit" humble :).


Heh... thanks for the compliment, Peter. I'm just "dedicated", that's all.

Interesting sidebar... do you know what the difference between being "dedicated" to a cause and being "loyal" to a cause is? You have to think of "ham and eggs"... heh... the chicken is loyal... the pig was dedicated. :P

Did you even sleep?


Ummmm.... ok... new word... guess I'll have to look that up in the dictionary when I get time.


Either way, you made a strong case here that something else must have been wrong in the original solution. Be it the queries themselfs, the modeling/indexing or even not up to date statistics.


I didn't post it, but I tested in many different scenarios... no indexes, reversed indexes, silly indexes, no statistics, etc, etc. The set based solution always beats the RBAR with the data I built. I'm thinking that there's something in the data itself that is causing this. It could be a simple as he's getting smaller rowcounts because there's a "time" element in the dates in his table or something similar. Whatever it is, I'd like to find out because I'm just not seeing it.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610403
Posted Friday, November 28, 2008 8:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 7, 2014 4:27 AM
Points: 324, Visits: 2,212
Play with my test code and it might help explain some things. But best ofcourse is to have a test database online somewhere that is accurate in modeling and in volume. The data problably needs to be made meaningless without altering distributions for obvious reasons!

Lets hear what he comes up with, so far I am sure we helped him shave off even more time (see my earlyer response).
Post #610409
Posted Friday, November 28, 2008 8:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:02 PM
Points: 23,210, Visits: 31,892
Time to compare functions.

In the code below I create a 1,000,000 row Tally table starting at 1. The table has one column, n. This is also clustered primary key.

I then create two functions, the first is the RBAR TVF, the second is a NoRBAR TVF using the Tally table.

I then use these functions to load a Table variable with dates.

-- Create and load 1,000,000 row Tally table
create table dbo.Tally (
n int primary key
);

with TallyLoad as (
select
row_number() over (order by a.object_id) as rownum
from
sys.objects a
cross join sys.objects b
cross join sys.objects c
cross join sys.objects d
)
insert into dbo.Tally
select top (1000000) rownum
from TallyLoad
order by rownum;
go
-- create GenRefDates functions, one RBAR (while loop), one NoRBAR (Tally table)
CREATE FUNCTION [dbo].[GenRefDates_RBAR]
(
@StartDate datetime,
@EndDate datetime
)
RETURNS @table TABLE (ADate datetime)
AS BEGIN

DECLARE @tmpDate datetime
SET @tmpDate = Convert(varchar, @StartDate, 112)
SET @EndDate = Convert(varchar, @EndDate, 112)

WHILE @tmpDate <= @EndDate
BEGIN
INSERT INTO @table VALUES (@tmpDate)
SET @tmpDate = DateAdd(dd, 1, @tmpDate)
END

RETURN
END
go

CREATE FUNCTION [dbo].[GenRefDates_NoRBAR]
(
@StartDate datetime,
@EndDate datetime
)
RETURNS @table TABLE (ADate datetime)
AS BEGIN

DECLARE @tmpDate datetime
SET @tmpDate = Convert(varchar, @StartDate, 112)
SET @EndDate = Convert(varchar, @EndDate, 112)

insert into @table
select
dateadd(dd, n - 1, @StartDate)
from
dbo.Tally
where
n <= datediff(dd, @tmpDate, @EndDate) + 1;

RETURN
END
go

declare @StartTime datetime,
@EndTime datetime,
@StartDate datetime,
@EndDate datetime,
@StrData varchar(256);
declare @MyDates table (
MyDate datetime
);
print '========================================= RBAR TVF =======================================================';
set @StartDate = '2008-11-01';
set @EndDate = '2008-11-30';
set @StartTime = getdate();
set statistics io on;
set statistics time on;
insert into
@MyDates
select
*
from
dbo.GenRefDates_RBAR (@StartDate, @EndDate)
set statistics io off;
set statistics time off;
set @EndTime = getdate();
select @StrData = char(13) + char(10) + '===> ' + 'Start Time: ' + convert(varchar(30), @StartTime, 121) +
' EndTime: ' + convert(varchar(30), @EndTime, 121) + ' Elapsed Time: ' +
convert(varchar(10), datediff(ms,@StartTime, @EndTime)) + ' Record Count: ' +
convert(varchar(10), count(*))
from
@MyDates;
print @StrData;
print '========================================= RBAR TVF =======================================================';
print char(13) + char(10) + char(13) + char(10)
go
declare @StartTime datetime,
@EndTime datetime,
@StartDate datetime,
@EndDate datetime,
@StrData varchar(256);
declare @MyDates table (
MyDate datetime
);
print '========================================= NoRBAR TVF =======================================================';
set @StartDate = '2008-11-01';
set @EndDate = '2008-11-30';
set @StartTime = getdate();
set statistics io on;
set statistics time on;
insert into
@MyDates
select
*
from
dbo.GenRefDates_NoRBAR (@StartDate, @EndDate)
set statistics io off;
set statistics time off;
set @EndTime = getdate();
select @StrData = char(13) + char(10) + '===> ' + 'Start Time: ' + convert(varchar(30), @StartTime, 121) +
' EndTime: ' + convert(varchar(30), @EndTime, 121) + ' Elapsed Time: ' +
convert(varchar(10), datediff(ms,@StartTime, @EndTime)) + ' Record Count: ' +
convert(varchar(10), count(*))
from
@MyDates;
print @StrData;
print '========================================= NoRBAR TVF =======================================================';
print char(13) + char(10) + char(13) + char(10)
go
declare @StartTime datetime,
@EndTime datetime,
@StartDate datetime,
@EndDate datetime,
@StrData varchar(256);
declare @MyDates table (
MyDate datetime
);
print '========================================= RBAR TVF =======================================================';
set @StartDate = '2008-11-01';
set @EndDate = '2009-10-31';
set @StartTime = getdate();
set statistics io on;
set statistics time on;
insert into
@MyDates
select
*
from
dbo.GenRefDates_RBAR (@StartDate, @EndDate)
set statistics io off;
set statistics time off;
set @EndTime = getdate();
select @StrData = char(13) + char(10) + '===> ' + 'Start Time: ' + convert(varchar(30), @StartTime, 121) +
' EndTime: ' + convert(varchar(30), @EndTime, 121) + ' Elapsed Time: ' +
convert(varchar(10), datediff(ms,@StartTime, @EndTime)) + ' Record Count: ' +
convert(varchar(10), count(*))
from
@MyDates;
print @StrData;
print '========================================= RBAR TVF =======================================================';
print char(13) + char(10) + char(13) + char(10)
go
declare @StartTime datetime,
@EndTime datetime,
@StartDate datetime,
@EndDate datetime,
@StrData varchar(256);
declare @MyDates table (
MyDate datetime
);
print '========================================= NoRBAR TVF =======================================================';
set @StartDate = '2008-11-01';
set @EndDate = '2009-10-31';
set @StartTime = getdate();
set statistics io on;
set statistics time on;
insert into
@MyDates
select
*
from
dbo.GenRefDates_NoRBAR (@StartDate, @EndDate)
set statistics io off;
set statistics time off;
set @EndTime = getdate();
select @StrData = char(13) + char(10) + '===> ' + 'Start Time: ' + convert(varchar(30), @StartTime, 121) +
' EndTime: ' + convert(varchar(30), @EndTime, 121) + ' Elapsed Time: ' +
convert(varchar(10), datediff(ms,@StartTime, @EndTime)) + ' Record Count: ' +
convert(varchar(10), count(*))
from
@MyDates;
print @StrData;
print '========================================= NoRBAR TVF =======================================================';
print char(13) + char(10) + char(13) + char(10)
go
declare @StartTime datetime,
@EndTime datetime,
@StartDate datetime,
@EndDate datetime,
@StrData varchar(256);
declare @MyDates table (
MyDate datetime
);
print '========================================= RBAR TVF =======================================================';
set @StartDate = '2008-11-01';
set @EndDate = '2018-10-31';
set @StartTime = getdate();
set statistics io on;
set statistics time on;
insert into
@MyDates
select
*
from
dbo.GenRefDates_RBAR (@StartDate, @EndDate)
set statistics io off;
set statistics time off;
set @EndTime = getdate();
select @StrData = char(13) + char(10) + '===> ' + 'Start Time: ' + convert(varchar(30), @StartTime, 121) +
' EndTime: ' + convert(varchar(30), @EndTime, 121) + ' Elapsed Time: ' +
convert(varchar(10), datediff(ms,@StartTime, @EndTime)) + ' Record Count: ' +
convert(varchar(10), count(*))
from
@MyDates;
print @StrData;
print '========================================= RBAR TVF =======================================================';
print char(13) + char(10) + char(13) + char(10)
go
declare @StartTime datetime,
@EndTime datetime,
@StartDate datetime,
@EndDate datetime,
@StrData varchar(256);
declare @MyDates table (
MyDate datetime
);
print '========================================= NoRBAR TVF =======================================================';
set @StartDate = '2008-11-01';
set @EndDate = '2018-10-31';
set @StartTime = getdate();
set statistics io on;
set statistics time on;
insert into
@MyDates
select
*
from
dbo.GenRefDates_NoRBAR (@StartDate, @EndDate)
set statistics io off;
set statistics time off;
set @EndTime = getdate();
select @StrData = char(13) + char(10) + '===> ' + 'Start Time: ' + convert(varchar(30), @StartTime, 121) +
' EndTime: ' + convert(varchar(30), @EndTime, 121) + ' Elapsed Time: ' +
convert(varchar(10), datediff(ms,@StartTime, @EndTime)) + ' Record Count: ' +
convert(varchar(10), count(*))
from
@MyDates;
print @StrData;
print '========================================= NoRBAR TVF =======================================================';
print char(13) + char(10) + char(13) + char(10)
go

Here are the results of the runs. If you note, the first run was for 1 month, then 1 year, then 10 years. You will note that the NoRBAR function scales extremely well, while the RBAR one, not so well.


========================================= RBAR TVF =======================================================
Table '#793441A2'. Scan count 0, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#5402BCF3'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 14 ms.

(30 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

===> Start Time: 2008-11-28 08:25:57.660 EndTime: 2008-11-28 08:25:57.677 Elapsed Time: 16 Record Count: 30
========================================= RBAR TVF =======================================================


========================================= NoRBAR TVF =======================================================
Table '#7A2865DB'. Scan count 0, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#55EB0565'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.

(30 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

===> Start Time: 2008-11-28 08:25:57.770 EndTime: 2008-11-28 08:25:57.770 Elapsed Time: 0 Record Count: 30
========================================= NoRBAR TVF =======================================================


========================================= RBAR TVF =======================================================
Table '#7B1C8A14'. Scan count 0, logical reads 365, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#5402BCF3'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 147 ms.

(365 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

===> Start Time: 2008-11-28 08:25:57.770 EndTime: 2008-11-28 08:25:57.927 Elapsed Time: 156 Record Count: 365
========================================= RBAR TVF =======================================================


========================================= NoRBAR TVF =======================================================
Table '#7C10AE4D'. Scan count 0, logical reads 365, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#55EB0565'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 9 ms.

(365 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

===> Start Time: 2008-11-28 08:25:57.957 EndTime: 2008-11-28 08:25:57.973 Elapsed Time: 16 Record Count: 365
========================================= NoRBAR TVF =======================================================


========================================= RBAR TVF =======================================================
Table '#7D04D286'. Scan count 0, logical reads 3659, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#5402BCF3'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 656 ms, elapsed time = 1127 ms.

(3652 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

===> Start Time: 2008-11-28 08:25:57.990 EndTime: 2008-11-28 08:25:59.113 Elapsed Time: 1123 Record Count: 3652
========================================= RBAR TVF =======================================================


========================================= NoRBAR TVF =======================================================
Table '#7DF8F6BF'. Scan count 0, logical reads 3659, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#55EB0565'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 77 ms.

(3652 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

===> Start Time: 2008-11-28 08:25:59.113 EndTime: 2008-11-28 08:25:59.190 Elapsed Time: 76 Record Count: 3652
========================================= NoRBAR TVF =======================================================





So, does performance matter, yes. While the code used in the RBAR function works well for a small number of values, it does not work well as the number of values increases.

What will happen is one of two things. Some poor Joe will find the RBAR TVF and simply decide it does exactly what he needs, but he uses it to generate a large number of dates like in my third test for example. Or, some poor Joe will find the RBAR TVF, look at the code, and then decide that is just what he was looking for functionally, and use that code as a template in another stored procedure or TVF, and have if fail miserably due to the large number of values he is generating.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #610416
Posted Friday, November 28, 2008 8:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:18 PM
Points: 36,938, Visits: 31,441
jacroberts (11/28/2008)
Joe Celko (11/28/2008)
Let me make an appeal to simple logic.

You build a look-up table once (and if you did a proper calendar table, you also benefit from other uses). It probably has a clustered index or key to take advantage of the sequential nature of time. It probably has other constraints to ensure data integrity.

You have a table-valued function that creates a new table every time, without an index or key. And certainly without constraints for the optimizer to use.

There are (n) sessions in the DB. The Calendar table is cached in main storage -- 20, 50 or even 100 years is small. The function is re-computed over and over.

So before you get to the query, the look-up table has won on both speed and integrity.


There is more than one way to skin a cat.


Absolutely correct! However, until I see proof in the form of demonstrable code, I still question the very existance of the cat. :P

Like I said, there's something else going on here... let's see if we can get to the bottom of it together. Let's keep the rhetoric down to a minimum because this thread already got close to flaming a couple of times.

All these hypothesis and claims have already been laid out over and over... the ONLY thing that matters now is demonstrable proof in the form of code and data with repeatable results.

So far, none of us has been able to duplicate your claims. In fact, most code offered, thus far, seems to repudiate your claims. You posted a run result that seems to support your claims but it looks like you may have used different data (because ot the table name change you made) and that MAY be the key.

Everybody stick to the subject, eh? Only the code and the data mean anything for this thread now.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610418
Posted Friday, November 28, 2008 8:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 1, 2014 9:56 AM
Points: 338, Visits: 1,422
Jeff Moden (11/28/2008)
I'm not sure what's going on... I ran the code you modified and here's what I got...

===== Set based method with Date table =====

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 931 ms.
====================================================================================================
===== Method with RBAR looping function =====

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

SQL Server Execution Times:
CPU time = 10531 ms, elapsed time = 11474 ms.

In other words, on my machine, your changes helped the RBAR solution a bit, but the set bsed solution still ate it up.

I also noticed that you changed my table reference from RefDate to RefDates which means you didn't use the same demo table as I which, of course, means another difference that I can't see. This is going to be a tough one to resolve.

It would be interesting to see what other folks get as a result from "our" test code.

Anyone care to assist?



I changed the code to use RefDate table

--===== Set based method with Date table
PRINT '===== Set based method with Date table ====='
SET STATISTICS TIME ON
DECLARE @MonthYear DATETIME,
@MonthStart DATETIME,
@MonthEnd DATETIME,
@NextMonthStart DATETIME

SELECT @MonthYear = 'May 2008',
@MonthStart = @MonthYear,
@NextMonthStart = DATEADD(mm,1,@MonthStart),
@MonthEnd = @NextMonthStart-1

SET NOCOUNT ON

SELECT d.Date, e.SomeInt, SUM(e.SomeMoney) AS Total
FROM dbo.Event e
RIGHT OUTER JOIN
dbo.RefDate d
ON e.SomeDate = d.Date
WHERE d.Date >= @MonthStart
AND d.Date < @NextMonthStart
GROUP BY d.Date, e.SomeInt
ORDER BY d.Date, e.SomeInt
SET STATISTICS TIME OFF
GO
PRINT REPLICATE('=',100)
GO
--===== Method with RBAR looping function
PRINT '===== Method with RBAR looping function ====='
SET STATISTICS TIME ON
DECLARE @MonthYear DATETIME,
@MonthStart DATETIME,
@MonthEnd DATETIME,
@NextMonthStart DATETIME

SELECT @MonthYear = 'May 2008',
@MonthStart = @MonthYear,
@NextMonthStart = DATEADD(mm,1,@MonthStart),
@MonthEnd = @NextMonthStart-1

SET NOCOUNT ON

SELECT d.Date, e.SomeInt, SUM(e.SomeMoney) AS Total
FROM dbo.Event e
RIGHT OUTER JOIN
dbo.GenRefDates(@MonthStart,@MonthEnd) d
ON e.SomeDate = d.Date
WHERE d.Date >= @MonthStart
AND d.Date < @NextMonthStart
GROUP BY d.Date, e.SomeInt
ORDER BY d.Date, e.SomeInt
SET STATISTICS TIME OFF
GO

The stats I got were:

===== Set based method with Date table =====

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 296 ms, elapsed time = 375 ms.
====================================================================================================
===== Method with RBAR looping function =====

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 282 ms, elapsed time = 333 ms.

There is a slight variation each time I run it and there really appears to be little difference in the two queries.
The real difference should be noticed with just:

SELECT * FROM GenRefDates('2008-01-01','2008-02-01') d
and
SELECT * FROM RefDate WHERE DATE BETWEEN '2008-01-01' AND '2008-02-01'

The difference in the run time of these two queries is just a couple of milliseconds in favour of table based query.
But my point was that it makes an insignificant difference in this case.
Post #610420
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»

Permissions Expand / Collapse