Query Plan Error or Correct Estimation

Hi All,

We have few queries which are simple selects . For simplicity let's say there is a query that joins two tables and gets few columns and both tables have no indexes.

Select Tab1.Field1, Tab2.Field2
From Table1 Tab1
Join Table2 Tab2
On Tab2.FK = Tab1.PK

When we do query plan for this it shows approx 6 million, however if we make a simple adjustment to the query

Select Tab1.Field1, Tab2.Field2
From Table1 Tab1
Join Table2 Tab2
On Tab2.FK = Tab1.PK
WHERE Tab1.Id > 0 (Which will always be the case)

The query plan comes down to few thousands. So approx 99% improvement.

Is this approximation correct or is it a bug in cache??

Eduard Lebedyuk · Jan 16, 2020

Please post query plans.

The most important question is - are they actually taking different time to run?

0 0

Neerav Verma · Jan 16, 2020

For this current one the execution in both cases is almost same .

Both are .05 ms

That's what made me thinking that 99% difference in relative cost should have had at least 50% impact on time.

0 0

Alexander Koblov · Jan 16, 2020

Neerav,

relative cost is only useful when you compare different plans for the same query. By itself "relative cost" means nothing.

0 0

Vitaliy Serdtsev · Jan 17, 2020

See answer by @Brendan Bannon about TuneTable.

Documentation:

“Relative cost” is an integer value which is computed from many factors as an abstract number for comparing the efficiency of different execution plans for the same query. This calculation takes into account (among other factors) the complexity of the query, the presence of indices, and the size of the table(s). Relative cost is not useful for comparing two different queries. ^proof

0 0

Brendan Bannon · Jan 17, 2020

Neerav

When the optimizer is looking at a query it does not look at the parameter values, in fact, they are removed before the query gets to the optimizer, so it sees: WHERE Tad1.Id > ? Because the value is removed the optimizer can only guess at how restrictive the condition is, and it is an optimist so it assumes it will be restrictive. That is why the Relative Cost changed so much. The optimizer thinks this query will return only a small number of rows.

Range conditions that are not restrictive can cause performance problems if there is an index on the property involved. Since the optimizer thinks the condition is restrictive it might choose to use the index and end up doing a table scan. If you have a range condition that you know is NOT restrictive you might want to put the %NOINDEX hint in front of the condition so the optimizer knows not to use it.

WHERE %NOINDEX Tad1.Id > ?

2 0