SQL Server Performance

Return Top row from group of records

Discussion in 'Performance Tuning for DBAs' started by PAMUR, Aug 2, 2006.

  1. PAMUR New Member

    Please tell me if there is another better way to do the following.

    COmpanySales
    -------
    compSalesID int,
    i_company_id int,
    i_year int,
    i_quarter int,
    i_sales_amount float,
    i_fte_number int -- latest number of employees in the company

    I know the table design stinks. But I have no other go but to work with it. Pleas edon't dwell into the design aspects.



    Select y.i_company_id,y.i_year,y.i_Sales_Amount,y.i_FTE_number
    -------------------From
    -------------------(Select i_company_id, max(i_year) i_year
    ------------------------From COmpanySales
    ------------------------group by i_company_id) x,

    ------------------------(Select s.i_company_id,s.i_year,s.i_Sales_Amount,fte.i_FTE_number
    --------------------------From
    -------------------------------(Select i_company_id, i_year, sum(i_Sales_amount) i_Sales_Amount
    -------------------------------From CompanySales
    -------------------------------Group by i_company_id, i_year
    -------------------------------) s ,
    -------------------------------(Select i_company_id,i_year,i_quarter,i_FTE_number
    ----------------------------------------From CompanySales a
    ----------------------------------------where exists(Select * from
    --------------------------------------------------------(Select i_company_id, Max(i_year) i_year, Max(i_quarter)i_quarter
    --------------------------------------------------------From CompanySales
    --------------------------------------------------------Group by i_company_id )f
    --------------------------------Where a.i_company_id = f.i_company_id and a.i_year = f.i_year and a.i_quarter = f.i_quarter))Fte

    ------------------------Where s.i_company_id = fte.i_company_id )y

    -------------------where x.i_company_id = y.i_company_id and x.i_year = y.i_year
    Usha Rani
  2. FrankKalis Moderator

    Now, for completeness (as I've locked your other thread), can you please post sample data and required output? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  3. PAMUR New Member

    Sample Data: data is not ordered.
    Company:
    companyid and other fields
    20
    21
    22
    CompanySales data
    1, 20, 1999, 1, 15, 100
    2, 20, 1999, 2, 20, 90
    3, 20, 1999, 3, 30, 120
    4, 20, 2000, 1, 15, 100
    5, 20, 2000, 2, 20, 90
    6, 20, 2000, 4, 30, 120
    7, 21, 1999, 1, 20, 100
    8, 21, 1999, 2, 30, 110
    9, 21, 2000, 1, 20, 100
    10, 21, 2000, 2, 30, 110
    Need to get sum sales of latest year, per company and latest FTE

    Usha Rani
  4. Roji. P. Thomas New Member

    quote:
    Need to get sum sales of latest year, per company and latest FTE

    I may be overlooking something. But I think it should be as simple as

    SELECT y.i_company_id,y.i_year,SUM(y.i_Sales_Amount),
    (SELECT i_fte_number FROM COmpanySales FTE
    WHERE FTE.i_company_id = Y.i_company_id
    AND FTE.i_year= Y.i_year
    AND FTE.i_quarter = (SELECT MAX(i_quarter)
    FROM COmpanySales Q
    WHERE FTE.i_company_id = Q.i_company_id
    AND FTE.i_year= Q.i_year))
    FROM COmpanySales Y
    WHERE Year = (SELECT MAX(Year) FROM COmpanySales i
    WHERE i.i_company_id = Y.i_company_id)

    --untested

















    Roji. P. Thomas
    http://toponewithties.blogspot.com
  5. PAMUR New Member

    SELECT y.i_company_id,y.i_year,SUM(y.i_Sales_Amount),
    (SELECT i_fte_number FROM COmpanySales FTE
    WHERE FTE.i_company_id = Y.i_company_id
    AND FTE.i_year= Y.i_year
    AND FTE.i_quarter = (SELECT MAX(i_quarter)
    FROM COmpanySales Q
    WHERE FTE.i_company_id = Q.i_company_id
    AND FTE.i_year= Q.i_year))
    FROM COmpanySales Y
    WHERE Year = (SELECT MAX(Year) FROM COmpanySales i
    WHERE i.i_company_id = Y.i_company_id)

    Group by y.i_company_id, y.i_year

    Must add group by.

    Thank you. This seems simpler. Will check for performance .

    Thank you once again.

    Usha Rani
  6. PAMUR New Member

    Sorry! Tomas, The query is not performing well.

    The Quer cost relative to the batch when I executed both mine and yours is:
    Usha:Thomas
    25%:75%

    I meant that I need simpler but more efficient query.

    Thank you for trying.

    Usha Rani
  7. Adriaan New Member

    Your main subquery has three embedded subqueries, all of which are looking for MAX() values on three columns, all for the same i_company_id in the CompanySales table.

    You can do the three aggregates in a single derived table, and join on the aliases for the MAX() expressions ...
  8. FrankKalis Moderator

    Hm, will this work?


    SELECT t1.i_year, t1.i_company_id, SUM(t1.i_Sales_Amount) SalesAmount,
    (SELECT MAX(compSalesID )
    FROM CompanySales
    WHERE i_company_id = t1.i_company_id
    GROUP BY i_company_id) FTE
    FROM CompanySales t1
    WHERE t1.i_year = (SELECT MAX(i_year)
    FROM CompanySales
    WHERE i_company_id = t1.i_company_id)
    GROUP BY t1.i_company_id, t1.i_year


    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  9. PAMUR New Member

    Thanks to both of you.

    Adriaan is this what you meant.

    Select y.i_company_id,y.i_year,y.i_Sales_Amount,y.i_FTE_number
    From
    (Select i_company_id, i_year, Sum(i_Sales_Amount)i_Sales_Amount, Max(i_FTE_number)i_FTE_number
    From CompanySales
    Group By i_company_id, i_year)y
    Where i_year = (SELECT MAX(i_year) FROM CompanySales I WHERE I.i_company_id = y.i_company_id)

    Order By y.i_company_id, y.i_year desc





    Usha Rani
  10. FrankKalis Moderator

    Forget it. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  11. PAMUR New Member

    Thank you Frank <br />I did as per your suggession and it is the best for performance.<br /><br /><br />SELECT t1.i_year, t1.i_company_id, SUM(t1.i_Sales_Amount) i_Sales_Amount, <br /> (SELECT MAX(i_CompanyYearlyData_id )<br /> FROM CompanySales<br /> WHERE i_company_id = t1.i_company_id<br /> GROUP BY i_company_id) FTE<br /> FROM CompanySales t1<br /> WHERE t1.i_year = (SELECT MAX(i_year)<br /> FROM CompanySales<br /> WHERE i_company_id = t1.i_company_id) <br /> GROUP BY t1.i_company_id, t1.i_year<br />Order By t1.i_company_id, t1.i_year desc<br /><br />Thank you.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />] But is there more simpler way....... He he eh<br /><br />Actually I don't get correct picture of FTE number, If I include Otherwise it is a good thought to use the CompanySales ID<br /><br /><br />Usha Rani
  12. Adriaan New Member

    I was thinking along these lines ... You need the latest i_quarter of the latest i_year for each i_company_id:

    select cs.i_company_id, max(cs.i_quarter)
    from CompanySales cs
    where cs.i_year IN (select max(x.i_year) from CompanySales x where x.i_company_id = cs.i_company_id)
    group by c.i_company_id

    Add this as a derived table to your main aggregate query:

    SELECT y.i_company_id, y.i_year, y.i_quarter, Sum(y.i_Sales_Amount) i_Sales_Amount, Max(y.i_FTE_number) i_FTE_number
    FROM CompanySales y
    INNER JOIN
    (select cs.i_company_id, max(cs.i_quarter) lastqtr, max(cs.i_year) lastyr
    from CompanySales cs
    where cs.i_year IN (select max(x.i_year) from CompanySales x where x.i_company_id = cs.i_company_id)
    group by c.i_company_id) x
    ON y.i_company_id = x.i_company_id AND y.i_year = x.lastyr AND y.i_quarter = x.lastqtr
    GROUP BY y.i_company_id, y.i_year

    This filters the results to the last known quarter of the last known year for each i_company_id. Note that the last quarter may be a different time period for each company.

    This would be so much easier if you had a combined year/quarter column ...
  13. PAMUR New Member

    Dear Frank!

    I need latest FTE for the year, for that company id and not total of FTE. Thank you.

    Usha Rani
  14. Roji. P. Thomas New Member

  15. FrankKalis Moderator

    That's why I wrote "Forget it". [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br />Realised that too late. Anyway, next try<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT t1.i_year, t1.i_company_id, x.i_Sales_Amount, t1.i_FTE_number<br /> FROM CompanySales t1<br /> JOIN <br /> (SELECT SUM(t1.i_Sales_Amount) i_Sales_Amount, MAX(t1.compSalesID) compSalesID<br /> FROM CompanySales t1<br /> GROUP BY i_company_id, i_year<br /> HAVING MAX(i_year) = (SELECT MAX(i_year) <br /> FROM CompanySales<br /> WHERE i_company_id = t1.i_company_id)) x<br /> ON t1.compSalesID = x.compSalesID<br /></font id="code"></pre id="code"><br />Not sure, if it's faster, though. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  16. PAMUR New Member

    Thanks Frank. The peformance is the same, but correct. Thank you.

    Usha Rani
  17. PAMUR New Member

    You are right Adriaan. This is what I have done in the first query and yours is performing better and simpler. But not much diff in Performance why?


    Usha Rani
  18. FrankKalis Moderator

  19. Adriaan New Member

    If you can add a composite field for yyyy-q (2006-3, etc.) and index that, then you can run simpler and better performing queries.

    As a workaround, you could see if creating a temp working table with proper indexes [edit]and that composite field[/edit] can improve matters ...
  20. PAMUR New Member

    Frank!
    We have 45000 rows at present
    and the indexes are on:
    CompanySales Table
    PK i_CompanyYearlyData_id identity clustured
    IX i_company_id
    Company
    i_company_id identity clus:


    WIll add check it out Adrian.


    Thank you.

    Usha Rani
  21. mmarovic Active Member

    quote:Originally posted by PAMUR

    Sample Data: data is not ordered.
    Company:
    companyid and other fields
    20
    21
    22
    CompanySales data
    1, 20, 1999, 1, 15, 100
    2, 20, 1999, 2, 20, 90
    3, 20, 1999, 3, 30, 120
    4, 20, 2000, 1, 15, 100
    5, 20, 2000, 2, 20, 90
    6, 20, 2000, 4, 30, 120
    7, 21, 1999, 1, 20, 100
    8, 21, 1999, 2, 30, 110
    9, 21, 2000, 1, 20, 100
    10, 21, 2000, 2, 30, 110
    Need to get sum sales of latest year, per company and latest FTE

    Usha Rani
    Is the expected result one i posted bellow?

    co, year, sum, fte
    -------------------
    20, 2000, 65, 120
    21, 2000, 100, 110
  22. mmarovic Active Member

    If I got your intention right try next query:


    select t.i_company_id, t.i_year, sum(c.i_sales_amount), t.i_fte_number
    from (
    select a.i_company_id, a.i_year, a.i_fte_number
    from
    (
    select i_company_ID, max(10*i_year + i_quarter) as i_quarter
    from COmpanySales
    group by icompany_ID, i_year
    ) as b
    inner loop join companySales a on b.i_company_Id = a.i_company_id and
    a.i_year = b.i_quarter /10 and
    a.i_quarter = b.i_quarter % 10
    option (force order)
    ) as t
    inner loop join companySales c on c.i_company_id = t.i_company_id and
    c.i_year = t.i_year
    group by t.i_company_id, t.i_year, t.i_fte_number
    option (force order)

    This query is far from simple, but I am curious about performance. I didn't test it, I hope the syntax is correct.

    quote:
    We have 45000 rows at present
    and the indexes are on:
    CompanySales Table
    PK i_CompanyYearlyData_id identity clustured
    IX i_company_id
    Company
    i_company_id identity clus:


    Index names don't help us, we need to know columns indexes consist of. For the query proposed above optimal index will contain columns i_company_id, i_year, i_quarter in that order.
  23. mmarovic Active Member

    Another (much simpler) solution that may even be faster then most solutions offered (not sure about that) involves the use of cursor.
  24. FrankKalis Moderator

    And if you are already on SQL Server 2005, it should be quite easy using RANK() which should run faster than a cursor. [<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>

Share This Page