September 12, 2012 at 9:30 am
Eugene Elutin (9/12/2012)
I've tried all three methods, INNER JOINs, CROSS APPLY and CROSS JOIN with WHERE: all produce very similar query plans with use of Nested Loops (Inner Join).Cannot see much difference at all.
They all do the same thing in the engine. It's just a really good illustration of how a Declarative Language like T-SQL differs from a Procedural Language.
The only way to make it more efficient is to shortcut the math. That's why some are hard-coding 3 as the final digit. (It only saves one step in the algorithm, and eliminates significant code-flexibility in order to do so.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2012 at 9:59 am
A completely different method, courtesy of Dwain C:
;WITH SourceNums AS (
SELECT strcol FROM (VALUES ('1'),('2'),('5'),('7'),('8'),('9')) a (strcol)
),
nTuples (n, Tuples) AS (
SELECT 1, CAST(strcol AS VARCHAR(max))
FROM SourceNums
UNION ALL
SELECT 1 + n.n, t.strcol + n.Tuples
FROM SourceNums t JOIN nTuples n ON t.strcol <> n.Tuples
WHERE CHARINDEX(t.strcol, n.Tuples) = 0
)
SELECT
num1,
num2
FROM nTuples
CROSS APPLY (
SELECT
num1 = CAST('4' + LEFT(Tuples,3) + '6' AS INT),
num2 = CAST(RIGHT(Tuples,3) + '3' AS INT)
) x
WHERE n = 6 AND num1 - num2 = 33333
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 12, 2012 at 10:05 am
...
The only way to make it more efficient is to shortcut the math. That's why some are hard-coding 3 as the final digit. (It only saves one step in the algorithm, and eliminates significant code-flexibility in order to do so.)
I don't code for flexibility where it's not required and probability of it very low. I choose "more efficient" path. 😉
BTW, you have hard-coded 4 and 6 in your where clause...
To make it really flexible, you need to come up with dynamic SQL, which will allow to find solution for any kind of similar puzzle:
(D)n [OPERATION] (D)m = R
where (D)n and (D)m is a number containing n and m number of digits, [OPERATION] - is any (or at least basic) mathematical operation and R a requested result of this operation.
You can also add here some other rules eg. maximum number of repeated digits for example 🙂
That what I would call really flexible....
September 12, 2012 at 10:07 am
ChrisM@Work (9/12/2012)
A completely different method, courtesy of Dwain C:
;WITH SourceNums AS (SELECT strcol FROM (VALUES ('1'),('2'),('5'),('7'),('8'),('9')) a (strcol)
),
nTuples (n, Tuples) AS (
SELECT 1, CAST(strcol AS VARCHAR(max))
FROM SourceNums
UNION ALL
SELECT 1 + n.n, t.strcol + n.Tuples
FROM SourceNums t JOIN nTuples n ON t.strcol <> n.Tuples
WHERE CHARINDEX(t.strcol, n.Tuples) = 0
)
SELECT
num1,
num2
FROM nTuples
CROSS APPLY (
SELECT
num1 = CAST('4' + LEFT(Tuples,3) + '6' AS INT),
num2 = CAST(RIGHT(Tuples,3) + '3' AS INT)
) x
WHERE n = 6 AND num1 - num2 = 33333
This one looks like the second-slowest method due to the use of recursive CTE...
But, I like that it's really completely different one.
September 12, 2012 at 11:20 am
This seems simple and fairly fast:
declare @n table ( n int not null primary key clustered)
insert into @n select top 1000 n=row_number() over(order by id)-1 from syscolumns
select*
from(selectx,y,z = x-y, c = convert(varchar(9),(x*10000+y))
from(select x = 40006+((n*10))from @n) a
cross join
(select y = (n*10)+3 from @n) b
wherex-y = 33333 ) t
where
c not like '%0%' and c not like '%1%1%' and c not like '%2%2%' and
c not like '%3%3%' and c not like '%4%4%' and c not like '%5%5%' and
c not like '%6%6%' and c not like '%7%7%' and
c not like '%8%8%' and c not like '%9%9%'
Results:
x y z c
----------- ----------- ----------- ---------
41286 7953 33333 412867953
September 12, 2012 at 11:24 am
Eugene Elutin (9/12/2012)
...
The only way to make it more efficient is to shortcut the math. That's why some are hard-coding 3 as the final digit. (It only saves one step in the algorithm, and eliminates significant code-flexibility in order to do so.)
I don't code for flexibility where it's not required and probability of it very low. I choose "more efficient" path. 😉
BTW, you have hard-coded 4 and 6 in your where clause...
To make it really flexible, you need to come up with dynamic SQL, which will allow to find solution for any kind of similar puzzle:
(D)n [OPERATION] (D)m = R
where (D)n and (D)m is a number containing n and m number of digits, [OPERATION] - is any (or at least basic) mathematical operation and R a requested result of this operation.
You can also add here some other rules eg. maximum number of repeated digits for example 🙂
That what I would call really flexible....
I already explained how mine could be made flexible based on simply defining parameters and working them in to the Where clause. Here's what that would look like:
CREATE PROC dbo.NumbersPuzzle
(@Col1_in CHAR(1) = NULL,
@Col2_in CHAR(1) = NULL,
@Col3_in CHAR(1) = NULL,
@Col4_in CHAR(1) = NULL,
@Col5_in CHAR(1) = NULL,
@Col6_in CHAR(1) = NULL,
@Col7_in CHAR(1) = NULL,
@Col8_in CHAR(1) = NULL,
@Col9_in CHAR(1) = NULL,
@FinalVal_in INT)
AS
SET NOCOUNT ON;
WITH Numbers
AS (SELECT *
FROM ( VALUES ( '1'), ( '2'), ( '3'), ( '4'), ( '5'), ( '6'), ( '7'), ( '8'), ( '9') ) AS Nums (Number))
SELECT N1.Number + N2.Number + N3.Number + N4.Number + N5.Number,
N6.Number + N7.Number + N8.Number + N9.Number
FROM Numbers AS N1
CROSS APPLY (SELECT Number
FROM Numbers AS N2
WHERE N2.Number != N1.Number) AS N2
CROSS APPLY (SELECT Number
FROM Numbers AS N3
WHERE N3.Number NOT IN (N1.Number, N2.Number)) AS N3
CROSS APPLY (SELECT Number
FROM Numbers AS N4
WHERE N4.Number NOT IN (N1.Number, N2.Number, N3.Number)) AS N4
CROSS APPLY (SELECT Number
FROM Numbers AS N5
WHERE N5.Number NOT IN (N1.Number, N2.Number, N3.Number, N4.Number)) AS N5
CROSS APPLY (SELECT Number
FROM Numbers AS N6
WHERE N6.Number NOT IN (N1.Number, N2.Number, N3.Number, N4.Number, N5.Number)) AS N6
CROSS APPLY (SELECT Number
FROM Numbers AS N7
WHERE N7.Number NOT IN (N1.Number, N2.Number, N3.Number, N4.Number, N5.Number, N6.Number))
AS N7
CROSS APPLY (SELECT Number
FROM Numbers AS N8
WHERE N8.Number NOT IN (N1.Number, N2.Number, N3.Number, N4.Number, N5.Number, N6.Number,
N7.Number)) AS N8
CROSS APPLY (SELECT Number
FROM Numbers AS N9
WHERE N9.Number NOT IN (N1.Number, N2.Number, N3.Number, N4.Number, N5.Number, N6.Number,
N7.Number, N8.Number)) AS N9
WHERE (N1.Number = @Col1_in
OR @Col1_in IS NULL)
AND (N2.Number = @Col2_in
OR @Col2_in IS NULL)
AND (N3.Number = @Col3_in
OR @Col3_in IS NULL)
AND (N4.Number = @Col4_in
OR @Col4_in IS NULL)
AND (N5.Number = @Col5_in
OR @Col5_in IS NULL)
AND (N6.Number = @Col6_in
OR @Col6_in IS NULL)
AND (N7.Number = @Col7_in
OR @Col7_in IS NULL)
AND (N8.Number = @Col8_in
OR @Col8_in IS NULL)
AND (N9.Number = @Col9_in
OR @Col9_in IS NULL)
AND CAST(N1.Number + N2.Number + N3.Number + N4.Number + N5.Number AS INT)
- CAST(N6.Number + N7.Number + N8.Number + N9.Number AS INT) = @FinalVal_in;
GO
EXEC dbo.NumbersPuzzle @Col1_in = '4', @Col5_in = '6', @FinalVal_in = 33333;
You're right that the shortcut methods would require dynamic SQL, to change the From clause. Becomes tremendously more complex to manage dynamic input that way. Mine allows for dynamic input without any significant level of complexity. Same for all of the other solutions that do the filtering in the Where clause, instead of in the From clause.
Is that overkill for this particular puzzle? Sure. But it's a good practice as a coder to assume extensibility and flexibility on inputs. At least allow variables instead of hard-coded absolutes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2012 at 5:09 pm
I think maybe you can save a little overhead by checking for the known difference expected in the result, naturally including the next-higher value as well to allow for "borrowing". Haven't verified this for all possible values, tho:
;WITH a (num) AS (SELECT * FROM (VALUES ('1'),('2'),('5'),('7'),('8'),('9')) a (num)),
b (num1,num2) AS (
SELECT
'4' + a.num + b.num + c.num + '6',
d.num + e.num + f.num + '3'
FROM a
CROSS join a as b
CROSS join a as c
CROSS join a as d
CROSS join a as e
CROSS join a as f
WHERE 1 = 1
AND b.num NOT IN (a.num)
AND c.num NOT IN (a.num, b.num)
AND d.num NOT IN (a.num, b.num, c.num)
AND (CAST(a.num AS smallint) - CAST(d.num AS smallint) IN (3, 4) OR CAST(a.num AS smallint) + 10 - CAST(d.num AS smallint) IN (3, 4))
AND e.num NOT IN (a.num, b.num, c.num, d.num)
AND (CAST(b.num AS smallint) - CAST(e.num AS smallint) IN (3, 4) OR CAST(b.num AS smallint) + 10 - CAST(e.num AS smallint) IN (3, 4))
AND f.num NOT IN (a.num, b.num, c.num, d.num, e.num)
AND (CAST(c.num AS smallint) - CAST(f.num AS smallint) IN (3, 4) OR CAST(c.num AS smallint) + 10 - CAST(f.num AS smallint) IN (3, 4))
)
SELECT num1, num2
FROM b
WHERE CAST(num1 AS INT) - CAST(num2 AS INT) = 33333
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 12, 2012 at 11:22 pm
ChrisM@Work (9/12/2012)
A completely different method, courtesy of Dwain C:
;WITH SourceNums AS (SELECT strcol FROM (VALUES ('1'),('2'),('5'),('7'),('8'),('9')) a (strcol)
),
nTuples (n, Tuples) AS (
SELECT 1, CAST(strcol AS VARCHAR(max))
FROM SourceNums
UNION ALL
SELECT 1 + n.n, t.strcol + n.Tuples
FROM SourceNums t JOIN nTuples n ON t.strcol <> n.Tuples
WHERE CHARINDEX(t.strcol, n.Tuples) = 0
)
SELECT
num1,
num2
FROM nTuples
CROSS APPLY (
SELECT
num1 = CAST('4' + LEFT(Tuples,3) + '6' AS INT),
num2 = CAST(RIGHT(Tuples,3) + '3' AS INT)
) x
WHERE n = 6 AND num1 - num2 = 33333
Hah! Just noticed this thread and immediately thought nTuples could be applied!
You beat me to it but only because I've been busy doing real work.
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 12, 2012 at 11:48 pm
Here's a version of the Tuples solution that runs in about 1/3 the time:
;WITH SourceNums AS (
SELECT strcol FROM (VALUES ('1'),('2'),('5'),('7'),('8'),('9')) a (strcol)
),
nTuples (n, Tuples) AS (
SELECT 1, CAST(strcol AS VARCHAR(10)) COLLATE LATIN1_GENERAL_BIN
FROM SourceNums
UNION ALL
SELECT 1 + n.n, CAST(t.strcol + n.Tuples AS VARCHAR(10))
FROM SourceNums t JOIN nTuples n ON t.strcol <> n.Tuples
WHERE CHARINDEX(t.strcol, n.Tuples) = 0
)
SELECT
num1=CAST('4' + LEFT(Tuples,3) + '6' AS INT),
num2=CAST(RIGHT(Tuples,3) + '3' AS INT)
FROM nTuples
WHERE n = 6 AND CAST('4' + LEFT(Tuples,3) + '6' AS INT) - (RIGHT(Tuples,3) + '3') = 33333
Changes:
1. Went to VARCHAR(10) instead of VARCHAR(MAX) [big improvement]
2. Removed the CROSS APPLY [slight improvement]
3. Added the BIN collation [slight but noticeable improvement]
Still doesn't beat Eugene's solution for time though.
BTW Chris - Thanks for the credit!
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 13, 2012 at 1:44 am
dwain.c (9/12/2012)
...BTW Chris - Thanks for the credit!
No worries mate - so long as you keep up the repayments! 😛
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 13, 2012 at 3:32 am
dwain.c (9/12/2012)
Here's a version of the Tuples solution that runs in about 1/3 the time:...
I like this one, as it's very different approach, but yeah it's still much slower.
I have 0 milliseconds for my option 9 of 10 times
DECLARE @Ddatetime2
SET @D = SYSDATETIME()
;with a(num) as
(select 1 union select 2 union select 3 union /*select 4 union*/ select 5 union /*select 6 union*/ select 7 union select 8 union select 9)
,b(num1,num2) as
(
select
convert(bigint, '4' + convert(varchar,a.num) + convert(varchar,b.num) + convert(varchar,c.num) + '6')
,convert(bigint, convert(varchar,d.num) + convert(varchar,e.num) + convert(varchar,f.num) + convert(varchar,g.num))
from
a
join a as b on a.num <> b.num
join a as c on a.num <> b.num and a.num <> c.num
and b.num <> c.num
join a as d on a.num <> b.num and a.num <> c.num and a.num <> d.num
and b.num <> c.num and b.num <> d.num
and c.num <> d.num
join a as e on a.num <> b.num and a.num <> c.num and a.num <> d.num and a.num <> e.num
and b.num <> c.num and b.num <> d.num and b.num <> e.num
and c.num <> d.num and c.num <> e.num
and d.num <> e.num
join a as f on a.num <> b.num and a.num <> c.num and a.num <> d.num and a.num <> e.num and a.num <> f.num
and b.num <> c.num and b.num <> d.num and b.num <> e.num and b.num <> f.num
and c.num <> d.num and c.num <> e.num and c.num <> f.num
and d.num <> e.num and d.num <> f.num
and e.num <> f.num
join a as g on a.num <> b.num and a.num <> c.num and a.num <> d.num and a.num <> e.num and a.num <> f.num and a.num <> g.num
and b.num <> c.num and b.num <> d.num and b.num <> e.num and b.num <> f.num and b.num <> g.num
and c.num <> d.num and c.num <> e.num and c.num <> f.num and c.num <> g.num
and d.num <> e.num and d.num <> f.num and d.num <> g.num
and e.num <> f.num and e.num <> g.num
and f.num <> g.num
)
select * from b
where num1-num2 = 33333
order by 1,2
SELECT DATEDIFF(ms, @D, SYSDATETIME()) [Original Join with duplicated check]
SET @D = SYSDATETIME()
;WITH Numbers(n) AS(
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '5' UNION ALL
SELECT '7' UNION ALL
SELECT '8' UNION ALL
SELECT '9' ),
Numbers2 AS(
SELECT CAST( '4' + n1.n + n2.n + n3.n + '6' AS int) num1,
CAST( n4.n + n5.n + n6.n + n7.n AS int) num2
FROM Numbers n1
JOINNumbers n2 ON n1.n <> n2.n
JOINNumbers n3 ON n3.n NOT IN(n1.n, n2.n)
JOINNumbers n4 ON n4.n NOT IN(n1.n, n2.n, n3.n)
JOINNumbers n5 ON n5.n NOT IN(n1.n, n2.n, n3.n, n4.n)
JOINNumbers n6 ON n6.n NOT IN(n1.n, n2.n, n3.n, n4.n, n5.n)
JOINNumbers n7 ON n7.n NOT IN(n1.n, n2.n, n3.n, n4.n, n5.n, n6.n)
)
SELECT num1, num2
FROM Numbers2
WHERE num1 - num2 = 33333;
SELECT DATEDIFF(ms, @D, SYSDATETIME()) [Join with NOT IN - without undersatnding that last digit can be only 3]
SET @D = SYSDATETIME()
;WITH Numbers(n) AS(
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '5' UNION ALL
SELECT '7' UNION ALL
SELECT '8' UNION ALL
SELECT '9' ),
Numbers2 AS(
SELECT CAST( '4' + n1.n + n2.n + n3.n + '6' AS int) num1,
CAST( n4.n + n5.n + n6.n + n7.n AS int) num2
FROM Numbers n1
JOINNumbers n2 ON n1.n <> n2.n
JOINNumbers n3 ON n1.n <> n3.n AND n2.n <> n3.n
JOINNumbers n4 ON n1.n <> n4.n AND n2.n <> n4.n AND n3.n <> n4.n
JOINNumbers n5 ON n1.n <> n5.n AND n2.n <> n5.n AND n3.n <> n5.n AND n4.n <> n5.n
JOINNumbers n6 ON n1.n <> n6.n AND n2.n <> n6.n AND n3.n <> n6.n AND n4.n <> n6.n AND n5.n <> n6.n
JOINNumbers n7 ON n1.n <> n7.n AND n2.n <> n7.n AND n3.n <> n7.n AND n4.n <> n7.n AND n5.n <> n7.n AND n6.n <> n7.n
)
SELECT num1, num2
FROM Numbers2
WHERE num1 - num2 = 33333;
SELECT DATEDIFF(ms, @D, SYSDATETIME()) [Join with NOT EQUAL single cheks]
SET @D = SYSDATETIME()
;WITH Numbers
AS (SELECT *
FROM ( VALUES ( '1'), ( '2'), ( '3'), ( '4'), ( '5'), ( '6'), ( '7'), ( '8'), ( '9') ) AS Nums (Number))
SELECT N1.Number + N2.Number + N3.Number + N4.Number + N5.Number, N6.Number + N7.Number + N8.Number + N9.Number
FROM Numbers AS N1
CROSS APPLY (SELECT Number
FROM Numbers AS N2
WHERE N2.Number != N1.Number) AS N2
CROSS APPLY (SELECT Number
FROM Numbers AS N3
WHERE N3.Number NOT IN (N1.Number, N2.Number)) AS N3
CROSS APPLY (SELECT Number
FROM Numbers AS N4
WHERE N4.Number NOT IN (N1.Number, N2.Number, N3.Number)) AS N4
CROSS APPLY (SELECT Number
FROM Numbers AS N5
WHERE N5.Number NOT IN (N1.Number, N2.Number, N3.Number, N4.Number)) AS N5
CROSS APPLY (SELECT Number
FROM Numbers AS N6
WHERE N6.Number NOT IN (N1.Number, N2.Number, N3.Number, N4.Number, N5.Number)) AS N6
CROSS APPLY (SELECT Number
FROM Numbers AS N7
WHERE N7.Number NOT IN (N1.Number, N2.Number, N3.Number, N4.Number, N5.Number, N6.Number)) AS N7
CROSS APPLY (SELECT Number
FROM Numbers AS N8
WHERE N8.Number NOT IN (N1.Number, N2.Number, N3.Number, N4.Number, N5.Number, N6.Number,
N7.Number)) AS N8
CROSS APPLY (SELECT Number
FROM Numbers AS N9
WHERE N9.Number NOT IN (N1.Number, N2.Number, N3.Number, N4.Number, N5.Number, N6.Number,
N7.Number, N8.Number)) AS N9
WHERE N1.Number = '4'
AND N5.Number = '6'
AND CAST(N1.Number + N2.Number + N3.Number + N4.Number + N5.Number AS INT)
- CAST(N6.Number + N7.Number + N8.Number + N9.Number AS INT) = 33333
SELECT DATEDIFF(ms, @D, SYSDATETIME()) [Join with CROSS APPLY]
SET @D = SYSDATETIME()
;with a (num)
as
( select * from (values ('1'),('2'),('5'),('7'),('8'),('9')) a(num) )
,b
as
(
select '4' + a.num + b.num + c.num + '6' as num1
,d.num + e.num + f.num + '3' as num2
from a
join a as b on a.num <> b.num
join a as c on c.num NOT IN (a.num, b.num)
join a as d on d.num NOT IN (a.num, b.num, c.num)
join a as e on e.num NOT IN (a.num, b.num, c.num, d.num)
join a as f on f.num NOT IN (a.num, b.num, c.num, d.num, e.num)
)
select * from b
where cast(num1 as int) - cast(num2 as int)= 33333
SELECT DATEDIFF(ms, @D, SYSDATETIME()) [My Join with NOT IN coded for highest efficiency]
SET @D = SYSDATETIME()
;WITH SourceNums AS (
SELECT strcol FROM (VALUES ('1'),('2'),('5'),('7'),('8'),('9')) a (strcol)
),
nTuples (n, Tuples) AS (
SELECT 1, CAST(strcol AS VARCHAR(10)) COLLATE LATIN1_GENERAL_BIN
FROM SourceNums
UNION ALL
SELECT 1 + n.n, CAST(t.strcol + n.Tuples AS VARCHAR(10))
FROM SourceNums t JOIN nTuples n ON t.strcol <> n.Tuples
WHERE CHARINDEX(t.strcol, n.Tuples) = 0
)
SELECT
num1=CAST('4' + LEFT(Tuples,3) + '6' AS INT),
num2=CAST(RIGHT(Tuples,3) + '3' AS INT)
FROM nTuples
WHERE n = 6 AND CAST('4' + LEFT(Tuples,3) + '6' AS INT) - (RIGHT(Tuples,3) + '3') = 33333
SELECT DATEDIFF(ms, @D, SYSDATETIME()) [Dwain Tuples - with knowinf that last digit is 3]
September 13, 2012 at 4:49 am
Eugene - Actually, I'm not sure which version of your code I tested against, possibly an earlier one. That one was running in about 16ms as I recall, where the latest Tuples solution ran in about 47ms.
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 13, 2012 at 4:51 am
ChrisM@Work (9/13/2012)
dwain.c (9/12/2012)
...BTW Chris - Thanks for the credit!
No worries mate - so long as you keep up the repayments! 😛
The check is in the mail!
I'm wondering how improvements #1 and #3 might have helped some of the other uses I've made of the Tuples script. Maybe I need to go back and check.
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 13, 2012 at 9:13 pm
Eugene Elutin (9/13/2012)
dwain.c (9/12/2012)
Here's a version of the Tuples solution that runs in about 1/3 the time:...
I like this one, as it's very different approach, but yeah it's still much slower.
You might like this one too then. Nearly an all numeric approach. Still no faster though but syntactically more elegant.
;WITH SourceNums AS (
SELECT num FROM (VALUES (1),(2),(5),(7),(8),(9)) a (num)
),
nTuples (n, Tuples) AS (
SELECT 1, num
FROM SourceNums
UNION ALL
SELECT 1 + n, 10 * Tuples + num
FROM SourceNums CROSS APPLY nTuples
WHERE CHARINDEX(RTRIM(num), RTRIM(Tuples)) = 0
)
SELECT num1, num2
FROM (
SELECT num1=40006 + 10 * FLOOR(Tuples / 1000)
,num2=3 + 10 * Tuples % 10000
FROM nTuples
WHERE n = 6) a
WHERE num1 - num2 = 33333
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 13, 2012 at 9:53 pm
And here's one specially built for ChrisM@Work, who loves cascading CROSS APPLYs:
;WITH SourceNums AS (
SELECT num FROM (VALUES (1),(2),(5),(7),(8),(9)) a (num)
), MyNums AS (
SELECT num=a.num + 10*b.num + 100*c.num + 1000*d.num + 10000*e.num + 100000*f.num
FROM SourceNums a
CROSS APPLY (SELECT num FROM SourceNums b WHERE num <> a.num) b
CROSS APPLY (SELECT num FROM SourceNums c WHERE num NOT IN(a.num, b.num)) c
CROSS APPLY (SELECT num FROM SourceNums d WHERE num NOT IN(a.num, b.num, c.num)) d
CROSS APPLY (SELECT num FROM SourceNums e WHERE num NOT IN(a.num, b.num, c.num, d.num)) e
CROSS APPLY (SELECT num FROM SourceNums f WHERE num NOT IN(a.num, b.num, c.num, d.num, e.num)) f)
SELECT num1, num2
FROM (
SELECT num1=40006 + 10 * FLOOR(num / 1000)
,num2=3 + 10 * num % 10000
FROM MyNums) a
WHERE num1 - num2 = 33333
Note the ambiguity resolution of num in the CROSS APPLYs. Runs about the same as Eugene's too.
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 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply