Post reply

DML - insert taking long

  • September 17, 2008 at 7:52 pm

    #154737

    Guys,

    For each unique record an insert statement is run on a table from the appllcation as part of transaction of 100 inserts, but random insert statement in between takes upto 2 secs.

    For example in Employee about inserts 100 statements are run for empid between 1 and 100

    insert into employee (empid, lastname) values (1, 'smith')

    insert into employee (empid, lastname) values (2, 'smith')

    .

    .

    insert into employee (empid, lastname) values (100, 'smith')

    However random insert statement in between like insert for empid = 75 takes long upto to 2 secs, how do we approach to troubleshoot this issue?

    Any suggestions/inputs would help

    Thanks

  • September 17, 2008 at 10:59 pm

    #873384

    1. First what is the the volume of data you are inserting, If you are inserting big volume of data it may take 2 seconds or more to insert.

    2. Check your database data file autogrowth properties. If you are inserting 10 MB of data in one insert statement and if you were set datafile growth each time 5MB then your data file has to grow two times for one insert statement. Data file growth is time consuming.

    Best practice is not set by percentage, depending on your database growth set the appropriate value.

    Rajesh Kasturi

  • September 18, 2008 at 1:48 pm

    #873900

    I have removed the clustered index to an ID field and set the growth to MB instead of %. Monitoring the results right now.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply