2013-04-25

<> operator

If you read my last article, I had optimized a query by transforming a sub-query in an inner join and reduced drastically the execution time of that query. This is the optimized 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

But it can be more optimized. This query handles more than 3 billions records and uses 3 '<>' operators. Substituting those operators for equal operators and then negate it did the job:
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 NOT (wi.r_runtime_state = 2) 
      AND (wk.r_runtime_state = 1) 
      AND NOT (qi.router_id = '0000000000000000')
      AND (dr.i_is_deleted = 0) 
      AND NOT (qi.task_state = 'paused')
ORDER BY Column1

This new query ran in 5 seconds. Almost in half time the before one (8 seconds). So in two steps I could optimize a query that took 4 minutes and 25 seconds to execute in 5 seconds. 53 times faster and no indexes created!!

I made some more tests with this solution and looks like that you can see only results when you work with massive data. With few thousands records I did not see any difference.
Curious is that there was no changes in the query plan. Means that it is how the SQL Server engine treats the data when use <> or = operators.

No comments:

Post a Comment

Comente aqui