0 Replies Latest reply on Sep 30, 2005 5:32 AM by anthropophagos

    MySQL vs. Oracle9i CMP-CMR Performance discrepancy

    anthropophagos

      Hi,

      I recently changed from MySQL to Oracle9i for CMP data persistence. Everthing still works but there is a certain query which takes about 10times longer on Oracle9i than on MySQL.
      This happens while I am trying to access Entity Beans over a CMR relation. The following query is generated:

      SELECT userID, userPic FROM VMOBILEUSER WHERE (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?) OR (userID=?)

      Normally - even with Oracle9i - accessing data over CMP relations is also pretty fast, but in the above case 'userPic' is a BLOB field about the size of 4KB. Only if try to access this BLOB field, I have poor performace.

      I am already using eager loading read ahead, and this is this is ok in all cases I do not need to access 'userpic'. As soon as I access the 'userpic' BLOB field, everything gets very slow.
      MySQL needs only 2sec. whereas Oracle9i needs around 18sec for the same query!
      Any ideas what is going on?

      Thank you
      AP