I have an update of a table (gg) from rows of another table (g).
There are about 800000 rows in gg and 140000 rows in g. Large file support is enabled.

The update hangs, and a query on gg shows no rows have been updated. It looks like Elevate is trying to perform the entire update inside one internally generated transaction. Is there a way to limit the size of such transactions so I can, for instance, update 10000 rows of A at a time? Any other ideas?

The query that hangs is:

     update hklogroomtask as gg
       set gg.completedstamp = (
          select g.completedstamp from hktblroomtask as g
             where (gg.taskid = g.id) and (gg.complete > 0)
   )
      where gg.completedstamp is null

An immediately preceding query, which completes in 42 seconds on my system, is:

     update hklogroomtask as gg
       set gg.createdstamp = (
          select g.createdstamp from hktblroomtask as g
             where (gg.taskid = g.id)
   )

Forget this post. I rewrote the hanging query. Now I have egg on my face.

Gregory T Swain wrote:

I have an update of a table (gg) from rows of another table (g).
There are about 800000 rows in gg and 140000 rows in g. Large file support is enabled.

The update hangs, and a query on gg shows no rows have been updated. It looks like Elevate is trying to perform the entire update inside one internally generated transaction. Is there a way to limit the size of such transactions so I can, for instance, update 10000 rows of A at a time? Any other ideas?

The query that hangs is:...

Gregory


Post the rewrite - it may help someone else.

Roy Lambert

Gregory,

<< The update hangs, and a query on gg shows no rows have been updated. It looks like Elevate is trying to perform the entire update inside one internally generated transaction. Is there a way to limit the size of such transactions so I can, for instance, update 10000 rows of A at a time? Any other ideas? >>

Just so that everyone understands how this works: ElevateDB *will* use an internal transaction with UPDATE/DELETE statements that aren't already part of a transaction, but it will also limit the number of rows that are updated (without being flushed) to 64K rows.  So, it will, at the very least, force a flush every 64K rows.

Tim Young
Elevate Software
www.elevatesoft.com