Menu
A similar question has been asked, but since it always depends, I'm asking for my specific situation separately.
I have a web-site page that shows some data that comes from a database, and to generate the data from that database I have to do some fairly complex multiple joins queries.
The data is being updated once a day (nightly).
I would like to pre-generate the data for the said view to speed up the page access.
For that I am creating a table that contains exact data I need.
Question: for my situation, is it reasonable to do complete table wipe followed by insert? or should I do update,insert?
SQL wise seems like DELETE + INSERT will be easier (INSERT part is a single SQL expression).
EDIT: RDBMS: MS SQL Server 2008 Ent
386
For best future query performance, it's better to do an update to keep the same extents. Delete and insert will not necessarily use the same extents. For a table of that size, it would be unlikely to do so. Furthermore, delete can leave "holes" in your data.
The UPDATE command is to modify the existing records in the database. To modify the limited records in the database you can use WHERE clause is used along with the UPDATE command. The DELETE command is used to delete the records in the database which are no longer required in the database.
Insert is for adding data to the table, update is for updating data that is already in the table.
Inserting rows in a table is faster than deleting them. Loading data into a new table using create-table-as-select (CTAS) is faster still. So if you're removing most of the rows from a table, instead of issuing a delete you can: Create a new table saving the rows you want to keep.
TRUNCATE will be faster than delete, so if you need to empty a table do that instead
You didn't specify your RDBMS vendor but some of them also have MERGE/UPSERT commands This enables you do update the table if the data exists and insert if it doesn't
77
It partly depends on how the data is accessed. If you have a period of time with no (or very few) users accessing it, then there won't be much impact on the data disappearing (between the DELETE and the completion of the INSERT) for a short while.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
