Hi all,

I took some sample code I ran in 2.0.3 and ran it with the jars and libs from 2.1 beta 6. My code ran fine but took more than 3 times as long to execute the database query!! Mind you this the same exact code run under both versions.

Following is snippets of my code:

Iterator it = null;
Session session = null;
Transaction t = null;

SessionFactory sessions = new Configuration()
.addClass(DataSrcClmn.class)
.addClass(FldrClmn.class)
.buildSessionFactory();
session = sessions.openSession();
t = session.beginTransaction();
it = session.iterate(
"from DataSrcClmn as f where f.SrcNum = :SrcNum",
"2000", Hibernate.STRING);

while (it.hasNext() ) {
DataSrcClmn clm = (DataSrcClmn)it.next();
}

t.commit();
session.close();

Under 2.0.3, this executed in about 22 seconds to return 714 objects, while under 2.1 beta 6 this takes 73 seconds.

Is there something wrong with my code that it runs so slowly with the beta?

Thanks in advance,
Daniel

Below is my mapping file for DataSrcClmn. (Let me know if you need the one for FldrClmn as well):

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>

<!--
Created by Middlegen Hibernate plugin

http://boss.bekk.no/boss/middlegen/
http://hibernate.sourceforge.net/
-->

<class
name="test.hibernate.DataSrcClmn"
table="DATA_SRC_CLMN"
>
<id
name="clmnNum"
type="int"
column="CLMN_NUM"
>
<generator class="assigned" />
</id>
<property
name="dataSrcNum"
type="int"
column="DATA_SRC_NUM"
not-null="true"
unique="true"
length="5"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="clmnName"
type="java.lang.String"
column="CLMN_NAME"
not-null="true"
unique="true"
length="30"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="attrName"
type="java.lang.String"
column="ATTR_NAME"
not-null="true"
length="200"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="clmnDesc"
type="java.lang.String"
column="CLMN_DESC"
length="2000"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="dataTypeCd"
type="java.lang.String"
column="DATA_TYPE_CD"
not-null="true"
length="12"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="dataOfstCnt"
type="int"
column="DATA_OFST_CNT"
not-null="true"
length="5"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="dataLngthCnt"
type="short"
column="DATA_LNGTH_CNT"
not-null="true"
length="4"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="dataScaleCnt"
type="boolean"
column="DATA_SCALE_CNT"
not-null="true"
length="1"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="nullSw"
type="java.lang.String"
column="NULL_SW"
not-null="true"
length="1"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="cdTblName"
type="java.lang.String"
column="CD_TBL_NAME"
length="30"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="lstName"
type="java.lang.String"
column="LST_NAME"
length="30"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="ngtvNumSw"
type="java.lang.String"
column="NGTV_NUM_SW"
not-null="true"
length="1"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="dataFrmtTxt"
type="java.lang.String"
column="DATA_FRMT_TXT"
length="50"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="sasFldName"
type="java.lang.String"
column="SAS_FLD_NAME"
unique="true"
length="8"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="pcSasFldName"
type="java.lang.String"
column="PC_SAS_FLD_NAME"
length="30"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="sasLabelTxt"
type="java.lang.String"
column="SAS_LABEL_TXT"
length="200"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="creatTs"
type="java.sql.Timestamp"
column="CREAT_TS"
not-null="true"
length="7"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="creatUserId"
type="java.lang.String"
column="CREAT_USER_ID"
not-null="true"
length="20"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="updtTs"
type="java.sql.Timestamp"
column="UPDT_TS"
length="7"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="updtUserId"
type="java.lang.String"
column="UPDT_USER_ID"
length="20"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="sqncNum"
type="int"
column="SQNC_NUM"
not-null="true"
length="6"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="sasCmtTxt"
type="java.lang.String"
column="SAS_CMT_TXT"
length="40"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="slctTxt"
type="java.lang.String"
column="SLCT_TXT"
length="2000"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="tmpltOnlySw"
type="java.lang.String"
column="TMPLT_ONLY_SW"
length="1"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="newClmnSw"
type="java.lang.String"
column="NEW_CLMN_SW"
not-null="true"
length="1"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="fldrUpgrdSw"
type="java.lang.String"
column="FLDR_UPGRD_SW"
not-null="true"
length="1"
>
<meta attribute="use-in-tostring">true</meta>
</property>
<property
name="padKeySqncNum"
type="java.lang.Integer"
column="PAD_KEY_SQNC_NUM"
length="1"
>
<meta attribute="use-in-tostring">true</meta>
</property>

<!-- associations -->
<!-- bi-directional one-to-one association to FldrClmn -->
<one-to-one
name="FldrClmn"
class="test.hibernate.FldrClmn"
outer-join="auto"
/>

</class>
</hibernate-mapping>

Ummm how scientfic was your test?

Did you time just the database query, or is this time including the configuration/startup time? Have you checked the SQL log, to see that exactly the same SQL is executed?

I *have* been doing some profiling of 2.1 and I havn't got results like this, but its not impossible that you have come across some bug....

Thanks Gavin for your quick reply.

I snipped out the code that gets the currentTimeMillis() for my post but I have long start = System.currentTimeMillis() right between session = sessions.openSession() and t = session.beginTransaction() and ends right after the close().

As far as the SQL log I will check it tomorrow when I go to my other computer. (I am currently not typing this on the machine I implemented this test on). I'll post my result when I have it.

BTW some more info, I got similar results when doing a find() as well, it also took much longer when using the beta (let me know if you would like me to post the code for that). I am also using and Oracle 8 database with the OracleDialect.

Also, I did a second call to iterate() which got the cached elements from first iterate() call, and using 8.0.3 took about 5 seconds to get all elements but with beta 6 it took 14 seconds. This is interesting bec. I do not think the database is called at all at this point, since it is all cached, but yet it is still much slower! This leads me to believe the cause for the slowness may not even be a db issue. (I am using the JCS caching setting.)

I have two run environments, one calls my java class with the 2.0.3 libraries in the classpath and one has the beta 6 jars. I call the same exact class but yet these timings persist.

Hmm.

Could it perhaps have anything to do with the fact that I used the R1 of middlegen plugin to create the xml files and the CodeGenerator from 2.0.1 to produce the classes? Are there perhaps some new properties are not being set properly, so when the beta takes the xml and class files from the older versions it runs much more slowly?

Thanks again,
Daniel

Submit the code to JIRA and I will take a look.

Make sure I can run it though.

I created an issue in JIRA with the code attached. This is at issue number HB-466

Try to access it at http://opensource.atlassian.com/project ... watch=true


Thanks,
Daniel

OK, this is dramatic demonstration of the difference that PreparedStatement caching can make in some cases!

Guys, Hibernate 2.1 removes the built-in prepared statement cache that was used with the built-in connection pool. This is because it was a pain to maintain, and no one should be using it in production anyway.

If you are using Oracle, make sure you use c3p0 or DBCP, which do preparedstatement caching!

I've been looking at Proxool for connection pooling because I'm not very happy with the other two "production" options.

It looks like Proxool doesn't do statement caching at all. At least, it doesn't have any statement pooling options and doesn't seem to mention statement pooling anywhere in the docs. So people might want to know about that if the PreparedStatement caching thing is really that drastic an issue.

What about for those of us who don't use one of the prebuilt pooling libraries ( dbcp, proxool, c3po ) , but instead use the jdni pooling resources provided by our container

Should we be expecting this functionality from the container provider and asking them if it's not present to put it there?


How do others currently deal with containers/drivers that don't do caching of any sort?

Any decent application server datasource does prepared statement caching.

I have talked to Adrian Brock about perhaps getting a standalone build of JBoss' connection pool for use with Hibernate, but would need to actually spend some time on this and havn't had a chance.

I generally am in agreement with you, however, to the best of my knowledge Resin 2.1 does not do prepared statement caching ( 3.0 apparently does )

I appreciate the heads up, as we will probably look for a different way to configure the container.

Also, I should mention for those using Oracle that the driver can do implicit prepared statement caching, so it might be possible to do a


session.getConnection().setStatementCacheSize(10).


Thanks again,

-a

Gavin,

Yep, that was the problem :-)

Switching to the connection pool speeded up the app to similar timings as 2.0.3.

Thanks again,
Daniel