Re: IOT, memory and transaction time

.
post
top
not
do
Please

On 18.04.2007 10:51, deja_at_2bytes.co.uk wrote:
> ok, I've discovered that it is the Update of the Dummy table that is
> the problem. It doesn't cause a problem in SQL Server but seems to be
> a massive problem in Oracle. Changing the Update statement to "Update
> DUMMY Set Col1 = 1" without the where clause eliminates the problem
> but obviously does not fulfill the requirement. Why does the where
> clause "...where @lastRead = (select max(versionNo) from Table1 where
> id = @id and versionNo <= @currentTransNum)" cause such a major
> problem with a seemingly endlessly increasing transaction time?
>
> What is a better way of structuring this SQL for Oracle?
>
> The client code already assumes a rowcount based on an update
> statement so I cannot change to a read statement, it still needs to be
> an update (so I am told, though I have not seen the client app code
> myself)

Frankly, I am more and more confused. You cannot change the application and you do not have access to the code but you can change SQL? Also, your changed logic of using those tables does not interfere with the supposedly fixed app logic?

Maybe it's easier to start over with a list of requirements and fixed interfaces and a description of what you want to do. At least I do not see your problem clear enough that I would dare to come up with suggestions.

Regards

        robert Received on Wed Apr 18 2007 - 04:36:11 CDT