• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

Help with query optimization

I have got a huge nested query that takes a very long time to execute. Below is the execution plan. Could someone please help me optimize it. I have tried using various indexes, but not very successful. I have here removed them all, apart from PKs.

--------------- [ start ] ---------------
  |--Compute Scalar(DEFINE:([Expr1032]=If ([Expr1018]=NULL) then 0 else 1))
       |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([tblBatch].[BatchID])=([tblBatch].[BatchID]), RESIDUAL:([tblBatch].[BatchID]=[tblBatch].[BatchID]))
            |--Sort(ORDER BY:([tblBatch].[BatchID] ASC))
            |    |--Merge Join(Right Outer Join, MERGE:([tblKommentar].[BatchID])=([tblBatch].[BatchID]), RESIDUAL:([tblBatch].[BatchID]=[tblKommentar].[BatchID]))
            |         |--Clustered Index Scan(OBJECT:([OvnDB].[dbo].[tblKommentar].[PK_tblKommentar2]), ORDERED FORWARD)
            |         |--Nested Loops(Left Outer Join, OUTER REFERENCES:([tblBatch].[BatchID]))
            |              |--Merge Join(Left Outer Join, MERGE:([tblBatch].[BatchID])=([Expr1018]), RESIDUAL:([tblBatch].[BatchID]=[Expr1018]))
            |              |    |--Sort(ORDER BY:([tblBatch].[BatchID] ASC))
            |              |    |    |--Hash Match(Right Outer Join, HASH:([tblOvn].[OvnID])=([tblBatch].[Ovn]), RESIDUAL:([tblBatch].[Ovn]=[tblOvn].[OvnID]))
            |              |    |         |--Table Scan(OBJECT:([OvnDB].[dbo].[tblOvn]))
            |              |    |         |--Table Scan(OBJECT:([OvnDB].[dbo].[tblBatch]))
            |              |    |--Sort(ORDER BY:([Expr1018] ASC))
            |              |         |--Filter(WHERE:([tblBatch].[Stopptid]=NULL))
            |              |              |--Bookmark Lookup(BOOKMARK:([Bmk1025]), OBJECT:([OvnDB].[dbo].[tblBatch]))
            |              |                   |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1018]))
            |              |                        |--Hash Match(Aggregate, HASH:([tblBatch].[Ovn]), RESIDUAL:([tblBatch].[Ovn]=[tblBatch].[Ovn]) DEFINE:([Expr1018]=MAX([tblBatch].[BatchID])))
            |              |                        |    |--Table Scan(OBJECT:([OvnDB].[dbo].[tblBatch]))
            |              |                        |--Index Seek(OBJECT:([OvnDB].[dbo].[tblBatch].[PK_tblBatch]), SEEK:([tblBatch].[BatchID]=[Expr1018]) ORDERED FORWARD)
            |              |--Hash Match(Cache, HASH:([tblBatch].[BatchID]), RESIDUAL:([tblBatch].[BatchID]=[tblBatch].[BatchID]))
            |                   |--Compute Scalar(DEFINE:([Expr1027]=datediff(minute, [tblBatch].[Stopptid], [Expr1013]), [Expr1028]=datediff(minute, [Expr1015], [tblBatch].[Starttid])))
            |                        |--Stream Aggregate(DEFINE:([Expr1012]=MIN([tblBatch].[BatchID]), [Expr1013]=MIN([tblBatch].[Starttid]), [Expr1014]=MAX([tblBatch].[BatchID]), [Expr1015]=MAX([tblBatch].[Stopptid]), [tblBatch].[Starttid]=ANY([tblBatch].
            |                             |--Hash Match(Left Outer Join, HASH:([tblBatch].[Ovn])=([tblBatch].[Ovn]), RESIDUAL:([tblBatch].[Ovn]=[tblBatch].[Ovn] AND [tblBatch].[BatchID]<[tblBatch].[BatchID]))
            |                                  |--Nested Loops(Left Outer Join, WHERE:([tblBatch].[Ovn]=[tblBatch].[Ovn] AND [tblBatch].[BatchID]>[tblBatch].[BatchID]))
            |                                  |    |--Bookmark Lookup(BOOKMARK:([Bmk1006]), OBJECT:([OvnDB].[dbo].[tblBatch]))
            |                                  |    |    |--Index Seek(OBJECT:([OvnDB].[dbo].[tblBatch].[PK_tblBatch]), SEEK:([tblBatch].[BatchID]=[tblBatch].[BatchID]) ORDERED FORWARD)
            |                                  |    |--Table Scan(OBJECT:([OvnDB].[dbo].[tblBatch]))
            |                                  |--Table Scan(OBJECT:([OvnDB].[dbo].[tblBatch]))
            |--Compute Scalar(DEFINE:([Expr1029]=If ([tblBatch].[Ovn]='U790') then [dbo].[N2_CubicToKilo](Convert([Expr1004]-[Expr1005])) else Convert((Convert([Expr1004])/10)), [Expr1031]=Convert(If ([tblBatch].[Ovn]='U790') then [dbo].[N2_CubicToKilo](Co
                 |--Sort(ORDER BY:([tblBatch].[BatchID] ASC))
                      |--Hash Match(Inner Join, HASH:([tblProdhist].[Batch])=([tblBatch].[BatchID]))
                           |--Hash Match(Aggregate, HASH:([tblProdhist].[Batch]) DEFINE:([Expr1004]=MAX([tblProdhist].[N2]), [Expr1005]=MIN([tblProdhist].[N2])))
                           |    |--Table Scan(OBJECT:([OvnDB].[dbo].[tblProdhist]))
                           |--Table Scan(OBJECT:([OvnDB].[dbo].[tblBatch]))
--------------- [ end ] ---------------
0
risoy
Asked:
risoy
2 Solutions
 
Thandava VallepalliCommented:
can u paste your query......

itsvtk
0
 
risoyAuthor Commented:
A quite comlex set of nested queries. I am hoping I can resolve this by adding some indexes...

--------------- [ start] ---------------
SELECT *
FROM batchlist
--------------- [ end ] ---------------

--------------- [ VIEW batchlist ] ---------------
SELECT     B.BatchID, B.Ovn, B.Starttid, B.Stopptid, B.ProgramNavn, LT.NextBatch, LT.NextStarttid, LT.PrevBatch, LT.PrevStopptid, LT.Lossetid, LT.Lastetid,
                      O.Navn AS OvnNavn, O.Coiler AS numCoils, O.Soner AS numZones, Running = CASE WHEN LO.Batch IS NULL THEN 0 ELSE 1 END, K.Kommentar,
            N.N2forbruk, N.N2kiloprtime
FROM         dbo.tblBatch B LEFT OUTER JOIN
                      dbo.tblOvn O ON B.Ovn = O.OvnID LEFT OUTER JOIN
                      dbo.last_open_batches LO ON B.BatchID = LO.Batch LEFT OUTER JOIN
                      dbo.load_time LT ON B.BatchID = LT.BatchID LEFT OUTER JOIN
               dbo.tblKommentar K ON B.BatchID = K.BatchID LEFT OUTER JOIN
            dbo.Nitrogenforbruk N ON B.BatchID = N.Batch
--------------- [ end ] ---------------

--------------- [ VIEW last_open_batches ] ---------------
SELECT last.Batch, last.Ovn
FROM  (SELECT MAX(BatchID) AS Batch, Ovn
               FROM   dbo.tblBatch
               GROUP BY Ovn) last INNER JOIN
               dbo.tblBatch B ON last.Batch = B.BatchID
WHERE (B.Stopptid IS NULL)
--------------- [ end ] ---------------

--------------- [ VIEW load_time ] ---------------
SELECT B1.BatchID, B1.Ovn, B1.Starttid, B1.Stopptid, MIN(B2.BatchID) AS NextBatch, MIN(B2.Starttid) AS NextStarttid, DATEDIFF(n, B1.Stopptid,
               MIN(B2.Starttid)) AS Lossetid, MAX(B3.BatchID) AS PrevBatch, MAX(B3.Stopptid) AS PrevStopptid, DATEDIFF(n, MAX(B3.Stopptid), B1.Starttid)
               AS Lastetid, B1.ProgramNavn
FROM  dbo.tblBatch B1 LEFT OUTER JOIN
               dbo.tblBatch B2 ON B1.Ovn = B2.Ovn AND B1.BatchID < B2.BatchID LEFT OUTER JOIN
               dbo.tblBatch B3 ON B1.Ovn = B3.Ovn AND B1.BatchID > B3.BatchID
GROUP BY B1.BatchID, B1.Ovn, B1.Starttid, B1.Stopptid, B1.ProgramNavn
--------------- [ end ] ---------------

--------------- [ VIEW Nitrogenforbruk ] ---------------
SELECT BatchID AS Batch, Ovn, ProgramNavn, B.Starttid AS Tidspunkt,
      CASE WHEN (ovn = 'U790') THEN dbo.N2_CubicToKilo(MAX(N2) - MIN(N2)) ELSE (MAX(N2) / 10) END AS N2forbruk,
      CAST(CAST(DATEDIFF(s, B.Starttid,B.Stopptid) AS real)/3600.0 AS decimal(18,2)) AS timer,
      CAST(((CASE WHEN (ovn = 'U790') THEN dbo.N2_CubicToKilo(MAX(N2) - MIN(N2)) ELSE (MAX(N2) / 10) END)
      /
      (CAST(DATEDIFF(s, B.Starttid,B.Stopptid) AS real)/3600.0)) AS decimal(18,1)) AS N2kiloprtime
FROM dbo.tblBatch B INNER JOIN dbo.tblProdhist P ON B.BatchID = P.Batch
GROUP BY BatchID, B.Ovn, ProgramNavn, B.Starttid, B.Stopptid
--------------- [ end ] ---------------
0
 
HilaireCommented:
--------------- [ VIEW last_open_batches ] ---------------
SELECT last.Batch, last.Ovn
FROM  (SELECT MAX(BatchID) AS Batch, Ovn
               FROM   dbo.tblBatch
               GROUP BY Ovn) last INNER JOIN
               dbo.tblBatch B ON last.Batch = B.BatchID
WHERE (B.Stopptid IS NULL)
--------------- [ end ] ---------------


could you try this instead ? should give the same output unless I'm wrong.

SELECT MAX(BatchID) AS Batch, Ovn
FROM   dbo.tblBatch
WHERE Stopptid IS NULL
GROUP BY Ovn

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Melih SARICAIT ManagerCommented:
Create these indexes
  TblBatch: 1 BatchID
                 2 BatchID + Ovn
   tblOvn : 1 OvnID
   tblProdhist : 1  Batch
   tblKommentar  : 1 BatchID
0
 
HilaireCommented:
You should also consider putting raw SQL instead of the function call
in the the Nitrogenforbruk  view

the dbo.N2_CubicToKilo has only one parameter, and I guess it's rather simple

Since the complexity is hidden in the view, you should really make the calculation in the view. Calling a user-defined function in the view means that there will be one function call per row ...

Maybe the view code will be less readable, but this should really improve perfomance ...
0
 
andy232Commented:
There are a lot of good index suggestions, but I will give more of a general pointer.  See wherever it says Table Scan?  You should create indexes on those columns.  

My second suggestion is to save your query as a .SQL file.  Then use Index Tuning Wizard in SQL Profiler.  It will suggest some helpful indexes to create.  You do need SQL 2000 for this.
0
 
risoyAuthor Commented:
This is actually the query I want to optimize:

--- --- ---
SELECT *
FROM OvnDB.dbo.batchlist
WHERE (Starttid BETWEEN '20040801 00:00' AND '20040806 00:00')
ORDER BY BatchID DESC
--- --- ---

Some good suggestions here, I'm looking into them.

Hilaire: Your last_open_batches rewrite return more rows than I desire. If the last (newest) batch has been stopped, I do not want to return the last one that is missing a stop time (for various reasons.) In a way, I want the ones that are running. I.e. only if the highest batch number per ovn (furnace) is missing a stop time.
0
 
risoyAuthor Commented:
Index Tuning Wizard gave me two new indexes and said it would optimize my query by 42%. Running the above query in QA reduced the time from 18s to 17s. Not very much.
0
 
HilaireCommented:
Did you try to modify the Nitrogenforbruk  view
as I suggested above ?
0
 
risoyAuthor Commented:
Yes, but it didn't make much of a difference. You are right, it _is_ very simple; Nitrogen cubic * 1.18 is Nitrogen kilograms.
0
 
risoyAuthor Commented:
I read somewhere something about execution plans only being stored if one use dbname.username.tablename, and not when one just uses tablename. Is this true and could it have an effect?
0
 
risoyAuthor Commented:
(...at a certain temperature to be correct)
0
 
Ken SelviaRetiredCommented:
It is not true that query plans are not stored for db.user.table.  What is true,  is that different query plans may be created for three part names than single part names.  They will both be stored, but they may differ.  Using the 3 part name supposedly eliminates an extra search for an existing query plan for dbname.currentuser.tablename, but some recent benchmarks I saw showed it to be an insignificant overhead.  

I suspect a lot of the problem is the self joins in view_load time.  A clustered index on tblbatch.Startid might help. How many rows are in tblBatch?

Also, a lot of people often ask for the query plan as you posted, and I guess it helps to identity table scans but personally I find it much easier to identify problems by running SET STATISTICS PROFILE ON and SET STATISTICS IO ON before running the query.  It will show you not only what is being done, but how many times it is done. A table scan done once may be better than a million index seeks.  Those 2 SET's will show you exactly how many rows each operation is done on, and how many physical and logical IO's are done on each table. Much more helpful for finding problems I think.
0
 
risoyAuthor Commented:
Here you will find an Excel-file with the output of SET STATISTICS PROFILE ON and SET STATISTICS IO ON. It wouldn't be readable if placed it here.
 - http://www.risoy.com/temp/batchlist_stats.xls [link may be broken in the future; when thread closed.]

Row counts:
tblBatch: 3,409
tblProdhist: 395,480
tblOvn: 6
tblKommentar: 213
0
 
risoyAuthor Commented:
I added the clustered index on tblBatch.Starttid, and voila, it is now executing in 1 second - down from 18 seconds. Fantastic! I have previously added an index on it, but I didn't make it clustered - so that was definately the trick.

Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now