April 24, 2009 at 3:42 pm
The good news will come when they finally get all of the windowing functions like SUM() OVER working correctly so we don't have to trick SQL server with things like quirky updates.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2009 at 4:17 pm
Jeff,
If you send me the test code I can run it in 2008. I have it on my laptop.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 24, 2009 at 5:15 pm
Jack Corbett (4/24/2009)
Jeff,If you send me the test code I can run it in 2008. I have it on my laptop.
Very cool, Mr. Corbett. I'll take you up on that offer as soon as I finish testing on my end. Thank you for the "leg up".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2009 at 6:17 pm
No problem. Depending on when you get it done I may even have 2008 on a server that I can play with!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 27, 2009 at 3:45 am
hmm ....
I ran this comparisson on
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
(32-bit / 1 proc / 4 cores / 4Gb ram / (/3GB not enabled) )
(4 files for tempdb)
/*
set statistics io on
set statistics time on
*/
-- JBMTest and JBMTest2 are exact copies ! of the original test table.
/* reset data */
/*
UPDate dbo.JBMTest
SET RunBal = NULL,
GrpBal = NULL ,
RunCnt = NULL ,
GrpCnt = NULL
UPDate dbo.JBMTest2
SET RunBal = NULL,
GrpBal = NULL ,
RunCnt = NULL ,
GrpCnt = NULL
*/
--go
/*
ALTER INDEX [IX_JBMTest_AccountID_Date] ON [dbo].[JBMTest] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
*/
/*
ALTER INDEX [IX_JBMTest2_AccountID_Date] ON [dbo].[JBMTest2] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
*/
-- NOW THE TEST
declare @tsb as datetime
set @tsb = getdate()
print '** Begin ' + convert(char(26),@tsb,21) + ' Begin **'
--===== Declare the variables for the "Code Basis"
DECLARE @PrevRunBal MONEY --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID INT --The "anchor" and "account change detector"
SET @PrevAcctID = 0
--===== Solve 2 types of Running Total and 2 types of Running Count problems
-- using a single update based on a Clustered Index at VERY high speeds.
UPDATE T
SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + Amount,
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = GrpBal = CASE
WHEN AccountID = @PrevAcctID
THEN @PrevGrpBal + Amount
ELSE Amount -- Restarts total at "0 + current amount"
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN AccountID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = AccountID
FROM dbo.JBMTest T WITH (INDEX(IX_JBMTest_AccountID_Date),TABLOCKX)
/*
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'JBMTest'. Scan count 1, logical reads 7222, 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 = 15140 ms, elapsed time = 28854 ms.
*/
declare @tse as datetime
set @tse = current_timestamp
print '** End ' + convert(char(26),@tse,21) + ' End ** - elaps ms: ' + convert(char(26),datediff(ms,@tsb, @tse))
go
declare @tsb as datetime
set @tsb = getdate()
print '** Begin P2 ' + convert(char(26),@tsb,21) + ' Begin **'
--===== Declare the variables for the "Code Basis"
DECLARE @PrevRunBal MONEY --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID INT --The "anchor" and "account change detector"
SET @PrevAcctID = 0
--===== Solve 2 types of Running Total and 2 types of Running Count problems
-- using a single update based on a Clustered Index at VERY high speeds.
UPDATE T
SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + Amount,
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = GrpBal = CASE
WHEN AccountID = @PrevAcctID
THEN @PrevGrpBal + Amount
ELSE Amount -- Restarts total at "0 + current amount"
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN AccountID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = AccountID
FROM (select top 100 percent *
from dbo.JBMTest2
order by AccountID, [Date]
) T
-- option (maxdop 1)
/*
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'JBMTest2'. Scan count 1, logical reads 7222, 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 = 14156 ms, elapsed time = 36166 ms.
(1000000 row(s) affected)
*/
declare @tse as datetime
set @tse = current_timestamp
print '** End P2' + convert(char(26),@tse,21) + ' End ** - elaps ms: ' + convert(char(26),datediff(ms,@tsb, @tse))
go
SELECT *
FROM dbo.JBMTest2
except
Select *
FROM dbo.JBMTest
ORDER BY AccountID, Date
/*
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(0 row(s) affected)
Table 'JBMTest2'. Scan count 5, logical reads 7288, physical reads 0, read-ahead reads 9, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'JBMTest'. Scan count 5, logical reads 7288, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, 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 = 25955 ms, elapsed time = 7012 ms.
*/
go
Note the maxdop 1 was not needed to get the correct result.
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
April 27, 2009 at 7:12 pm
Johan,
Run the first test using the clustered index as it is. Then, drop the clustered index and add a new one based on some other columns. Run your ORDER BY code and do the compare. I believe you'll find it fails to work as advertised and that's part of the reason why I'm rewriting the article.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 28, 2009 at 12:46 am
Indeed.
The prerequisite is you need the clustered index organized for your processing needs ( AccountID, [Date] in the example ).
Any other organisation will cause the mechanisme to fail.
I tested multiple compositions of clix and NCI (on AccountID, [Date]) and couldn't push it to work correct. (Only clix on AccountID, [Date] gives the correct results)
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
May 3, 2009 at 12:42 am
Jeff: I also have SQL Server 2008 Developer Edition on my laptop if you need it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 3, 2009 at 6:27 am
Hey Jeff... If you need to test anything, I've got 2005 & 2008 Dev Editions on my laptop.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 3, 2009 at 9:09 am
Thanks for the offers of help guys. I love this team.
I've got 2k Dev, 2k5 Dev, and I'll soon have 2k8 Dev. I may also be shifting primary computers... the one I have is rock solid and trustworthy but it's also 7 years old and isn't portable. Looks like I have to break down and buy a laptop. Probably won't go with Vista because it's my understanding that Vista won't support 2k and I still need to support 2k.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2009 at 1:16 pm
Jeff Moden (5/3/2009)
Thanks for the offers of help guys. I love this team.I've got 2k Dev, 2k5 Dev, and I'll soon have 2k8 Dev. I may also be shifting primary computers... the one I have is rock solid and trustworthy but it's also 7 years old and isn't portable. Looks like I have to break down and buy a laptop. Probably won't go with Vista because it's my understanding that Vista won't support 2k and I still need to support 2k.
Yeah I gave in on the Laptop as primary thing two years ago. It's really great for me as a roaming consultant though, no matter where I am, I always have all of my stuff with me.
One thing that I overlooked at first though: good backups. They are even more important when I have everything on one box, plus the wear and tear of startup/shutdown & transportation puts that much more stress on the disks. Fortunately, USB drives are cheap and many come with Backup software (Maxtor's are pretty good). So now I just have a pair of 250GB's beside my desk at home that I attach at night to do the overnight backups.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 241 through 250 (of 250 total)
You must be logged in to reply to this topic. Login to reply