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,
ManpreetOriginally 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?|||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?|||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)|||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 :)|||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?|||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

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)|||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

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|||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.|||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.|||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).|||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.|||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|||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).|||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.|||did you try the trigger thing. i assumed you were't really doing a reindex as in dbcc. how did it go?