I am experiencing a really odd behaviour, using PDAC for P.SQL 2000i SP4 under Delphi 6 (I have not tried other versions of P.SQL or Delphi). As the behaviour is VERY random, I am not giving an example. I'll just describe the symptom.

Updating 3 columns in about 20000 records (out of 30000). The values to update originally came from a MySQL database, but experiments showed that it does not really matter where the come from. The Update looks like this:

UPDATE Table SET Col1=:Col1, Col2=:Col2, Col3=:Col3 WHERE Key=:Key

Well, sometimes this takes 4 minutes, sometimes 7, which can be explained by the different caching situation. But what is REALLY odd is the fact, that sometimes it may take 90+ minutes!! But that still does not puzzle me. What REALLY puzzles me, that with one and the same source, I get different timings whether I run the program in the IDE (with Borland debugging RTLs) or right from the EXE. Those timings are CONSISTANT. To make it worse, the behaviour also depends on the source code itself.

Originally it was faster in the IDE, later I managed to rewrite it to achieve the opposite. Though, it still remains the same once I freeze the source code. What teh hell is going on here? Why can there such great differences in timing with an UPDATE operation? Why does the envirnoment (debugging vs. production) have an impact. Why is the behaviour so random, even when the UPDATE statement remains the same, just the source of the Params[] changing? Btw, updating with a PvTable runs well under a minute.

I am suspecting that the UPDATE operation sometimes fails to make use of the index on the key column. But why? Why is it random with regard to different source code, but constant with regard to the same source? And why does it work in one environment and not the other? Why is sometimes the debugged version slow and sometimes the runtime?

I would mix PvTable with PvSqlQuery, if they would not run on a different connection, thus disbabling transactions! Not to mention that such an approach is not very clean.

Another thing. Why can't I prepare an UPDATE like the one above? PvSqlQuery.Prepare throws an exception, talking about "invalid precision"? Go figure!

Btw, the behaviour is identical when I run the app against a workgroup database or a client server database. Thus I strongly consider this to be a PDAC issue. Thanks to Pervasive for NOT supplying sources of PDAC!

Christian