Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
swan
Starting Member
11 Posts |
 Posted - 2002-12-03 : 21:15:48
|
| I've heard that with .NET CLR it is possible to write stored procedures for SQL Server in several programming languages including C# and VB. I am wondering whether this could be a solution for executing triggers fired on the same action on the same table concurrently.In other words, if a trigger is fired, I can make it call a C# procedure, which spwans multiples threads each of which executes, for example, a T-SQL procedure. This would model a concurrent execution of multiple procedures from a single trigger.Any ideas, feedback?Thank you. |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-12-03 : 21:44:07
|
| HeiThat isn't currently the case, it will be possible to some extent in the next release of SQL Server.There is some reading here (articles + comments) http://www.sqlteam.com/SearchResults.asp?SearchTerms=yukonEdit : I just re-read what you wanted to do...and my thought is that there would be FAR better ways of doing what you want. Perhaps write a service that checks for changes in a table and fires off it's own threads.DamianEdited by - Merkin on 12/03/2002 21:56:56 |
 |
|
|
swan
Starting Member
11 Posts |
Posted - 2002-12-06 : 10:42:24
|
| Hi,I've read the last two articles about Yukon from the link you mentioned. It seems like it will be possible to spawn multiple threads inside triggers. I will be looking forward to getting Yukon as soon as it's released.Writing a service, as you suggested, may not be approprate in some situations. If the service is run periodically, then no assumptions can be made about data between sequential service runs. If the service is run from inside the trigger, then it seems to be no different from the approach I was suggesting.Am I missing something?Thanks. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-06 : 11:10:39
|
| It depends on what you need the trigger to do. Triggers are mostly intended to work on the data in the database, and those operations are already running in a multi-threaded process space (SQL Server) The server can utilize multiple threads automatically, if it is most effective, to process the trigger. Explicitly spawning multiple threaded processes to work only on data can only interfere with that process and will probably slow things down.If you're doing something OTHER than manipulating data, then spawning multiple threads may help, but unless you write some pretty elaborate code, it will all run in SQL Server's process space anyway, and will compete with its thread pool.What exactly are you intending to do with the trigger? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-08 : 07:30:57
|
| I sense a disturbance in the force.Danger Will Robinson, Danger.Usually what you are suggesting should be avoided due to extending the transaction time and locking triggers should be lept as small as possible.I suspect you could design your system so that this isn't necessary.You can already generate multple threads by using the scheduler to have a few spids waiting to do things.If you want to act on data from something spawned by the trigger you will probably have to bind the connections too.All things which will tend to destabilise the server.It sounds a lot like a solution looking for a problem.Saying all that you may have a situation where it's useful but I would avoid controlling it from within a trigger if possible.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-12-08 : 12:23:47
|
| As a note, it is indeed possible to currently write an extended procedure in any .NET language by importing the ODS functions. I don't personally recomend it, but it's doable.This question probably should be in the PASS topic but.....Is a new API being released that sits on top of (or replaces) ODS to do the manipulations needed by an extended procedure, or will it more or less mimic what there is now?----------------------"O Theos mou! Echo ten labrida en te mou kephale!"Edited by - Lavos on 12/08/2002 12:24:17 |
|
|
|
swan
Starting Member
11 Posts |
Posted - 2002-12-17 : 15:54:19
|
| The idea I am working is materialized view maintenance.If there are several materialized view on the data warehouse server, they need to be maintained with respect to updates on the information cliends. So, I need to build a bunch of triggers on the clients' base relations which would propagate update to the server.AFAIK, SQL Server executes triggers sequentially, so I thought it would be nice to run them in parallel. So, the idea was to build one trigger, which would call a multi-threaded program, each thread of which will execute a trigger/procedure.So, I was curious what you guys thought about it and whether it's going to improve performance. Remember that each trigger has to connect to the DW server, which could be pretty slow. |
|
|
|
|
|
|
|
|
SQL Server and .NET