SQL Server Performance

Subquery delay with variable

Discussion in 'General Developer Questions' started by anrovi, Oct 25, 2006.

  1. anrovi New Member


    I have a query like this that is executed in 4 seconds:

    select field1
    , ( select field2 from table2 where table2.state = 1 )
    from mytable

    If I replace number of subquery with a variable, query delay more than one minute:
    set @myvar = 1
    select field1
    , ( select field2 from table2 where table2.state = @myvar)
    from mytable

    How can I use a variable and mantain performance ???
  2. Adriaan New Member

    Use qualified object names.
    Use a stored procedure. Else, use sp_ExecuteSQL with parameter(s).

    Anyway, that looks like a funny query. Are you expecting a single result from (1) the main query, and (2) the subquery?

    Explain what the query IN REAL LIFE is doing.
  3. anrovi New Member

    I need a query just to check warehouse movements (products, initial inventory, purchase, sales, adjustments and actual inventory).

    Sample output:
    Product ID | Product description | Initial inventory | Purchases | Sales | Actual Inventory (At System)
    100 Milk 13 3 5 10
    ...

    In this case, (initial inventory) + (Purchases) - (Sales) <> (Actual inventory) (13+3-5 <> 10)
    When I have a bad product like sample, my next step is audit records with this product id.

    So, my query is like:

    select id, ProductDescription
    , ( select Quantity from InitialInventory where id=P.id and branch=1 ) as 'Initial inventory'
    , ( select sum(Quantity) from Purchases where id=P.id and branch=1 ) as 'Purchases'
    , ( select sum(Quantity) from Sales where id=P.id and branch=1 ) as 'Sales'
    , ( select Quantity from ActualInventory where id=P.id and branch=1 ) as 'Actual Inventory (At System)'
    from Products P

    I need to run twice with branch=1 and branch=2. Due to this reason, I put a variable instead of =1.
    It was for me very strange that query taken over 1 minute with variable and 4 seconds with a constant. My doubt is... why???
  4. Adriaan New Member

    Is this an ad-hoc query, or a stored procedure with a parameter?

    If a stored procedure, then you might be suffering from 'parameter sniffing' - which is when SQL Server starts second-guessing the statistics and using an incorrect execution plan for the given parameter values.

    The work-around for that would be to copy the procedure parameters into local variables, and filter on the local variables.

    On the other hand, I'm not sure subqueries are the way to go here. How does this aggregate query perform?

    select P.id, P.ProductDescription,
    ii.Quantity 'Initial inventory',
    sum(Pu.Quantity) 'Purchases',
    sum(S.Quantity) as 'Sales',
    AI.Quantity 'Actual Inventory (At System)'
    from Products P
    left join InitialInventory ii on ii.id = P.id
    left join Purchases Pu on Pu.id = P.id and Pu.branch = ii.branch
    left join Sales S on S.id = P.id and S.branch = ii.branch
    left join ActualInventory AI where AI.id = P.id and AI.branch = ii.branch
    where ii.branch = 1

    Can't really test the syntax - if you cannot join on te branch codes, then move those branch code criteria to the WHERE clause.
  5. DilliGrg Member

    quote:Originally posted by anrovi

    I need to run twice with branch=1 and branch=2. Due to this reason, I put a variable instead of =1.
    It was for me very strange that query taken over 1 minute with variable and 4 seconds with a constant. My doubt is... why???



    I hardly realize that there could be differences. It seems to me that 1 minute is not that long but depends on how many records you are returning. Since you are only selecting, you can use some table hint WITH (NOLOCK) which might help speed up the performance. Also, Not sure why ID = Product(ID) for every table. You can try joins instead of subquery?



    DECLARE @Branch smallint

    SET @Branch = 1 --2

    SELECTp.ID AS [Product ID]
    , p.ProductDescription AS [Product Description]
    , init.Quantity AS 'Initial inventory'
    , sum(pur.Quantity) AS 'Purchases'
    , sum(sal.Quantity) AS 'Sales'
    , act.Quantity AS 'Actual Inventory (At System)'
    FROMProduct P WITH (NOLOCK)
    JOINInitialInventory init WITH (NOLOCK)
    ONp.ID = init.ID
    JOINPurchase pur WITH (NOLOCK)
    ONP.ID = pur.ID
    JOINSales sal WITH (NOLOCK)
    ONP.ID = sal.ID
    JOINActualInventory act WITH (NOLOCK)
    ONP.ID = act.ID
    WHEREBranch = @Branch
    GROUP BY p.ID, p.ProductDescription, init.Quantity, act.Quantity


    Thanks,
    DilliGrg
  6. anrovi New Member

    Hi all! I can't use join with all tables, because not all products have records in each other table.

    This query is just for obtain products with problems, I'll copy results in Microsoft Excel. Maybe wait for results more than one minute is not very important, but I'd like to know best way.

    Thanks all!

  7. Adriaan New Member

    LEFT JOIN means there doesn't have to be a match.

    For the SUM expressions, you could use ISNULL(column, 0)
  8. DilliGrg Member

    quote:Originally posted by anrovi

    Hi all! I can't use join with all tables, because not all products have records in each other table.

    This query is just for obtain products with problems, I'll copy results in Microsoft Excel. Maybe wait for results more than one minute is not very important, but I'd like to know best way.

    Thanks all!




    Almost all the subquery can be written as joins with better readability and performance wise. LEFT JOIN will give you all the records from Product table with matching from all other tables and also rest of the non matching records from Product table.



    DECLARE @Branch smallint

    SET @Branch = 1 --2

    SELECTp.ID AS [Product ID]
    , p.ProductDescription AS [Product Description]
    , init.Quantity AS 'Initial inventory'
    , sum(pur.Quantity) AS 'Purchases'
    , sum(sal.Quantity) AS 'Sales'
    , act.Quantity AS 'Actual Inventory (At System)'
    FROMProduct P WITH (NOLOCK)
    LEFT JOINInitialInventory init WITH (NOLOCK)
    ONp.ID = init.ID
    LEFT JOINPurchase pur WITH (NOLOCK)
    ONP.ID = pur.ID
    LEFT JOINSales sal WITH (NOLOCK)
    ONP.ID = sal.ID
    LEFT JOINActualInventory act WITH (NOLOCK)
    ONP.ID = act.ID
    WHEREBranch = @Branch
    GROUP BY p.ID, p.ProductDescription, init.Quantity, act.Quantity


    Thanks,
    DilliGrg
  9. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan


    For the SUM expressions, you could use ISNULL(column, 0)
    Sum does not care about NULLs. So
    SUM(ISNULL(column, 0))
    is equivalent to
    SUM(column)



    Roji. P. Thomas
    http://toponewithties.blogspot.com
  10. Adriaan New Member

    Was thinking about unmatched rows - since the sum is on a column from the outer table, if there's no match, that 'single' null does not sum to 0 - at least that's what I would think.
  11. FrankKalis Moderator

    Adriaan, getting strangely addicted to this forum, eh? Got nothing else to do at almost 22:30? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  12. Roji. P. Thomas New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Adriaan, getting strangely addicted to this forum, eh? Got nothing else to do at almost 22:30? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Talk about addiction. I posted my last reply around 12:30 AM yesterday.<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />
  13. Roji. P. Thomas New Member

    quote:Originally posted by Adriaan

    Was thinking about unmatched rows - since the sum is on a column from the outer table, if there's no match, that 'single' null does not sum to 0 - at least that's what I would think.
    Thats correct.

    Roji. P. Thomas
    http://toponewithties.blogspot.com
  14. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Adriaan, getting strangely addicted to this forum, eh? Got nothing else to do at almost 22:30? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Talk about addiction. I posted my last reply around 12:30 AM yesterday.<br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Get a life. [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  15. Adriaan New Member

    The joys of working from home one day a week ...
  16. anrovi New Member

    Hi everybody! Thanks for your contribution.

Share This Page