My query takes a long time

Question

Here is my code:

SELECT SpecieCode AS [Species]
    ,InventoryGrade AS [Grade]
    ,(
        SELECT Sum(SquareFeet)
        FROM Export AS Export1
        WHERE isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(OrderNumber, '') = ''
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
            AND isnull(ShowNumber, '') = ''
        ) AS [SqFt Avail]
    ,(
        SELECT Sum(SquareMeters)
        FROM Export AS Export1
        WHERE isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(OrderNumber, '') = ''
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
            AND isnull(ShowNumber, '') = ''
        ) AS [SqMt Avail]
    ,(
        SELECT Sum(SquareFeet)
        FROM Export AS Export1
        WHERE isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(OrderNumber, '') > ''
        ) AS [SqFt on Order]
    ,(
        SELECT Sum(SquareMeters)
        FROM Export AS Export1
        WHERE isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
            AND isnull(OrderNumber, '') > ''
        ) AS [SqMt on Order]
    ,(
        SELECT Sum(SquareFeet)
        FROM Export AS Export1
        WHERE isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(OrderNumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
            AND isnull(ShowNumber, '') > ''
        ) AS [SqFt on Show]
    ,(
        SELECT Sum(SquareMeters)
        FROM Export AS Export1
        WHERE isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(Ordernumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
            AND isnull(ShowNumber, '') > ''
        ) AS [SqMt on Show]
    ,(
        SELECT Sum(SquareMeters)
        FROM Export AS Export1
        WHERE isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(Export1.InvoiceNumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
        ) AS [Tot SqMt]
    ,Max(SubGrade) AS SubGrade
    ,Min(ShortLength) AS Short
    ,Max(Length) AS Long
    ,Max(InventoryRow) AS Location
FROM Export
WHERE Export.SpecieCode = '01'
    AND isnull(Export.InvoiceNumber, '') = ''
    AND isnull(FaceClipDate, '') = ''
GROUP BY SpecieCode
    ,InventoryGrade
ORDER BY SpecieCode
    ,InventoryGrade

The query currently takes several minutes to run. Is there anything I can do to speed it up? The Analyzer is not suggesting to add any indexes.

Try to remove all those pesky subqueries, make sure to have all the *right* indexes in place..... sorry this is waaaay too broad to answer - we'd need table structures, execution plan, data that's in the tables - SQL Server query tuning isn't an easy undertaking.... — marc_s, Mar 17 '15 at 18:16

score 1 · Accepted Answer · edited May 23 '17 at 12:12

In addition to the answers given by Simo and Benjamin, you should check out this question about sargable queries. In short, by saying something like where IsNull(MyColumn,'') = '' you are killing the query analyzer's ability to find a good index, if one exists, since there can be no index on IsNull(MyColumn,''). If you were to rewrite that as where (MyColumn is null or MyColumn = '') then you will probably get better performance, since the query analyzer will be more likely to find a good index.

benjamin moskovits · Answer 2 · 2015-03-17T18:57:24.820

Your results were probably incorrect because of nulls appearing in one of the columns so you may have gone overboard by wrapping every column with an isnull. In my experience only certain columns are likely to contain nulls or will mess up your results by containing nulls. Otherwise (leaving in all the isnulls) the query will take time.

You probably should use if var1 is null instead of isnull(something,'')='' when I tried this:

declare @i1 int=0
declare @a2 varchar(20)
set @a2 = null
begin
if ISNULL(@a2,'')='' set @i1 +=1
end
go 1000

vs

 declare @i1 int=0
    declare @a2 varchar(20)
    set @a2 = null
    begin
    if @a2 is null  set @i1 +=1
    end
    go 1000

The second test took 18 seconds vs 35 seconds for the first type of compare.

score 0 · Answer 3 · answered Mar 17 '15 at 18:32

You have multiple subqueries in the SELECT list and many of these are not necessary. You could at least try to optimize the query a little with CASE clauses. Note: you have to verify the results and possibly use only parts of this query depending on you data.

SELECT Export.SpecieCode AS [Species]
    ,Export.InventoryGrade AS [Grade]
    ,sum(CASE WHEN
            isnull(OrderNumber, '') = ''
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
            AND isnull(ShowNumber, '') = ''
        THEN Export1.SquareFeet ELSE 0 END) AS [SqFt Avail]
    ,sum(CASE WHEN
            isnull(OrderNumber, '') = ''
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
            AND isnull(ShowNumber, '') = ''
        THEN Export1.SquareMeters ELSE 0 END) AS [SqMt Avail]
    ,sum (CASE WHEN
            isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(OrderNumber, '') > ''
        THEN Export1.SquareFeet ELSE 0 END) AS [SqFt on Order]
    ,sum (CASE WHEN
            isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
            AND isnull(OrderNumber, '') > ''
        THEN Export1.SquareMeters ELSE 0 END) AS [SqMt on Order]
    ,sum(CASE WHEN
            isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(OrderNumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
            AND isnull(ShowNumber, '') > ''
        THEN Export1.SquareFeet ELSE 0 END) AS [SqFt on Show]
    ,sum(CASE WHEN
            isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(InvoiceNumber, '') = ''
            AND isnull(Ordernumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
            AND isnull(ShowNumber, '') > ''
        THEN Export1.SquareMeters ELSE 0 END) AS [SqMt on Show]
    ,sum(CASE WHEN
            isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
            AND isnull(Export1.InvoiceNumber, '') = ''
            AND isnull(FaceClipDate, '') = ''
        THEN Export1.SquareMeters ELSE 0 END) AS [Tot SqMt]
    ,Max(Export.SubGrade) AS SubGrade
    ,Min(Export.ShortLength) AS Short
    ,Max(Export.Length) AS Long
    ,Max(Export.InventoryRow) AS Location
FROM Export
JOIN Export AS Export1 ON isnull(Export1.SpecieCode, '') = isnull(Export.SpecieCode, '')
            AND isnull(Export1.InventoryGrade, '') = isnull(Export.InventoryGrade, '')
WHERE Export.SpecieCode = '01'

My query takes a long time

3 Answers3