T-SQL and The Excel Solver
-
And this is when I log off.
C u Monday guys.
-
Thanks for your comments, apologies again for not making my question clearer at the start. If I find a pre-canned solution, I'll post it here.
-
I think your first step must be to find out exactly what equations are being done (both the explicit ones wrote for the solver and the implicit ones carried out by the solver). The last time I used the solver it was for linear programming which was 90% itteration based upon the output from the previous calculation until some error hit an acceptable amount.
If the solver is using iteration it maybe that implementing it in sql server isn't your best choice.
-
Hi Chris,
I think you a right about the solver, from what I have gathered so far it does about a thousand iterations. I've had it in the back of my mind that trying to duplicate this functionality in T-SQL was not going to be a good idea, but I was hoping to get some feedback from others as to their thoughts, in case someone had already tried something similar.
-
Your best bet is to pull the data into an app, process it, then store the result. If it really must be done by the sql server I suppose you could look at an extended procedure or something like that. Might want to look at http://www.solver.com
-
Thanks for the link Chris, it looks interesting. I'm hoping that that we may be able to pre-calculate the numbers that we need in a self-contained process rather than run them as part of our batch process (where performance is extremely critical), in which case an additional app outside of the database would be a veryworkable solution.
-
The eventual solution to our problem was to code each of the different solver equations separately rather than try to find a generic solution. I understand from the programmer who did the work that an "iterative reductive algorithm" was used. We had to duplicate some work because the solution wasn't generic, but it works quickly and efficiently.
Unfortunately I am not allowed to post any code samples due to a client confidentiality agreement.
-
Thanks for letting us know
. -
Am I too late?
This method is about 15% slower, but is easier to read (and extend to more columns, with or without the use of dynamic sql?)
SELECT
x.ID,
MIN(x.theValue)
FROM (
SELECT ID, Val01 AS theValue FROM #MyHead UNION ALL
SELECT ID, Val02 FROM #MyHead UNION ALL
SELECT ID, Val03 FROM #MyHead
) AS x
GROUP BY x.ID
N 56°04'39.16"
E 12°55'05.25" -
I'm curious, have you tried this with 1M rows in the table and let's say anywhere between 2 and 5 columns?
-
Yes. It is slower. I told so in the previous post.
BUT...
The upside is how easy it is to change the number of columns to investigate.
2? No problems. 11? No problems. Slower? Yes. Does it work? Yes.
Is it easy to change for whatever number of columns to check? Yes.
Can it easily be transformed to a dynamic sql query if needed? Yes.
Imagine having to write nasty a CASE statements with 11 column checks, or an UDF.
N 56°04'39.16"
E 12°55'05.25" -
Good points. But the DBA coming to me wondering how to get the min value between 11 columns is going to go back to school and learn to design databases
. -
That I can agree on!
But it is not uncommon. One of my clients are in the Insurance business. They have some formulas in an application that can export the values to Excel (7 formulas actually), where they rank risks and other information for each client. So the Excel sheet has 8 columns.
They want the largest value for each customer. I can understand why they do it in Excel because they have a better overview of the data! And since SQL Server has this ability as my approach before, the only thing needed to change when business rules changes, is to add a new UNION ALL in the derived table. Quick and easy maintenance.
I have thought of learning them to write the formula "=MIN(P6:w6)" in cell m6 and copy the formula down, but since after initial test I decided to drop that approach, since the analysts always forgot to copy the formula for ALL rows needed.
N 56°04'39.16"
E 12°55'05.25" -
Macro that executes on load and reexecutes every 10 seconds should do it
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I've found that carrying a yard stick and smacking them upside the head when they forget to be the best training tool around.
Sorry Justice, I just couldn't resist chimming in on this one.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply