Re: refreshRow is slow

On Fri, 15 Jan 2010 10:59:00 +1300, Oliver Jowett wrote:

>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)

The original query is user supplied. It can range from a query using a primary key that selects a single row to a query selecting half a dozen rows using their primary keys to a complicated query testing ranges on several columns and resulting in a thousand rows. If I knew the query would only return a small number of rows, I'd just redo the query. (That's what I have to do with Derby.) But you'd think that if there are a thousand rows, refreshing one row would be better than rereading all of them. That's the theory anyway. That's why I'm hoping refreshrow would be one solution that fits all user supplied queries.

Each row in a postgres database has a unique identifier. I would think that refreshrow could use that to target the specific row to be refreshed.

I realize that I only want to refresh one row, and that refreshrow actually uses the fetch size and may get more than one if required. Refreshing many rows using their unique identifiers might not be desirable.

My program of course knows what the refreshed row should look like. Too bad there's not a way to switch out a row in the result set.

>
>> 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..)

I kind of got that impression regarding postgres. For another database I was reading about (Oracle?), it was said to not be reliable as to whether it would pick up all changes.

>
>-O
>