Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Question Unanswered: Multi Threading in Stored Procedures?

    hi,

    I want to execute two user created stored procedures in a multithreaded manner in ms-sql server . Can some tell me how i can do that.

    Thanks in advance.

    Regards,
    Manpreet

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Multi Threading in Stored Procedures?

    Originally posted by chugh_manpreet
    hi,

    I want to execute two users

    Ya know.....I've ALWAYS wanted to do that...

    but then who would use the system?

    Well, I guess if you limit to just 2.....

    To answer though...every exec od a sproc is threaded...it's not serial....

    Are you talking about withIN the sproc itself?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    By its design the SQL engine is single-process multi-threaded. A series of asynchronous calls always result in multi-threaded processing on the server. I guess I have the same question as Brett, - are you talking about multi-threading within a stored procedure? And why do you need that?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No chuckles?

    Anyway...To thread in a sproc, create jobs and start'em...

    They'll be aysyncronus and thread....

    although I swear (and can't confirm) that using xp_cmdshell does...even though everywhere I read it's synchronous...(I couldn;t explain some blocking awhile back....still don't know why)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I can! In pre-SP3 era when everybody and their mother was firing it while trying to be cute, and coming back with OSQL through it. There was a hell of a blocking going on. It is actually due to the nature of OSQL, rather than xp_cmdshell, but those who were using it didn't even bother to check into the possibility of it to occur, and we were stuck to debug, and what's most frustrating, - explain, how it happened. Now they are all squealing, because they can't do any more damage

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, but in a sproc if you do...

    DELETE FROM myTable99

    master xp_cmdshell 'bcp command...for myTable99'

    Wouldn't you expect the DELETE to be completed before the bcp?

    It actually launches 2 spids (of course) but spid 1 (the execution of the sproc and the delete) blocks spid 2, the execution of xp_cmdshell

    huh?

    Is this a fundamental thing I'm totally missing?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2003
    Posts
    8
    hi,

    thanks for your reply. Actually i have written two stored procedures which call a dll(non-sql) funtion. Now i want these two stored procedures simultaneously in a multi threaded manner.

    I tried executing it like this
    Code:
    exec sp_Procedure1
    exec sp_Procedure2
    but the second stored procedures is not getting executed untill first gets executed. I want both the stored procedures execute simultaneously in a multi threaded manner.

    Any inputs in this regard are welcome

    Regards,
    Manpreet

    Originally posted by Brett Kaiser
    No chuckles?

    Anyway...To thread in a sproc, create jobs and start'em...

    They'll be aysyncronus and thread....

    although I swear (and can't confirm) that using xp_cmdshell does...even though everywhere I read it's synchronous...(I couldn;t explain some blocking awhile back....still don't know why)

  8. #8
    Join Date
    Nov 2003
    Posts
    29
    Quote Originally Posted by chugh_manpreet
    hi,

    thanks for your reply. Actually i have written two stored procedures which call a dll(non-sql) funtion. Now i want these two stored procedures simultaneously in a multi threaded manner.

    I tried executing it like this
    Code:
    exec sp_Procedure1
    exec sp_Procedure2
    but the second stored procedures is not getting executed untill first gets executed. I want both the stored procedures execute simultaneously in a multi threaded manner.

    Any inputs in this regard are welcome

    Regards,
    Manpreet
    Sorry for bumping an old post, but I am looking to do the same thing, pseudocode below for those that want to know why.

    ------------------------------------------------
    CREATE PROC COMPANY_ISP
    @company_id INT
    AS

    INSERT COMPANY(COMPANY_ID)
    SELECT @company_id

    EXEC COMPANY_REBUILD_INDEX @company_id

    RETURN
    ------------------------------------------------

    The proc is actaully a little longer then the above but you get the idea, COMPANY_REBUILD_INDEX takes about 10 seconds to run and waiting for it to process is not critical to the web page calling this proc, but rather a hindrance since the application needs to wait for this process to finish.

    I suppose the application could call this proc separately behind the scenes somehow, I can talk with the .Net developer to see what he thinks, but thought it would be nice to have a solution that would work in SQL. xp_cmdshell? I thought that was only for DOS commands, does not seem like it is the solution I am looking for.

    Thanks,
    -John

  9. #9
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Applicaton design issues should be handled by the application designers.

    If I want an application to run two stored procedures simultaneously, then I expect the application to thread the executions. If the applicaton wants serial execution, then serialize them.

    You should consider not only the near term results, but also the long term maintainability. Cute solutions usually compromise the latter for the former.

    For your solution, it seems you are expecting an inherently single threaded application (browser) to handle asynchronous communications.

    The reindex should probably be done via a job that detects the request (writes a flag somewhere), and then you can write a control that polls a progress table for completion. This way, a user can refresh their browser w/o losing (needing to maintain) the state of the ReIndex.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    first thing that bothers me here is the fact that you are reindexing after every insert. why not just reindex the table overnight in a job.

    I wonder what effect of (and I would never try this exepcially with a 10 second execution) is of putting your reindex into an insert trigger (which I use sparingly) on the table. Would the ASP page go about it's merry business before the trigger completed or would you still get held up on your page waiting on the trigger to execute.

    By the way if your trying to insert and reindex at the same time on the same table I do imagine there would be some blocking even if you used multi-threading as implimented in JAVA.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Nov 2003
    Posts
    29
    Quote Originally Posted by Thrasymachus
    first thing that bothers me here is the fact that you are reindexing after every insert. why not just reindex the table overnight in a job.

    I wonder what effect of (and I would never try this exepcially with a 10 second execution) is of putting your reindex into an insert trigger (which I use sparingly) on the table. Would the ASP page go about it's merry business before the trigger completed or would you still get held up on your page waiting on the trigger to execute.

    By the way if your trying to insert and reindex at the same time on the same table I do imagine there would be some blocking even if you used multi-threading as implimented in JAVA.
    I was curious myself about the trigger, if I created a trigger would the calling proc wait for the trigger to finish before returning to the application?

    The reason I need to incrementally rebuild the index (I currently have an overnight job btw) is that when a user adds a new company they need a way to be able to search for it later, our search page does a phonetic search for companies (in case they misspelled it), if the job only runs nightly then the user would have to wait until the next day to see the company in the search results. This is an intranet application for about 50 users and adding companies is not going to be a very frequent process so I am not concerned with the strain on the server.

    For anyone interested in implementing a phonetic search here is the pseudocode:

    1. Split the company name into words
    2. Translate each word into its phonetic representation (soundex is one option albeit bad - I implemented a double metaphone translation)
    3. Remove duplicate phonetic words per company
    4. Aggregate the occurrences of each phonetic
    5. Give a score based on (in)frequency of each phonetic to aid in improved search results.
    6. Increase the score for exact word matches
    7. Increase the score for exact phrase matches
    8. Return the results by score descending

    There are about 40k unique phonetic words that are created as a result of this (out of a pool of close to a million companies).

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by bivonic
    I was curious myself about the trigger, if I created a trigger would the calling proc wait for the trigger to finish before returning to the application?
    Try it and let me know.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Maybe I'm missing something really fundamental, but what on earth are you trying to accomplish? Reindexing is a performance issue, it can improve the internal structure of an index after massive inserts or deletes. Reindexing had better not have any effect at all on what rows are visible in the table!

    Did I miss a meeting, or is this entire discussion moot?

    -PatP

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Pat,

    from what it sounds like his sp (COMPANY_REBUILD_INDEX) is a misnomer. It sounds like he is really doing all of his processing for his phonetic thingy majiggy and not a DBCC DBREINDEX. (which sounds like a lot of trouble to go through to tame some bad data entry).
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #15
    Join Date
    Nov 2003
    Posts
    29
    It's a nessary evil, we have over 100k council members that enter their own data, the search is used to help normalize the company name that they enter into their job history profile (each member can enter multiple job profiles based on their work history). The search had to be as flexible as possible and had to search on a per word basis and handle spelling mistakes, I think in the next version they want us to use a thesaurus to be able to match firm to company but in my mind THAT is going way overboard.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •