Can a SP invoke a thread to call another function

Question

Can a SP invoke a thread to call another function

Anand S-265483

SSC Enthusiast

Points: 127
More actions
August 28, 2010 at 4:44 am

#237258

Hi,
In a project we have the follwoing situation
A stored procedure is called which has to do analysis of all products.
The analysis part is written as a function (or another SP as there is nothing to return). The output will be placed in a table.
Since the analysis of each product is independent, what I would liek to do is
****** Current approach ******
Stored procedure
Begin
Get all products
Loop
Call AnalysisProduct(Product)
End loop
end Procedure
what I would like to do is
Stored procedure
Begin
Get all the products
Divide into 5 arrays/data tables
Create 5 threads
Pass one array to each thread
Each thread will process the products in its array one by one.
Close threads
End Procedure
Is this feasible? It is possible in sqlserver 2008 stored procs to do this? Does it need any specific installation option?
If you have any links/sample code etc, it would be of great help.
Thanks a lot
Anand

Viewing 6 posts - 1 through 5 (of 5 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I think it will be better to redesign the whole process. Modify the analysis procedure to process all products at once instead of calling it for each product separately. Or, at least design that subroutine as a inline-table-valued function.

You should see this process as "I'd like to get the analysis results for all my products" instead of "I'd like to get the analysis results for one specific product, repeated for every product".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 2

Hi Anand.

What is your analysis function (AnalysisProduct()) doing with each row? What, if any, other tables are accessed?

MCITP SQL Server 2005/2008 DBA/DBD

Anand S-265483 SSC Enthusiast Points: 127 More actions · Answer 3

Hi Lutz, Brian,

Thanks a lot for the replies.

For each product, the analysis is done by querying various tables - about 5 tables to find out the inventory, incoming supplier order, orders in process, outgoing customer order etc and then it creates new rows in a diff table. It will also compare the values against a different table and create adjustement data as required. It will do these queries/saves for all dates between a given date range

That's the reason, I do not want to rewrite the function but to see whether I can call this particular analysis function in parallel rather than in serial.

thanks again

Regards

Anand

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 4

Hi Anand

You are approaching this in the wrong way using SQL. As mentioned by Lutz, you need to approach this using set based logic rather than row based.

There are many great articles online on the subject!

MCITP SQL Server 2005/2008 DBA/DBD

Anand S-265483 SSC Enthusiast Points: 127 More actions · Answer 5

Hi Brian,

Thanks for the inputs.

This is a project where the client already has the SPs and we are just creating a wrapper on top of the SP to provide a user interface and a .net app to call the app and generate report.

I want to avoid re-engineering the sP that this point and that's why the question about just tweeking the calls and creating threads.

thanks

Anand