September 9, 2013 at 11:29 pm
As you said, in the interests of science, I removed the duplicate scenario in your test harness, added back my original at the end and included an extra one based on that good old binary collation within REPLACE.
Test harness:
--===== 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 the test table on-the-fly.
SELECT TOP 1000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
INTO #TestTable
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Create a target variable that will take the display time out of the picture.
DECLARE @Bitbucket CHAR(9);
PRINT '========== Traditional RIGHT+RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT('000000'+CAST(C1 AS VARCHAR(6)),6) + RIGHT('000'+CAST(C1 AS VARCHAR(3)),3)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Less Traditional RIGHT+RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(REPLICATE('0',6)+CAST(C1 AS VARCHAR(6)),6) + RIGHT(REPLICATE('0',3)+CAST(C1 AS VARCHAR(3)),3)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Double STUFF Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Integer Math RIGHT(RIGHT) Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Integer Math RIGHT(CAST) Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT('000000000'+CAST(C1*1000+C2 AS VARCHAR(9)),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== REPLACE(STR) Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = REPLACE(STR(C1*1000+C2,9),' ','0')
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== REPLACE(STR) Method with BIN collation =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = REPLACE(STR(C1*1000+C2,9) COLLATE Latin1_General_BIN,' ','0')
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '========== Another Integer Math Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
Test results:
========== Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 577 ms, elapsed time = 614 ms.
========== Less Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 593 ms, elapsed time = 608 ms.
========== Double STUFF Method ==========
SQL Server Execution Times:
CPU time = 889 ms, elapsed time = 890 ms.
========== Integer Math RIGHT(RIGHT) Method ==========
SQL Server Execution Times:
CPU time = 437 ms, elapsed time = 432 ms.
========== Integer Math RIGHT(CAST) Method ==========
SQL Server Execution Times:
CPU time = 405 ms, elapsed time = 400 ms.
========== REPLACE(STR) Method ==========
SQL Server Execution Times:
CPU time = 2637 ms, elapsed time = 2660 ms.
========== REPLACE(STR) Method with BIN collation ==========
SQL Server Execution Times:
CPU time = 1451 ms, elapsed time = 1441 ms.
========== Another Integer Math Method ==========
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 387 ms.
Despite what some pundits may disclaim, competition is a really good thing to help improve people's code and coding style.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 9, 2013 at 11:40 pm
dwain.c (9/9/2013)
PRINT '========== Another Integer Math Method =========='SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
Oh, now that's just bloody clever. You stayed away from anything character based until "display time". Well done!
--Jeff Moden
Change is inevitable... Change for the better is not.
September 9, 2013 at 11:43 pm
Jeff Moden (9/9/2013)
PRINT '========== Another Integer Math Method =========='SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
Oh, now that's just bloody clever. You stayed away from anything character based until "display time". Well done!
Couldn't have done it without you actually. Your first post gave me the inspiration.
[face-to-palm] Why didn't I think of it initially!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 10, 2013 at 1:26 am
dwain.c (9/9/2013)
Jeff Moden (9/9/2013)
PRINT '========== Another Integer Math Method =========='SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
Oh, now that's just bloody clever. You stayed away from anything character based until "display time". Well done!
Couldn't have done it without you actually. Your first post gave me the inspiration.
[face-to-palm] Why didn't I think of it initially!
Nice one, Dwain. Tight little examples like this make concepts easier to remember.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 10, 2013 at 6:43 am
Here is another variation on the math method that my testing says is a little faster:
DECLARE @Billion INT;
SET @Billion = 1000000000;
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(@Billion+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
September 10, 2013 at 6:53 am
paul.s.lach (9/10/2013)
Here is another variation on the math method that my testing says is a little faster:DECLARE @Billion INT;
SET @Billion = 1000000000;
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(@Billion+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
How does it perform if you set the constant 1000 to be a variable too?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 10, 2013 at 7:10 am
Interestingly, by using a variable instead of 1000000 it was faster, but using a variable instead of 1000 makes it slower!
Here is the script I used for testing followed by my results (sorry my machine is not as fast as Jeff's!!!)
--===== 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 the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT
);
INSERT INTO [#TestTable] ( [C1], [C2] )
SELECT TOP 1000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Create a target variable that will take the display time out of the picture.
DECLARE @Bitbucket CHAR(9);
PRINT '
========== Traditional RIGHT+RIGHT Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT('000000'+CAST(C1 AS VARCHAR(6)),6) + RIGHT('000'+CAST(C1 AS VARCHAR(3)),3)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Double STUFF Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = STUFF(C1, 1, 0, REPLICATE('0',6-LEN(C1)))+STUFF(C2, 1, 0, REPLICATE('0',3-LEN(C2)))
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Integer Math RIGHT(RIGHT) Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT('000000000'+RIGHT(C1*1000+C2,9),9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Alternate Math + Right Method =========='
DECLARE @Billion INT;
SET @Billion = 1000000000;
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(@Billion+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Second Alternate Math + Right Method =========='
DECLARE @Thousand INT;
SET @Thousand = 1000;
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(@Billion+(C1*@Thousand)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
(1000000 row(s) affected)
========== Traditional RIGHT+RIGHT Method ==========
SQL Server Execution Times:
CPU time = 3093 ms, elapsed time = 3341 ms.
========== Double STUFF Method ==========
SQL Server Execution Times:
CPU time = 3547 ms, elapsed time = 3559 ms.
========== Integer Math RIGHT(RIGHT) Method ==========
SQL Server Execution Times:
CPU time = 2391 ms, elapsed time = 2395 ms.
========== Alternate Math + Right Method ==========
SQL Server Execution Times:
CPU time = 2062 ms, elapsed time = 2070 ms.
========== Second Alternate Math + Right Method ==========
SQL Server Execution Times:
CPU time = 2235 ms, elapsed time = 2250 ms.
September 10, 2013 at 7:21 am
Try running this 3-4 times and you'll see that the winner bounces around among the 3 (you forgot my version in your test harness).
--===== 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 the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT
);
INSERT INTO [#TestTable] ( [C1], [C2] )
SELECT TOP 1000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
--===== Create a target variable that will take the display time out of the picture.
DECLARE @Bitbucket CHAR(9);
PRINT '========== Another Integer Math Method =========='
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(1000000000 + C1*1000 + C2, 9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Alternate Math + Right Method =========='
DECLARE @Billion INT;
SET @Billion = 1000000000;
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(@Billion+(C1*1000)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
PRINT '
========== Second Alternate Math + Right Method =========='
DECLARE @Thousand INT;
SET @Thousand = 1000;
SET STATISTICS TIME ON;
SELECT @Bitbucket = RIGHT(@Billion+(C1*@Thousand)+C2,9)
FROM #TestTable;
SET STATISTICS TIME OFF;
Here are sample results from one run on my machine:
========== Another Integer Math Method ==========
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 388 ms.
========== Alternate Math + Right Method ==========
SQL Server Execution Times:
CPU time = 390 ms, elapsed time = 400 ms.
========== Second Alternate Math + Right Method ==========
SQL Server Execution Times:
CPU time = 405 ms, elapsed time = 395 ms.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 10, 2013 at 9:05 am
You guys are awesome. That's why I love this site, it reminds me to think different to look for better results. 🙂
September 10, 2013 at 10:22 am
OK, this one really sucked me in for more than I planned on doing!
I was surprised by the variability in the results and thought that there had to be something other than the algorithms we were testing affecting the results. One of the things I thought of was an article I read that said that the compiler optimizes "SELECT @Variable = ..." to just set it to the first value when you select from a table with more than one row (I would reference the article, but I do not remember it and do not want to spend any more time on this than I already have!). I also thought that the short durations might be affecting the results.
Therefore, I modified the code to do the following:
1. add a column to the temporary table to hold the result of the calculation
2. increase the number of records from 1,000,000 to 10,000,000
3. modify the queries to UPDATE where the calculated value is not equal to the current value
The key thing here is that the queries should never modify the table because the calculated value will always be equal to the stored value, but the values will have to be calculated for every row. This should ensure that the timings are representing the time it takes to do the calculations and the comparisons and (hopefully) nothing else.
I also modified the test to do 6 different calculations:
1. All numbers using RIGHT
2. All numbers using SUBSTRING
3. Billion variable and 1000 using RIGHT
4. Billion variable and 1000 using SUBSTRING
5. Billion variable and Thousand variable using RIGHT
6. Billion variable and Thousand variable using SUBSTRING
This seemed like a good plan, but the results continue to be variable:
First Run Second Run
-------------------- --------------------
RIGHT SUBSTRING RIGHT SUBSTRING
-------------------- --------------------
All Numbers 5704 5047 5078 6047
Billion 6469 4953 5843 5906
Both 5953 4891 6281 5219
Here is the script I used for the test:
--===== 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 the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT,
[Result] CHAR(9),
);
INSERT INTO [#TestTable] ( [C1], [C2], [Result] )
SELECT TOP 10000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
,'000000000'
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
-- Initialize the Result column value
UPDATE #TestTable SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9);
--===== Create a target variable that will take the display time out of the picture.
DECLARE @Billion INT;
SET @Billion = 1000000000;
DECLARE @Thousand INT;
SET @Thousand = 1000;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9);
SET STATISTICS TIME OFF;
If anyone can explain the variability in the timings I would be interested in hearing about it.
September 10, 2013 at 1:51 pm
dwain.c (9/10/2013)
(you forgot my version in your test harness).[/code]
Who you talking to, Dwain?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2013 at 1:55 pm
paul.s.lach (9/10/2013)
One of the things I thought of was an article I read that said that the compiler optimizes "SELECT @Variable = ..." to just set it to the first value when you select from a table with more than one row (I would reference the article, but I do not remember it and do not want to spend any more time on this than I already have!).
Not quite right. It will be given the value of each row and whichever row is the last row processed by the query is the value the variable will have. It's an important trait for doing things such as the "Quirky Update", which can be used for doing very high speed running totals and the like.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 10, 2013 at 5:53 pm
Jeff Moden (9/10/2013)
dwain.c (9/10/2013)
(you forgot my version in your test harness).[/code]
Who you talking to, Dwain?
Paul.S here: http://www.sqlservercentral.com/Forums/FindPost1493158.aspx
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 10, 2013 at 7:36 pm
Paul - I get fairly stable results with this test harness. Can you confirm?
I am also running on a SQL Server that is fairly quiescent (no other processes running).
--===== 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 the test table on-the-fly.
CREATE TABLE #TestTable ([C1] INT,
[C2] INT,
[Result] CHAR(9),
);
INSERT INTO [#TestTable] ( [C1], [C2], [Result] )
SELECT TOP 10000000
C1 = ABS(CHECKSUM(NEWID()))%1000000
,C2 = ABS(CHECKSUM(NEWID()))%1000
,'000000000'
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
;
-- Initialize the Result column value
UPDATE #TestTable SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
OPTION (RECOMPILE);
--===== Create a target variable that will take the display time out of the picture.
DECLARE @Billion INT;
SET @Billion = 1000000000;
DECLARE @Thousand INT;
SET @Thousand = 1000;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(1000000000+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(1000000000+(C1*1000)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*1000)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*1000)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Right Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = RIGHT(@Billion+(C1*@Thousand)+C2,9)
WHERE [Result] != RIGHT(@Billion+(C1*@Thousand)+C2,9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Straight Math + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((1000000000+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With Billion Variable + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*1000)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
PRINT '
========== Math With 2 Variables + Substring Method =========='
SET STATISTICS TIME ON;
UPDATE #TestTable
SET [Result] = SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
WHERE [Result] != SUBSTRING(CAST((@Billion+(C1*@Thousand)+C2) AS VARCHAR(10)), 2, 9)
OPTION (RECOMPILE);
SET STATISTICS TIME OFF;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 10, 2013 at 9:19 pm
Part of the reason why Paul S. might be seeing some instability is because he didn't trust the @Bitbucket variable and took to writing to disk. Depending on what else is running, there could be substantial disruptions that you wouldn't see on a quieter machine.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply