A Simple Formula to Calculate the ISO Week Number
-
ChrisM@Work (4/8/2013)
How often we see "so how does that work, exactly?" when someone posts an up-to-date solution to their tricky problem. Try explaining this algorithm in the remaining 3 minutes of your lunch break!Thanks Jeff for taking the time to write - for a brilliant algorithm - an equally brilliant explanation. Bookmarked.
I sure do appreciate that kind of feedback, Chris. I guess "how it works" is what made things like the Tally Table article so popular in it's day. I didn't invent it... I just 'splained 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.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) -
ALZDBA (4/8/2013)
It took me some time to crawl back into working position after reading this finding.Completely astonished by the simplicity of the solution.
Huge thank you for notifying and publishing another great help for the community.
Thanks, Johan. I agree. The author of that formula did an amazing job at reducing the formula to such a nice, tight, short, high performance bit o' code. I sat there with my mouth open for an unknown but long period of time when I realized what he'd done.
BTW, when you were crawling around, did you happen to see any of my dust bunnies? After the Tally Oh article, some of them said they were going overseas for an extended vacation. 😛
--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) -
*removed*
N 56°04'39.16"
E 12°55'05.25" -
After further investigation, it seems you don't need the CASE constructor either.
Another simple math trick will do.
SELECT (DATEPART(DAYOFYEAR, DATEDIFF(DAY, -53690, Date) / 7 * 7 - 53687) + 6) / 7N 56°04'39.16"
E 12°55'05.25" -
SwePeso (4/8/2013)
Jeff Moden (4/8/2013)
Thanks for stopping by, Peter. Haven't seen you around much and it's a real pleasure to see that fabulous mind at work here, again.I've been trying to reach you at the email adress I have for you. All emails last year are returned with "Email address unknown".
It hasn't changed in 16 years although some ISP's block it because the old provider that I used to have was famous for spamming folks. I'll send it to you again.
--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) -
Jeff, just FYI...I created the million row table on my slow laptop and tested the code from your article. With a normal SELECT statement it took appx 19-22 secs per run to get a million rows.
Now out of curiosity, I whipped up an iTVF and ran that against the data with a cross apply and it did NOT perform well. Something on the order of 2 minutes or more. That just goes to show that iTVFs are not always the answer. I also re-tested after adding a non-clustered index to the temp table. That dropped about 3 seconds off the ordinary SELECT and about 10 seconds when using the iTVF.
I tried the iTVF both with a WITH SELECT/INSERT INTO and an garden-variety INSERT INTO SELECT and the WITH statement version was the slowest.
Not sure what use anyone might have with that information, but that's my report! 😉
-
Steven Willis (4/8/2013)
Jeff, just FYI...I created the million row table on my slow laptop and tested the code from your article. With a normal SELECT statement it took appx 19-22 secs per run to get a million rows.Now out of curiosity, I whipped up an iTVF and ran that against the data with a cross apply and it did NOT perform well. Something on the order of 2 minutes or more. That just goes to show that iTVFs are not always the answer. I also re-tested after adding a non-clustered index to the temp table. That dropped about 3 seconds off the ordinary SELECT and about 10 seconds when using the iTVF.
I tried the iTVF both with a WITH SELECT/INSERT INTO and an garden-variety INSERT INTO SELECT and the WITH statement version was the slowest.
Not sure what use anyone might have with that information, but that's my report! 😉
Could you post your code, including the itvf you created?
-
Lynn Pettis (4/8/2013)
Steven Willis (4/8/2013)
Jeff, just FYI...I created the million row table on my slow laptop and tested the code from your article. With a normal SELECT statement it took appx 19-22 secs per run to get a million rows.Now out of curiosity, I whipped up an iTVF and ran that against the data with a cross apply and it did NOT perform well. Something on the order of 2 minutes or more. That just goes to show that iTVFs are not always the answer. I also re-tested after adding a non-clustered index to the temp table. That dropped about 3 seconds off the ordinary SELECT and about 10 seconds when using the iTVF.
I tried the iTVF both with a WITH SELECT/INSERT INTO and an garden-variety INSERT INTO SELECT and the WITH statement version was the slowest.
Not sure what use anyone might have with that information, but that's my report! 😉
Could you post your code, including the itvf you created?
Jeff's test script:
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate a million row test table with random dates/times
-- where (01 Jan 2000) <= Date < (01 Jan 2020)
SELECT TOP 1000000
Date = CAST(RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2000','2020')+DATEDIFF(dd,0,'2000') AS DATETIME)
INTO #TestTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
GO
SELECT
tvf.*
FROM
#TestTable tt
CROSS APPLY
dbo.itvfGetWeekNumberFromDate_ISO(tt.Date) tvf
CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO]
(
@dDate DATETIME
)
RETURNS @WeekNumber TABLE
(
[Date] DATE NULL
,[DayOfWeek] VARCHAR(20) NULL
,[ISOWeek] INT NULL
)
WITH SCHEMABINDING
AS
BEGIN
/*
SELECT * FROM dbo.itvfGetWeekNumberFromDate_ISO('2009-12-24')
*/
;WITH cte AS
(
SELECT
CONVERT(VARCHAR(20),@dDate,120) AS [Date]
,LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek]
,(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek]
)
INSERT INTO @WeekNumber
SELECT
Date
,DayOfWeek
,ISOWeek
FROM
cte
RETURN
END
CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v2]
(
@dDate DATETIME
)
RETURNS @WeekNumber TABLE
(
[Date] DATE NULL
,[DayOfWeek] VARCHAR(20) NULL
,[ISOWeek] INT NULL
)
WITH SCHEMABINDING
AS
BEGIN
/*
SELECT * FROM dbo.itvfGetWeekNumberFromDate_ISO_v2('2009-12-24')
*/
INSERT INTO @WeekNumber
SELECT
CONVERT(VARCHAR(20),@dDate,120) AS [Date]
,LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek]
,(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek]
RETURN
END
-
Steven Willis (4/8/2013)
Lynn Pettis (4/8/2013)
Steven Willis (4/8/2013)
Jeff, just FYI...I created the million row table on my slow laptop and tested the code from your article. With a normal SELECT statement it took appx 19-22 secs per run to get a million rows.Now out of curiosity, I whipped up an iTVF and ran that against the data with a cross apply and it did NOT perform well. Something on the order of 2 minutes or more. That just goes to show that iTVFs are not always the answer. I also re-tested after adding a non-clustered index to the temp table. That dropped about 3 seconds off the ordinary SELECT and about 10 seconds when using the iTVF.
I tried the iTVF both with a WITH SELECT/INSERT INTO and an garden-variety INSERT INTO SELECT and the WITH statement version was the slowest.
Not sure what use anyone might have with that information, but that's my report! 😉
Could you post your code, including the itvf you created?
Jeff's test script:
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate a million row test table with random dates/times
-- where (01 Jan 2000) <= Date < (01 Jan 2020)
SELECT TOP 1000000
Date = CAST(RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2000','2020')+DATEDIFF(dd,0,'2000') AS DATETIME)
INTO #TestTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
GO
SELECT
tvf.*
FROM
#TestTable tt
CROSS APPLY
dbo.itvfGetWeekNumberFromDate_ISO(tt.Date) tvf
CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO]
(
@dDate DATETIME
)
RETURNS @WeekNumber TABLE
(
[Date] DATE NULL
,[DayOfWeek] VARCHAR(20) NULL
,[ISOWeek] INT NULL
)
WITH SCHEMABINDING
AS
BEGIN
/*
SELECT * FROM dbo.itvfGetWeekNumberFromDate_ISO('2009-12-24')
*/
;WITH cte AS
(
SELECT
CONVERT(VARCHAR(20),@dDate,120) AS [Date]
,LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek]
,(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek]
)
INSERT INTO @WeekNumber
SELECT
Date
,DayOfWeek
,ISOWeek
FROM
cte
RETURN
END
CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v2]
(
@dDate DATETIME
)
RETURNS @WeekNumber TABLE
(
[Date] DATE NULL
,[DayOfWeek] VARCHAR(20) NULL
,[ISOWeek] INT NULL
)
WITH SCHEMABINDING
AS
BEGIN
/*
SELECT * FROM dbo.itvfGetWeekNumberFromDate_ISO_v2('2009-12-24')
*/
INSERT INTO @WeekNumber
SELECT
CONVERT(VARCHAR(20),@dDate,120) AS [Date]
,LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek]
,(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek]
RETURN
END
I hate to be the bearer of bad news but your function is not an inline table valued function, it is a multi-statement table valued function.
The following is your function rewritten as an inline table valued function:
CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v2]
(
@dDate DATETIME
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURNSELECT
CONVERT(VARCHAR(20),@dDate,120) AS [Date],
LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek],
(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek];
GO
-
Spot on, Lynn. Thanks for the cover. Busy 20 hour day at work yesterday.
--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) -
Lynn Pettis (4/8/2013)
I hate to be the bearer of bad news but your function is not an inline table valued function, it is a multi-statement table valued function.The following is your function rewritten as an inline table valued function:
CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v2]
(
@dDate DATETIME
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURNSELECT
CONVERT(VARCHAR(20),@dDate,120) AS [Date],
LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek],
(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek];
GO
Lynn, of course you're correct. Too many late hours and not enough sleep. :blush:
But I tested the iTVF against the mlTVF and really found no difference. Again, surprising results.
SELECT
CONVERT(VARCHAR(20),tt.Date,120) AS [Date]
,DATENAME(weekday,tt.Date) AS [DayOfWeek]
,(DATEPART(dy,DATEDIFF(dd,0,tt.Date) / 7 * 7 + 3) + 6) / 7 AS [ISOWeek]
FROM
#TestTable tt
CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO]
(
@dDate DATETIME
)
RETURNS @WeekNumber TABLE
(
[Date] DATE NULL
,[DayOfWeek] VARCHAR(20) NULL
,[ISOWeek] INT NULL
)
WITH SCHEMABINDING
AS
BEGIN
INSERT INTO @WeekNumber
SELECT
CONVERT(VARCHAR(20),@dDate,120) AS [Date]
,LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek]
,(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek]
RETURN
END
GO
SELECT
tvf.*
FROM
#TestTable tt
CROSS APPLY
dbo.itvfGetWeekNumberFromDate_ISO(tt.date) tvf
CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v2]
(@dDate DATETIME)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
CONVERT(VARCHAR(20),@dDate,120) AS [Date]
,LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek]
,(DATEPART(dy,DATEDIFF(dd,0,@dDate) / 7 * 7 + 3) + 6) / 7 AS [ISOWeek] ;
GO
SELECT
tvf.*
FROM
#TestTable tt
CROSS APPLY
dbo.itvfGetWeekNumberFromDate_ISO_v2(tt.date) tvf
-
Create an empty database and run the following:
--===== Conditionally drop the test table to make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate a million row test table with random dates/times
-- where (01 Jan 2000) <= Date < (01 Jan 2020)
SELECT TOP 1000000
Date = CAST(RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2000','2020')+DATEDIFF(dd,0,'2000') AS DATETIME)
INTO #TestTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
GO
CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v2]
(
@dDate DATETIME
)
RETURNS @WeekNumber TABLE
(
[Date] DATE NULL
,[DayOfWeek] VARCHAR(20) NULL
,[ISOWeek] INT NULL
)
WITH SCHEMABINDING
AS
BEGIN
/*
SELECT * FROM dbo.itvfGetWeekNumberFromDate_ISO_v2('2009-12-24')
*/
INSERT INTO @WeekNumber
SELECT
CONVERT(VARCHAR(20),@dDate,120) AS [Date]
,LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek]
,(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek]
RETURN
END
GO
CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v3]
(
@dDate DATETIME
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURNSELECT
CONVERT(VARCHAR(20),@dDate,120) AS [Date],
LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek],
(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek];
GO
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @BitBucket1 sql_variant,
@BitBucket2 sql_variant,
@BitBucket3 sql_variant; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
SELECT
@BitBucket1 = tvf.Date,
@BitBucket2 = tvf.DayOfWeek,
@BitBucket3 = tvf.ISOWeek
FROM
#TestTable tt
CROSS APPLY
itvfGetWeekNumberFromDate_ISO_v2(tt.Date) tvf
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @BitBucket1 sql_variant,
@BitBucket2 sql_variant,
@BitBucket3 sql_variant; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
SELECT
@BitBucket1 = tvf.Date,
@BitBucket2 = tvf.DayOfWeek,
@BitBucket3 = tvf.ISOWeek
FROM
#TestTable tt
CROSS APPLY
itvfGetWeekNumberFromDate_ISO_v3(tt.Date) tvf
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @BitBucket1 sql_variant,
@BitBucket2 sql_variant,
@BitBucket3 sql_variant; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
SELECT
BitBucket1 = tvf.Date,
BitBucket2 = tvf.DayOfWeek,
BitBucket3 = tvf.ISOWeek
FROM
#TestTable tt
CROSS APPLY
itvfGetWeekNumberFromDate_ISO_v2(tt.Date) tvf
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO
set nocount on;
--===== Create the timer and bit-bucket variables and start the timer.
DECLARE @BitBucket1 sql_variant,
@BitBucket2 sql_variant,
@BitBucket3 sql_variant; --< change or add variables needed here to eliminate display distortion
Declare @cpu_ int,
@lreads_ bigint,
@eMsec_ bigint,
@Reads_ bigint,
@Writes_ bigint;
declare @CpuMs int,
@LogRds bigint,
@Elapsed bigint,
@Reads bigint,
@Writes bigint;
dbcc freeproccache with no_infomsgs;
dbcc freesystemcache('ALL') with no_infomsgs;
dbcc dropcleanbuffers with no_infomsgs;
Select
@cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
, @Reads_ = reads
, @Writes_ = writes
From
sys.dm_exec_requests
Where
session_id = @@spid;
---
SELECT
BitBucket1 = tvf.Date,
BitBucket2 = tvf.DayOfWeek,
BitBucket3 = tvf.ISOWeek
FROM
#TestTable tt
CROSS APPLY
itvfGetWeekNumberFromDate_ISO_v3(tt.Date) tvf
---
Select
@CpuMs = cpu_time - @cpu_
, @LogRds = logical_reads - @lreads_
, @Elapsed = total_elapsed_time - @eMsec_
, @Reads = reads - @Reads_
, @Writes = writes - @Writes_
From
sys.dm_exec_requests
Where
session_id = @@spid;
--===== Display the duration
PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +
' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +
' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +
' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +
' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);
set nocount off;
GO
-
Wow.
Amazing work Jeff. Great article too. 5 stars.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Alan.B (4/9/2013)
Wow.Amazing work Jeff. Great article too. 5 stars.
Thanks, Alan. I really appreciate that.
--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) -
Lynn Pettis (4/9/2013)
Create an empty database and run the following:...
Tested your script at my slow box:
CPU(ms): 102009 Logical Reads: 10002259 Elapsed(ms): 104478 Reads: 2044 Writes: 0CPU(ms): 2745 Logical Reads: 2175 Elapsed(ms): 2867 Reads: 2016 Writes: 0
CPU(ms): 100028 Logical Reads: 10002257 Elapsed(ms): 101861 Reads: 2043 Writes: 0
CPU(ms): 2090 Logical Reads: 2175 Elapsed(ms): 11723 Reads: 2016 Writes: 0
[
Clear case with regards to the conclusion for the test 😎
Lovely script on how easy one can just measure such consumption.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 16 through 30 (of 77 total)
You must be logged in to reply to this topic. Login to reply