Bevan, thanks for the article and the code. It's past 11 pm here, and I can't get my old brain around this tonight, but I'll be playing with this later. You are obviously much more of a mathematical guy than I ever was. I love working with logic even though I've been retired for nearly 10 years. I still enjoy all the SSC articles and comments and the regular emails.
Hi Skeleton567
Glad that you are chasing the logic problems - I do find them enjoyable too. The math side certainly does not come naturally but I've been doing lots of course in Machine Learning and the like and it is showing me the value. It makes me wish I was smart enough to have studied more pure math but rocks were interesting too!! So to speed it up you can add set forceplan on - Richard Romley was kind enough to share a few pointers and this one makes the whole thing run much faster! Enjoy take care Bevan
Hi Bevan,
Just for the record - Using FORCEPLAN is almost always a very bad idea and is frowned upon by the SQL Server community. The Sudoku query is clearly a very unique and unusual situation and one of the few examples I'm aware of where it happens to make sense. But I'd be very reluctant to suggest it as a normal performance-enhancing tool. And I'd always accompany any such recommendation with appropriate warnings.
Richard
Hi Richard
I left one puzzle run and it actually took 17 days to complete until this option was added from your suggestion. Then it completed in 400ms!
HI Bevan,
Any time the optimizer spends more time optimizing a query plan than it will save at execution time, it is a net loss and it would be better if it did nothing at all. That's the situation here. Also - you need to keep that in mind when you attempt to dynamically construct the WHERE clauses to maximize performance - Unless you can accomplish that task in less time than you will save, you'd be better off doing nothing at all. The problem for the optimizer in this example is the huge number of possible plans it's trying to evaluate - and it's all wasted for the next query anyway because the ideal execution plan is totally dependent on the data for a particular puzzle. I don't know how difficult it would be for the optimizer to determine that its most effective approach is to do nothing but that logic is either missing (or not working) in the SQL Server. Therefore, the need to manually intervene with FORCEPLAN. But it's important to understand that this really is an exception to what is normally good practice.
Richard
Hi Richard
That all makes sense - I think before you mentioned this option I had thought my only option was to pre-solve segments, enhance indexes, etc however the issue is even when solving segments if there are lots of possibilities, then using that outcome has a huge penalty for maybe only reducing a few options for one cell which in most cases is not worth the time spent. I already have the option counts per cell so that is available for the ordering with no penalty - I'll post what I come up with once my machine is free.
Thanks Bevan