Two VFP9 methods of updating an Oracle table, one is faster than the other?

[torturedmind]

Good morning to all.

I have this "regular" method of updating an Oracle table. Let's just call it Method 1:

1. SET MULTI LOCKS ON
2. Connect to DB using SQLCONNECT()
3. Open the table using SQLEXEC()
4. Set buffering to table buffering optimistic using CURSORSETPROP()
5. Update several thousand records.
6. Commit changes using TABLEUPDATE()

Method 2, on the other hand, uses a different way of updating.

1. Connect to DB using SQLCONNECT()
2. Open and Oracle table (let's call it TBL1) using SQLEXEC() into a resulting READ-WRITE cursor (let's call the cursor CUR1)
3. Edit values of several thousand records in CUR1
4. Update the back-end table TBL1 using SCAN...ENDSCAN like so:

SCAN && the cursor
    SQLEXEC(db_conn, "UPDATE tbl1 SET f1 = " + ;
        fld1 + ;
        ", f2 = " + ;
        <blah><blah> + ;
        " FOR tbl1.ndx_fld = " + )
    SQLCOMMIT(db_conn)
ENDSCAN

I use Method 1. I know someone who uses Method 2. Why is Method 1 way slower than Method 2? I'm almost tempted to use Method 2 but I know it just causes inefficiency and high "hard-parses" on the DB side.

TIA

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Olaf Doschke]

Behind the scenes a TABLEUPDATE does nothing else as sending UPDATE statements to the remote database. You influence what SQL is generated eg by the CURSORSETPROP of the WhereType. This already hints on that background behaviour of TABLEUPDATE.

So TABLEUPDATE is a convenience function, mainly. Not only convenience, though, as a batch of commands put togther and send in one DB request has advantages, if that is done. Also TABLEUPDATE can create queries only having SET clauses for really changed fields, not always the full field list.

You could measure this, but do so in multiple passes and different orders, as you always have influences on many outside factors, eg the network load due to any other apps and clients and of course the second used method always profits from caching, also when writing. So a better speed can be a false positive.

What might pay is, if you can use SQLPrepare() to prepare one Update statement with parameters and repeat it after only setting the next rows parameter values. When using cursoralias.field as parameter name that can just mean SKIP to the next row or move to it with GETNEXTMODIFED. Tableupdate might do nothing else internally, when it doesn't create a batch of commands.

If there would be a profit of that, you should use parameters, simply also for safety of it. For example instead of [tt]...f1 = " + fld1 + ", f2 =...[/tt] as you indicated. This would need Fld1 to be a string and then would need [tt]...f1 = '" + fld1 + "', f2 =...[/tt] anyway, just as sidenote. It doesn't pay to put together the statements, it is too error prone and Tableupdate cpuld send a batch of commands, even though they are also all single UPDATEs, INSERTs and DELETEs, they could be in one request to the database. What really arrives remote could perhaps be logged there. In MSSQL for example via SQL Profiler. Similar things should exist in Oracle.

That said I don't have experience with Oracle sepcialities and how it influences the whole situation. I also don't know, whether Tableupdate makes use of prepared statements in some way. If so, it wouldn't send out a batch of commands, though. Different databases also might handle similar adhoc queries arriving with some optimization detecting the smae thing is requestes over and over again just with different values. The one big downside of doing a SCAN loop yourself is you a) don't just process modified rows and you b) make a request for every single record. A remote database would have it easier to summarize a batch of similar looking queries arriving as one batch script request. I'd rather use TABLEUPDATE, unless there is a well known reason and better SQL you can create manually or you even know the SQL Tableupdate creates has flaws.

Also for example updating thousands of rows with same value like "UPDATE sometable SET year = 2016 WHERE year = 2015" would be very much faster than loading the thousands of 2015 records, changing their value to 2016 and then updating them row by row. You even don't query the data to VFP, you let the remote server do the update without any data travelling LAN.

And last not least. All that only will play a role with more than just a few changes a user does in an application. It doesn't pay to think about optimizing 10 row updates after each 5 minutes. Life is too short. But you make me wonder, why you load thousand of rows for VFP side processing. Update the data where it is is faster than both approaches with TABLEUPDATE or SQLEXEC.

Bye, Olaf.

 

[torturedmind]

Dear Sir Olaf,

Thanks for the info. The developers who use method 2 are part of the team I'm in and always have the argument that their way of doing things is faster, which I'm sill yet to prove. Problem is, their method (method 2) produces high "hard-parsing" on the DB side which in return locks the DB at some point. I tried to reason out with them that they were doing it wrong and should use "bind variables" instead of passing the SQL statements as literals. Unfortunately, it's a huge task for them since they will be reviewing and modifying thousands of lines of codes and thus, avoid of doing so.

Anyway, it does take some time to update a single record in a table of several hundred thousand records. Issuing a direct UPDATE thru SQLEXEC() won't do since there are still some calculations and checking needed before the actual update happens.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Olaf Doschke]

>their way of doing things is faster, which I'm sill yet to prove
I strongly assume their method 2 is slower, though TABLEUPDATE() also executes SQL statements.
Method 2 is certainly processing more rows, since a good loop wouldn't SCAN..ENDSCAN all records, but only rows with buffered changes via GETNEXTMODIFIED.

And once more: Alone avoiding the read of 1000s rows for VFP side processing and then updating is slower than doing such automatic processing server side.

Bye, Oaf.

 

[Olaf Doschke]

Another question: Assume all rows are changed anyway in these cases. And assume they found out their self generated statements are really faster than those of TABLEUPDATE: Did they investigate about the reason for this?

The WHERETYPE setting alone can mean much longer statements than self generated ones, if you for example choose the wrong type of DB_KEYANDUPDATABLE, which always finds the row to update via a very long WHERE clause comparing all columns to their old read values. It's a way to ensure no other concurrent change was made, but it also makes a very long statement. It can even fail on becoming too long.

The ideal WHERETYPE is DB_KEY, which creates an [tt]UPDATE SomTable SET... WHERE ID = loadedID[/tt], the shortest possible WHERE clause identifying a record by it's primary key field only.

Bye, Olaf.

 

[torturedmind]

Hello Olaf,

My apologies for the late response. Been busy with other projects and stuffs.

Anyways, the answer to your last question is I think not. They say that if it works and as long as it's faster, why bother. I tested their method by updating only record in a several-thousand-record transaction table and indeed, it is much faster by up to a margin of seconds. All tables have a primary key set to an auto incrementing field called UNIQUE_ID and is populated by a trigger on the DB side.

Here's what I did for the buffered table (Method 1):
1. Connected to the DB using SQLCONNECT().
2. SET MULTILOCKS to ON
3. Opened the table using SQLEXEC(). Since am sampling a transaction table, only those records with RSTATUS = 'Run' are fetched.
4. Issued CURSORSETPROP("KeyFieldList", "unique_id", "mytran")
5. Set the Buffering to 5 (Optimistic table buffering).
6. Edited ONE record's RSTATUS to "End" and set DT_END field to DATETIME().
7. Issued TABLEUPDATE() and it took almost half a minute to finish before I can issue SQLCOMMIT().

For Method 2 (the other dev's method):
1. Connected to the DB using SQLCONNECT().
2. SET MULTILOCKS to ON
3. Opened the table using SQLEXEC(). Again, only those records with RSTATUS = 'Run' are fetched.
4. Issued

SQLEXEC(db_conn, ;
[indent]"UPDATE myschema.mytran " + ;[/indent]
[indent][indent]"SET mytran.rstatus = 'End', " + ;[/indent][/indent]
[indent][indent]"mytran.dt_end = TO_DATE('" + TRANSFORM(DATETIME()) + "', 'MM/DD/YYYY HH24:MI:SS') " + ;[/indent][/indent]
[indent][indent]"WHERE mytran.unique_id = " + TRANSFORM(mytran.unique_id))[/indent][/indent]

So their method is indeed faster because it pinpoints what record to update by specifying the UNIQUE_ID. But when done in batch, this creates a very high overhead resulting in poor DB performance. Also, Oracle's SGA (System Global Area where every unique SQLs are stored) gets easily full because of the "uniqueness" of each SQL. Please read here if you may.

But performance is also an issue in method 1. It's worse than method 2. I was just wondering why the buffered method is way slower. I also tried updating a cursor created thru the remote query designer and it's much slower to update anything. Maybe this is just one of the things that proves the saying "Not all people who go to church are good."

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Olaf Doschke]

There are several more things to set than CURSORSETPROP("KeyFieldList", "unique_id", "mytran"):

SET MULTILOCKS ON && Must turn on for table buffering.
CURSORSETPROP('Buffering' , 5 , cAlias)
CURSORSETPROP('SendUpdates' , .T. , cAlias)
CURSORSETPROP('Tables' , cTable , cAlias)
CURSORSETPROP('KeyFieldlist' , cKeyfieldlist , cAlias)
CURSORSETPROP('UpdatableFieldList', cUpdatableFieldList, cAlias)
CURSORSETPROP('UpdateNameList', cUpdateNameList, cAlias)
CURSORSETPROP('WhereType',1,cAlias) && WHERE ID=x

>Edited ONE record's RSTATUS to "End" and set DT_END field to DATETIME().

Are you sure, that you never touch any other record? You can easily make a major mistake on the SPT cursor, setting states of all records.
To ensure that only one record has a buffered change to commit, look at this:

lnModifiedRec = GETNEXTMODIFIED(0,cAlias)
DO WHILE lnModifiedRec<>0
   ? lnModifiedRec
   lnModifiedRec = GETNEXTMODIFIED(m.lnModifiedRec)
ENDDO

Does this really just show you one record number?
Please test this after your single record update and also right before TABLEUPDATE().

If only one record number shows, TABLEUPDATE will only send one UPDATE comparable to what method 2 does. If that's the case it would be the job of a profiling tool in the Oracle database to determine what happens. I can not imagine VFP sends updates for all records, even when no buffered change exists.

Because you mention SQLCOMMIT(): Do you really start a transaction manually? Do you set SQLSETPROP(db_conn, "Transactions", 2) after connecting? When and how do you start the manual transaction? In MSSQL you'll then have to send "BEGIN TRANSACTION" via SQLEXEC, I don't know if that's the same in PL/SQL. You shouldn't start the transaction too early, just right before the TABLEUPDATE, never ever do this right after querying data. You better not do that at all, rather set this to automatic and leave it to VFP, manually managing transactions is a job only needed, when updates of several tables are done, several TABLEUPDATE()s of several aliases.

In regard of the pool of similar queries Oracle uses to reexecute the execution plan previously determined, you would need to use SQLPREPARE for that and the collegues also don't make use of that, all their manually put together UPDATE statements all also differ in the ID value. Both method1 and 2 are weak in that respect.

Bye, Olaf.

 

[torturedmind]

Hello Sir,

There are several more things to set than CURSORSETPROP("KeyFieldList", "unique_id", "mytran"):

Yes, I also issued those in my code but not in my sample above. I thought it's the "standard" for back-end DB setting. But I don't issue them if the requirement is to just view the data.

Are you sure, that you never touch any other record?

Yes, I am. I issued REPLACE to where the current record pointer was, then exit my sample program. And yes, the SQLSETPROP(db_conn, "Transactions", 2) was issued right after connecting. I now know it's a bad idea putting it there. I'll make the necessary revision ASAP.

You don't know how grateful I am for giving your (and other VFP gurus') precious time in giving good coding advice, not only to me but to others as well, explaining things and helping me and the others understand more about VFP. Thank you very much. I'll now put all your advice to good use and post back the result.


kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[Olaf Doschke]

I trust you do a REPLACE with only current record, there still are other things, which could touch records state. Simply check how many rows are in the buffered state via the code I gave, even if you trust your REPLACE, the change of the states can happen form anywhere else.

The long runtime might be explained by a long running transaction, but you don't start a transaction by setting the connection property to manual. You only say you don't want automatic transactions and start a transaction yourself - will start a transaction yourself. As said in MSSQL you'd send a "BEGIN TRANSACTION" via SQLEXEC, most likely that's also PL/SQL syntax. If you don't do, then most probably oracle will start one automatically anyway.

If you want manual transactions it is best practice to do as you do and set this right after the db_conn handle is established, you could even set this for handle "0" beforehand. But you don't start a manual transaction that way, you have a misunderstanding here.

Bye, Olaf.