Solved

Help with query optimization

Posted on 2004-08-05
15
738 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
Comment Utility
can u paste your query......

itsvtk
0
 

Author Comment

by:risoy
Comment Utility
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
Comment Utility
--------------- [ 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
 
LVL 19

Expert Comment

by:Melih SARICA
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:risoy
Comment Utility
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
Comment Utility
Did you try to modify the Nitrogenforbruk  view
as I suggested above ?
0
 

Author Comment

by:risoy
Comment Utility
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
Comment Utility
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
Comment Utility
(...at a certain temperature to be correct)
0
 
LVL 12

Accepted Solution

by:
kselvia earned 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now