September 14, 2012 at 2:08 am
dwain.c (9/13/2012)
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.
Here's a less ambiguous version
;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 b.num FROM SourceNums b WHERE b.num <> a.num) b
CROSS APPLY (SELECT c.num FROM SourceNums c WHERE c.num NOT IN(a.num, b.num)) c
CROSS APPLY (SELECT d.num FROM SourceNums d WHERE d.num NOT IN(a.num, b.num, c.num)) d
CROSS APPLY (SELECT e.num FROM SourceNums e WHERE e.num NOT IN(a.num, b.num, c.num, d.num)) e
CROSS APPLY (SELECT f.num FROM SourceNums f WHERE f.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
😀
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 14, 2012 at 3:41 am
ChrisM@Work (9/14/2012)
dwain.c (9/13/2012)
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.
Here's a less ambiguous version
;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 b.num FROM SourceNums b WHERE b.num <> a.num) b
CROSS APPLY (SELECT c.num FROM SourceNums c WHERE c.num NOT IN(a.num, b.num)) c
CROSS APPLY (SELECT d.num FROM SourceNums d WHERE d.num NOT IN(a.num, b.num, c.num)) d
CROSS APPLY (SELECT e.num FROM SourceNums e WHERE e.num NOT IN(a.num, b.num, c.num, d.num)) e
CROSS APPLY (SELECT f.num FROM SourceNums f WHERE f.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
😀
Damn it! You guys have wasted my morning! 😛
Here was my attempt, obviously should've read the whole thread through first: -
SELECT num1, num2
FROM (SELECT 40006 + 10 * FLOOR(N / 1000), 3 + 10 * N % 10000
FROM (SELECT a.N + 10 * b.N + 100 * c.N + 1000 * d.N + 10000 * e.N + 100000 * f.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) a(N)
CROSS APPLY (SELECT b.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) b(N)
WHERE b.N <> a.N) b
CROSS APPLY (SELECT c.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) c(N)
WHERE c.N NOT IN(a.N, b.N)) c
CROSS APPLY (SELECT d.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) d(N)
WHERE d.N NOT IN(a.N, b.N, c.N)) d
CROSS APPLY (SELECT e.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) e(N)
WHERE e.N NOT IN(a.N, b.N, c.N, d.N)) e
CROSS APPLY (SELECT f.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) f(N)
WHERE f.N NOT IN(a.N, b.N, c.N, d.N, e.N)) f
) a(N)
)a(num1,num2)
WHERE num1 - num2 = 33333;
September 14, 2012 at 7:29 am
Cadavre - Cool idea! Here's a terser version of yours:
;WITH MyNums AS (
SELECT num=a.num + 10*b.num + 100*c.num + 1000*d.num + 10000*e.num + 100000*f.num
FROM (VALUES (1),(2),(5),(7),(8),(9)) a (num)
CROSS APPLY (VALUES (1),(2),(5),(7),(8),(9)) b (num)
CROSS APPLY (VALUES (1),(2),(5),(7),(8),(9)) c (num)
CROSS APPLY (VALUES (1),(2),(5),(7),(8),(9)) d (num)
CROSS APPLY (VALUES (1),(2),(5),(7),(8),(9)) e (num)
CROSS APPLY (VALUES (1),(2),(5),(7),(8),(9)) f (num)
WHERE b.num <> a.num AND
c.num NOT IN (a.num, b.num) AND
d.num NOT IN (a.num, b.num, c.num) AND
e.num NOT IN (a.num, b.num, c.num, d.num) AND
f.num NOT IN (a.num, b.num, c.num, d.num, e.num))
SELECT num1, num2
FROM (
SELECT num1=40006 + 10 * FLOOR(num / 1000)
,num2=3 + 10 * num % 10000
FROM MyNums
) 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 14, 2012 at 10:38 am
Do you always give untested results?
Where are Series defined? Where's DDL? Where's the data? Why aren't you using SQL-92 JOINS that are more clear? Are you stuck in the past?
September 15, 2012 at 8:59 am
Another solution, I think shorter and more efficient, this because only 865 rows need to be generated and inspected.
with a as ( select 123 as n union all select 1 + n from a where n < 987),
b as (select 'first: ' +convert(char(5),4*10000+n*10+6) +
' second: ' +convert(char(4),4*10000+n*10+6-33333) as s from a)
select '--' [--], * from b where
patindex('%1%',s)*patindex('%2%',s)*patindex('%3%',s)*patindex('%5%',s)*
patindex('%7%',s)*patindex('%8%',s)*patindex('%9%',s)>0 option (maxrecursion 999)
This code results in:
-- S
---- ---------------------------
-- First : 49516 Second : 2837
The puzzle looks very similar to The professor Layton Nintendo DS Curious Village game. (Puzzle 99, 33333)
Puzzle : Use each of the number one to nine exactly once to fill in the blanks an complete this equation.
_ _ _ _ _ - _ _ _ _ = 33333
Solving this puzzle in a similar way is far more efficient than (most) of the solutions I have seen here.
The number of rows to inspect for this puzzel would be (9876-1234+1)=8643, which is far less than the 9^9=387420489 rows on a completely brute force method. A solution to the Professor Laytons Puzzle is below a spoiler alert.
I am not completely happy with using the CTE recursion to generate the first set, but the notation is fairly short.
Thanks for sharing the puzzle,
Ben Brugman
SPOILER ALERT
SPOILER ALERT
-- Laytons Puzzle 99 33333
with a as ( select 1234 as n union all select 1 + n from a where n < 9876),
b as (select 'first: ' +convert(char(5),33333+n) +
' second: '+convert(char(4), n) as s from a)
select '--' [--], * from b where
1.0*patindex('%1%',s)*patindex('%2%',s)*patindex('%3%',s)*patindex('%4%',s)*
patindex('%5%',s)*patindex('%6%',s)*patindex('%7%',s)*patindex('%8%',s)*patindex('%9%',s)>0
option (maxrecursion 9999))
September 17, 2012 at 1:43 am
ben.brugman (9/15/2012)
Another solution, I think shorter and more efficient, this because only 865 rows need to be generated and inspected.
with a as ( select 123 as n union all select 1 + n from a where n < 987),b as (select 'first: ' +convert(char(5),4*10000+n*10+6) +
' second: ' +convert(char(4),4*10000+n*10+6-33333) as s from a)
select '--' [--], * from b where
patindex('%1%',s)*patindex('%2%',s)*patindex('%3%',s)*patindex('%5%',s)*
patindex('%7%',s)*patindex('%8%',s)*patindex('%9%',s)>0 option (maxrecursion 999)
This code results in:
-- S
---- ---------------------------
-- First : 49516 Second : 2837
Easy way to check the efficiency: -
SET NOCOUNT ON;
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'Eugene Elutin';
PRINT REPLICATE('-',80);
with a(num) as
(select 1 union select 2 union select 5 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) + '3')
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 distinct *, num1-num2 from b
where num1-num2 = 33333
order by 1,2;
PRINT REPLICATE('-',80);
PRINT 'TIME TAKEN';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
GO
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'GSquared';
PRINT REPLICATE('-',80);
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;
PRINT REPLICATE('-',80);
PRINT 'TIME TAKEN';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
GO
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'Eugene Elutin';
PRINT REPLICATE('-',80);
;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;
PRINT REPLICATE('-',80);
PRINT 'TIME TAKEN';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
GO
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'ChrisM@Work';
PRINT REPLICATE('-',80);
;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 e.num NOT IN (a.num, b.num, c.num, d.num)
AND f.num NOT IN (a.num, b.num, c.num, d.num, e.num)
)
SELECT num1, num2
FROM b
WHERE CAST(num1 AS INT) - CAST(num2 AS INT) = 33333;
PRINT REPLICATE('-',80);
PRINT 'TIME TAKE: ';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
GO
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'ChrisM@Work / Dwain';
PRINT REPLICATE('-',80);
;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;
PRINT REPLICATE('-',80);
PRINT 'TIME TAKE: ';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
GO
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'Michael Valentine Jones';
PRINT REPLICATE('-',80);
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%';
PRINT REPLICATE('-',80);
PRINT 'TIME TAKE: ';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
GO
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'ScottPletcher';
PRINT REPLICATE('-',80);
;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;
PRINT REPLICATE('-',80);
PRINT 'TIME TAKE: ';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
GO
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'Dwain';
PRINT REPLICATE('-',80);
;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;
PRINT REPLICATE('-',80);
PRINT 'TIME TAKE: ';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
GO
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'Dwain';
PRINT REPLICATE('-',80);
;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 ;
PRINT REPLICATE('-',80);
PRINT 'TIME TAKE: ';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
GO
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'Cadavre';
PRINT REPLICATE('-',80);
SELECT num1, num2
FROM (SELECT 40006 + 10 * FLOOR(N / 1000), 3 + 10 * N % 10000
FROM (SELECT a.N + 10 * b.N + 100 * c.N + 1000 * d.N + 10000 * e.N + 100000 * f.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) a(N)
CROSS APPLY (SELECT b.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) b(N)
WHERE b.N <> a.N) b
CROSS APPLY (SELECT c.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) c(N)
WHERE c.N NOT IN(a.N, b.N)) c
CROSS APPLY (SELECT d.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) d(N)
WHERE d.N NOT IN(a.N, b.N, c.N)) d
CROSS APPLY (SELECT e.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) e(N)
WHERE e.N NOT IN(a.N, b.N, c.N, d.N)) e
CROSS APPLY (SELECT f.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) f(N)
WHERE f.N NOT IN(a.N, b.N, c.N, d.N, e.N)) f
) a(N)
)a(num1,num2)
WHERE num1 - num2 = 33333;
PRINT REPLICATE('-',80);
PRINT 'TIME TAKE: ';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
GO
DECLARE @time DATETIME2 = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'ben.brugman';
PRINT REPLICATE('-',80);
with a as ( select 123 as n union all select 1 + n from a where n < 987),
b as (select 'first: ' +convert(char(5),4*10000+n*10+6) +
' second: ' +convert(char(4),4*10000+n*10+6-33333) as s from a)
select '--' [--], * from b where
patindex('%1%',s)*patindex('%2%',s)*patindex('%3%',s)*patindex('%5%',s)*
patindex('%7%',s)*patindex('%8%',s)*patindex('%9%',s)>0 option (maxrecursion 999);
PRINT REPLICATE('-',80);
PRINT 'TIME TAKE: ';
PRINT DATEDIFF(MS,@time,SYSDATETIME());
PRINT REPLICATE('-',80);
Results: -
--------------------------------------------------------------------------------
Eugene Elutin
--------------------------------------------------------------------------------
num1 num2
-------------------- -------------------- --------------------
41286 7953 33333
--------------------------------------------------------------------------------
TIME TAKEN
11
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
GSquared
--------------------------------------------------------------------------------
----- ----
41286 7953
--------------------------------------------------------------------------------
TIME TAKEN
48
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Eugene Elutin
--------------------------------------------------------------------------------
num1 num2
----- ----
41286 7953
--------------------------------------------------------------------------------
TIME TAKEN
5
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
ChrisM@Work
--------------------------------------------------------------------------------
num1 num2
----- ----
41286 7953
--------------------------------------------------------------------------------
TIME TAKE:
4
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
ChrisM@Work / Dwain
--------------------------------------------------------------------------------
num1 num2
----------- -----------
41286 7953
--------------------------------------------------------------------------------
TIME TAKE:
67
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Michael Valentine Jones
--------------------------------------------------------------------------------
x y z c
----------- ----------- ----------- ---------
41286 7953 33333 412867953
--------------------------------------------------------------------------------
TIME TAKE:
4299
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
ScottPletcher
--------------------------------------------------------------------------------
num1 num2
----- ----
41286 7953
--------------------------------------------------------------------------------
TIME TAKE:
0
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Dwain
--------------------------------------------------------------------------------
num1 num2
----------- -----------
41286 7953
--------------------------------------------------------------------------------
TIME TAKE:
32
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Dwain
--------------------------------------------------------------------------------
num1 num2
----------- -----------
41286 7953
--------------------------------------------------------------------------------
TIME TAKE:
7
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Cadavre
--------------------------------------------------------------------------------
num1 num2
----------- -----------
41286 7953
--------------------------------------------------------------------------------
TIME TAKE:
3
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
ben.brugman
--------------------------------------------------------------------------------
-- s
---- -------------------------
-- first: 41286 second: 7953
--------------------------------------------------------------------------------
TIME TAKE:
35
--------------------------------------------------------------------------------
So the fastest ones after one run are: -
ScottPletcher: 0ms
Cadavre: 3ms
ChrisM@Work: 4ms
Eugene Elutin: 5ms
Dwain: 7ms
Eugene Elutin: 11ms
Personally, I'd call those times equivalent. I suspect that the fastest one would change per execution. But I guess you could always execute the script above 100 times then get the averages to determine a winner.
September 17, 2012 at 3:09 am
Cadavre (9/17/2012)
So the fastest ones after one run are: -
ScottPletcher: 0ms
Cadavre: 3ms
ChrisM@Work: 4ms
Eugene Elutin: 5ms
Dwain: 7ms
Eugene Elutin: 11ms
Personally, I'd call those times equivalent. I suspect that the fastest one would change per execution. But I guess you could always execute the script above 100 times then get the averages to determine a winner.
Hello All,
Thanks for the script en the results from the script. I have added some results. (Using 2 distinct machines and using two runs on each).
Used the script of Cadavre to do 4 extra runs, on the Pc and on a server. Used the first and the sixth run on both machines.
Results:
[font="Courier New"].........................Cadavre....Ben.Pc.1...Ben.Pc.6.Ben.sv1..Ben.sv6
Eugene.Elutin............11.........56.........9........21.......20
GSquared.................48.........39.........26.......53.......53
Eugene.Elutin............5..........3..........26.......5........8
ChrisM@Work..............4..........2..........3........4........9
ChrisM@Work./.Dwain......67.........35.........32.......105......100
Michael.Valentine.Jones..4299.......1014.......863......1018.....872
ScottPletcher............0..........0..........0........1........1
Dwain....................32.........21.........22.......58.......59
Dwain....................7..........10.........2........9........8
Cadavre..................3..........2..........2........5........9
ben.brugman..............35.........13.........12.......32.......32
[/font]
Here ScottPletcher is the winner, but he used 'extra' intelligence to solve the puzzle using reasoning he solved part of the puzzle, if that is allowed, some more reasoning must be allowed as wel. (As said just displaying the solution with a single print).
By now I think the 'orginal' puzzle was taken from the Nintendo DS game Professor Layton. And in that puzzle, in the Hints, some positions where given away, but without the hints no positions were given away. So I'll try to extend my own and the fastest scripts to the complete puzzle and see what the timings of those are. (This will take some home time instead of office time).
Again thanks for the timings,
This will help to understand coding/scripts/performance.
Ben Brugman
(Sorry for the dots in the table, is there a solution to add a table to a post ?).
September 17, 2012 at 6:26 am
ben.brugman (9/17/2012)
Hello All,Thanks for the script en the results from the script. I have added some results. (Using 2 distinct machines and using two runs on each).
Used the script of Cadavre to do 4 extra runs, on the Pc and on a server. Used the first and the sixth run on both machines.
Results:
[font="Courier New"].........................Cadavre....Ben.Pc.1...Ben.Pc.6.Ben.sv1..Ben.sv6
Eugene.Elutin............11.........56.........9........21.......20
GSquared.................48.........39.........26.......53.......53
Eugene.Elutin............5..........3..........26.......5........8
ChrisM@Work..............4..........2..........3........4........9
ChrisM@Work./.Dwain......67.........35.........32.......105......100
Michael.Valentine.Jones..4299.......1014.......863......1018.....872
ScottPletcher............0..........0..........0........1........1
Dwain....................32.........21.........22.......58.......59
Dwain....................7..........10.........2........9........8
Cadavre..................3..........2..........2........5........9
ben.brugman..............35.........13.........12.......32.......32
[/font]
Here ScottPletcher is the winner, but he used 'extra' intelligence to solve the puzzle using reasoning he solved part of the puzzle, if that is allowed, some more reasoning must be allowed as wel. (As said just displaying the solution with a single print).
By now I think the 'orginal' puzzle was taken from the Nintendo DS game Professor Layton. And in that puzzle, in the Hints, some positions where given away, but without the hints no positions were given away. So I'll try to extend my own and the fastest scripts to the complete puzzle and see what the timings of those are. (This will take some home time instead of office time).
Again thanks for the timings,
This will help to understand coding/scripts/performance.
Ben Brugman
(Sorry for the dots in the table, is there a solution to add a table to a post ?).
I guess I'd have done it like this: -
SET NOCOUNT ON;
DROP TABLE Performance_Comparision
CREATE TABLE Performance_Comparision(Performance_Comparision_ID INT IDENTITY(1,1), Execution_Number INT, Time_Taken INT, Solution_Name VARCHAR(100),
Solution_ID INT);
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;
DECLARE @iterations INT = 5, @iteration_count INT = 1, @time DATETIME2;
WHILE @iterations >= @iteration_count
BEGIN
SET @time = SYSDATETIME();
with a(num) as
(select 1 union select 2 union select 5 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) + '3')
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 distinct *, num1-num2 from b
where num1-num2 = 33333
order by 1,2;
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'Eugene Elutin', 1;
SET @time = 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;
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'GSquared',2;
SET @time = 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;
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'Eugene Elutin',3;
SET @time = SYSDATETIME();
;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 e.num NOT IN (a.num, b.num, c.num, d.num)
AND f.num NOT IN (a.num, b.num, c.num, d.num, e.num)
)
SELECT num1, num2
FROM b
WHERE CAST(num1 AS INT) - CAST(num2 AS INT) = 33333;
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'ChrisM@Work',4;
SET @time = 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(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;
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'ChrisM@Work / Dwain',5;
SET @time = SYSDATETIME();
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%';
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'Michael Valentine Jones',6;
SET @time = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'ScottPletcher';
PRINT REPLICATE('-',80);
;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;
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'ScottPletcher',7;
SET @time = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'Dwain';
PRINT REPLICATE('-',80);
;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;
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'Dwain',8;
SET @time = SYSDATETIME();
;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 ;
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'Dwain',9;
SET @time = SYSDATETIME();
PRINT REPLICATE('-',80);
PRINT 'Cadavre';
PRINT REPLICATE('-',80);
SELECT num1, num2
FROM (SELECT 40006 + 10 * FLOOR(N / 1000), 3 + 10 * N % 10000
FROM (SELECT a.N + 10 * b.N + 100 * c.N + 1000 * d.N + 10000 * e.N + 100000 * f.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) a(N)
CROSS APPLY (SELECT b.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) b(N)
WHERE b.N <> a.N) b
CROSS APPLY (SELECT c.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) c(N)
WHERE c.N NOT IN(a.N, b.N)) c
CROSS APPLY (SELECT d.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) d(N)
WHERE d.N NOT IN(a.N, b.N, c.N)) d
CROSS APPLY (SELECT e.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) e(N)
WHERE e.N NOT IN(a.N, b.N, c.N, d.N)) e
CROSS APPLY (SELECT f.N
FROM (VALUES (1),(2),(5),(7),(8),(9)) f(N)
WHERE f.N NOT IN(a.N, b.N, c.N, d.N, e.N)) f
) a(N)
)a(num1,num2)
WHERE num1 - num2 = 33333;
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'Cadavre',10;
SET @time = SYSDATETIME();
with a as ( select 123 as n union all select 1 + n from a where n < 987),
b as (select 'first: ' +convert(char(5),4*10000+n*10+6) +
' second: ' +convert(char(4),4*10000+n*10+6-33333) as s from a)
select '--' [--], * from b where
patindex('%1%',s)*patindex('%2%',s)*patindex('%3%',s)*patindex('%5%',s)*
patindex('%7%',s)*patindex('%8%',s)*patindex('%9%',s)>0 option (maxrecursion 999);
INSERT INTO Performance_Comparision(Execution_Number, Time_Taken, Solution_Name, Solution_ID)
SELECT @iteration_count, DATEDIFF(MS,@time,SYSDATETIME()), 'ben.brugman',11;
SET @iteration_count = @iteration_count + 1;
END
SELECT Solution_ID, Solution_Name, Time_Taken/@iterations AS Time_Taken
FROM (SELECT Solution_ID, MAX(Solution_Name), SUM(Time_Taken)
FROM Performance_Comparision
GROUP BY Solution_ID) a(Solution_ID, Solution_Name, Time_Taken);
Results: -
Solution_ID Solution_Name Time_Taken
----------- ---------------------------------------------------------------------------------------------------- -----------
1 Eugene Elutin 9
2 GSquared 37
3 Eugene Elutin 3
4 ChrisM@Work 3
5 ChrisM@Work / Dwain 53
6 Michael Valentine Jones 1215
7 ScottPletcher 0
8 Dwain 34
9 Dwain 3
10 Cadavre 0
11 ben.brugman 28
That's over 5 iterations. Shall we try 100?
Solution_ID Solution_Name Time_Taken
----------- ---------------------------------------------------------------------------------------------------- -----------
1 Eugene Elutin 8
2 GSquared 35
3 Eugene Elutin 2
4 ChrisM@Work 3
5 ChrisM@Work / Dwain 49
6 Michael Valentine Jones 1208
7 ScottPletcher 0
8 Dwain 31
9 Dwain 2
10 Cadavre 3
11 ben.brugman 18
Please note that I'm not preserving decimals.
September 17, 2012 at 8:42 am
ben.brugman (9/17/2012)
Here ScottPletcher is the winner, but he used 'extra' intelligence to solve the puzzle using reasoning he solved part of the puzzle, if that is allowed, some more reasoning must be allowed as wel. (As said just displaying the solution with a single print).
Yes, but not unfairly so; the values I used are always inherent in the problem. In order to solve the problem, you must know at least one of the desired values (in this case "33333"), so I took advantage of that fact to reduce the data permutations. If all three values were unknown, you couldn't solve the problem anyway :-).
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 17, 2012 at 2:38 pm
ScottPletcher (9/17/2012)
Yes, but not unfairly so; the values I used are always inherent in the problem. In order to solve the problem, you must know at least one of the desired values (in this case "33333"), so I took advantage of that fact to reduce the data permutations. If all three values were unknown, you couldn't solve the problem anyway :-).
Hello Scott and other readers.
With the intelligence used I was revering to the knowledge where the '3' was positioned. So for me personally I think that the script should solve that. (Or the time needed needed to find that out should be added to the time :-)). I think this because the puzzle can be solved and then a print would be sufficient.
So what I did was rebuild Scott's script leaving out the knowledge where the 3 should go in the solution.
The results were not that different from if the knowledge was there, so taking out this knowledge of the position of the 3 Scott's script was still the fastest.
So going on from there I took out the knowledge of the position of the 4 and of the 6. That also produced a fast solution.
Kudo's to you Scott,
So thanks Scott, this is a learning moment for me.
So even if you have a potentially far larger amount of rows to process eliminating on the fly was in this case faster than reducing the number of rows with the design. This did suprise me, so I have learned something from this.
With my orginal design for the puzzle without the knowledge of any of the numbers position, I would have thought that the 'brute' force over 9^9 or 9! would not work, so from the design time on I tried to reduce the number to 10^4 (the second number). But with your strategie you have proven me wrong.
Thanks to everybody,
ben brugman
September 17, 2012 at 6:33 pm
One point that is worthy of note. While the rCTE nTuples solutions didn't make the winners cut here, it does have advantages in certain cases.
It loses because it generates all of the Tuples of lesser order than the desired count (in this case 1 through 6).
In cases where it is possible to prune out of the earlier Tuples before reaching the max Tuple (e.g., if you have another constraint you're applying such as a weight applicable to each element of the Tuple that must not exceed a certain value), it is possible to eliminate non-feasible solutions early in the recursive cycle.
In those sorts of cases, the nTuples solution stands up pretty well.
The methods that use JOIN and Cascading CROSS APPLY must be hard-coded to exactly the Tuple order desired. That is a potential disadvantage of those solutions.
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 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply