Simple insert

• osama hussein

  SSC Journeyman

  Points: 82

  More actions

  November 19, 2012 at 4:58 pm

  #285125

  Am running simple loop to insert two same values 1000 times in a2 columns table The insert time normally is 2 sec or less

  Am getting sporadic results. That varies from 1 sec to on min

  Any idea were to start debugging , running SQL 2008

  Thank you in advance for your help or suggestion

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  November 19, 2012 at 8:11 pm

  #1560585

  With the information you have posted, it is difficult to say anything other than "It depends", which is NOT a suitable answer to your question. Now

  a. Any other procedures accessing this table ?

  b. Are there only those 2 columns in the table

  c. No indexes defined

  d. Table does not have an identity value

  e. In performing your multiple attempts how busy was the DB, i.e. other procedures accessing tables in this database.

  And to insert a thousand rows .. even a few seconds seems like an inordinate time to do that simple task .... can you post your code

  I performed a quick test using:

  DECLARE @s-2 DATETIME

  DECLARE @a INT

  SET @a = 1

  SET @s-2 = GETDATE()

  WHILE @a <=9

  BEGIN

  INSERT INTO #A VALUES(1,10)

  INSERT INTO #A

  SELECT Col1,Col2 FROM #A

  SET @a = @a + 1

  END

  SELECT @s-2, GETDATE(),DATEDIFF(MS,@S,GETDATE())

  SELECT COUNT(Col1) FROM #A

  Results:

  213 milliseconds to insert 1,022 rows

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]