The Old Joel on Software Forum: Part 4 (of 5)

DB Tuning advice

Hi,

I have a VB6 / SQL Server 2K application that I have sold to a number of different clients. One of my clients is getting some timeout issues against some queries and I was hoping that you might provide some suggestion how I can improve the performance of the queries. I have checked the indexing strategy on the tables and I believe that SQL Server is using the queries. I show the execution plan and I don't quite understand it but it appears to be ok.

BTW the query takes around 30 seconds to complete and I don't think it should take that long. The query joins 9 tables. Should I break it out into a temp table ?

Kevin

Kevin Moore
Friday, February 13, 2004

It is impossible to tell without the query and the complete DDL (to include index definitions).

m
Friday, February 13, 2004

Tmep tables will probably not help.

There is whole host of things could be going on. The execution plan should tell you where SQL server is spending the most time on. SQl Server will not always use indexes. Look for 'table scans'. Which are fine for small tables but, of course, bad for large tables.

Also, thirty seconds may not be too bad depending on the situation.

DJ
Friday, February 13, 2004

http://www.sql-server-performance.com/rd_query_tuning.asp

m
Friday, February 13, 2004

Kevin, I realize you want to solve the problem without giving away proprietary information, but.. you haven't even bother to come up with a hypothetical situation that mirrors your actual situations. Where are the indexes, on what kind of fields, what do the queries do and how did you word it?

Li-fan Chen
Friday, February 13, 2004

The execution plan should show you which part of the query is taking % of time. Look at that and proceed from there.

How big are the tables?
How many results are returned?
How big is the results (size and # of fields)? OR If you dump that to a text file, how big is it?

AEB
Friday, February 13, 2004

Thanks for the tips.

I get so wrapped up in the problem that I look for advice without supplying more information needed to help.

The query is executing against and summarizing approximately 86000 rows. The resulting query returns around 250 rows.

In the execution plan there is a step that is called a bookmark lookup that takes around 46%. All other queries against tables use indexes and aren't doing a table scan.

I'll post more info and I glean more from the execution plan.

Here is the query (it is a big one):

    SELECT CASE WHEN @DivisionID = -1 THEN 0 ELSE IM.DivisionID END as DivisionID,
        ISNULL(I.ThicknessID,'') + 'x' + ISNULL(I.WidthID,'') as Dimension, I.LengthID, I.SpeciesID, I.InventoryGroupID, I.GradeID, I.CustomerGradeID, I.TrimID,
        @YardLocation as YardLocationID, ISNULL(P.SalesPrice, 0) as SalesPrice,
        SUM(ISNULL(IT.BoardMeasure, 0)) as Volume,
        AVG(ISNULL(IDT.MillPrice, 0)) as AveragePrice,
        SUM(CASE WHEN IDT.InventoryID IS NULL THEN Amount ELSE 0 END) as MiscAmount,
        SUM(ISNULL(IT.BoardMeasure,0) * ISNULL(O.FreightPerMBF,0) / 1000) as Freight,
        SUM(ISNULL(TaxAmount,0)) as TaxAmount,
        SUM(CASE WHEN IDT.InventoryID IS NOT NULL THEN ISNULL(Amount,0) ELSE 0 END) as Sales
        FROM tblInvoiceMaster IM
            JOIN tblInvoiceDetail IDT
                ON IM.InvoiceNumber = IDT.InvoiceNumber
                AND IM.DivisionID = IDT.DivisionID
            JOIN tblCustomer C
                ON IM.CustomerID = C.CustomerID
            JOIN tblOrder O
                ON IM.OrderNumber = O.OrderNumber
                AND IM.DivisionID = O.DivisionID
            JOIN tblShipment S
                ON O.OrderNumber = S.OrderNumber
                AND O.DivisionID = S.DivisionID
            JOIN tblShipmentDetail SD
                ON S.ShipmentID = SD.ShipmentID
                AND S.DivisionID = SD.DivisionID
                AND IDT.InventoryID = SD.InventoryID
            LEFT OUTER JOIN tblInventory I
                ON IDT.InventoryID = I.InventoryID
            LEFT OUTER JOIN tblInventoryTally IT
                ON I.InventoryID = IT.InventoryID
            LEFT OUTER JOIN tblProduct P
                ON I.DivisionID = P.DivisionID
                AND I.ThicknessID = P.ThicknessID
                AND I.WidthID = P.WidthID
                AND I.LengthID = P.LengthID
                AND I.SpeciesID = P.SpeciesID
                AND I.GradeID = P.GradeID
                AND I.CustomerGradeID = P.CustomerGradeID
                AND I.TrimID = P.TrimID
                AND I.MillingID = P.MillingID
                AND I.StateID = P.StateID
            WHERE InvoiceDate >= @FromDate
            AND InvoiceDate <= CONVERT(DateTime, CONVERT(Varchar (10), @ToDate, 101) + ' 23:59')
            AND IDT.InventoryID IS NOT NULL
            AND (IM.DivisionID = @DivisionID OR @DivisionID = -1)
            AND (I.ThicknessID = @ThicknessID OR @ThicknessID = '-1')
            AND (I.WidthID = @WidthID OR @WidthID = '-1')
            AND (I.LengthID = @LengthID OR @LengthID = '-1')
            AND (I.InventoryGroupID = @InventoryGroupID OR @InventoryGroupID = '-1')
            AND (I.GradeID = @GradeID OR @GradeID = '-1')
            AND (I.CustomerGradeID = @CustomerGradeID OR @CustomerGradeID = '-1')
            AND (I.SpeciesID = @SpeciesID OR @SpeciesID = '-1')
            AND (IM.CustomerID = @CustomerID OR @CustomerID = '-1')                -- Added 9/12/03
            AND (I.TrimID = @TrimID OR @TrimID = '-1')
            AND (OrderTypeID = @OrderType OR @OrderType = -1)
            AND (S.ShipToCity = @ShipToCity OR @ShipToCity = '-1')
            AND ((@YardLocation = '-1')
                OR (@YardLocation = '-2' AND (ISNULL(I.YardLocationID,'') = '' OR ISNULL(I.YardLocationID,'') = 'SKOG' OR ISNULL(I.YardLocationID,'') = 'Daybreak'))
                OR (@YardLocation = '-3' AND ISNULL(I.YardLocationID,'') <> '' AND ISNULL(I.YardLocationID,'') <> 'SKOG' AND ISNULL(I.YardLocationID,'') <> 'Daybreak')
                OR (ISNULL(I.YardLocationID,'') = @YardLocation))
            GROUP BY CASE WHEN @DivisionID = -1 THEN 0 ELSE IM.DivisionID END,
                I.ThicknessID, I.WidthID, I.LengthID, I.SpeciesID, I.InventoryGroupID, I.GradeID, I.CustomerGradeID, I.TrimID, ISNULL(P.SalesPrice,0)

Kevin

Kevin Moore
Friday, February 13, 2004

In a query I had a while back I noticed a huge performance bottle neck using ISNULL(field, defvalue) in my WHERE clause expressions.

apw
Friday, February 13, 2004

Regarding `ISNULL(I.YardLocationID,'') <> ''' and many like it -- never do this. SQL Server does not use indexes for function embedded where predicates, and as ISNULL is a function....

Use I.YardLocationID IS NOT NULL

Bookmark lookups is the process of taking an non-clustered index match and looking up the real record in the data table (this happens if the index is used for filtering, but the resultset needs to include fields outside of the index). Do your tables have a clustered index? Is it the best clustered index?

Dennis Forbes
Friday, February 13, 2004

LEFT OUTER JOIN tblProduct P
ON I.DivisionID = P.DivisionID
AND I.ThicknessID = P.ThicknessID
AND I.WidthID = P.WidthID
AND I.LengthID = P.LengthID
AND I.SpeciesID = P.SpeciesID
AND I.GradeID = P.GradeID
AND I.CustomerGradeID = P.CustomerGradeID
AND I.TrimID = P.TrimID
AND I.MillingID = P.MillingID
AND I.StateID = P.StateID

That join is horrible - don't you have a Primary Key for tblProduct ???

DJ
Friday, February 13, 2004

The left joins might waste a little memory. A left join will always return all the records in the table mentioned on the left of a left join (and one of the tables in the ON subclause)... thousands of sane records may go into your report, but millions might be null in memory, causing slow downs. Also your final table is really wide and huge and one way to solve this problem is to break the problem into stages and see if you can break the query into 3 queries

Li-fan Chen
Saturday, February 14, 2004

Do a subselect on this first:
WHERE InvoiceDate >= @FromDate
AND InvoiceDate <= CONVERT(DateTime, CONVERT(Varchar (10), @ToDate, 101) + ' 23:59')

That alone could cut your execution time by an order of magnitude.

Then on each of these:
AND (IM.DivisionID = @DivisionID OR @DivisionID = -1)

Flip your conditions:
AND (IM.DivisionID = -1 OR IM.DivisionID=@DivisionID)

From the list of them, I'm assuming that in general most of them are -1; putting that check first short-circuits the second one.

Rule of thumb - for general select statements, if a query takes 30 seconds, you're doing something wrong, esp. if you have less than 500,000 rows and your system isn't loaded with users.

Philo

Philo
Saturday, February 14, 2004

As a rule of thumb always filter on the term that will give the smallest population first, and so on.

Never use functions in where clauses, if you have to apply a function then use it as the target to produce a column and then subsequently select on that (usually using some form or nested query).

If the above is going to be a frequent occurrence then store the result of that function when you create the row. Data hidden in functions is not data.

Simon Lucy
Saturday, February 14, 2004

Is it a multi-user system? Are there several different parts of the system that access (specifically update) the same tables in different order? In my experience, SQL Server timeout issues are frequently a result of resource locking. Your query basically gets no processor time because it is waiting for other queries to release pages that it needs to read.

You can check a system under load by using the sp_who stored procedure and looking for processes with another process id listed in the 'block' column. This indicates that a process is waiting for a resource held by another active process. This is usually a locked record, but sometimes system resources like a table creation lock in tempdb.

If you find that processes are being blocked consistently, use DBCC INPUTBUFFER on both the blocked and blocking process to find out what queries are executing. It takes some time, and it's frustrating because the input buffer changes fairly rapidly, but you can solve a lot of timeout problems this way.

There is an article in the knowledge base, that I'm too lazy to look up right now, that discusses this technique in a lot more detail.

Craig
Saturday, February 14, 2004

"As a rule of thumb always filter on the term that will give the smallest population first, and so on. "

This generally shouldn't make a difference on modern database systems, as the query optimizer will automatically scan the query and from statistics guesstimate which filter predicates are most restrictive, and start from there.

Dennis Forbes
Saturday, February 14, 2004

Yep guessing is always better than knowing.

Simon Lucy
Sunday, February 15, 2004

"Yep guessing is always better than knowing."

Err, I think you miss the point, and the reality is more that your "knowing" is wishful thinkful. The query engine automatically parses your long, carefully ordered query into predicates and joins, and automatically orders them -- "knowing" is ridiculous because it's not going to honour your ordering regardless. Feel free to make a complex query with several filter predicates and view the query plan. Now reorder and view the query plan again.

Dennis Forbes
Sunday, February 15, 2004