Solved

Help with query optimization

Posted on 2004-08-05
15
750 Views
Last Modified: 2008-03-03
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
Comment
Question by:risoy
15 Comments
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11725007
can u paste your query......

itsvtk
0
 

Author Comment

by:risoy
ID: 11725042
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 11725182
--------------- [ 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11725251
Create these indexes
  TblBatch: 1 BatchID
                 2 BatchID + Ovn
   tblOvn : 1 OvnID
   tblProdhist : 1  Batch
   tblKommentar  : 1 BatchID
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11725316
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
 
LVL 6

Assisted Solution

by:andy232
andy232 earned 25 total points
ID: 11725592
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
 

Author Comment

by:risoy
ID: 11725804
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
 

Author Comment

by:risoy
ID: 11726189
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 11726285
Did you try to modify the Nitrogenforbruk  view
as I suggested above ?
0
 

Author Comment

by:risoy
ID: 11726404
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
 

Author Comment

by:risoy
ID: 11726450
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
 

Author Comment

by:risoy
ID: 11726488
(...at a certain temperature to be correct)
0
 
LVL 12

Accepted Solution

by:
kselvia earned 100 total points
ID: 11732869
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
 

Author Comment

by:risoy
ID: 11733614
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
 

Author Comment

by:risoy
ID: 11733712
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

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question