Now my SP working slow?

Question

Post reply

Now my SP working slow?

IN_Sandeep

SSCertifiable

Points: 6259
More actions
June 10, 2008 at 9:23 am

#186666

Hi Friends,
I have one SP it was working fine when i was doing single insert to it.
but when I am making multiple inserts its going slow and sometimes showing error like Connection Closed, transaction open and Opening puling Error.
Actually I am making inserts from my fronted side which is loop wise inserts, I means at a time more than inserts with in a short span of time.. and I also handled clearly the transactions in side the SP too.
Still SP is working slow.
Now What should be the next steps?
Cheers!
Sandy.
--

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

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

haroon waheed SSC Veteran Points: 261 More actions · Answer 1

It seems that your are calling the SP in a loop for mulitple inserts.

Calling SP again and again has its cost.

Try calling the insert SP once even for mutiple inserts. You my achive this by passing the SP an xml containing the information that needs to be inserted.

A batch insert will be much faster than a loop insert

If I misunderstood please share the code for greater understanding.

Arivumani Grasshopper Points: 17 More actions · Answer 2

Hi,

You can do this by creating a temporary Dataset or Table, Insert all the needed records into it and pass it to Stored Procedures as Parameter, so that the procedure can process all those records at once.

Thanks,

Arivu

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 3

haroon waheed
------------------------------------------------------------------
It seems that your are calling the SP in a loop for mulitple inserts.
Calling SP again and again has its cost.
Try calling the insert SP once even for mutiple inserts. You my achive this by passing the SP an xml containing the information that needs to be inserted.
A batch insert will be much faster than a loop insert
If I misunderstood please share the code for greater understanding.

Yeah, Sure....

Actually It was working fine with single inserts but for multiple time it was showing error. So I will make it once but I guess I have to build X ML Type to make this..

Anyways I will make it easier as for SP....

so it will work fine and smooth to my fronted application...

Thanks all...I got the way....:)

Cheers!

Sandy.

--