SQL Server Performance

Help on a SELECT Query

Discussion in 'General Developer Questions' started by lookaround, Oct 5, 2007.

  1. lookaround New Member

    Hi everyone,
    I have a problem in writing a SELECT query for a stored procedure...
    I have two tables, Users and Orders which fileds are (simplifying):
    Users: UserID, UserName, etc...
    Orders: OrderID, UserID, OrderDate, etc...

    I should select the first N most recent orders for EACH User in Users Table.
    I've thought this method, using a temporary table which stores only the OrderID:
    - Loop through Users, and select the First N most recent Order for the user and insert the OrderIDs in a temp table
    - Return the Temp Table Joined with Orders Table.

    Is there a most efficient way to select with only one query?
    Thank you very much in advance!
  2. Adriaan New Member

    Use a correlated subquery:

    SELECT o1.OrderId, o1.UserId FROM Orders o1
    WHERE o1.OrderId IN
    (SELECT TOP n o2.OrderId FROM Order o2 WHERE o2.UserId = 01.UserId ORDER BY o2.OrderDate DESC)
    The ORDER BY clause of the subquery in this case means that you get the rows with the most recent OrderDate - but you can of course use different sorting logic.
    Note that in SQL 7.0 and 2000, you havve to write the number for the TOP clause. If you want to make this flexible, you have to use dynamic SQL:

    DECLARE @SQL NVARCHAR(500), @TopNumber INT
    SET @TopNumber = 5
    SET @SQL = 'SELECT o1.OrderId, o1.UserId FROM Orders o1 WHERE o1.OrderId IN (SELECT TOP '
    + CAST @TopNumber AS VARCHAR(10))
    + ' o2.OrderId FROM Order o2 WHERE o2.UserId = 01.UserId ORDER BY o2.OrderDate DESC)'
    EXEC (@SQL)
  3. lookaround New Member

    Thank you very much, it works perfectly!
  4. lookaround New Member

    Hi,
    the query works great, but I have a performance problem...

    I have to restrict the users to get the most recent orders from.
    I've adjusted the query in this way:

    SELECT o1.OrderId, o1.UserId
    FROM Orders o1
    WHERE
    o1.OrderId IN
    (SELECT TOP 5 o2.OrderId
    FROM Order o2
    WHERE o2.UserId = o1.UserId
    AND o2.UserId IN (SELECT UserId FROM [Another subquery])
    ORDER BY o2.OrderDate DESC)


    But it's very slow...

    Should be better if I put the Query to select the users to get from in the external query?
    The query should be:

    SELECT o1.OrderId, o1.UserId
    FROM Orders o1
    WHERE
    o1.UserId IN (SELECT UserID FROM [Another subquery])
    AND
    o1.OrderId IN
    (SELECT TOP 5 o2.OrderId
    FROM Order o2
    WHERE o2.UserId = o1.UserId
    ORDER BY o2.OrderDate DESC)
    Or this last query isn't more performant?
    Thanks in advance for any suggestion...
  5. Adriaan New Member

    Correct, this should improve performance - but you won't know until you test it.
  6. lookaround New Member

    Ok, I'll try and then I'll let you know which is the best solution...
    Thank you!
  7. lookaround New Member

    Hi,
    after some test on production server, unexpectedly the first one (with the subquery that filters the users in the inner query) is faster then second one...
    Altough it's faster, it'is still extremely slow...
    I've tried this alternative replacing a IN clause with a join but is worst than the two previous solutions...
    SELECT o1.OrderID
    FROM Orders o1
    INNER JOIN
    (SELECT UserId FROM [Another Subquery]) AS u1
    ON o1.UserId = u1.UserId
    WHERE o1.OrderId IN
    (SELECT TOP 5 o2.OrderId
    FROM Orders o2
    WHERE o2.UserId = o1.UserId
    ORDER BY o2.OrderDate DESC
    )
    Is there a more efficient solution to retrieve this data?
    Thanks again in advance for any idea!

  8. Adriaan New Member

    If it's extremely slow then it would be interesting to see that other subquery. Check if the join columns and the filter columns are properly covered by indexes.
  9. lookaround New Member

    I've checked again the indexes, all the fields included in JOINS and in WHERE clauses are indexed...
    I've looked at Actual Execution Plan of the query and there's no "Table Scan" (if I'm not wrong that should indicated a scan on a non-indexed column). Furthermore the 86% of the plan is spent for 'TOP N SORT' (whose "Number of Executions" is 7431; this sound strange to me, because the maximum number of users is less than 5000...)
    Tnx for your help!

  10. FrankKalis Moderator

    Can you please post your index definitions? I'm guessing that a composite index might be beneficial here.
  11. Adriaan New Member

    Then the filtering subquery for users is best done inside the TOP 5 subquery (WHERE clause).
    Problem is that you're not telling is how this filtering subquery for user works - perhaps it is again an aggregate query?
  12. lookaround New Member

    [quote user="Adriaan"]
    Then the filtering subquery for users is best done inside the TOP 5 subquery (WHERE clause).
    Problem is that you're not telling is how this filtering subquery for user works - perhaps it is again an aggregate query?
    [/quote]
    I agree with you, I thought that the filtering subquery should be more performant in the inner query... But trying it on our actual server it's faster the one with the filtering subquery in the outer one...
    The filtering subquery is made on a third table,
    SELECT UserId FROM dbo.Contacts u1 WHERE u1.IDUser = @IDUser
    @IDUser is a parameter of the stored procedure the query is executed within.
    I have reported a simplified query trying to keep the problem simply an easy to explain....
    If you think it's important, I'll write all the content of the stored procedure (which uses a dynamic SQL sp_executesql, a part related to paging result and so on..)
  13. Adriaan New Member

    Did you already include that part in the code so far?
    You're executing dynamic SQL through sp_ExecuteSQL - which is a wise decision in itself, but are you using a proper parameter list for that?
    Also try copying the sproc parameter into a local parameter before passing it into sp_ExecuteSQL - this is ye olde parameter sniffing feature/problem.
  14. lookaround New Member

    [quote user="Adriaan"]
    Did you already include that part in the code so far?
    You're executing dynamic SQL through sp_ExecuteSQL - which is a wise decision in itself, but are you using a proper parameter list for that?
    Also try copying the sproc parameter into a local parameter before passing it into sp_ExecuteSQL - this is ye olde parameter sniffing feature/problem.
    [/quote]
    The code related to paging is already in the sp I use, but I've not included this piece in the above code..
    The stored procedure works as desidered, i.e. the records returned are the expected ones... The problem is only related to performance...
    I have to admin that I do not copy the sp paramter in a local parameters, but I've added a "debug" parameter so I can print out and check if the query is correct...
    i've also tryed to executed directly the query that is execute by the sp_ExecuteSQL and the poor performance persists... I have gathered that it's not related to sp, it's the query itself that is slow... Moreover the Actual Execution plan reveal that 100% of the sp-batch is dedicated to the "incrimated" query... Am I wrong?
  15. Adriaan New Member

    If 100% is spent on the query, then the time taken by the other statements in the sp in is negligeable - the rest is probably just string concatenation. If there's a call to an external sp or udf, that will have its own execution plan, and not carry any weight in this sp's plan.
    When you use the actual parameters inside the procedure, SQL can sometimes use the same execution plan for the sp, where it should assess the new parameter values for the query itself. Moving the parameters into local variables sometimes helps resolve that issue.
    There are lots of articles on paging - and I'm certainly not an expert on that. I would hazard a guess that your key columns, as well as the indexing, should be set up in a way that mirrors the paging logic, and to minimize (if not eliminate) the calculations required to find the next set of rows.

Share This Page