CODEWITHSUNDEEP
sqlsql-server
user1011394
user1011394

Reputation: 1666

More efficient SQL Server statement

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

I'm grateful for your help.

Upvotes: 0

Views: 209

Answers (4)

Jules
Jules

Reputation: 1081

depending on the size of tables it may help a lot to get your list into an indexed column.

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

if a unique AntragID is not returned from the subquery use a temp table instead of the table variable and create an index on 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

It would be possible to optimise the logic of the query if you post the exec plan.

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112772

NOT IN clauses are often not very efficient. Try to use a LEFT JOIN instead. Then test if AntragID in the joined table (on the right side) IS NULL.

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

Upvotes: -1

jbindel
jbindel

Reputation: 5635

Look at the actual query plan, and see where you have index scans and table scans. Find ways to use index seeks to remove those bottlenecks.

I might guess that all of the predicates you have are not indexed, and perhaps the most selective predicates could be indexed, which would greatly help the query planner to choose index seeks to identity the rows to return.

You could also try using a NOT EXISTS form instead of your NOT IN form in the WHERE clause. Sometimes that can help, and you should run examine the query execution plan for each.

edit:

Also remove redundant code. For example,

      (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)

can be simplified:

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

Upvotes: 1

Milimetric
Milimetric

Reputation: 13549

You're not doing anything glaringly wrong in your query. The SQL optimizer will take a large statement like that and figure out the best way to execute it. Where you can run into problems is if your indices are fragmented or non-existent.

From SQL Server Management Studio, click on Show Execution Plan and see if it suggests any additional indices. De-fragment your indices (you can just drop and re-add them if you're in a testing environment).

If your intuition is that a big statement is bad for performance, that should not be the case here. In fact, it should help the optimizer because it gives it more choices on how to execute it.

Upvotes: 1

Related Questions