Re: Slow queries over I-Star

Andy Kent (akent@cix.compulink.co.uk) wrote: : My client needs to transfer selected rows from tables on one machine to : another server. I am using SPL. I have tried two approaches and am amazed : at the differences in timings: : (1) insert into remote_table : select * from local_table : where <condition>; : (2) foreach select * from local_table where <condition> : ... : ... : insert into remote_table values (x,y,z....); : end foreach; : Approach (1) takes 35 seconds. Approach (2) takes 8 mins 40 seconds : without explicit transactions (ie. each insert is its own transaction) : and 5 mins 00 secs with the whole procedure in a single transaction. : The client favours Approach (2) because it allows the rest of the load to : proceed if one or two inserts encounter an error. (yes I know we'll have : to be careful to differentiate between kinds of error.) : The boxes are connected over X.25. I have no idea how much of the time : difference is made up by poor network tuning and how much is inherent in : the working of I-Star. : I'm using 5.00 and onsoctcp - would 6.00 and relay modules be any : quicker? Or is the real ball-and-chain simply X.25? : akent@cix.compulink.co.uk (Andy Kent) : ------------------------------------- Interesting... I have been examining cases like this on my current project and haven't yet observed such a time discrepancy. But I also haven't tried a remote insert like yours (yet). Do you have any idea if an insert cursor would help? That is, if you replace the "insert" with a "put" statement? Intuitively, I would expect it to work better, because the rows can be buffered and flushed en masse to the remote database. Just wondering, -Jeff