The query plan displays the cost of 54% for insertion when the rows are not actually involved

Question

One of my queries has insert data in the temp table. Looking at the query plan, it shows that the actual insertion into the temp table took 54% (just pasting the data into the temp table). However, no rows are inserted into the temp table.

Why does the plan display a non-zero value when no rows are inserted?

Answer 1

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

Playback example

 create table #t ( i int ) insert into #t select number from master.dbo.spt_values where number = 99999999 

The actual insert is zero rows, but the score was 1 row, in which the subtree cost occurs.

Edit: I just tried the following

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

Even when he gets the approximate number of rows to the right, he still assigns a small non-zero insertion cost. I assume that the heuristic he uses always assigns a small element of fixed value.

Answer 2

take a look at this

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

which is not sargable , so it will cause a scan, but if no rows are found, the insert will not happen ... the scan is still happening

but if you did, the cost should drop sharply, because now you can use the index

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

BTW I would use STATISTICS TIME and STATISTICS IO instead of measuring performance, these two are much better indicators. When you see 3 reads versus 10,000 reads, you know what happens. What 45% tell you exactly when the whole process can run for 3 minutes or 3 seconds

Answer 3

The cost of 54 does not mean that the lines should be involved. Perhaps there was an index check or other search operation, or perhaps some suboptimal search in the WHERE clause of this INSERT in the temp table?