我有一个大约9 GB大小的Azure Sql数据库。它服务于一个每小时处理大约135 K请求的Web应用程序。大多数数据都是瞬时的，它们在数据库中停留几分钟到五天就会被删除。每天大约有10 GB在数据库中移动。
我尝试对一个表运行删除查询，以便从总共350，000条记录中删除大约250，000条记录。大约10%的记录具有一个或两个大到足以存储在LOB存储中的nvarchar（max）值。
周末的时候，我试着一次把它们全部删除。在我取消查询之前，它运行了4个小时，然后又回滚了8个小时--糟糕的举动。我真的没想到会这么糟糕。
然后我尝试了另一种方法。这个批处理在晚上运行，当时Web应用程序每小时处理大约10万个请求。tblJobs Id字段是一个唯一标识符，它是主键。
insert @tableIds select Id from dbo.tblJobs with(nolock)
where (datediff(day, SchedDate, getDate()) > 60)
or (datediff(day, ModifiedDate, getDate()) > 3 and ToBeRemoved = 1)
set @maintLogStr = 'uspMaintenance [tblJobs] Obsolete J records count @tableIds: ' + convert(nvarchar(12), (select count(1) from @tableIds))
insert dbo.admin_MaintenanceLog(LogEntry) values(@maintLogStr)
set @maintLogId = newid()
set @maintLogStr = 'uspMaintenance [tblJobs] Obsolete J records beginning loop...'
insert dbo.admin_MaintenanceLog(Id, LogEntry) values(@maintLogId, @maintLogStr)
while exists(select * from @tableIds)
begin
delete @tableIdsTmp
begin transaction
insert @tableIdsTmp select top 1000 id from @tableIds
delete p from @tableIdsTmp i join dbo.tblJobs p on i.id = p.Id
delete x from @tableIdsTmp t join @tableIds x on t.id = x.id
set @maintLogStr = 'uspMaintenance [tblJobs] Obsolete J records remaining count @tableIds: ' + convert(nvarchar(12), (select count(1) from @tableIds))
update dbo.admin_MaintenanceLog set LogEntry = @maintLogStr, RecordCreated = getdate() where Id = @maintLogId
commit transaction
if @dowaits = 1 WAITFOR DELAY '00:00:01.000'
endSchedDate、ModifiedDate和ToBeRemoved没有索引，因此收集@tableIds中的ID大约需要3分钟-还不错。
然后从日志条目中可以看出，从tblJobs中删除11，000条记录花费了1小时55分钟，此时从远程计算机调用的作业超时。
为什么要这么久？我该怎么做才能加快速度？
4条答案
按热度按时间9cbw7uwe1#
您的许多性能将与您所使用的预留大小相关联（正如前面的答案中提到的）。但是，您根本不需要在代码中使用表变量来实现所需的功能。实际上，当涉及到连接时，您几乎不应该使用它们，因为它们没有关于它们的统计信息（因此，当优化程序需要做出复杂的选择时，很可能会有糟糕的计划选择）。table variables documentation。
因此，如果你退一步，看看你试图做的核心，你可以这样做：删除前（1000）个dbo.TblJobs，其中（日期差异（天，计划日期，getDate（））〉60）
或者（日期差异（天，修改日期，获取日期（））〉3且待删除= 1）
您可能会从该查询中获得表扫描，因为：
当您执行扫描时，您可能会遇到锁定问题，因为您有一个工作负载正在表上并发运行。因此，如果其他一些请求正在执行select语句，您可能会在更新查询扫描表时阻塞它。（顺便说一句，发布查询计划确实有助于讨论扩展/并发问题）。
如果你有一个循环，从表中取出1000行，将它们复制到一个表变量中，然后再将它们复制到另一个表中，并在删除操作中与原始表连接，那么你就将一个O（N）的问题变成了O（N^2）。从算法上讲，如果你用这种方法向表中添加更多的行，你的查询可能会变得越来越慢。
您可以执行一些操作来改进此查询（可能）：
了解每个操作的开销有助于进行大多数性能优化和分析。使用“set statistics time on”和“set statistics io on”可以很好地跟踪查询的物理开销。“set statistics profile on”更适合于查看每个查询操作符的算法开销（对于N^2问题）。
迟做总比不做好，但我希望这能帮助您（和其他人）了解如何提高您的SQL Azure性能，如果您在未来遇到类似的情况。
0h4hbjxa2#
IT取决于DTU（性能层）。请检查查询执行期间数据库的资源消耗情况，以查看是否达到了任何资源限制。此外，将来在发出删除操作时，还可以将查询分解为多个事务处理。这在事务处理必须回退的情况下很有帮助（例如升级到SQL DB）或连接到数据库的一端出现暂时性网络故障
6l7fqoea3#
作为一个快速的解决方案/黑客，在SSMS中，我右键单击数据库，然后选择生成脚本，在高级选项中，我选择创建DROP ONLY脚本。从那里，把它放在一个新的查询窗口中，我做了一个查找和替换，把
DROP TABLE改为DELETE FROM。它仍然有一些问题，把它们放在错误的顺序为外键依赖关系，但经过一些调整，我很快就删除了所有表。8yoxcaq74#
我在sql azure上的一个客户端数据库上也发生了同样的事情。删除在where filter中使用了日期，仅此而已。我在日期上添加了clusterd索引。在仅8 M行上创建一个clusterd索引需要25分钟的忙碌，但不幸的是，我没有看到大的改进。
截断整个表-立即完成并再次插入所有记录要麻烦得多。