SQL Server Performance

Formula concatenation performance...cursors??

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by cbmeeks, Dec 6, 2006.

  1. cbmeeks New Member

    I have a project that deals with building formulas....simple formulas.

    Something like: ((A * B) / C) + D

    However, since the formulas are dynamic (using a GUI) I thought about storing each character in a record. For example:

    SortOrder Value
    --------- -----
    1 (
    2 (
    3 A
    4 *
    5 B
    6 )
    7 /
    8 C
    9 )
    10 +
    11 D

    Now, once that is done, I can create a temporary table, concatenate that into one string, and EXEC the string. Works great.

    Now hear are my questions:

    1) These are called a lot. So temp tables seem slow/kludgy. I've tried derived tables but can't seem to get it to work.

    2) Is there a smarter way? I thought about storing the entire formula in one record however the A/B/C/D will change frequently.

    Basically what I am trying to do is take this formula: ((A * B) / C) + D
    and store it in the database. However, the values of A,B,C,D will actually be GUIDs that point to another "Values" table so they will be dynamic.

    Hope that makes sense.

    Would this be a better use of a cursor?

    Any help would be appreciated.

    Thanks!

    cbmeeks
    http://www.codershangout.com


    http://www.codershangout.com
  2. ndinakar Member

    Generally I donot recommend cursors for any scenario. Try using table variables. There are cases when temp tables can cause trouble other than filling up the tempDB. They can cause proc recompilations. Once your app grows you could also see tempDb contention. Try table variables. Cursors, no.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  3. cbmeeks New Member

    Thanks.

    I found the same thing. I actually got two versions working. The first uses a table like above but I figured out how to select an entire table (or filtered rows) into one string! Works perfectly.

    The seconds uses one row to hold the entire formula and parses the string but I'm afraid of the CPU overhead on that one.

    cbmeeks
    http://www.codershangout.com

    http://www.codershangout.com

Share This Page