3
\$\begingroup\$

I use SQL Server 2014 and I have three tables:

  • Student (ID, Name, Age)
  • Product (ID, Name)
  • StudentProduct (StudentID, ProductID, Date)
TABLE Student(ID, Name, Age) >>
ID  Name    Age
1   Adam    20
2   Bob     17
3   Ben     18
4   Joseph  18 
5   Jon     18

TABLE Product (ID, Name) >>
ID  Name
1   SQLServer
2   Visual Studio
3   Idea
4   Eclipse

TABLE StudentProduct (StudentID, ProductID, Date) >>
StudentID   ProductID     Date
1           1             25.05.2016 0:00:00
1           4             26.06.2016 0:00:00
1           3             27.06.2016 0:00:00
1           2             28.06.2016 0:00:00
2           1             29.07.2016 0:00:00    

I've written a query to get all students who meet these criteria

  1. Are older than 19
  2. Participate in developing more than 3 products

Both criteria should be checked in a single query.

The desired result should then contain the

  1. Age of the student
  2. Name of student
  3. Name of the last developed product (using StudentProduct.Date)

Result should be:

Age   StudentName      ProductName
20    Adam             Visual Studio

My query is:

select e.age, e.name ,
(select top 1 p.Name from Product p
inner join StudentProduct peOut on p.ID = peOut.ProductID
where peOut.StudentID = e.ID
order by peOut.Date desc)
from Student e where e.age > 19
and exists (select pe.StudentID, count(1) from StudentProduct pe
where pe.StudentID = e.ID group by pe.StudentID
having count(1) > 3)

How can I improve the efficiency of my query?

\$\endgroup\$
  • 1
    \$\begingroup\$ Please edit your title to tell us what the query is doing - as it stands this title would be applicable to every single SQL question on this site. Also, given your "wishful result", it's not clear whether the query actually does what you intend it to do; we help making your code work better, but we don't change what it does. \$\endgroup\$ – Mathieu Guindon Aug 15 '16 at 13:29
  • \$\begingroup\$ And add the actual DBMS (and version) you're using (TOP seems to indicate MS SQL Server). \$\endgroup\$ – dnoeth Aug 15 '16 at 13:33
  • \$\begingroup\$ @Mat'sMug I just want to make my query better, not checking whether it is correct. \$\endgroup\$ – StepUp Aug 15 '16 at 13:33
  • 1
    \$\begingroup\$ Your WHERE-conditions don't match your description. \$\endgroup\$ – dnoeth Aug 15 '16 at 13:34
  • 3
    \$\begingroup\$ I've edited your post for grammar - please let me know if any change I made changes the intent of your post \$\endgroup\$ – Dannnno Aug 15 '16 at 16:10
4
\$\begingroup\$

Aside from the answer from dnoeth suggesting the use of analytical functions and common table expressions, your query style in general could be improved in several ways.

Note that these in no way will affect the performance, however they will greatly help the readability and maintainability of your queries.

  1. Use meaningful alias names. Single-letter aliases are discouraged when they do not give any information about the object or value they are referencing.

  2. Use white space to clarify structure. It's common to separate each individual statement by a new line, as well as to indent query code that is not the primary keywords (select, from, where, having, group by, order by, etc.)

  3. Use column aliases to format your result set. As written, your expected result column names will not be returned, rather they will be age, name and (no column name) respectively.

  4. Use code comments sparingly to clarify the intention of certain expressions, when it is not completely clear.

I applied the above to your original query and this is what the reformatted query could look like:

select 
    std.age as [Age], 
    std.name as [StudentName],
    /* Subquery to find most recent product worked on by student: */
    (select top 1 prd.Name 
        from 
            Product as prd
            inner join StudentProduct as stdPrdOut
                on prd.ID = stdPrdOut.ProductID
        where 
            stdPrdOut.StudentID = std.ID
        order by 
            stdPrdOut.Date desc
    ) as [ProductName]
from 
    Student as std
where 
    std.age > 19
    /* Subquery to return only students that are working on more than 3 products: */
    and exists (
        select 
            stdPrd.StudentID, 
            count(ProductID) 
        from StudentProduct as stdPrd
        where stdPrd.StudentID = std.ID 
        group by stdPrd.StudentID
        having count(ProductID) > 3
    )
;
\$\endgroup\$
5
\$\begingroup\$

Without additional info about indexes and row counts it's hard to tell, but the most elegant solution is probably using Analytical Functions (using employees instead of students):

with cte as
 (
    select s.age, s.name as EmployeeName, p.Name as ProductName,
       -- row with the newest date
       row_number() over (partition by s.ID order by sp.Date desc) as rn,
       -- number of Products per employee
       count(*) over (partition by s.ID) as cnt
    from Student as s 
    join StudentProduct as sp
      on sp.EmployeeID = e.ID
    join Product as p
      on p.ID = sp.ProductID
    where s.age > 19
 )
select age, EmployeeName, ProductName
from cte
where cnt > 3  -- more than three products
  and rn = 1   -- the latest product
\$\endgroup\$
  • \$\begingroup\$ Maybe add p.ID to order by \$\endgroup\$ – paparazzo Feb 26 '17 at 16:21
  • \$\begingroup\$ @Paparazzi: Adding it to the row_number? Why? \$\endgroup\$ – dnoeth Feb 26 '17 at 16:46
  • \$\begingroup\$ In the case of a tie they will always get the same result \$\endgroup\$ – paparazzo Feb 26 '17 at 16:47
  • \$\begingroup\$ @Paparazzi: Ok, but it might be any other column, which makes the order unique, too. \$\endgroup\$ – dnoeth Feb 26 '17 at 16:50
  • \$\begingroup\$ OK them maybe don't do it \$\endgroup\$ – paparazzo Feb 26 '17 at 16:51
4
\$\begingroup\$

I always try to keep the sql query as simple to read as possible, so the next person working on the stored procedure can quickly understand it. This is what I would do (I used #temp tables in my example for testing purposes):

SELECT
    A.Age,
    A.Name AS StudentName,
    C.Name AS ProductName
FROM #Student AS A
    INNER JOIN #StudentProduct AS B
        ON B.StudentID = A.ID
    INNER JOIN #Product AS C
        ON C.ID = B.ProductID
WHERE A.Age > 19
    AND B.[Date] = (SELECT 
                        MAX(D.[Date]) 
                    FROM #StudentProduct AS D 
                    WHERE A.ID = D.StudentID)
    AND 3 < (SELECT 
                COUNT(D.StudentID) 
            FROM #StudentProduct AS D 
            WHERE A.ID = D.StudentID)

Once again, the goal of this style is readability. For the select portion, all 3 lines are very clear on what will be returned. No weird select within a select - making it all cluttered. The from portion is simple as well. Inner join all 3 tables, with a simple 'on' clause. The last portion is where all the work should be IMO. Its the 'where' portion that should house all conditional statements - not the 'select' portion imo. So I have 3 very clear where clauses. If age is > 19. If the MAX date is equal to the date (only allows the max date for the student). And if the count of the student ID is greater than 3.

IMO, this way of doing things is the cleanest and easiest to read by far.

\$\endgroup\$
  • \$\begingroup\$ That does read quite good, +1, I need to start applying some of this to my own queries. \$\endgroup\$ – Phrancis Aug 15 '16 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.