具有多个更新的查询非常慢 [英] Queries with multiple updates very slow

问题描述

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[^]

这篇关于具有多个更新的查询非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！