September 20, 2016 at 9:15 am
In my query, I have two columns displaying a value based on a sub query. If you notice, I have two sub queries almost identical but displaying different information. I dont like the fact I'm doing the same operation but repeating it twice to display two different column information. In sub query, you can only display one expression.
What is the alternate and more optmized way to display both columns in the table without running the query twice.
SELECT Distinct T.ticketid AS [Ticket ID],
t.TType as [TicketType],
(SELECT Top 1
case when Month(TaskFinish)=Month(TaskSTart) THEN Month(TaskStart) ELSE '0' END
FROM queues q
LEFT JOIN Response2 R ON R.QAssignID=AssignID
WHERE q.ticketid = t.TicketID And TaskNameID=1 AND ttype =1
ORDER BY TaskSTart desc) [MonthSubmitted],
(SELECT Top 1 Response
FROM queues Q
LEFT JOIN Response2 R ON R.QAssignID=AssignID
WHERE Q.ticketid = t.TicketID And TaskNameID=1 AND ttype =1 ORDER BY TaskSTart desc ) [RESPONSE]
FROM tickets T
WHERE T.ticketstatus = 'O' AND T.globalid = '66'
September 20, 2016 at 9:27 am
I don't have time to rewrite it (and no DDL anyway), but the main performance problem there is the TOP (1) ... ORDER BY in the subquery.
Try changing the subquery to a derived table (nested select statement in the FROM clause), using ROW_NUMBER if necessary to get the row you want. That should make the query a fair bit better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2016 at 9:45 am
One thing is that your second subquery does not have an ordered specified, so the order is non-deterministic.
The other thing is that your subqueries are not obviously the same. Yes, they have some of the same parts, but they may return different records. One issue is that the first subquery has an order specified, and the desired order for the second subquery may not match the first.
The second issue is that the second subquery has an inner join that is not part of the first query and the record returned in the first query may be filtered out by the join condition.
If you decide that the two subqueries really do match, you can use the CTE/derived table with the ROW_NUMBER, or you could use a CROSS APPLY with a TOP(1). CROSS APPLY with TOP(1) can perform fairly well under certain conditions, but it's difficult to tell whether your data will meet those conditions. The conditions are you have the right index in place and that the number of records in the main query is small and the number of records per main record is large.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 20, 2016 at 9:58 am
drew.allen (9/20/2016)
One thing is that your second subquery does not have an ordered specified, so the order is non-deterministic.The other thing is that your subqueries are not obviously the same. Yes, they have some of the same parts, but they may return different records. One issue is that the first subquery has an order specified, and the desired order for the second subquery may not match the first.
The second issue is that the second subquery has an inner join that is not part of the first query and the record returned in the first query may be filtered out by the join condition.
If you decide that the two subqueries really do match, you can use the CTE/derived table with the ROW_NUMBER, or you could use a CROSS APPLY with a TOP(1). CROSS APPLY with TOP(1) can perform fairly well under certain conditions, but it's difficult to tell whether your data will meet those conditions. The conditions are you have the right index in place and that the number of records in the main query is small and the number of records per main record is large.
Drew
You are absolutely right. I just updated my query to match because the difference that you pointed out shouldn't affect the top result in my result set.
Can you show how I can use CTE or cross aply in this case?
September 20, 2016 at 12:08 pm
I had to make some assumptions, and those assumptions may not be valid. I've noted those in the comments of the code.
First, you need to be consistent in your naming conventions. If you're going to use CamelCase for a particular element, ALWAYS use CamelCase for that element. (And if you don't separate the words in your names with underscores (_), you better be using CamelCase.) I use SQL Prompt to help with this consistency.
Also, don't force the compiler to do implicit conversions. If you know the data type of something and something needs to match that data type, use that data type. For instance, all of the values returned by a CASE statement have to have compatible data types. In some cases, you're returning the MONTH() which is an integer, but in other cases you're returning '0' which is a string, forcing the compiler to do an implicit conversion of the string to an integer.
;
-- CTE version. I've assumed that TaskNameID and TType are fields in queues.
-- You should ALWAYS qualify your field names.
WITH queues_ordered AS (
SELECT
q.TicketID,
CASE WHEN MONTH(q.TaskFinish) = MONTH(q.TaskStart) THEN MONTH(q.TaskStart) ELSE 0 END AS MonthSubmitted,
-- Month() returns an integer, so the else case should also be integer
q.Response,
ROW_NUMBER() OVER(PARTITION BY q.TicketID ORDER BY q.TaskStart DESC) AS rn
FROM queues q
)
SELECT *
FROM tickets t
INNER JOIN queues_ordered qo
ON t.TicketID = qo.TicketID
WHERE qo.rn = 1
AND t.TicketStatus = 'O'
AND t.GlobalID = 66 -- I'm assuming that this is an int field.
;
-- CROSS APPLY/TOP(1) version
SELECT *
FROM tickets t
CROSS APPLY (
SELECT TOP(1)
CASE WHEN MONTH(q.TaskFinish) = MONTH(q.TaskStart) THEN MONTH(q.TaskStart) ELSE 0 END AS MonthSubmitted,
q.Response
FROM queues q
WHERE q.TicketID = t.TicketID AND q.TaskNameID = 1 AND q.TType = 1
ORDER BY q.TaskStart DESC
) qo
WHERE t.TicketStatus = 'O'
AND g.GlobalID = 66
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 20, 2016 at 3:50 pm
beautiful!
Thank you so much! This is exactly what I was looking for
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply