Performance difference between READ and WRITE RECORD to ODBC channel

[mok]

I have these two lines of code accessing records from SQL table via ODB channel. Channel opened in this case is the same. Why is the READ slower to respond than the WRITE RECORD. Using READ sometimes result in error 0 because of the length of time result comes back to the current program but WRITE RECORD has not yet resulted in error 0.

READ (IBIS_DB'ODB,KEY="!"+SQL$)

WRITE RECORD(IBIS_DB'ODB)SQL$

Thanks

[Devon Austen]

The read has to find the data at the key and then download the data to your system. The write just uploads the data to the DB which then puts it in the DB. The read you have to wait for the search while the write you don't have to wait for any search.

[mok]

So, is it correct to imply that if I want to see performance gain, I should change all my READ (IBIS_DB'ODB, KEY="!" to WRITE RECORD (IBIS_DB'ODB)

[Devon Austen]

Sorry, I think I misunderstood your question. I thought you were asking why a read from a database took longer then a write to a database.

What I think you are actually asking is why is it slower to do a READ directive on an database vs executing a SQL read directly via the WRITE RECORD directive?

If that is the question you are asking then the answer is when you do the WRITE RECORD you are using whatever SQL statement is in SQL$ and this SQL can be simple and direct making it fast. When you use the READ directive you are asking PxPlus to write the SQL for you and it has to make it's best guess at what is the fastest SQL to do that. How good a job it does depends on how much information it has about the tables fields and its keys. Also it depends on if the data given in the KEY= , if the data is only a partial match it may have to do more work.

You can turn on the '!Q' parameter to take a look at what SQL PxPlus is sending the DB. Comparing the READ to the WRITE RECORD will probably tell you why it is slower. To make the READ faster as mentioned above give PxPlus as much info about the table as possible using REC= and KEY= in the open, and make sure the KEY= is the correct length for the full key.