I have a stored procedure which inserts some records in a few tables. In couple of tables at least , the inserted records number 10000+. Not more than 15K though. Takes 3-5 mins
for this procedure to complete. Also same procedure can be called from multiple user sessions which results in some sessions waiting for 20 mins to get a response. Is there something I can do to reduce this time?
The database recovery mode is FULL(cannot be changed) and hence per my understanding sqlBulkCopy wont help here. Would love to hear your thoughts on this.
The table contains 15 columns. Among which 5 columns are foreign key to this table. There is no identity column but clustered index in combination of all the 5 foreign key columns. I have couple of index on other key columns . The rest of columns are decimal and varchar(50). I do have one varchar(max) column though.
Trying to get query or query same for your reference . A screen grab of most expensive operation(54) in the query:
If the clustered index of the table is on combination of columns instead of an identity column , will the inserts be impacted?
Basically the query is 'insert into this_table select from this table join with 5 other tables whose primary keys are foreign keys here to update those values. Joins are all on keys between the tables. I wouldn't mind posting the query but would you need the schema all tables involved in query as well?
EDIT2: First of all thanks all of you guys for your answers,comments and thoughts. The object from which read happens turned out to be a heap with no clustered index. Modifying it did give significant improvement in read operation which improved overall .
I marking David Spillet answer as accepted as it gave a methodical way to approach the problem. Learned a few things about posting questions :)
Also thanks for Frisbee for comments and answers. I know i kept on modifying the question :)