Insertion of rows in DB table taking too long

Hi All,

I am trying to insert about 700 rows into a table using a!writeToDataStoreEntity Smart service as well as function. With both of these approaches, it takes about 1.5 to 2 minutes to write to DB ( SQL Server 2012).

I tried with a smaller datasubset of 200 records, still takes about 30 seconds - which is a lot!

Also, tried to manually insert these 700 rows from Microsoft SQL Mgmt Studio - takes a few milliseconds. 

We have a use case where in user can "SAVE" upto 700 records (max possible scenario) - in which case we have to insert these rows into DB and then show the updated data on the interface. Since it is taking this much time, the interface times out and there is no way for the activity chaining to hold up for this long.

Any ideas what might be causing this issue? Appreciate your help on this.

Regards,

Ishani

  • A few things to check:

    • what secondary indexing do you have applied on the database table? Inserts will make the database update any secondary indexes which will make the inserts slower
    • what size is the data being inserted? have you checked to see if the network is not acting as a bottleneck for this activity?
    • what 'updates' do the users need to see after the insert? If there is nothing different (other than perhaps the Primary Key being generated) you do not need to 'refresh' the data from the database after the insert because it will effectively be identical

 Discussion posts and replies are publicly visible