Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Help with query optimization

Posted on 2004-08-05
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 ] ---------------
Question by:risoy
LVL 14

Expert Comment

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


Author Comment

ID: 11725042
A quite comlex set of nested queries. I am hoping I can resolve this by adding some indexes...

--------------- [ start] ---------------
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
               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 ] ---------------
LVL 26

Expert Comment

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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
LVL 26

Expert Comment

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 ...

Assisted Solution

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.

Author Comment

ID: 11725804
This is actually the query I want to optimize:

--- --- ---
FROM OvnDB.dbo.batchlist
WHERE (Starttid BETWEEN '20040801 00:00' AND '20040806 00:00')
--- --- ---

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.

Author Comment

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.
LVL 26

Expert Comment

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

Author Comment

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.

Author Comment

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?

Author Comment

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

Accepted Solution

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.

Author Comment

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

Author Comment

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!

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Powershell v3 - SQLCMD 3 27
Are triggers slow? 7 12
MS SQL Delete Duplicate Rows Only 2 19
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 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