Need help Evaluating formulas and prices in stored procedure -- sql server programming

Lee Boozer
2/11/2007 6:16:52 PM

First, it sounds like your SQL Server machine is woefully underpowered and short
on RAM. But I can't fix that here...

As for the software issue, have you tried an UPDATE with a CASE expression?
I.E.:

Update Products
Set Price =
Case
When PriceLevel = 1 Then
<appropriate computation or function call>
When PriceLevel = 2 Then
<appropriate computation or function call>
When PriceLevel = 3 Then
<appropriate computation or function call>
When PriceLevel = 4 Then
<appropriate computation or function call>
When PriceLevel = 5 Then
<appropriate computation or function call>
When PriceLevel = 6 Then
<appropriate computation or function call>
Else
<RAISERROR ?>
End

Just out of curiosity:

o how big (rowcount) is your products table?
o how much memory is allocated to SQL Server?

You might also want to ask if all prices really need to be recomputed every day?
And conversely, is daily often enough for all prices? There are almost certainly
business events that lead to a price update being required. You really should
have the updated triggered by those events if at all possible.

Regards,
Lee

On Sun, 11 Feb 2007 23:48:55 GMT, "Steve Ledbetter" <sledbetter@comcast.net>
[quoted text, click to view]

Steve Ledbetter
2/11/2007 11:48:55 PM

I maintain a products module and have some issues in keeping prices updated.
Our product records each have 6 pricing 'levels', each price level has an
associated formula for calculating the price based on several cost factors,
and has a set of rounding parameters for each level so that each product has
six resulting prices. For example, each price level can have a formula such
as '(I+.1)/.6' where 'I' is the cost, to give a simple example, and the
result can be rounded to the nearest .49 or .99 cents, or the nearest nickel
or dime, or the cents part and the dollar part can be rounded to the nearest
user selected digit (e.g., the dollar part and the cents part can be rounded
up to the next 3 or 8 for example, so that a formula result of $6.25 would
be rounded to $8.28 if 3/8 were chosed for both parts).

I can do this from my application but it's slow, since each record has to
be retrieved, the formulas evaluated, the rounding applied, and the record
updated on a record by record basis. If I try to do this in a stored
procedure, it's still slow plus I get a message that system resources are
low and processing is terminated (and I have to reboot the workstation it's
running on - this is when I execute the sp from the query analyser).

I have written individual tsql functions to replace the formula components
with the appropriate cost values (which are stored on the product record),
evaluate the resulting expression, and apply the rounding rules using a
number table and these work fine for a single or small set of records. I've
tried using temp tables, cursors, and tables to retrieve the product
information (the formulas and rounding information), updating the prices in
the table/cursor, and then updating the tables from the temp tables/cursor -
always slow with the eventual 'system resources are low' error and it
terminates without completing.

Does anyone have any suggestions as to a strategy or method for evaluating
these parameters and applying these values and updating the records that a)
works! and is b) faster than retrieving the product info, calcing the
prices, and then applying the results inside my application? I would really
like to do this in a stored proc that could be started from my app for
example the first time it's run daily. I'll be happy to post code I've
written if anyone would like to see it.

Thanks for any help!

Regards,

Steve Ledbetter

Steve Ledbetter
2/13/2007 12:00:00 AM

Thanks for the response, Lee. The server is definitely short on RAM (XEON
2.4 with 2 gigs and about 15 gigs of client databases). It usually has less
than 256 megs free but it's only used for development work, not production
and I can't be sure our clients would have anything with more resources.

I tried the direct update method (similar to what you mentioned) but my
stumbling block was evaluating the formula. This was reasonable (without
actually evaluating the formulas!) taking about 5 seconds for 10.000 records
with 6 prices on each record on our decvelopment server. I don't think a
CASE statement would work as there are 6 prices/6 formular/six rounding
paramters on each record all of which need updating. I wrote a function to
call like:

Update Pricelist set Price1 = FunctionCall(list of params), Price2 -
FunctionCall(list of params), etc which worked great EXCEPT in the function
I could replace the formulas with the cost, and do the rounding, but I could
not figure a way to evaluate the expression that resulted from the cost
substitution. E.g, once I got from '(I+.2)/.6' to '(2.45+.2)/.6' I was
stuck.

In other situations I've evaluated expressions using something like 'INSERT
INTO #PTable EXEC('SELECT' + @ExpressionVariable)' and then retrieving the
result from #PTable - I can't access a temp table in the function call and I
can't figure out another way to access the reults of that SELECT. I tried
creating a temporary function on the fly (for each price) and call it like
'CREATE FUNCTION Test() RETURNS FLOAT AS BEGIN RETURN (2.45+.2)/.6 END'
which worked fine outside of a function - except you can't create a
function inside a function. I found a routine on the Internet called
InFixVal which seemed to work except it's recursive and some of the
formula's were too complex and caused it to fail. I would give my left
tooth for an Eval function...

In answer to your other questions, yes the prices likely need to be recalced
daily at least (most of these client will be on an ecommerce program which
import product info and costs several times a week) and one of the
motivations for moving this to a stored procedure is to allow the other
applications that feed the product files to update costs and prices with the
same set of (faster) code after each update. Determining if cost changed is
time consuming because the costs are date sensitive (some with beginning and
ending dates, others with open-ended starting dates, and there are multiple
cost types per product (quantity senstive costs as well as freight,
installation costs, etc) - just checking the current costs to see if they
changed would be more time consuming that updating the prices regardless.

Thanks for your interest and suggestions! If you have any suggestions on
evaluating the formula expressions those could be a lifesaver!

Regards,

Steve Ledbetter
RFMS Research and Development
(800) 701-7367 x3651
www.rfms.com
[quoted text, click to view]

Lee Boozer
2/13/2007 10:27:20 PM

Steve,

Sorry, I don't understand what you mean by "I could not figure a way to evaluate
the expression that resulted from the cost substitution". A good description of
the problem to be solved would probably be your best bet at getting someone to
chime in with a possible solution.

A couple points to consider regardless:

- SQL functions are great - from a logical viewpoint. In practice, used the way
you're using them (multiple functions executed for every row processed), they
slow SQL execution down tremendously. If speed and resources are important, you
may want to try to do without the function calls.
- No law of nature says all fields have to be updated at once. You could execute
multiple UPDATE statements, maybe recalculating one field per statement. This
would have the added benefit of keeping each statement simpler.
- If you have some numeric ID field that could be used to access subsets of the
data, you could run a loop updating a subset of the data with each UPDATE
statement. I.E. If you have records 1 through 1,000,000, maybe run through 10
loop iterations updating 100,000 rows at a time. On a machine that has to do a
lot of page swapping, this may keep the working set down to a small enough size
to be processed in memory.

Best of luck,
Lee

[quoted text, click to view]

Steve Ledbetter
2/14/2007 12:00:00 AM

Hi Lee...

I initially tried to do this to solve the problem with a function:

Update Pricelist Set Price1 = dbo.EvaluatePrice( [list of parameters]),
Price2 = dbo.EvaluatePrice([list of
parameters]),
Price3 = dbo.EvaluatePrice([list of
parameters]),
Price4 = dbo.EvaluatePrice([list of
parameters]),
Price5 = dbo.EvaluatePrice([list of
parameters]),
Price6 = dbo.EvaluatePrice([list of
parameters])

[list of parameters] included the formula, the costs involved, and rounding
parameters.

In the EvaluatePrice function I substituted the cost values for the
variables in the formula - no problem here - and then when I got the
expression resulting from the substitution (just an arithmetic expression
like '(2.45 + .2)/.6' ) I could find no way to evaluate that expression in
a function (the next step was rounding the result which was also no
problem).
Cast/convert won't do it, and the only other way I know of would be
'EXEC('SELECT ' + [Expression]) but the only way I know to get at the result
ot that call would be something like inserting it into a table and reading
back out which I can't do in a function.

Hope that makes my dilemma clearer - sorry if I have rambled but I have been
up so many paths trying to solve this that I'm getting punch-drunk :-)
Thanks for your help and suggestions.

Regards,

Steve Ledbetter
RFMS Research and Development
(800) 701-7367 x3651
www.rfms.com

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Need help Evaluating formulas and prices in stored procedure