#1
  1. Since 439000000
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2004
    Location
    Canada
    Posts
    1,132
    Rep Power
    127

    How to come about a set-based solution


    Hey everyone,

    I've got a query that is going through transactions of sales, purchases and refunds. It is an average cost based inventory system and there was a bug found with how the average cost was being calculated, throwing out reports by as much as $10,000.

    I came up with a script that recalculates the inventory valuation and average cost. The only problem is it takes between 45 minutes for the smallest dataset of about 13 months and 3 hours for the largest dataset of about 6 months. As you can tell some clients are busier than others.

    I was reading about optimizing this script and I found that one of the major issues with it's speed is the fact that it uses multiple, nested, cursors. So I found that the solution would be to do a set based solution. The only real problem I'm having is that I'm not a DBA. I'm expecting that this means a query, or set of queries, that acts on a set of data all at once.

    The only problem is that I don't think that this is a case where a set-based solution would work anyway, since I have to calculate one row before I can calculate the next, as the calculated value(average cost) will most likely be used in the next row. This average cost will be calculated using it's current state and the data in the row presented by the cursor.

    In any case, I would like to avoid using cursors when I don't have to, do to this performance issue. How would one start thinking in set-based solutions and avoid using loops and cursors?
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
    Posting sample table structure, data and desired output would be too much for you?

    Comments on this post

    • acidfourtyfive agrees : Yes, company policy is not to give away that type of this. This is a software company afterall.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by acidfourtyfive
    How would one start thinking in set-based solutions and avoid using loops and cursors?
    here is probably the easiest way to think about it

    procedural solutions (which includes cursor-based logic) state what to do, but leave the knowledge of why to do it in the mind of the programmer

    set-based solutions describe the results and leave the database engine to figure out what to do

    note that stuff like running totals are sometimes tricky to describe
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. Since 439000000
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    May 2004
    Location
    Canada
    Posts
    1,132
    Rep Power
    127
    Awesome, that's how I thought about it. I guess a procedural solution is the only solution given my data and what is required.


    I ran my script and between clients, on identical production machines, it ran between 12 minutes to 6 hours. Locally it ran for 13.5 hours on the longest running client.

    Originally Posted by r937
    procedural solutions (which includes cursor-based logic) state what to do, but leave the knowledge of why to do it in the mind of the programmer
    Or in the comments

    I could probably achieve the same results using many many hard to read sub queries.
    Last edited by acidfourtyfive; August 31st, 2009 at 12:32 PM.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4332
    Originally Posted by acidfourtyfive
    Awesome, that's how I thought about it. I guess a procedural solution is the only solution given my data and what is required.
    since you did not share the details with us, i can quite comfortably say that you are wrong in this assessment, until you prove otherwise

    how have you indexed the tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo