I am comparing two tables. They are:
CREATE TABLE Table1( Column1 [bigint] NOT NULL, Column2 [VARBINARY](max) NULL, Column3 [int] NULL, Column4 [DATETIME] NULL, Column5 [INT] NULL, Column6 [DATETIME] NULL, [RowVersionId] [timestamp] NOT NULL, CONSTRAINT [PK_BagImage] PRIMARY KEY CLUSTERED (Column1 ASC) )
This is the second table:
CREATE TABLE Table2( Column1 [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, Column2 [BIGINT] NULL, Column3 [VARCHAR](1) NOT NULL, Column4 [int] NOT NULL, Column5 [varchar](11) NOT NULL, Column6 [varchar](13) NOT NULL, Column7 [datetime] NOT NULL, Column8 [bigint] NULL, Column9 [bit] NOT NULL, Column10 [uniqueidentifier] NOT NULL, [RowVersionId] [timestamp] NOT NULL, CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ( Column1 ASC) )
Neither of them have any non-clustered indexes. They do each have a foreign key, but the query plans for looking them up are lightening fast! (Clustered Index Seek that takes 00:00:00).
When I copy 55K of rows into the first one it takes 45 minutes. When I copy 7.7 million rows into the second it takes 7 minutes.
That all seems to make sense to me. (copying images should take longer, right?)
But I wanted to calculate a rough IO for this operation. So I ran
dbcc showcontig on these tables.
Table MinimumRecordSize MaximumRecordSize AverageRecordSize Table1 75 123 81.733 Table2 101 101 101
So here is where I am getting confused. If the average record size is 20 less for the table with the images in it, then why does it take so very much longer to insert the data? Especially when I am inserting 7 Million rows more into the second table.