SQL Server Performance

Update vs Delete/Insert

Discussion in 'SQL Server 2005 General Developer Questions' started by cchaney_64, Sep 3, 2006.

  1. cchaney_64 New Member

    I am developing a warehouse box packing application that will sample inventory every 1 minute to determine, based on certain algorithms, exactly how many boxes can be packed for each product type. This inventory turns over very quickly since they pack boxes 24x7.

    What I planned on doing was truncate and insert approximately 3000 to 5000 records every 1 minute or so in order to give them a constant view of what boxes are available to pack.

    I can't use a view or a set of views for this (AFAIK) since the alogorithms used to determine what widgets can be packed in each layer of each box are quite sophisticated based on a target quality median for the sum of each layer which requires sampling individual widget properties until a target median is found for the box layer. This is done for each layer in each box. I believe a cursor process is necessary for this - please let me know if there's a better way...

    What I'm wondering is would it be better perfomance-wise if instead of truncating and inserting 3000 to 5000 rows of data every 1 or 2 minutes of every day 24x7, would it be better to just create the 5000 rows of data one time and re-initialize the data every time via an Update (basically just have a set of records to be re-used every 1 or 2 minutes). Which methodology is better for (1) performance and (2) database fragmentation/etc.?

  2. FrankKalis Moderator

    Sorry, but we're here SQL Server guys. Can you explain your problems in terms we all understand? That is tables structures, indexes, sample data and required result. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  3. cchaney_64 New Member

    Sorry...

    I want to delete/re-create 3000-5000 records every 2 minutes of every day (24x7). The records contain a result set of data that changes frequently and is based upon complex algorithms.

    Is is better to
    (1) delete/recreate(insert) these records every time or
    (2) create the 3000-5000 "empty" records one time, and re-initialize the data(Update) every time

    Thanks
  4. Adriaan New Member

    Looks more like you need to make a projection of 'boxability', which will be a calculation based on two inventory tables (1=boxes and 2=products) going by widgets (complexity). You can produce this data without storing the data in any table, as a result from a view or a stored procedure.

    You need to store this in a table only if the data has to be available to different processes. In that case, truncate and insert would be the quickest solution - knowing that update and delete queries are slow operations.

Share This Page