You can keep a counter and only send updates every 100 commands for example (just build a string with all the commands, reset the counter and the string after you have sent the update). I have done this and it works OK, but you may need to experiment with the counter to find the best performance.

I have turned on profiler (SQL Server) and examined the commands that are generated when doing updates with remote views. Most of this code is pretty tight, so it may be more trouble than it is worth to convert to SPT.

I haven't studied your code in detail, but if you want the optimal peformance I would think that you should be doing these updates with a stored procedure (especially when doing batch updates / inserts).



>One thing that I have to keep in mind is to be able to rollback should something go wrong.
>
>Is there any way to batch update with sql pass thru. Views had the batch count property.
>
>Jason
>
>>It looks like you have quite a few variables for the timing the different operations. You should probably continue with that and figure out what is taking so long. Like Mike mentioned, maybe you will discover the SQLEXEC() within the SCAN() is the bottleneck, etc.
>>
>>Another thought I had when looking at all the code is what is going on in prepare_for_sqlexec(). Function calls like this can be expensive when you are making a bunch of them (just for the call alone, not to mention any code that is being executed). If you are doing a strtran() or something like that then it might be more efficient to just call strtran() directly instead of prepare_for_sqlexec().
>>
>>Something else you may be able to do is instead of calling sqlexec(), call mysqlexec() or something similar and send all the commands to a text file and then go execute the commands in Oracle (I don't use Oracle, but do you have something similar to Query Analyzer?). Obviously you will not be able to call mysqlexec() for command that drive the scans, etc.
>>
>>>The places I am doing sqlexec() are where I was updating various views. The views I had setup to updating based on pk only and had a batch count. When I was done all the changes I had a routine that ran through all the tables that were being updated which started a sql transaction, did the table updates and then did sqlcommits.
>>>
>>>I thought commands sent through sqlexec would be quite efficient since they are bring sent directly to oracle.
>>>
>>>Could the large number of sqlexec commands being run be the problem here?
>>>