CODEWITHSUNDEEP
sqlsql-server
Marc Guillot
Marc Guillot

Reputation: 6455

Optimize Query (remove subquery)

Can you help me to optimize this Query ?. I need to remove the subquery because the performance is awful.

select LICENSE,
       (select top 1 SERVICE_KEY 
        from SERVICES 
        where SERVICES.LICENSE = VEHICLE.LICENSE
        order by DATE desc, HOUR desc)
from VEHICLE

The problem is that I can have two SERVICES on the same DATE and HOUR, so I haven't been able to code an equivalent SQL avoiding the subquery.

The query runs on a Legacy database where I can't modify its metadata, and it doesn't have any index at all. That's the reason to look for a solution that can avoid a correlated query.

Thank you.

Upvotes: 4

Views: 152

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

You can express your query using ROW_NUMBER() without the need for a correlated subquery. Try the following query and see how the peformance is:

SELECT t.LICENSE, t.SERVICE_KEY
FROM
(
    SELECT t1.LICENSE, t1.SERVICE_KEY
           ROW_NUMBER() OVER (PARTITION BY t1.LICENSE
                          ORDER BY t2.DATE DESC, t2.HOUR DESC) rn
    FROM VEHICLE t1
    INNER JOIN SERVICES t2
        ON t1.LICENSE = t2.LICENSE
) t
WHERE t.rn = 1

The performance of this query would depend, among other things, on having indices on the join columns of your two tables.

Upvotes: 4

Related Questions