Question

I d like to know how I can increase the performance and efficiency of my SQL statement. Does anyone have some ideas or advices?

DECLARE @RoleName NVARCHAR(MAX)
DECLARE @result INT

SELECT @result = COUNT(DurchlaufVorgang.Durchlaufname)
FROM   DurchlaufVorgang
INNER JOIN RoleDepartment
        ON DurchlaufVorgang.AbteilungID = RoleDepartment.ID
WHERE  DurchlaufVorgang.Depricated =  FALSE 
       AND RoleDepartment.RoleName = @RoleName
       AND RoleDepartment.Depricated =  FALSE 

SELECT Antraege.AntragID
FROM   ArtikelMitteilung,
       Antraege
WHERE  ArtikelMitteilung.Status =  Opened 
       AND Antraege.AntragID NOT IN 
          (SELECT Antraege.AntragID
           FROM   Vorgang,
                  Antraege
           WHERE  Vorgang.StepID IN 
                  (SELECT DurchlaufVorgang.ID
                   FROM   DurchlaufVorgang
                   INNER JOIN RoleDepartment
                           ON DurchlaufVorgang.AbteilungID = RoleDepartment.ID
                   WHERE DurchlaufVorgang.Depricated =  FALSE 
                     AND RoleDepartment.RoleName = @RoleName
                     AND RoleDepartment.Depricated =  FALSE )
              AND Vorgang.AntragsID = Antraege.ID
            GROUP  BY Antraege.AntragID
            HAVING COUNT(Antraege.AntragID) >= @result)
       AND ArtikelMitteilung.AntragsID = Antraege.ID
GROUP  BY Antraege.AntragID

我感谢您的帮助。

Answer 1

你在问询中不做任何明显错误的事情。优化机构将作出像这样的大量发言,并指明执行的最佳方式。如果您的指数不成体系或不存在,那么你会遇到问题。

从服务器管理演播室,点击显示执行计划,看看它是否建议增加指数。取消你们的指数(如果你在试验环境中重新计算,你就只能退缩并重新计算)。

如果您的直言不正确,那就不应如此。事实上,它应当帮助最优化者,因为它为如何执行它提供了更多的选择。

Answer 2

看看实际的查询计划,看看你有指数扫描和表扫描。选择使用指数的方法设法消除这些瓶颈。

我也许会猜测,你的所有立场都没有指数化,也许最有选择的前提可以指数化,这将大大有助于格里尔人选择指数,以便确定回归者的身份。

也可以尝试使用<条码>。 NOT EXISTS 取代《WHERE条款》中的“NOT IN表格”。有时可以提供帮助,而且你应当审查每个单位的询问执行计划。

编辑:

还删除多余的法典。例如,

      (SELECT Antraege.AntragID
       FROM   Vorgang,
              Antraege
       WHERE  Vorgang.StepID IN 
              (/* your subquery */)
          AND Vorgang.AntragsID = Antraege.ID
        GROUP  BY Antraege.AntragID
        HAVING COUNT(Antraege.AntragID) >= @result)

可简化:

      (SELECT Vorgang.AntragsID
       FROM   Vorgang
       WHERE  Vorgang.StepID IN 
              (/* your subquery */)
        GROUP  BY Vorgang.AntragsID
        HAVING COUNT(Vorgang.AntragsID) >= @result)

Answer 3

视各表的规模而定,它可能有助于将你的名单列入一个指数列。

declare @tab (AntragID int primary key clustered)
insert @tab
(AntragID)
SELECT Antraege.AntragID 
           FROM   Vorgang, 
                  Antraege 
           WHERE  Vorgang.StepID IN  
                  (SELECT DurchlaufVorgang.ID 
                   FROM   DurchlaufVorgang 
                   INNER JOIN RoleDepartment 
                           ON DurchlaufVorgang.AbteilungID = RoleDepartment.ID 
                   WHERE DurchlaufVorgang.Depricated =  FALSE  
                     AND RoleDepartment.RoleName = @RoleName 
                     AND RoleDepartment.Depricated =  FALSE ) 
              AND Vorgang.AntragsID = Antraege.ID 
            GROUP  BY Antraege.AntragID 
            HAVING COUNT(Antraege.AntragID) >= @result


SELECT Antraege.AntragID 
FROM   
       Antraege
       INNER JOIN ArtikelMitteilung
         ON Antraege.ID = ArtikelMitteilung.AntragsID 
       LEFT JOIN @tab x
           ON Antraege.AntragID = X.AntragID
WHERE
    ArtikelMitteilung.Status =  Opened  AND
    X.AntragID IS NULL  
GROUP BY

如果一个独一无二的AntragID没有从分局返回,则使用一个温表而不是表格变量,并编制关于AntragID的索引。

create table #tab (AntragID int)  
create clustered index cix_antragid_tab on #tab(AntragID)


insert #tab
(AntragID)
SELECT Antraege.AntragID 
           FROM   Vorgang, 
                  Antraege 
           WHERE  Vorgang.StepID IN  
                  (SELECT DurchlaufVorgang.ID 
                   FROM   DurchlaufVorgang 
                   INNER JOIN RoleDepartment 
                           ON DurchlaufVorgang.AbteilungID = RoleDepartment.ID 
                   WHERE DurchlaufVorgang.Depricated =  FALSE  
                     AND RoleDepartment.RoleName = @RoleName 
                     AND RoleDepartment.Depricated =  FALSE ) 
              AND Vorgang.AntragsID = Antraege.ID 
            GROUP  BY Antraege.AntragID 
            HAVING COUNT(Antraege.AntragID) >= @result


SELECT Antraege.AntragID 
FROM   
       Antraege
       INNER JOIN ArtikelMitteilung
         ON Antraege.ID = ArtikelMitteilung.AntragsID 
       LEFT JOIN #tab x
           ON Antraege.AntragID = X.AntragID
WHERE
    ArtikelMitteilung.Status =  Opened  AND
    X.AntragID IS NULL  
GROUP BY

如果你提出外部计划,就有可能选择询问的逻辑。

Answer 4

NOT IN条款往往效率不高。而是使用LEFT JOIN。接着,如果AntragID在合并表格(右边)IS NUL进行测试。

SELECT *
FROM
   Antrage A
   LEFT JOIN Antrage B
      ON A.AntragID = B.AntragID
WHERE
   B.AntragID IS NULL AND
   ...

SELECT Antraege.AntragID 
FROM   
       Antraege
       INNER JOIN ArtikelMitteilung
         ON Antraege.ID = ArtikelMitteilung.AntragsID 
       LEFT JOIN
          (SELECT Antraege.AntragID 
           FROM   Vorgang, 
                  Antraege 
           WHERE  Vorgang.StepID IN  
                  (SELECT DurchlaufVorgang.ID 
                   FROM   DurchlaufVorgang 
                   INNER JOIN RoleDepartment 
                           ON DurchlaufVorgang.AbteilungID = RoleDepartment.ID 
                   WHERE DurchlaufVorgang.Depricated =  FALSE  
                     AND RoleDepartment.RoleName = @RoleName 
                     AND RoleDepartment.Depricated =  FALSE ) 
              AND Vorgang.AntragsID = Antraege.ID 
            GROUP  BY Antraege.AntragID 
            HAVING COUNT(Antraege.AntragID) >= @result) X
              ON Antraege.AntragID = X.AntragID
WHERE
    ArtikelMitteilung.Status =  Opened  AND
    X.AntragID IS NULL  
GROUP BY
    Antraege.AntragID

友情链接