| Author |
Topic |
|
lebedev
Posting Yak Master
126 Posts |
 Posted - 2002-06-09 : 22:22:14
|
| Here is another question.Can multiple triggers of the same type declared on the same table be executed in parallel?thanks,Alec |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-09 : 23:01:57
|
| No. In SQL 2000 you can set a trigger to run first, or run last, but they all run sequentially. There's really not much benefit to having multiple INSERT triggers, because other than first and last you cannot otherwise affect the order of execution (2 or more middle steps could run in any order) |
 |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2002-06-11 : 11:48:41
|
| So, is there any kind of parallelism I can exploit when executing stored procedures and/or triggers. I don't want to use term multithreading, but is it possible to invoke and execute multiple procedures/triggers at the same time?thanks,Alec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-11 : 11:57:10
|
| SQL Server is naturally a multi-threaded application, so it will use multiple threads to execute a process. I think you might mean multi-processing, which it can also do, but not in this particular scenario.The only time a trigger or procedure can be executed simultaneously (I think) is if it is called by separate sessions/connections. At some point though, there is a process queue where operations get lined up and processed one by one (disk operations, for example)What exactly are you trying to do? Meaning, what is the process you need to accomplish, and why does it NEED to be done in parallel? Can you post the code you have now?Edited by - robvolk on 06/11/2002 11:58:07 |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2002-06-11 : 12:54:17
|
| I haven't written the code for the whole routine, but here is what I am trying to do. I have an insertion trigger, and I have to modify rows of table A joining with inserted tuples, I also have to modify rows of table B joining with inserted tuples, etc. All these modifications could be done in parallel. Any ideas?thanks,Alec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-11 : 13:10:25
|
| How many rows are you talking about? Are there 2 tables involved or 3, or more? Unless you're talking about updating millions of rows each time the trigger fires, there won't be much to gain with parallel execution in this case.Secondly, if the tables are all stored on the same disk, guess what? Parallel execution will get queued up at the disk-write part of the process, not to mention the transaction log activity, so there's no improvement at all really.I think it's better to look at how much work is being done and how often it needs to be done and determine if it's really necessary to do it that way. In your specific case, you can improve performance by indexing the columns used in the join clause for the update statements. Other than indexing and other general performance improvement techniques there's not much else you can do. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-11 : 13:44:37
|
| I understand what you're driving at ... it's not possible in SQL Server (or any RDBMS I am aware of), but it's not necessary either.SQL is a declarative vice procedural language. There are many rather verbose definitions of those two terms, but in sum 'procedural' means you tell the machine how to process data to return a specific result, whereas 'declarative' means you tell the machine what the specific result should be and it determines how to get there.Ultimately that means SQL Server decides which statements to execute in which order. You don't have control over it. In many ways that's good - when you state that updates to table A and table B can occur in parallel, that's an assertion made without the benefit of knowing how SQL Server processes those updates. If you had a materialized view referencing tables A and B, for example, it may not necessarily be true that they can be updated in parallel.Expanding on Rob's comments vis-a-vis the disk subsystem, the I/O issued by SQL Server will be buffered and re-ordered in a SCSI implementation. The degree to which I/O is executed in parallel is not determined by SQL Server but rather by the RAID controller.setBasedIsTheTruepath<O> |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2002-06-11 : 14:24:44
|
| RobVolk, the triggers I am implementing will be used in a data warehouse environment, so large sizes of tables are typical. Besides, in this kind of environments data are distributed over multpile remote information sources, which provides multiple hard drives and more opportunity for parallelism. Any ideas how to implement those triggers efficiently?Thanks.Alec |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2002-06-11 : 14:25:30
|
| setbasedisthetruepath, I realize that SQL is a declarative language, but I am trying to implement my triggers in T-SQL. So, I thought I could better manage execution paths and maybe spawn separate processes to handle independent tasks. Also read my previous response.Thanks.Alec |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-11 : 15:04:17
|
| No. The best you can do is rely on the parallel execution SQL Server will perform using a multiple-CPU machine, if you have multiple CPUs. You cannot control the degree of parallel execution other than to set the maxium number of threads used by the server (parallel query execution won't affect update statements in any case) See Books Online under "threads" or "max threads" and "parallel execution" for more info.Unless you set up the tables on separate filegroups on separate physical drives, then you'll still have a bottleneck for disk writes.If the processing takes place over several servers, you might be able to offload the rows from the inserted table into another table (a worktable) You can then create SQL jobs that populate the newly inserted rows into another table, and fire the jobs from the trigger. Be very careful with this because if any of these processes gets blocked the whole trigger could be held up.The problem in this approach is that the process would then leave the confines of the trigger, and you may not be able to rollback if there's a problem. Transaction isolation levels will also become problematic; so will concurrent insert operations from multiple connections.What about offloading inserted rows into the worktable inside the trigger, and that's all? Then a scheduled jobs runs at periodic intervals to add those rows to the other tables (then flushes them from the worktable) You can have this run every minute if you want. Unless instantaneous population is essential, this will be much more efficient than putting it all into the trigger.And last but far from least, have you tested anything yet? Is there anything to indicate the performance WON'T be acceptable? Don't assume a straight trigger will perform badly, unless you've got some tests done already and it's crawling. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-12 : 10:58:39
|
| Have you explored a DTS solution?setBasedIsTheTruepath<O> |
|
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2002-06-12 : 23:42:35
|
| I haven't tried DTS solution. The problem is that triggers need to fire on each insertion. Considering the high frequency rate of insertions in the DW environment running DTS jobs every minute will not be sufficient.I decided to follow the suggestion of Rob Volk and implement the triggers first and see whether performance overhead of updating tables sequentially is significant.thanks,Alec |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-06-12 : 23:56:35
|
| What storage option are you using for the DW?Have you looked at ROLAP? My guess is that you are using either HOLAP or MOLAP...ROLAP will provide better "real-time" support but your OLTP DB will suffer....DavidM"SQL-3 is an abomination.." |
|
|
|
|
Parallel trigger execution