SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 Just out of curiosity		  New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 02/05/2008 :  06:41:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
why doesn't this work the way I thought it should work?

I create a #Sudoku table and then create nine unique indexes (one for each column).
In the select statement I get all possible combinations of 1-9.
I also make sure there are no duplicates "per record".

Then I thougth the nine unique indexes should deal with duplicates per "column".

But this is not happening. I get only 1-3 records per execution.
Every execution takes about 5 seconds.

DROP TABLE	#Sudoku

CREATE TABLE	#Sudoku
		(
			Row INT IDENTITY(1, 1),
			Col1 TINYINT,
			Col2 TINYINT,
			Col3 TINYINT,
			Col4 TINYINT,
			Col5 TINYINT,
			Col6 TINYINT,
			Col7 TINYINT,
			Col8 TINYINT,
			Col9 TINYINT
		)

CREATE UNIQUE NONCLUSTERED INDEX [IX_Col1] ON #Sudoku (Col1) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col2] ON #Sudoku (Col2) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col3] ON #Sudoku (Col3) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col4] ON #Sudoku (Col4) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col5] ON #Sudoku (Col5) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col6] ON #Sudoku (Col6) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col7] ON #Sudoku (Col7) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col8] ON #Sudoku (Col8) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Col9] ON #Sudoku (Col9) WITH (IGNORE_DUP_KEY = ON)

INSERT		#Sudoku
		(
			Col1,
			Col2,
			Col3,
			Col4,
			Col5,
			Col6,
			Col7,
			Col8,
			Col9
		)
SELECT		c1.Number,
		c2.Number,
		c3.Number,
		c4.Number,
		c5.Number,
		c6.Number,
		c7.Number,
		c8.Number,
		c9.Number
FROM		master..spt_values AS c1
INNER JOIN	master..spt_values AS c2 ON c2.Type = 'p'
INNER JOIN	master..spt_values AS c3 ON c3.Type = 'p'
INNER JOIN	master..spt_values AS c4 ON c4.Type = 'p'
INNER JOIN	master..spt_values AS c5 ON c5.Type = 'p'
INNER JOIN	master..spt_values AS c6 ON c6.Type = 'p'
INNER JOIN	master..spt_values AS c7 ON c7.Type = 'p'
INNER JOIN	master..spt_values AS c8 ON c8.Type = 'p'
INNER JOIN	master..spt_values AS c9 ON c9.Type = 'p'
WHERE		c1.Type = 'p'
		AND c1.Number BETWEEN 1 AND 9
		AND c2.Number BETWEEN 1 AND 9
		AND c3.Number BETWEEN 1 AND 9
		AND c4.Number BETWEEN 1 AND 9
		AND c5.Number BETWEEN 1 AND 9
		AND c6.Number BETWEEN 1 AND 9
		AND c7.Number BETWEEN 1 AND 9
		AND c8.Number BETWEEN 1 AND 9
		AND c9.Number BETWEEN 1 AND 9
		AND c1.Number NOT IN (c2.Number, c3.Number, c4.Number, c5.Number, c6.Number, c7.Number, c8.Number, c9.Number)
		AND c2.Number NOT IN (c1.Number, c3.Number, c4.Number, c5.Number, c6.Number, c7.Number, c8.Number, c9.Number)
		AND c3.Number NOT IN (c1.Number, c2.Number, c4.Number, c5.Number, c6.Number, c7.Number, c8.Number, c9.Number)
		AND c4.Number NOT IN (c1.Number, c2.Number, c3.Number, c5.Number, c6.Number, c7.Number, c8.Number, c9.Number)
		AND c5.Number NOT IN (c1.Number, c2.Number, c3.Number, c4.Number, c6.Number, c7.Number, c8.Number, c9.Number)
		AND c6.Number NOT IN (c1.Number, c2.Number, c3.Number, c4.Number, c5.Number, c7.Number, c8.Number, c9.Number)
		AND c7.Number NOT IN (c1.Number, c2.Number, c3.Number, c4.Number, c5.Number, c6.Number, c8.Number, c9.Number)
		AND c8.Number NOT IN (c1.Number, c2.Number, c3.Number, c4.Number, c5.Number, c6.Number, c7.Number, c9.Number)
		AND c9.Number NOT IN (c1.Number, c2.Number, c3.Number, c4.Number, c5.Number, c6.Number, c7.Number, c8.Number)

SELECT	*
FROM	#Sudoku


E 12°55'05.25"
N 56°04'39.16"

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts
Posted - 02/05/2008 :  06:48:22  Show Profile  Reply with Quote
Yeah - that's right. You sure you don't want composite indexes? At max this table can have nine rows...
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts
Posted - 02/05/2008 :  06:55:21  Show Profile  Reply with Quote
Sorry - I'm wrong - I don't know why only 3. But I can see why no more than 9...

EDIT - duh - yes I can. Each unique index omits rows.
Edited by - pootle_flump on 02/05/2008 06:56:29
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 02/05/2008 :  07:11:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Exactly.
Every unique index should make sure there were no more than 9 records in the table.
And since I INSERT all possible combinations I should get a sudoku board of 9 rows and 9 columns.
The INSERT statement itself make sure there are no duplicate value along the record itself.

Any more ideas?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts
Posted - 02/05/2008 :  07:46:16  Show Profile  Reply with Quote
Oh - you are populating a board. I thought you were preparing some sort of brute force solver. The only thing interesting with the records that get through for me are that they are (inferred from the identity values) from the "beginning" or "end" of the result set.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 02/05/2008 :  08:53:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Almost always I get Row #1. The other one or two records are from the middle and end.
They are not consistent either. Sometime I get first and last row, sometimes first and 10,000th row.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 02/05/2008 :  10:49:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If I only create 1 of the 9 indexes, I get 9 records as expected.
For 2 or more indexes, I only get 2 or 3 records even if I insert all combinations...


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts
Posted - 02/05/2008 :  11:14:46  Show Profile  Reply with Quote
Is this inspired by Craig Freedman's recent blog on IGNORE_DUP_KEY?

http://blogs.msdn.com/craigfr/archive/2008/01/30/maintaining-unique-indexes-with-ignore-dup-key.aspx
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 02/05/2008 :  12:00:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No, I am optimizing my application for with Belbin usage
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68606

and I thought a Sudoku approach would be more appealing.

The link you provided give some clues but not why it doesn't work.
Not even with less columns I get it to work. 
CREATE TABLE	#Sample
		(
			SendID TINYINT,
			RecvID TINYINT
		)

CREATE UNIQUE NONCLUSTERED INDEX IX_SendID ON #Sample (SendID) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX IX_RecvID ON #Sample (RecvID) WITH (IGNORE_DUP_KEY = ON)

INSERT		#Sample
		(
			SendID,
			RecvID
		)
SELECT		v1.Number,
		v2.Number
FROM		master..spt_values AS v1
INNER JOIN	master..spt_values AS v2 ON v2.Type = 'p'
WHERE		v1.Type = 'p'
		AND v1.Number BETWEEN 1 AND 3
		AND v2.Number BETWEEN 1 AND 3
		AND v1.Number <> v2.Number
ORDER BY	NEWID()

SELECT	SendID,
	RecvID
FROM	#Sample
I often get this result back
SendID	RecvID
1	2
2	3
The mystery is why I don't get 3 1 back too...


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts
Posted - 02/05/2008 :  12:08:39  Show Profile  Reply with Quote
If the problem you're trying to solve is "Six different regiments have six officers, each one belonging to different ranks. Can these 36 officers be arranged in a square formation so that each row and column contains one officer of each rank and one of each regiment?" Then I think the answer's no.

Sorry, I don't know anything about IGNORE_DUP_KEY.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 02/05/2008 :  12:16:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I think I can, because 1 out of 15 execution I do get full formation back, as
SendID	RecvID
1	2
2	3
3	1 -- Often missing record

The mystery for me is that why don't I always get three records back?

I know if 2 first records are
SendID	RecvID
1	2
2	1
We have a circular reference.
I also can't put 3 3 to the resultset because then Sender and Receiver is same person.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 02/05/2008 :  12:19:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by Arnold Fribble

If the problem you're trying to solve is

"Six different regiments have six officers, each one belonging to different ranks. Can these 36 officers be arranged in a square formation so that each row and column contains one officer of each rank and one of each regiment?"

Then I think the answer's no.
Please don't say it can't be done.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts
Posted - 02/05/2008 :  12:27:00  Show Profile  Reply with Quote
quote:
Please don't say it can't be done.


If you Tarry longer, I shall give worse payment!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11741 Posts
Posted - 02/05/2008 :  12:27:57  Show Profile  Visit spirit1's Homepage  Reply with Quote
these are Euler squares and the only impossible Euler squares are of order two and six.
http://mathdl.maa.org/convergence/1/?pa=content&sa=viewDocument&nodeId=1434&pf=1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 02/05/2008 :  12:34:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes, but I only need Latin squares.
For me

A B
B A

is a valid result, because I don't care where the officers are located (color).
I only need to combine their skills "A or "B" (ranks).

E 12°55'05.25"
N 56°04'39.16"
Edited by - SwePeso on 02/05/2008 12:38:28
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts
Posted - 02/05/2008 :  16:43:06  Show Profile  Reply with Quote
quote:
The mystery for me is that why don't I always get three records back?


Heh, have you tried it with OPTION (MAXDOP 1) ?

I think the short answer to why it isn't going to insert 9 rows is simply that it it's inserting greedily (well, you can hardly expect it to backtrack!) and it's really unlikely to see the permutations in an order that allows it.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 02/05/2008 :  16:46:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Thanks for the input, but as you can see in my simplified example posted 02/05/2008 : 12:00:10, also this doesn't work.
One out of 6 executions, I get a valid result, for 1 I get "circular reference" and the other 4 executions I incomplete result.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

nathans
Aged Yak Warrior

USA
933 Posts
Posted - 02/05/2008 :  22:59:06  Show Profile  Reply with Quote
quote:
Originally posted by Peso

The mystery for me is that why don't I always get three records back?

I know if 2 first records are
SendID	RecvID
1	2
2	1
We have a circular reference.


Why not order by v2.Number to ensure your first two rows are:
2	1
3	1


What is purpose of newid() ?

Nathan Skerl
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 02/06/2008 :  03:26:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
2 1
3 1

Is not a valid resultset, since an ID can only be present once in any column.

I think I will stick with my while loop for the time being.

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

nathans
Aged Yak Warrior

USA
933 Posts
Posted - 02/06/2008 :  12:46:07  Show Profile  Reply with Quote
Why does the insert order matter? Also, the order in which you create the index seems to affect whether you get the 3 rows or not.

In this example, I switch the ordering of the insert. But the same result occurs if you only switch the order of the index creates.


CREATE TABLE #Sample (SendID TINYINT, RecvID TINYINT)
CREATE UNIQUE NONCLUSTERED INDEX IX_SendID ON #Sample (SendID) WITH (IGNORE_DUP_KEY = ON)
CREATE UNIQUE NONCLUSTERED INDEX IX_RecvID ON #Sample (RecvID) WITH (IGNORE_DUP_KEY = ON)

-- insert ordered by SendID
INSERT #Sample (SendID, RecvID)
	select 1, 2 union all
	select 1, 3 union all
	select 2, 1 union all
	select 2, 3 union all
	select 3, 1 union all
	select 3, 2

SELECT 'SendID' [InsOrder], * FROM #Sample

delete #Sample

INSERT #Sample (SendID, RecvID)
	select 2, 1 union all
	select 3, 1 union all
	select 1, 2 union all
	select 3, 2 union all
	select 1, 3 union all
	select 2, 3

SELECT 'RecvID' [InsOrder], * FROM #Sample

drop table #Sample
go


Nathan Skerl
Go to Top of Page

nathans
Aged Yak Warrior

USA
933 Posts
Posted - 02/06/2008 :  12:55:56  Show Profile  Reply with Quote
Ok, Craigs most recent post [see Arnolds link above] explains this pretty well.

Nathan Skerl
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000