Re: triggers/stored procedures/SYSTEM command

Chao Y. Din wrote: } } Jack Parker wrote: } > } > } > A cow-orker tested out 'system' in a situation where he needed OLTP style } > transactions. He found that it consistently took about .5 seconds to invoke } > the system command each time. In his case that wasn't acceptable. } > } > Errr, don't ask me how he timed it.... :-) } > } } We have similar problem with SYSTEM command. } } We developed a stored procedure which issued 9 SYSTEM commands to create 9 subdirectories. Each of the } system command calls a single "mkdir" command. The performance of the stored procedure was so bad that it would } take a few days to handle 300,000 rows. Yet the computer is a Sparc 2000E with 12 CPUs and 1 GB memory. } } After we switched to a single SYSTEM command which calls a shell script, it was more than 10 times faster. } } Chao This is hardly surprising. Each system command launches it's own unix shell process in which the command requested is then invoked. The command invoked, if not a simple shell command, will also launch itself as a unix process. As most, if not all, unix systems take an appreciable amount of time to launch a process if you do several system commands in a SP the time spent launching these processess will soon add up to significant levels however powerfull the box is. Cheers - Jim -- ------------------------------------------------------------------------ Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ------------------------------------------------------------------------ My opinions are my own. They may vary with time but they remain mine!