Post reply

Can a SP invoke a thread to call another function

  • 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

  • August 28, 2010 at 5:56 am

    #1213401

    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]

  • August 28, 2010 at 7:18 am

    #1213408

    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

  • August 29, 2010 at 10:21 am

    #1213540

    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

  • August 29, 2010 at 1:33 pm

    #1213559

    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

  • August 29, 2010 at 8:03 pm

    #1213588

    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

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

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