I'm in the process of looking hard at the execution plan of a stored procedure to optimize access to a 13M record table, and I'm seeing something odd.
When looking at the cost of each subtree in the proc, the results for the following statement have stumped me:
select col1, col2, etc
from table1 a
inner join table2 b on a.id=b.id
inner join table3 c on a.col=c.col
where @var=1
In my testing, @var is always equal to 1, so the effect of the WHERE clause is to let all the resulting rows (about 850 of them) into the resulting recordset.
Here's what's so odd. If I remove the WHERE clause and leave everything else in the statement identical, the cost for this statement increases by a factor of 3! The same number of rows are returned.
So... I'm confused about how to interpret the "cost" of a subtree (or an operation within the subtree). Is the cost an absolute value such that a lower number is always better, or is it somehow relative to the rest of the procedure. I presume the overal cost is derived somehow from the CPU cost and the IO cost, but how? Does it make sense that my cost would go up even though I'm effectively making the statement simpler?
When looking at the cost of each subtree in the proc, the results for the following statement have stumped me:
select col1, col2, etc
from table1 a
inner join table2 b on a.id=b.id
inner join table3 c on a.col=c.col
where @var=1
In my testing, @var is always equal to 1, so the effect of the WHERE clause is to let all the resulting rows (about 850 of them) into the resulting recordset.
Here's what's so odd. If I remove the WHERE clause and leave everything else in the statement identical, the cost for this statement increases by a factor of 3! The same number of rows are returned.
So... I'm confused about how to interpret the "cost" of a subtree (or an operation within the subtree). Is the cost an absolute value such that a lower number is always better, or is it somehow relative to the rest of the procedure. I presume the overal cost is derived somehow from the CPU cost and the IO cost, but how? Does it make sense that my cost would go up even though I'm effectively making the statement simpler?