具有多个更新的查询非常慢

本文介绍了具有多个更新的查询非常慢的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

Hi then the query I'm running below works correctly but it is very slow to perform the operations as I can to speed it up? Unfortunately it is a very oneorose query and I have to run it on a server sql 2014 some advice?

UPDATE KilometriCantiereRapportoMobile SET IdRapportoMobile = @IdRapporto WHERE IdKilometri IN (SELECT Kilometri.IdKilometri FROM Kilometri INNER JOIN KilometriCantiereRapportoMobile ON Kilometri.IdKilometri = KilometriCantiereRapportoMobile.IdKilometri WHERE KilometriCantiereRapportoMobile.IdRapportoMobile IS NULL AND CONVERT(varchar(10), @Data, 105) = CONVERT(varchar(10), Kilometri.Data, 105) AND Kilometri.IdCantiere = @IdCantiere AND Kilometri.IdUtenteInserimento = @IdUtente); /*Articoli */ UPDATE ArticoloCantiereRapportoMobile SET IdRapportoMobile = @IdRapporto WHERE IdArticoloCantiere IN (SELECT ArticoloCantiere.IdArticoloCantiere FROM ArticoloCantiere INNER JOIN ArticoloCantiereRapportoMobile ON ArticoloCantiere.IdArticoloCantiere = ArticoloCantiereRapportoMobile.IdArticoloCantiere WHERE ArticoloCantiereRapportoMobile.IdRapportoMobile IS NULL AND CONVERT(varchar(10), @Data, 105) = CONVERT(varchar(10), ArticoloCantiere.Data, 105) AND ArticoloCantiere.IdCantiere = @IdCantiere AND ArticoloCantiere.IdUtente = @IdUtente); /* risorse */ UPDATE RisorsaRapportoMobile SET IdRapportoMobile = @IdRapporto WHERE IdRisorseUmane IN (SELECT RisorseUmane.IdRisorseUmane FROM RisorsaRapportoMobile INNER JOIN RisorseUmane ON RisorseUmane.IdRisorseUmane = RisorsaRapportoMobile.IdRisorseUmane WHERE RisorsaRapportoMobile.IdRapportoMobile IS NULL AND CONVERT(varchar(10), @Data, 105) = CONVERT(varchar(10), RisorseUmane.Data, 105) AND RisorseUmane.IdCantiere = @IdCantiere AND RisorseUmane.IdUtenteInserimento = @IdUtente)

我的尝试： 重新启动sql server

What I have tried: restart sql server

推荐答案

添加到上面的注释中，没有理由进行这些子查询。您可以执行以下操作（尽管只是稍微好一点） Adding to the comments above, there is no reason to have those sub-queries. You can do something like the following (although it is only marginally better) UPDATE RRM SET IdRapportoMobile = @IdRapporto FROM RisorsaRapportoMobile RRM INNER JOIN RisorseUmane RU ON RU.IdRisorseUmane = RRM.IdRisorseUmane WHERE RRM.IdRapportoMobile IS NULL AND CONVERT(varchar(10), @Data, 105) = CONVERT(varchar(10), RU.Data, 105) AND RU.IdCantiere = @IdCantiere AND RU.IdUtenteInserimento = @IdUtente

查看有关使用CONVERT的评论 - 摆脱它们。确保@Data是日期（不是 datetime 。日期自SQL2008以来一直存在，因此它将适用于2014）。如果RisorseUmane.Data是数据库上的日期时间，则尝试使用

See the comments about the use of CONVERT - get rid of those. Ensure @Data is a date (not a datetime. Date has been around since SQL2008 so it will work with 2014). If RisorseUmane.Data is a datetime on the database then try using

AND @Data = CAST(RU.Data AS date)

应该更快转换为字符串。 您正在加入关于字符串的表格并不总是最好的 - 如果您有改变的范围，那么它可能会有所帮助。 临时表几乎肯定会有所帮助，因为您将限制深度和宽度检查的数据，您还可以向临时表添加索引。但是不要试图使用CTE。 有些CodeProject文章在调整性能方面也可能对你有帮助： SQL Tuning Tutorial - 了解数据库执行计划（1） [ ^ ] SQL Server Profiler一步一步 [ ^ ] 如何分析SQL Server性能 [ ^ ]

It should be faster than converting to strings. You're joining tables on strings which isn't always the best - if you have scope to change that then it might help a little. Temp tables will almost certainly help, as you will be limiting the data examined both in depth and width and you can also add indexes to the temp tables. Don't be tempted to use CTEs however. There are some CodeProject articles that might also help you when it comes to tuning performance: SQL Tuning Tutorial - Understanding a Database Execution Plan (1)[^] SQL Server Profiler Step by Step[^] How to Analyze SQL Server Performance[^]