Devart Forums

I'm useing SDAC in a small program that runs updates on the database and I've encountered a problem with updating large triggers - it seems that due to some reason most of the time it takes a lot of time to run a single create/alter trigger statement and it seems to run slower on larger triggers... a 50K trigger can run for 10 min., but it is also strange that it does take different time on different servers and DB.

To summarize: a single create/alter trigger statement with a large trigger body ran from the query analyer can take like 5s. and with SDAC it can take 10-15 min.

We couldn't reproduce the problem. Please send us complete small sample to demonstrate it and include
script to create server objects.

hmm that will be hard to do... I have here a very large triggers, but they depend on lots of objects (referential integrity triggers) and making a small sample won't be very easy

basicly it seems that there is a slow down on executing large scripts... I'll do some more experimenting tomorrow and try to make some sample, may be it will work with a large insert script like 200-500K of inserts as I had this problem once, but most of the time I just put go between every few lines and the script goes as fast as a rocket (the program does execute the statements between two 'go' lines as a separate script)

Please supply us exact version of SDAC.

we were useing 2.45, but recently updated to sdac 3, I'll go and download the latest build and try again, but the problem seem to start from 2.45 at some point, because I'm pretty sure that in the beggining the scripts ran normally and this occured after some update of the componets, but since we didn't have to often update triggers I don't know when.
Also, we are useing Delphi 7.

Try to remove "go" statements from script.

there are no go statement in the script, as I tried to explain the program would parse it and run the statements between two 'go' statemetns.

I've tried to make some simple script to simulate it, but unfortunately by running my random generated inserts on a simple table didn't show any slowdown and generating a suitable script for testing won't be very easy as I use refferential integrity triggers which would require a rather complex script to create.

Also, I've just been able to do some more tracing with SQL profiler and it actually seems like the delay come from SDAC itself (or OLE?), because when I run something like CREATE TRIGGER (with a pretty large and complex refferential integrity trigger in it) the program pauses for a few minutes, but Profiler doesn't actually show any activity and after that the statment would appear in profiler and execute for like 5 sec. So I get an impression that SDAC does some kind of parsing or other processing which takes a lot of time whenever you try to execute complex SQL statements.

SDAC doesn't perform any long-lasting procedures of processing text of the query. Moreover, there are no procedures of processing depended on triggers. The only that seems to be possible is processing various messages (PRINT etc).

Try nevertheless to create an example.