SUBQUERY - be careful when using it

Few years ago I had a call from a client that was complaining about one slow query.
As always I started a SQL Profiler to catch long running queries. It wasn't hard  to find in the trace the following query that took 4 minutes and 25 seconds to run:

SELECT Column1, Column2, Column3, Column4, Column5

FROM dbo.wi  

INNER JOIN dbo.qi ON wi.r_object_id = qi.item_id 

INNER JOIN dbo.p ON wi.r_workflow_id = p.r_workflow_id 

INNER JOIN dbo.wk ON wi.r_workflow_id = wk.r_object_id 

CROSS JOIN dbo.dr 

WHERE (p.r_object_id IN (SELECT r_object_id 
                   FROM dbo.dmi 
                   WHERE (r_component_id = dr.r_object_id)))        

      AND (wi.r_runtime_state <> 2) 

      AND (wk.r_runtime_state = 1) 

      AND (qi.router_id <> '0000000000000000')

      AND (dr.i_is_deleted = 0) 

      AND (qi.task_state <> 'paused')

ORDER BY Column1


The cross join and the sub-query called my attention immediately. Cross join produces the Cartesian product of the tables involved in the join. In this example the cross join returns more than 3 billions rows and each of these rows will be correlated with the sub-select.
I changed that query and transformed the sub-query in inner join clause. The new query:

SELECT Column1, Column2, Column3, Column4, Column5

FROM dbo.wi  

INNER JOIN dbo.qi ON wi.r_object_id = qi.item_id 

INNER JOIN dbo.p ON wi.r_workflow_id = p.r_workflow_id 

INNER JOIN dbo.wk ON wi.r_workflow_id = wk.r_object_id 

CROSS JOIN dbo.dr 

    INNER JOIN dbo.dmi ON dmi.r_component_id = dr.r_object_id
WHERE (p.r_object_id = dmi.r_object_id) 

      AND (wi.r_runtime_state <> 2) 

      AND (wk.r_runtime_state = 1) 

      AND (qi.router_id <> '0000000000000000')

      AND (dr.i_is_deleted = 0) 

      AND (qi.task_state <> 'paused')

ORDER BY Column1

This new query took 8 seconds to run.

The difference is in the number of executions that SQL Server engine needs to make so be very careful when using sub-queries. Only use it if necessary. Whenever you can use inner join instead.

This query can be more optimized but I will let that for my next article.