CODEWITHSUNDEEP
t-sqlsql-server-2012query-optimization
par181
par181

Reputation: 401

Avoiding Order By in T-SQL

Below sample query is a part of my main query. I found SORT operator in below query is consuming 30% of the cost.

To avoid SORT, there is need of creation of Indexes. Is there any other way to optimize this code.

SELECT TOP 1 CONVERT( DATE, T_Date) AS T_Date
FROM TableA
WHERE ID = r.ID
 AND Status = 3
 AND TableA_ID >ISNULL((
                        SELECT TOP 1 TableA_ID
                        FROM TableA
                        WHERE ID = r.ID
                            AND Status <> 3
                        ORDER BY T_Date DESC 
                        ), 0)
ORDER BY T_Date ASC

Upvotes: 0

Views: 155

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

Try this:

SELECT TOP 1 CONVERT( DATE, T_Date) AS T_Date
FROM TableA a1
LEFT JOIN (
    SELECT ID, MAX(TableA_ID) AS MaxAID
    FROM TableA
    WHERE Status <> 3
    GROUP BY ID
) a2 ON a2.ID = a1.ID AND a1.TableA_ID > coalesce(a2.MAXAID,0)
WHERE a1.ID = r.ID AND a1.Status = 3
ORDER BY T_Date ASC

The use of TOP 1 in combination with the unexplained r alias concern me. There's almost certainly a MUCH better way to get this data into your results that doesn't involve doing this in a sub query (unless this is for an APPLY operation).

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

Looks like you can use not exists rather than the sorts. I think you'll probably get a better performance boost by use a CTE or derived table instead of the a scalar subquery.

select *
from r ... left outer join
(
    select ID, min(t_date) as min_date from TableA t1
    where status = 3 and not exists (
        select 1 from TableA t2
        where   t2.ID = t1.ID
            and t2.status <> 3 and t2.t_date > t1.t_date
    )
    group by ID
) as md on md.ID = r.ID ...

or

select *
from r ... left outer join
(
    select t1.ID, min(t1.t_date) as min_date
    from TableA t1 left outer join TableA t2
        on t2.ID = t1.ID and t2.status <> 3
    where t1.status = 3 and t1.t_date < t2.t_date
    group by t1.ID
    having count(t2.ID) = 0
) as md on md.ID = r.ID ...

It also appears that you're relying on an identity column but it's not clear what those values mean. I'm basically ignoring it and using the date column instead.

Upvotes: 1

Related Questions