August 13, 2013 at 10:19 am
ChrisM@Work (8/13/2013)
On my machine, your code is three times faster - but the negative numbers are different to mine and to Dwain's.
I think my handling of negative numbers is correct. See the following link for a clear description of how overpunch works:
http://en.wikipedia.org/wiki/Signed_overpunch
The code in the OP was buggy for negative numbers.
August 13, 2013 at 7:11 pm
ChrisM@Work (8/13/2013)
Here's a sample data generator. I've tested it using Stefan's code and mine and it seems ok. Anyone else up for a race? Package your code into a iTVF.
Due to lack of time I'm unable to but I'd suggest putting a binary collation sequence on the CHARINDEX examples to see if that improves them.
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
August 14, 2013 at 6:36 am
dwain.c (8/13/2013)
ChrisM@Work (8/13/2013)
Here's a sample data generator. I've tested it using Stefan's code and mine and it seems ok. Anyone else up for a race? Package your code into a iTVF.Due to lack of time I'm unable to but I'd suggest putting a binary collation sequence on the CHARINDEX examples to see if that improves them.
It does but not a hell of a lot. I've run out of time/interest with this one now, if anyone else wants to run with it, here's a sample data script;
DROP TABLE #SomeTestTable
SELECT TOP (1000000)
Amount,
IngredientCost
INTO #SomeTestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
CROSS APPLY (SELECT Amount = CHECKSUM(NEWID())%1000000) x
CROSS APPLY (
SELECT IngredientCost = CASE
WHEN Amount >= 0 THEN LEFT(RIGHT('00000'+CAST(Amount AS VARCHAR(8)),8),7) + SUBSTRING('{ABCDEFGHI',1+RIGHT(AMOUNT,1),1)
ELSE LEFT(RIGHT('00000'+CAST(ABS(Amount) AS VARCHAR(8)),8),7) + SUBSTRING('}JKLMNOPQR',1+RIGHT(AMOUNT,1),1)
END
) y ;
and the fastest query so far is this;
SELECT
t.*,
result = CAST(d.[Sign] + LEFT(IngredientCost,LEN(IngredientCost)-1) + d.Digit AS MONEY)/100
FROM #SomeTestTable t
CROSS APPLY (VALUES
('}', '0', '-'),('J', '1', '-'),('K', '2', '-'),('L', '3', '-'),('M', '4', '-'),
('N', '5', '-'),('O', '6', '-'),('P', '7', '-'),('Q', '8', '-'),('R', '9', '-'),
('{', '0', '+'),('A', '1', '+'),('B', '2', '+'),('C', '3', '+'),('D', '4', '+'),
('E', '5', '+'),('F', '6', '+'),('G', '7', '+'),('H', '8', '+'),('I', '9', '+')
) d (Code, Digit, [Sign])
WHERE RIGHT(IngredientCost,1) = d.Code
which takes slightly more CPU cost than Stefan's query.
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
August 14, 2013 at 7:09 am
ChrisM@Work (8/14/2013)
dwain.c (8/13/2013)
ChrisM@Work (8/13/2013)
Here's a sample data generator. I've tested it using Stefan's code and mine and it seems ok. Anyone else up for a race? Package your code into a iTVF.Due to lack of time I'm unable to but I'd suggest putting a binary collation sequence on the CHARINDEX examples to see if that improves them.
It does but not a hell of a lot. I've run out of time/interest with this one now, if anyone else wants to run with it, here's a sample data script;
DROP TABLE #SomeTestTableSELECT TOP (1000000)
Amount,
IngredientCost
INTO #SomeTestTable
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
CROSS APPLY (SELECT Amount = CHECKSUM(NEWID())%1000000) x
CROSS APPLY (
SELECT IngredientCost = CASE
WHEN Amount >= 0 THEN LEFT(RIGHT('00000'+CAST(Amount AS VARCHAR(8)),8),7) + SUBSTRING('{ABCDEFGHI',1+RIGHT(AMOUNT,1),1)
ELSE LEFT(RIGHT('00000'+CAST(ABS(Amount) AS VARCHAR(8)),8),7) + SUBSTRING('}JKLMNOPQR',1+RIGHT(AMOUNT,1),1)
END
) y ;
and the fastest query so far is this;
SELECTt.*,
result = CAST(d.[Sign] + LEFT(IngredientCost,LEN(IngredientCost)-1) + d.Digit AS MONEY)/100
FROM #SomeTestTable t
CROSS APPLY (VALUES
('}', '0', '-'),('J', '1', '-'),('K', '2', '-'),('L', '3', '-'),('M', '4', '-'),
('N', '5', '-'),('O', '6', '-'),('P', '7', '-'),('Q', '8', '-'),('R', '9', '-'),
('{', '0', '+'),('A', '1', '+'),('B', '2', '+'),('C', '3', '+'),('D', '4', '+'),
('E', '5', '+'),('F', '6', '+'),('G', '7', '+'),('H', '8', '+'),('I', '9', '+')
) d (Code, Digit, [Sign])
WHERE RIGHT(IngredientCost,1) = d.Code
which takes slightly more CPU cost than Stefan's query.
My God! How ghastly! But strangely cool at the same time (only because it uses CROSS APPLY VALUES :-D).
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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply