Re: refreshRow is slow

John T. Dow wrote:
> I am having no luck getting responses to a question I posted earlier about optimistic locking with multiple rows.
>
> I'll ask a more targeted question. How do I efficiently refresh a single row in a resultset?
>
> Suppose I allow users to view multiple records with TYPE_SCROLL_INSENSITIVE, CONCUR_READ_ONLY.
>
> The user might select one row or 1,000 rows and scroll back and forth at will.
>
> The user might choose to change one of the rows. I do this with a separate FOR UPDATE query.
>
> The update query will fetch the current row, which might be different from the row in the original resultset. My programming compares the two rows and can report to the user the differences. After the row has been updated, I need to refresh that row in the result set so that if the user continues to scroll back and forth, it will see the updated information.
>
> I am using refreshRow. I do setFetchSize(1) first. This works, but sometimes it is very slow. It can take many seconds over a remote connection. Can I fix that? What's going on?

What's your original query?
What's the query that the driver ends up synthesizing when you call
refreshRow()?
Have you run that query separately under EXPLAIN ANALYZE etc?

Do you have knowledge of the structure of the original query (i.e. are
you building it yourself, or is it user-supplied?) If you do, have you
considered running suitable update/refresh query yourself directly? (The
driver has limited knowledge of your database structure and may not be
able to produce 'good' queries for update/refresh via the resultset
interface)

> Note: I don't want to use TYPE_SCROLL_SENSITIVE because it would update the row in the result set silently and I wouldn't be able to tell the user that somebody had changed anything. (Besides, I understand that TYPE_SCROLL_SENSITIVE doesn't work reliably.)

From memory the driver doesn't implement TYPE_SCROLL_SENSITIVE at all
(that type of resultset is quite strange..)

-O