English 中文(简体)
sql:DELETE + INSERT vs UPDATE + INSERT
原标题:sql: DELETE + INSERT vs UPDATE + INSERT

有人提出了类似的问题,但由于这个问题始终取决于,我要求单独提出具体情况。

我有一个网页,显示数据库中的一些数据,并从该数据库中生成数据,我不得不做一些相当复杂的多个链接查询。

数据每天都在更新。

我愿预先提出上述意见的数据,以加快页面访问。

为此,我正在编制一份表格,列出我所需要的确切数据。

Question: for my situation, is it reasonable to do full table wipe 然后插入? 或者我是否做了更新?

看来,正如DELETE + INSERT(INSERT部分是单一表述)一样,这似乎是明智的。

http://www.un.org。 RDBMS:2008年MSQ服务器 Ent

最佳回答

TRUNCATE将比删除更快,这样,如果你需要空出一张桌子,就更不用说了。

你没有具体说明你的房舍管理事务供应商,但其中一些供应商也有MERGE/UPSERT的指挥。 这样,如果数据存在,就能够更新表格,并在数据没有数据时插入

问题回答

这部分取决于数据的获取方式。 如果你有一段时间,没有(或很少)用户使用,那么数据消失(在DELETE和完成INSERT之间)就会受到严重影响。

您是否考虑采用一种实质性观点(MSSQL称其有指数的观点),而不是以人工方式这样做? 这还可能具有其他业绩效益,因为指数化的观点使得在为其他询问制定执行计划时,查询者会作出更多的选择,而其他询问参考表格。

它取决于表格的规模和数据库的恢复模式。 如果你删除了数以百计的记录,重新记录这些记录,以更新数百个小批记录,并插入数个行,就会给你的交易日志增加不必要的规模。 但是,当贸易商协会赢得对交易记录的影响时,你可以利用它来接手。

您是否可选择一个市面汇率/UPSERT? 如果你重新使用MS-SQL,你可以使用CROSS AppLY做类似的事情。

处理这类问题的一个办法是在新的表格中插入一个表格,然后填写一个表格。 这将确保所有新数据同时出现。

如果昨天出现的一些数据不再存在? 删除可能比较安全,或可以最终删除某些记录。

And in the end it doesnt really matter which way you go. Unless on the case @kevinw mentioned

Although I fully agree with SQLMenace s answer I do would like to point out that MERGE does NOT remove unneeded records ! If you re sure that your new data will be a super-set of the existing data, then MERGE is great, otherwise you ll either need to make sure that you delete any superfluous records later on, or use the TRUNCATE + INSERT method ... (Personally I m still a fan of the latter as it usually is quite fast, just make sure to drop all indexes/unique constraints upfront and rebuild them one by one. This has the benefit of the INSERT transaction being smaller and the index-adding being done in (smaller) transactions again later on). (**)

(**) 是的,这或许会渗透到生活体系中,但后来他再次提到,这是在几条双夜之间,Im <>extrapolating。 当时没有用户进入。





相关问题
How to write this T-SQL WHERE condition?

I ve got two tables: TableA Col1 Col2 TableB Col3 Col4 I want to join them together: SELECT * from TableA join TableB ON (...) Now, in place of ... I need to write an expression ...

Customer and Order Sql Statement

TSQL query to select all records from Customer that has an Order and also select all records from customer that does not have an Order. The table Customer contains a primary key of CustomerID. The ...

Recommended way of querying multiple Versioned tables

Have a win 2003 box with MSSQL 2005 running on it. There is a database which is populated every morning with new/modified SalesOrder made the previous day. The database has several tables: SalesOrder, ...

update duplicate record

I have a table with the following fields Id Name IsPublic i need to write a sql query that updates IsPublic to false where name has a duplicate. Only one of the duplicates should have IsPublic = ...

Define variable to use with IN operator (T-SQL)

I have a Transact-SQL query that uses the IN operator. Something like this: select * from myTable where myColumn in (1,2,3,4) Is there a way to define a variable to hold the entire list "(1,2,3,4)"? ...

Selecting records during recursive stored procedure

I ve got a content management system that contains a hierarchical structure of categories, with sub-categories subject to different ordering options at each level. Currently, that s retrieved by a (...

热门标签