Why a TSQL query that combines 3 separate queries runs much slower?

I'm writing a query and as I go along I'm analysing the SQL Profiler trace to monitor my query's performance and speed.

While doing this I noticed that if I run 3 different selects on the same table, these are much faster than if I ran them grouped in one select with OR's in the WHERE clause.

A)

SELECT * FROM navigation_trees nt  INNER JOIN contents c ON c.navigation_tree_id = nt.id WHERE nt.id = @NodeID

SELECT * FROM navigation_trees nt INNER JOIN contents c ON c.navigation_tree_id = nt.id WHERE nt.id = @ParentID

SELECT * FROM navigation_trees nt INNER JOIN contents c ON c.navigation_tree_id = nt.id WHERE nt.id IN (SELECT id FROM @ChildrenIDS)

B)

SELECT * FROM navigation_trees nt  INNER JOIN contents c ON c.navigation_tree_id = nt.id 
    WHERE 
        nt.id = @NodeID OR
        nt.id = @ParentID OR
        nt.id IN (SELECT id FROM @ChildrenIDS)

And here is the Profiler readings for the two queries:

CPU | Reads | Writes | Duration(ms)

A) 15 | 1095 | 0 | 26

B) 531 | 456139 | 0 | 541

Can you shed some light?

Many thanks.

Answers


Three selects are just three selects, A select with three conditions, joined by OR are actually selects plus the additional cost of removing duplicates.

If you're sure that there will never be duplicates, consider using UNION ALL rather than OR. Or course, that's a very general advice. When adjusting your queries only for the sake of performance, always refer to your execution plan to see that the specific piece of advice applies in your scenario.


I agree with David but wanted to put a code example in; those Or's are likely a killer. A basic rule i teach new developer is if you need an Or you need a union; which correlates with the other basic rule of filter early filter often. A query that filters on every join is ofttimes quicker than a filter that runs in a where clause as the optimiser can see better routes to reduce the data.

Your query may work like this or need other Union All.

Select
    *
From
(
    SELECT * 
    FROM 
        navigation_trees nt  
    WHERE 
        nt.id = @NodeID 

    union 
    SELECT 
        * 
    FROM 
        navigation_trees nt  
    WHERE 
        nt.id = @ParentID 

    union 
    SELECT 
        * 
    FROM 
        navigation_trees nt  
    WHERE
        nt.id IN (SELECT id FROM @ChildrenIDS)

) nt

INNER JOIN 
    contents c 
ON 
    c.navigation_tree_id = nt.id 

Need Your Help

LCD shift right and shift left bugs

c avr atmega lcd hd44780

I have made a library for LCD with HD44780 controller in it, and from the datasheet I read that the code 0x18 will shift the entire display one position to the left, but when I made that, the display

Signalr deserializes my objects incorrectly in IIS 7.5 and Edge/IE, foreverFrame broken?

asp.net iis knockout.js signalr knockout-mapping-plugin

So I have a simple Signalr/Knockout project that uses the mapping plugin to bind a simple object (item with an array of more items) to viewModels I defined in JS: