TSQL Sudoku II

As luck would have it, today I came across this TSQL Challenge.  It just so happens that I had already worked on a SQL Sudoku script and blogged about it less than one week ago – here.

Since posting that last article on my work on that script, I had already gone to work figuring out a few things to improve it.  One was to improve performance.  I felt it was a little slow and had an idea of what was causing it.  I’ll talk a little about that later.  The other issue was to present the output in a grid.  The first half of that was quickly solved too through a suggestion to do some string manipulation on the output.  I’ll also point that out in a bit.

If you read the Challenge from the first link, there is a requirement for it to be a grid output.  There is also what appears to be a requirement to be able to solve these puzzles through data stored in a table.  The solution I posted last week doesn’t do either of those things.  Soooo, that meant I needed to be able to do those things if I wanted to submit for this challenge.  One of the requirements was already on my to-do list – so not really that much different than my intents anyway.

For the purposes of this blog, I will only post the parts of my solution relevant to solving a puzzle from a string input.  The solution I submitted, solves from both string input as well as from table data (woot woot).

Let’s first look at the performance issue I was experiencing.  I suspected that the performance lag was related to constantly hitting back to the CTE called dual.  What I found was that there was only one very specific place that was causing the slowness.  The anchor of the recursive CTE that solves the Sudoku was referencing the dual CTE and it was dogging the performance.  The original looked like this:

,x( s, ind ) as
( select CONVERT(VARCHAR(100),sud), CHARINDEX(' ',Ss.sud ) AS ind
from
( select @SudokuGivens AS sud from dual ) Ss

I changed it to the following and still saw the slowness.

,x( s, ind ) as
( select @SudokuGivens, CHARINDEX(' ',@SudokuGivens ) AS ind
From dual d

Finally, I decided to go ahead and whack dual from that section of code.  I had wanted to leave it because it proved useful in creating a 9 record result set.  I found a way to solve that part too – without the severe performance impact.

,x( s, ind ) as
( select @SudokuGivens, CHARINDEX(' ',@SudokuGivens ) AS ind
--From dual d

This change alone was responsible for reducing the query time for a Sudoku with 30 givens from three seconds down to < 300ms.  That was a marked improvement.  I saw similar results in performance gains when working with more difficult puzzles such as those with only 19 givens.

The next thing that needed to be done was to display only the relevant data for each of the 9 rows.  Initially the solution just provides a single 81 character data string.  The fix for that is as follows.

select TOP 9 SUBSTRING(s, ROW_NUMBER() OVER (ORDER BY s) * 9 - 8, ROW_NUMBER() OVER (ORDER BY s) * 9 - (ROW_NUMBER() OVER (ORDER BY s) * 9 - 9)) as ConcatRow
FROM x
Cross Apply dual
WHERE ind = 0

The code to split out the substrings was suggested to me with a minor flaw.  Each row only produced 8 digits in the result set.  This was quickly fixed by adjusting the length parameter of the substring function.  Also note that I have a Cross Apply back to the dual cte here.  This wasn’t in the original solution either.  Remember that I removed it from the anchor portion of the recursive CTE?  Well, to get the 9 record result set that I wanted, I needed to put it back somewhere.  Admittedly, this could be faster by doing a Cross Apply to a value set rather than the dual cte.  It could save a bit of memory too – I still need to test that.  Maybe I will submit another solution to the challenge with that fix if it works better.

That pretty much takes care of the performance problem as well as the first part of creating the grid.  To finish building the grid, I used a table variable and a Pivot (as I had planned).  The table variable is rather straight forward.

Declare @SudTableTable (RowCol Int Primary Key Clustered, ConcatRow varchar(10))

I populated that Table with the following after the ctes.  Nothing real fancy here.

Insert Into @SudTable (RowCol,ConcatRow)
Select Row_Number() Over (Order by (Select 1)),ConcatRow
From Sud

This is where the code starts to get a bit fancy.  I am getting better at using the Pivot function, but sometimes it seems a bit tricky.  For instance, this time around, my numbers wouldn’t work out very well.  I figured out that the order of my columns in the Select has an impact on the Pivot as well.  Now I know.  Anyway, here is the Pivot functionality to move things into a grid.

Select @SudokuNo as SudokuNo,c1.RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
From @SudTable S
Cross Apply (Select RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
From (Select S.RowCol
,ColNo = Row_Number() Over (Partition By RowCol Order By ConcatRow)
,Data = SUBSTRING(ConcatRow, ROW_NUMBER() OVER (Partition By S.RowCol ORDER BY ConcatRow), 1)
From @SudTable S
Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
) Intr
Pivot (
Max(Data)
FOR ColNo IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])
) PVT) as C1
Where c1.RowCol = S.RowCol
Order by S.RowCol Asc

Not only is it important to have the columns just right, you also need to have the windowed functions done just right in order to produce a full result set.  Notice here that I do a couple Cross Apply calls.  The first is to get that Subquery result set with the pivots.  The second is using the Cross Apply against a value set.  Note that I am not using Dual in this case.  Simply put, I can’t.  I have an Insert statement between the CTEs and this Select/Pivot statement.

Another important element here is the final where clause.  This simple addition reduces my final result set from 81 records to just the 9 that I desired.  All of that, I get a grid result and the solution is done in ~40ms given the puzzle provided with the challenge.  Not too bad.

Here is the entire updated script to solve these puzzles from a string input.

set statistics time on
Declare @SudokuNo Int = 1 --this is relevant to solving from table data ;)
,@SudokuGivens VARCHAR(100) = null
Declare @SudTableTable (RowCol Int Primary Key Clustered, ConcatRow varchar(10))
SET @SudokuGivens = '53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79' 
--Solve the Sudoku - into a string
;WITH dual(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
),x( s, ind ) as
( select @SudokuGivens, CHARINDEX(' ',@SudokuGivens ) AS ind
  union all
  select CONVERT(VARCHAR(100),substring( s, 1, ind - 1 ) + z + substring( s, ind + 1 ,81))
       , CHARINDEX( ' ', s, ind + 1 ) AS ind
  from x
    CROSS APPLY (
select convert(VARCHAR(25), N ) z
 from dual
 Where N <= 9
       ) z (z)
  where ind > 0
  and not exists (select null
from (
select N AS lp
from dual
Where N <= 9
                        ) ww (lp)
where z = substring( s, ( ind - 1)% 9  - 8 + lp * 9, 1 )
or    z = substring( s, ( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
or    z = substring( s, (( ( ind - 1 ) / 3 )%3) * 3
                                      + ( ( ind - 1 ) / 27 ) * 27 + lp
                                      + ( ( lp - 1 ) / 3 ) * 6
                                   , 1 )
                 )
), Sud as (
--Create a 9 record result set that has the string solution duplicated 9 times.  Then show only relevant 9 data for each row
select TOP 9 SUBSTRING(s, ROW_NUMBER() OVER (ORDER BY s) * 9 - 8, ROW_NUMBER() OVER (ORDER BY s) * 9 - (ROW_NUMBER() OVER (ORDER BY s) * 9 - 9)) as ConcatRow
FROM x
Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) y(N)
WHERE ind = 0
)
--Populate a Table Variable for further Row/Col manipulation
Insert Into @SudTable (RowCol,ConcatRow)
Select Row_Number() Over (Order by (Select 1)),ConcatRow
From Sud
--Pivot the data out to produce a 9x9 grid
Select @SudokuNo as SudokuNo,c1.RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
From @SudTable S
Cross Apply (Select RowCol,[1],[2],[3],[4],[5],[6],[7],[8],[9]
From (Select S.RowCol
,ColNo = Row_Number() Over (Partition By RowCol Order By ConcatRow)
,Data = SUBSTRING(ConcatRow, ROW_NUMBER() OVER (Partition By S.RowCol ORDER BY ConcatRow), 1)
From @SudTable S
Cross Apply ( VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) x(N)
) Intr
Pivot (
Max(Data)
FOR ColNo IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])
) PVT) as C1
Where c1.RowCol = S.RowCol
Order by S.RowCol Asc

Once the challenge has been closed, I will consider revisiting this solution and posting the remainder of the script pertinent to solving a puzzle from table data.  I have adjusted my script to work for either method (from string or from a table like that in the Challenge).  I did that because I couldn’t really see somebody taking that much time to set up the data in a table in order to quickly solve the Sudoku puzzle in the Sunday paper.

I hope you enjoy these little improvements.