January 30, 2012 at 11:10 am
I have to execute a stored procedure against over 100K records in a table.
EXEC dbo.MY_SPROC @CustomerId
What would be the best way to accomplish this?
I would like to avoid using cursor if possible.
The procedure itself is a third party product and cannot be changed.
I appreciate al the help
Thanks
Umit
January 30, 2012 at 11:23 am
do you want to loop the SP on select list of a table?
January 30, 2012 at 1:21 pm
Yes, that is correct.
The procedure will use one field from each record of the selected list. and run it to process it.
The table will contain about 100K unique records
January 30, 2012 at 1:31 pm
If it were me I would consider writing my own version that can process more than 1 row at a time. I know what you mean when you have stuff like that you can't change because of SLA and such. You have two choices in those cases, either write your own and use that one, or roll them through a cursor and get a cup of coffee while the thing chugs along. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2012 at 1:44 pm
Thank you. I didn't want to do that but I just kind of have I guess 🙁
I am going to go for the cursor...
At least I don't feel too guilty anymore:-D
January 30, 2012 at 1:55 pm
Any chance you can just roll your own? I can't imagine the performance burn of a cursor calling a stored proc 100k times.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2012 at 2:16 pm
Not really. It is a pretty heavy duty procedure and it could cause more problems if the results come out unexpectedly.
I will have to run this process once a month so I am hoping it won't be so bad.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply