THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

How NOT to pass a lot of parameters

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2007/10/how-not-to-pass-a-lot-of-parameters/

Published Thursday, October 4, 2007 10:07 AM by Hugo Kornelis

Attachment(s): Sudoku.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Hugo Kornelis said:

I just realised that I forgot to include the attachment with the scripts to create and test the procedures. This has now been corrected.

October 4, 2007 3:41 AM
 

Denis Gobo said:

Tony Rogerson wrote something similar a while back (only not as nice in tone, I wonder why? <vbg>)

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/17/joe-celko-don-t-use-csv-xml-use-1-000-parameters-instead.aspx

October 4, 2007 8:06 AM
 

Alexander Kuznetsov said:

Hi Hugo,

Actually 81 parameters is no big deal. Our business needs up to 100,000 parameters, and I handle it easily with an image, as described here:

http://sqlblogcasts.com/blogs/alex_kuznetsov/archive/2007/09/23/mimicking-a-table-variable-parameter-with-an-image.aspx

Of course my approach is also based on Erland Sommarskog's article too.

It would be interesting to benchmark it against Celko's approach at its upper limit, let's say 2000 parameters.

October 4, 2007 8:33 AM
 

Hugo Kornelis said:

Hi Denis,

Thanks for the pointer to Tony's article. I did read this at the time it was posted, but somehow forgot about it later. Sorry, Tony - I should have mentioned and linked to your post in my article.

Hi Alexander,

Such benchmarking has already been done, for 1,000 parameters. Just follow the link Denis posted. I don't think Tony used Erland's version, though.

However, I guess it's safe to assume that, given the increase in performance of the many-parameter approach I have already seen when using 81 parameters, you won't find good performance figures when you scale it up to or over the 1,000 parameter mark.

Your 100,000 parameters encoded in an image also looks interesting - I just have no idea how to enter that lightbulb icon into my C# source code :)

Thanks for your comments, Alex and Denis!

October 4, 2007 9:08 AM
 

James Luetkehoelter said:

Another great post Hugo - I often see arguments both ways on large parameter numbers - this spells things out fairly clearly.

October 4, 2007 9:15 AM
 

Alexander Kuznetsov said:

Hugo,

I replaced the [i] lightbulb with [ i ], so it's readable now. Thanks for noticing the problem. Tony in his benchmarks did not use fixed width numbers in binary representation as I do, he used comma separated numbers in character representation, which are slower.

October 4, 2007 9:19 AM
 

Hugo Kornelis said:

Hi Alexander,

There's another lightbulb in the second snippet. Now that I know it represents [i], I understand what you meant - but you might want to fix it for the benefit of future readers! <g>

October 4, 2007 9:46 AM
 

Bart Czernicki said:

Nice article.  Joe Celko answered one of my forum questions in the EXACT manner you replied..not really answering the question, just saying I don't have a clue about how RDBMS works and  I am asking the wrong question.

He is one of those people I would love to call "theory architect".  I worked with a few before..and they love to get in a room and argue the MOST abstract nonsensical crap, while OTHERS are actually doing work.

One thing I have learned thus far in my software dev. career is that "perfect design" doesn't exist nor is it worth it.  I have seen people make a TON of money not using the correct architecture, however they were first to market or idea.  Joe Celko would be the type of person to rip you a new one having seen it and having a smirk on his face how he "set you straight"....while the company is 4 months behind on their deliverable and losing deals in the process.

October 4, 2007 8:50 PM
 

Joe Celko said:

>> To which Joe will always reply that he has received a smart stored procedure that will solve a Sudoku puzzle, and that takes 81 parameters (one for each cell in the puzzle) as its input – unfortunately, Joe has so far refused to actually publish his code to let other people verify his claims. <<

No quite true; it is not MY  code to publish.  It was written by  Richard Romley, a retired SQL expert from Smith-Barney Solomon.  It was in response to an incomplete Sudoku query I had written.  People who have read my columns and books over the decades might remember him as the guy who could cook my puzzles and solutions damn near every time.  I do  not yet have his permission to publish it on my website (it is about 18 pages long)

I got his permission to send the routine to Tony Rogerson, along with an offer to run his own tests remotely or on his own hardware.  Tony bragged that he would pound out a better answer in five minutes, but it has not happened yet as far as I know :)

The procedure is in T-SQL dialect.  You input a 9x9 Sudoku grid (81 parameters), it prints out an ASCII picture of the grid.  It then finds ALL solutions for that grid.  This is part of why Sodoku can be a good math problem!  You need at least 24 numbers in the initial grid to **guarantee** a unique answer.  

If a grid has fewer than that, then it can have multiple solutions and there are 6.671 * 10^21 possible valid grids. In fact, there is an article on Sodoku solving from IEEE SPECTRUM on-line about how it is NP-complete.  Richard found one published puzzle when he was pulling in samples to test his procedure that had 39 answers (it  finds all of them).  

If you want to really freak out, there is a 81-table self-join in the code.  And the response time is in milliseconds for any grid tested so far.  The optimizer guys at MS are better than I would have guessed!  

October 7, 2007 5:15 PM
 

Hugo Kornelis said:

Hi Joe,

I did write that you "received" it. But it's good that you point out once more that the actual Sudoku-solving code has to be attributed -if it ever gets published- to Richard Romley.

Anyway, you now know that you can shave off another ~0.5 milliseconds of the solver's execution time by replacing the 81 parameters with a single char(81) parameter, which was the point of this post.

October 7, 2007 5:44 PM
 

Kalen Delaney said:

How do these solutions compare with Itzik's solution?

http://www.sqlmag.com/Article/ArticleID/47752/Solve_Sudoku_Puzzles_with_TSQL.html

October 11, 2007 6:50 PM
 

Hugo Kornelis said:

Hi Kalen,

Was this question directed at Joe, or at me?

In the former case, I'll await Joe's answer with bated breath.

In the latter case, the answer is that they don't compare at all. I merely focused on how to pass a Sudoku puzzle to a stored proc and skipped the solving part completely :-)

October 13, 2007 2:04 PM
 

William Harris said:

Not sure if this is applicable or not, but hasn't Celko posted the code on his SQL puzzles site?

http://www.celko.com/puzzles.htm

More specifically, here : http://www.celko.com/sudoku.txt

November 26, 2007 9:31 PM
 

Hugo Kornelis said:

Hi William,

Thanks for the pointer. Yes, that must be the code Joe has previously been referring to without having the permission to publish it - appparently, Richard Romley changed his mind.

It's a crying shame that Joe published his code in this form, claiming that this code "demonstrates that a long parameter list is not harmful" (granted, there won't be any smoke in the server room if you run this, but I have proven that there are better [read: faster] alternatives and he doesn't even mention this - I'm sorry, Joe, but pretending my counterarguments don't exist doesn't make you any less wrong).

It's also a shame that the code looks awfully formatted in a browser window (hint: right-click the link, choose "Save as", then open the downloaded file in any text editor). And that he chose to use table names, coolumn names, and variable names that are for the most part completely non-descript.

I am tempted to copy and paste one of Joe's own newsgroup rants about using IEEE-11179, about temp tables mimicking a scratch tape, and so on, but I'll resist temptation. It seems that Joe is perfectly able of making a fool of himself without me driving home the point.

That being said, I'll have to admit that Richard's solution is pretty smart. I'll admit that I did spend an hour or two trying to come up with my own algorithm for solving a sudoku, but I didn't get near the performance and efficiency of Richard's solution.

Best, Hugo

November 27, 2007 3:24 AM
 

sudoku solver said:

ye I do believe Celko did post that allready

January 9, 2010 3:33 AM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement