删除许多行

本文介绍了删除许多行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

大家好， 我正在设计一个已经增长到近200GB的db的清除过程。 我的清除过程将删除大约1/3的500百万行传播 超过七张桌子。目前，这七张桌子上有大约35个索引。 。我的问题是，通过删除这些索引，执行清除以及重新创建索引，可以获得性能提升。我希望通过维护 索引，留下这些索引将会在我的删除语句中产生大量的额外开销。 。我知道之后可能需要花费很多时间来重建索引 ，但无论如何我打算这样做。我之所以想要知道我是否应该提前删除索引，是因为我可能不会立刻完成整个清除工作需要在清除之间访问。如果发生这种情况，我需要准备好那些 索引。 所以我要在清除之前删除索引并稍后重新创建它们或者 我是否留下它们并在之后重新索引它们？ 在此先感谢 ph

Hi All, I am designing a purge process for a db that has grown to almost 200GB. My purge process will remove about 1/3 of the 500 million rows spread over seven tables. Currently there are about 35 indexes defined on those seven tables. My question is will there be a performance gain by dropping those indexes, doing my purge, and re-creating the indexes. I am afraid that leaving those indexes in place will create a lot of extra overhead in my delete statements by having to maintain the indexes. I know that it could take many hours to rebuild the indexes afterward, but I am planning on doing that anyway. The reason that I want to know whether I should drop the indexes ahead of time, is I may not be able to do the entire purge at once and the tables may need to be accessed between purges. If this occurs, I will need to have those indexes in place. So do I drop the indexes before the purge and re-create them later or do I leave them in place and re-index them afterward? Thanks In Advance p.h.

推荐答案

查询在这些表上运行的频率是多少？ - Jack Vamvas ___________________________________ 免费获得SQL提示 - www.ciquery/sqlserver.htm < ha ***** @ yahoo>在消息中写道 news：11 ********************* @ p10g2000cwp.googlegro ups ... How often are queries run on these tables? -- Jack Vamvas ___________________________________ Receive free SQL tips - www.ciquery/sqlserver.htm <ha*****@yahoo> wrote in message news:11*********************@p10g2000cwp.googlegro ups... 大家好，我正在设计一个已经增长到近200GB的数据库的清除过程。我的清除过程将删除5亿行中的1/3左右传播七张桌子。目前，在这七个表中定义了大约35个索引。我的问题是，通过删除这些索引，进行清除以及重新创建索引可以获得性能提升。我担心留下这些索引会在我的删除语句中产生大量额外开销，因为必须维护索引。我知道之后可能需要花费很多时间来重建索引，但我还是打算这样做。我之前想知道是否应该提前删除索引的原因是我可能无法立即执行整个清除工作并且可能需要访问表格清洗之间。如果发生这种情况，我需要准备好这些索引。 所以我应该在清除之前删除索引并稍后重新创建它们或者我是否留下它们到位并重新索引它们？ 在此先感谢 ph Hi All, I am designing a purge process for a db that has grown to almost 200GB. My purge process will remove about 1/3 of the 500 million rows spread over seven tables. Currently there are about 35 indexes defined on those seven tables. My question is will there be a performance gain by dropping those indexes, doing my purge, and re-creating the indexes. I am afraid that leaving those indexes in place will create a lot of extra overhead in my delete statements by having to maintain the indexes. I know that it could take many hours to rebuild the indexes afterward, but I am planning on doing that anyway. The reason that I want to know whether I should drop the indexes ahead of time, is I may not be able to do the entire purge at once and the tables may need to be accessed between purges. If this occurs, I will need to have those indexes in place. So do I drop the indexes before the purge and re-create them later or do I leave them in place and re-index them afterward? Thanks In Advance p.h.

我明白了这些表在白天经常访问。我知道我必须对影响响应时间非常敏感 对这些表的查询。我不是拥有这个 数据库的DBA，所以我无法访问有关查询的具体细节 对表。 I understand that the tables are accessed frequently during the day. I know that I have to be very sensitive to affecting the response times of queries against these tables. I am not the DBA that owns this database, so I do not have access to specific details about the queries against the tables.

在这段时间内不知道查询访问权限很难说，但 另一个想法 你可以创建一个临时表（一个持久的备份表，包含所有 日期，而不是sql临时表）并在那里移动记录。 （你想要的那些 要保留） 截断你的父表。并将记录移回原始表格 ，并附上您想要的记录。 表格是否有聚集索引？ - Jack Vamvas ___________________________________ 免费获得SQL提示 - www.ciquery/sqlserver.htm < ha ***** @ yahoo>在消息中写道 news：11 ******************** @ p10g2000cwp.googlegrou ps ... Without knowing the query access during this time it''s hard to say, but another idea is you can create a Temporary Table (a persistent backup table with all the date , not a sql temp table) and move the records there. (the ones you want to keep) Truncate your parent table. And move the records back to the original table with the records you want. Do the tables have clustered indexes? -- Jack Vamvas ___________________________________ Receive free SQL tips - www.ciquery/sqlserver.htm <ha*****@yahoo> wrote in message news:11********************@p10g2000cwp.googlegrou ps... 我知道这些表在白天经常被访问。我知道我必须非常敏感地影响对这些表的查询的响应时间。我不是拥有此数据库的DBA，因此我无法访问针对表格的查询的具体细节。 I understand that the tables are accessed frequently during the day. I know that I have to be very sensitive to affecting the response times of queries against these tables. I am not the DBA that owns this database, so I do not have access to specific details about the queries against the tables.