SQL Server Performance Forum – Threads Archive

Update vs Delete/Insert

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 24×7. 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 24×7, 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.?

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=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Sorry… I want to delete/re-create 3000-5000 records every 2 minutes of every day (24×7). 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

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.

]]>