Too many reads when inserting data

• Bimal Fernando

  SSCommitted

  Points: 1945

  More actions

  May 19, 2008 at 11:47 pm

  #186782

  Hi all,

  I was fine tuning a SP code and came accross this issue which i could find an proper explanation. Basically what i want to understand is why inserting the same amount of data in different methods do different number of reads and why method one does so much reads.

  --METHOD1

  create table Table2 (

  ID int not null

  ,AnotherID int

  ,Code nvarchar(20)

  ,RegCode nvarchar(10)

  )

  -- This insert does around 4000 logical reads according to profiler and

  insert into Table2

  select * from Table1 -- has the same structure as table2 and contains 4000 records

  --METHOD2 - This method only does 350 reads

  select *

  into Table3

  from Table1

  this get worse if you use a table variable.

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  May 20, 2008 at 8:16 pm

  #818323

  Outstanding question! And, sadly, I have no idea why... but it certainly explains why SELECT/INTO is faster even in the FULL recovery mode.

  I did it on a 3 column wide million row table... got the same results whether in FULL or SIMPLE recovery....

  DROP TABLE JBMTest

  GO

  --===== Create and populate a 1,000,000 row test table.

  SELECT TOP 10000

  SomeID = IDENTITY(INT,1,1),

  SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,

  SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

  + CHAR(ABS(CHECKSUM(NEWID()))%26+65)

  INTO dbo.JBMTest

  FROM Master.dbo.SysColumns t1,

  Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN

  --===== A table is not properly formed unless a Primary Key has been assigned

  -- Takes about 1 second to execute.

  ALTER TABLE dbo.JBMTest

  ADD PRIMARY KEY CLUSTERED (SomeID)

  CREATE TABLE Table1 (SomeID INT,SomeInt INT, SomeLetters2 CHAR(2))

  GO

  SET NOCOUNT ON

  set statistics io on

  set statistics time on

  INSERT INTO Table1

  SELECT * FROM dbo.JBMTest

  set statistics time off

  set statistics io off

  print replicate('=',78)

  GO

  SET NOCOUNT ON

  set statistics io on

  set statistics time on

  SELECT *

  INTO Table2

  FROM dbo.JBMTEST

  set statistics time off

  set statistics io off

  print replicate('=',78)

  GO

  drop table table1,table2

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)