Hibernate Version: 3.0.5
Database: MSSQL Server 2000
JDBC Driver: 2.2.0.0.40 Microsoft SQLServer 2000 Driver for JDBC


The following sql-query takes to many time to execute in hibernate(10 to 15 minutes):

SELECT S.SKU_PART_NUMBER, S.SKU_ID, A.ACTIVITY_NAME,
D.DATA_LABEL, D.DATA_ID, SD.DATA_VALUE,
D.DATA_IS_PARAMETER,
upper(P.PARAMETER_VALUE) as PARAMETER_VALUE,
DT.DATA_TYPE_DESCRIPTION, S.FLOW_ID
FROM PDM_DATA AS D
INNER JOIN PDM_DATA_PDM_PLUGIN DP
ON D.DATA_ID = DP.DATA_ID
INNER JOIN PDM_ACTIVITY_PDM_PLUGIN AP
ON DP.PLUGIN_ID = AP.PLUGIN_ID AND DP.ACTIVITY_ID = AP.ACTIVITY_ID
INNER JOIN PDM_ACTIVITY A
ON AP.ACTIVITY_ID = A.ACTIVITY_ID
INNER JOIN PDM_SKU_PDM_ACTIVITY SA
ON A.ACTIVITY_ID = SA.ACTIVITY_ID
INNER JOIN PDM_SKU S
ON S.SKU_ID = SA.SKU_ID
INNER JOIN PDM_STATUS_SKU AS SS
ON SS.STATUS_SKU_ID = S.STATUS_SKU_ID
INNER JOIN PDM_COMPONENT_DATA AS C
ON D.COMPONENT_ID = C.COMPONENT_DATA_ID
INNER JOIN PDM_DATA_TYPE AS DT
ON C.DATA_TYPE_ID = DT.DATA_TYPE_ID
LEFT JOIN PDM_SKU_PDM_DATA AS SD
ON D.DATA_ID = SD.DATA_ID AND S.SKU_ID = SD.SKU_ID
LEFT JOIN PDM_PARAMETER AS P
ON D.DATA_ID = P.DATA_ID
WHERE S.SKU_PART_NUMBER IN ('102306-B21', '112138-B23', '12C#ABA', '202552-563', 'ZD303AV') AND A.ACTIVITY_NAME IN ('CENTRAL PDM TEAM', 'GPG Release', 'ICOST/CSSN/CPL', 'RESELLER WEB', 'Velocity LAR Material Master', 'Velocity LAR Released') AND D.DATA_LABEL IN ('BOM COPIED TO MFG', 'P/N EXTENDED TO PROPER PLANT', 'GPG data accuracy', 'VENDOR CONTRACT PRICE - U$', 'Reseller Check', 'CALADO PLATFORM', 'MANUFACTURER', 'VENDOR', 'NOTES') AND S.REGION_ID = 1 ORDER BY S.SKU_ID, A.ACTIVITY_ID, D.DATA_ID


The same sql-query using JDBC takes 2 seconds to execute. If you use the TOP operator in the select clause, the sql-query in hibernate takes 3 seconds.

SELECT TOP 10000000 S.SKU_PART_NUMBER, S.SKU_ID, A.ACTIVITY_NAME,
D.DATA_LABEL, D.DATA_ID, SD.DATA_VALUE,
D.DATA_IS_PARAMETER,
upper(P.PARAMETER_VALUE) as PARAMETER_VALUE,
DT.DATA_TYPE_DESCRIPTION, S.FLOW_ID
FROM PDM_DATA AS D
INNER JOIN PDM_DATA_PDM_PLUGIN DP
ON D.DATA_ID = DP.DATA_ID
INNER JOIN PDM_ACTIVITY_PDM_PLUGIN AP
ON DP.PLUGIN_ID = AP.PLUGIN_ID AND DP.ACTIVITY_ID = AP.ACTIVITY_ID
INNER JOIN PDM_ACTIVITY A
ON AP.ACTIVITY_ID = A.ACTIVITY_ID
INNER JOIN PDM_SKU_PDM_ACTIVITY SA
ON A.ACTIVITY_ID = SA.ACTIVITY_ID
INNER JOIN PDM_SKU S
ON S.SKU_ID = SA.SKU_ID
INNER JOIN PDM_STATUS_SKU AS SS
ON SS.STATUS_SKU_ID = S.STATUS_SKU_ID
INNER JOIN PDM_COMPONENT_DATA AS C
ON D.COMPONENT_ID = C.COMPONENT_DATA_ID
INNER JOIN PDM_DATA_TYPE AS DT
ON C.DATA_TYPE_ID = DT.DATA_TYPE_ID
LEFT JOIN PDM_SKU_PDM_DATA AS SD
ON D.DATA_ID = SD.DATA_ID AND S.SKU_ID = SD.SKU_ID
LEFT JOIN PDM_PARAMETER AS P
ON D.DATA_ID = P.DATA_ID
WHERE S.SKU_PART_NUMBER IN ('102306-B21', '112138-B23', '12C#ABA', '202552-563', 'ZD303AV') AND A.ACTIVITY_NAME IN ('CENTRAL PDM TEAM', 'GPG Release', 'ICOST/CSSN/CPL', 'RESELLER WEB', 'Velocity LAR Material Master', 'Velocity LAR Released') AND D.DATA_LABEL IN ('BOM COPIED TO MFG', 'P/N EXTENDED TO PROPER PLANT', 'GPG data accuracy', 'VENDOR CONTRACT PRICE - U$', 'Reseller Check', 'CALADO PLATFORM', 'MANUFACTURER', 'VENDOR', 'NOTES') AND S.REGION_ID = 1 ORDER BY S.SKU_ID, A.ACTIVITY_ID, D.DATA_ID



Why hibernate takes to many times to execute with no TOP operator, and
we put the TOP operator, the same query executes with good performance?

Observation: The number of lines returned by this query is about 700.
The same problem was verified in version 3.1 of Hibernate.

Thanks in Advance.

I think this has little to do with Hibernate and more with MSSQL.
Just curios, how long does it take for straight JDBC to execute the query without top?

TOP is the MS 'extension' to SQL, you may try setting limit on query and check if H will produce SQL with 'TOP'
session.createQuery( "zzzz").setMaxResults( 10000).list();

At least for Postgres and ORACLE H produces correct SQL with LIMIT clause.

Using straight JDBC to execute the query without TOP takes
2 seconds. We shouldn't want to use TOP operator in hibernate to resolve the problem or straight JDBC(without hibernate). We want to know why this is happening and a more elegant solution rather than TOP.

I believe that H does more than performing this single query. Do you have any non-lazy associations? H may do loading of all properties for all the associations. Did you see additional queries in the log after the query in question?

Doesn't exist associations, we're using sql-query with return-scalar strategy.
So, H doesn't need create associating objects and set properties for each record returned by the query.

Could you post your code and mapping files?

Are you sure the query is the time sink? See what process is using the CPU, (or if it's all disk and I/O) and/or profile the java. This will also give a better idea of the nature of the slow process.

(Remember that a poor man's java profiler is to use a debugger and stop the VM every few seconds, or even just dump the threads via ctrl-brk in the command window to see which threads are actively working).

The following is the query's file:

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

<hibernate-mapping>
<sql-query name="findSkusDataValue">
<return-scalar column="SKU_PART_NUMBER" type="java.lang.String" />
<return-scalar column="SKU_ID" type="java.lang.Integer" />
<return-scalar column="ACTIVITY_NAME" type="java.lang.String" />
<return-scalar column="DATA_LABEL" type="java.lang.String" />
<return-scalar column="DATA_ID" type="java.lang.Integer" />
<return-scalar column="DATA_VALUE" type="java.lang.String" />
<return-scalar column="DATA_IS_PARAMETER" type="java.lang.String" />
<return-scalar column="PARAMETER_VALUE" type="java.lang.String" />
<return-scalar column="DATA_TYPE_DESCRIPTION" type="java.lang.String" />
<return-scalar column="FLOW_ID" type="java.lang.Integer" />
SELECT TOP 10000000 S.SKU_PART_NUMBER, S.SKU_ID, A.ACTIVITY_NAME,
D.DATA_LABEL, D.DATA_ID, SD.DATA_VALUE,
D.DATA_IS_PARAMETER,
upper(P.PARAMETER_VALUE) as PARAMETER_VALUE,
DT.DATA_TYPE_DESCRIPTION, S.FLOW_ID
FROM PDM_DATA AS D
INNER JOIN PDM_DATA_PDM_PLUGIN DP
ON D.DATA_ID = DP.DATA_ID
INNER JOIN PDM_ACTIVITY_PDM_PLUGIN AP
ON DP.PLUGIN_ID = AP.PLUGIN_ID AND DP.ACTIVITY_ID = AP.ACTIVITY_ID
INNER JOIN PDM_ACTIVITY A
ON AP.ACTIVITY_ID = A.ACTIVITY_ID
INNER JOIN PDM_SKU_PDM_ACTIVITY SA
ON A.ACTIVITY_ID = SA.ACTIVITY_ID
INNER JOIN PDM_SKU S
ON S.SKU_ID = SA.SKU_ID
INNER JOIN PDM_STATUS_SKU AS SS
ON SS.STATUS_SKU_ID = S.STATUS_SKU_ID
INNER JOIN PDM_COMPONENT_DATA AS C
ON D.COMPONENT_ID = C.COMPONENT_DATA_ID
INNER JOIN PDM_DATA_TYPE AS DT
ON C.DATA_TYPE_ID = DT.DATA_TYPE_ID
LEFT JOIN PDM_SKU_PDM_DATA AS SD
ON D.DATA_ID = SD.DATA_ID AND S.SKU_ID = SD.SKU_ID
LEFT JOIN PDM_PARAMETER AS P
ON D.DATA_ID = P.DATA_ID
WHERE S.SKU_PART_NUMBER IN (:list1)
AND A.ACTIVITY_NAME IN (:list2)
AND D.DATA_LABEL IN (:list3)
AND S.REGION_ID = :region
ORDER BY S.SKU_ID, A.ACTIVITY_ID, D.DATA_ID
</sql-query>
</hibernate-mapping>

Debugging the code, We found that problem in AbstractBatcher class,
method "public ResultSet getResultSet(PreparedStatement ps) throws SQLException", line "ResultSet rs = ps.executeQuery();".

So, the following is the mapping file:

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

<hibernate-mapping package="br.com.atlantico.hp.pdm.SKUManagement.model">
<class name="SkuVO" table="PDM_SKU">
<id name="skuId" type="java.lang.Integer" column="SKU_ID">
<generator class="native" />
</id>
<property name="skuPartNumber" type="java.lang.String" column="SKU_PART_NUMBER" not-null="true"/>
<property name="flowId" type="java.lang.Short" column="FLOW_ID" not-null="true"/>
<property name="regionId" type="java.lang.Short" column="REGION_ID" not-null="true"/>
<property name="requestorId" type="java.lang.Short" column="USER_ID_REQUESTOR" not-null="true"/>
<property name="systemManagerId" type="java.lang.Short" column="USER_ID_SYSTEM_MANAGER" not-null="true"/>
<property name="coordinatorId" type="java.lang.Short" column="USER_ID_COORDINATOR" not-null="false"/>
<property name="productLineId" type="java.lang.Short" column="PRODUCT_LINE_ID" not-null="false"/>
<property name="platformId" type="java.lang.Short" column="PLATFORM_ID" not-null="false"/>
<property name="productFamilyId" type="java.lang.Short" column="PRODUCT_FAMILY_ID" not-null="false"/>
<property name="skuDescription" type="java.lang.String" column="SKU_DESCRIPTION" not-null="true"/>
<property name="skuRequestDate" type="java.sql.Timestamp" column="SKU_REQUEST_DATE" not-null="true"/>
<property name="skuNeedDate" type="java.sql.Timestamp" column="SKU_NEED_DATE" not-null="true"/>
<property name="skuStatusDate" type="java.sql.Timestamp" column="SKU_STATUS_DATE" not-null="false"/>
<!-- Set up field removed to accord with CR EEA-9620 -->
<!-- property name="skuSetup" type="java.lang.String" column="SKU_SETUP" not-null="true" /-->
<property name="productTypeId" type="java.lang.Short" column="PRODUCT_TYPE_ID" not-null="false"/>
<property name="productManagerId" type="java.lang.Short" column="PRODUCT_MANAGER_USER_ID" not-null="false"/>
<property name="lineId" type="java.lang.Short" column="LINE_ID" not-null="false"/>
<many-to-one name="statusVO" class="StatusVO" not-null="true" lazy="true">
<column name="STATUS_SKU_ID" />
</many-to-one>
</class>
<class name="StatusVO" table="PDM_STATUS_SKU">
<id name="statusId" type="java.lang.Integer" column="STATUS_SKU_ID">
<generator class="native" />
</id>
<property name="description" type="java.lang.String" column="STATUS_SKU_DESCRIPTION" not-null="false"/>
<property name="type" type="java.lang.Integer" column="STATUS_SKU_TYPE" not-null="false"/>
</class>
</hibernate-mapping>

I think that when using sql-query with return-scalar strategy, Hibernate doesn't use the mapping file above, because the data returned is scalar, and doesn't need to create a list of SkuVO.

Thansks in Advance.