query plan shows cost of 54% for an insert when no rows actually involved

In one of my queries there's an inse开发者_JAVA百科rt of data into a temp table. Looking at the query plan, it shows the the actual insert into temp table took 54% (just inserting data into temp table). However, no rows are being inserted into the temp table.

Why does the plan show a non zero value when no rows are being inserted?

---

Even in the actual query plan the subtree costs shown are based on estimates as well as various heuristics and magic numbers used by the cost based optimiser. They can be woefully wrong and should be taken with a big pinch of salt.

Example to reproduce

create table #t
(
i int
)

insert into #t
select number
from master.dbo.spt_values
where number = 99999999

The actual insert was zero rows but the estimate was 1 row which is where the subtree cost comes from.

Edit: I just tried the following

insert into #t
select top 0 number
from master.dbo.spt_values
where number = 99999999

Even when it gets the estimated number of rows right it still assigns a small non zero cost to the insert. I guess the heuristic it uses always assigns some small element of fixed cost.

---

take a look at this

insert into #temp
select * from sometable 
where left(Somecol,3) = 'BLA'

that is not sargable so it will cause a scan, but if no rows are found the insert doesn't happen...the scan still happens

but if you did this then the cost should drop dramatically because now the index can be used

insert into #temp
select * from sometable 
where Somecol like 'BLA%'

BTW I would use STATISTICS TIME and STATISTICS IO instead to measure performance, those two are much better indicators..when you see 3 reads vs 10000 reads you know what is happening..what does 45% tell you exactly when the whole process could run 3 minutes or 3 seconds

---

The cost of 54 doesn't mean that rows need be involved. Perhaps there was an index scan or other seek operation or perhaps some non-optimal lookup in the WHERE clause of that INSERT into temp table?