I’ve been drumming on Oracle for a while trying to merge a PHP-MySQL application’s data with data available from an Oracle database server running on VMS.

I’ve learned about views, stored procedures, query efficiency and adodb as a result of my pains. I’ve been trying to use $db->Prepare() to do a query for a set of 351 local IDs as an alternative to using an IN() with 351 IDs as an argument.

So far I haven’t had much luck using Prepare() in a select, but I’ll keep working on it this weekend. Supposedly you should gain 20-40% performance over doing repetative queries because you are saving some overhead.

I had originally assumed that using the IN(), although somewhat costly, would be optimized on the database end and was a healthy alternative to doing 351 queries (because you save the overhead being wasted by doing multiple queries 351 times).

I do know this – that the view, which I don’t have access to, is terribly inefficient – each additional argument adds roughly .5 seconds to the overall query time using the IN() and if I do multiple queries I see a 1-second-per query situation. Seems fishy to me.

In other instances, PHP memory and database server cycles were saved when I used the IN() as an alternative to looping queries – which is something I almost always try to avoid. Both methods are killing my script, though, and the only difference is the view.

So – hopefully when I can get this thing figured out I will have learned a bit more about Prepare() and Oracle query efficiency. It could very well be that looping queries properly would be less of a hit than an IN().

IN() or Prepare()? More to come…