Does ADO.net return results after the specified stored procedure call only or does it wait until all triggers are executed?

I am trying to record each words used in a nvarchar(max) field in my database and each word will become a data row and it takes a lot of time when I do it in one stored procedure. SO I have created another stored procedure which adds al开发者_如何学Gol the words as new data rows in another table but it takes very long and I dont want the user to wait for this operation. My solution was to run the time consuming insert into query in a separate process(system.thread) but now I am thinking of triggers. If I trigger the insertion of all words in a trigger, will the user still wait for all the insert queries, including the triggers? or will the sql run the stored procedure, return the results to the user and then the triggers will run? assuming triggers are after triggers.

---

That's right - stored procedure waits until the trigger is complete (to support transaction commit/rollback). If you need asynchronous behavior, you can get rid of trigger and use SQL server service broker to handle it in a asynchronous way. Check this thread for more information.

---

Triggers will run sequentially along with the stored procedure and the user will have to wait for the m to complete.

You either want to run the stored procedure in a separate thread as you indicated or have a separate background process run on a schedule to pull out updated records and then apply the updates to the related tables on a delay. Batching the changes like this would improve overall performance, but it means a longer delay in when the data is fully updated.

Another thing to look at is if the words table is fully normalized if you're taking advantage of caching appropriately. Perhaps you can simply speed up these inserts, which solves the problem without the multithreaded complications or delays.