SQLTeam.com | Weblogs | Forums

Logical Reads when inserting into a heap


#1

Hi,

I have a table with no indexes (PK was dropped before the actual inserts). When the insert takes place the logical reads (both in statistics io and in the profiler) show a number matching the number of records being inserted. So if we are inserting 10 million records it shows up 10 million reads.
Will this always be true and is it Ok for it to be this way or is there any way this figure can be curtailed?

Below is a sample that I created and the logical reads=no. of records being inserted into the heap table:

SET STATISTICS IO ON

CREATE TABLE TMP (ID INT)
CREATE TABLE TMP2 (ID INT)

INSERT INTO TMP
SELECT TOP 100 ROW_NUMBER()OVER(ORDER BY (SELECT NULL))
FROM MASTER..SPT_VALUES A
CROSS JOIN MASTER..SPT_VALUES B
WHERE A.TYPE='P' AND B.type='P'
--CHECK THE STATISTICS IO FIGURE FOR LOGICAL READS INTO TMP2
INSERT INTO TMP2
SELECT ID FROM TMP

SET STATISTICS IO ON

DROP TABLE TMP
DROP TABLE TMP2

Thanks


#2

Anyone? I do understand that this is correct but still just want a confirmation of the same.

Thanks


#3

Finally I was able to minimize the reads, so this is for others who might face a similar issue. Appropriate Indexes are obviously required but also I did a Select into instead of Insert into. There was a marked improvement maybe due to minimal logging that select into has.

But still my question remains unanswered, why would there be so many reads for the target insert table? I am hoping someone would reply this time.

Thanks