Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with query optimization

Posted on 2004-08-05
15
Medium Priority
?
761 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
[X]
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
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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 100 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 400 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

670 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