Hi I am trying to read all the rows from MS SQL Server.
There are 70000 rows in a table.
Blob field size in each row is 33KB.
When read all records with the JDBC program, it took 2-3 minutes to read all the records.
But when tried to with hibernate, its throwing Outof memory Exception.
I used JDBC 2 driver provided by Microsoft.
But in practical, when used Scrollable Results with Hibernate, It shouldn't pool the objects in
memory and it shouldn't eat memory. Why it is happening like this for MS SQL Server 2005
When run the same program for Oracle 10G, it just ran in 11 Sec, without any memory occupancy.
Hibernate version: 3.2
Mapping documents:
SlipMnt.hbm.xml
Code:
<?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>
<class name="SlipMnt" table="SLIP_MNT" >
<composite-id>
<key-property name="factsPin" column="FACTS_PIN"/>
<key-property name="digitNo" column="DIGIT_NMBR" />
</composite-id>
<property name="rows" column="MNT_ROWS"/>
<property name="columns" column="MNT_COLUMNS"/>
<property name="gender" column="GENDER"/>
<property name="agencyID" column="CONTROL_AGENCY_ID"/>
<property name="factsPinDigit" column="FACTS_PIN_DIGIT"/>
<property name="compressionFormat" column="COMPRESSION_FORMAT"/>
<property name="mnt" column="DIGIT_MNT" />
<property name="mntSize" column="MNT_SIZE"/>
</class>
</hibernate-mapping>
Code:
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:p="http://www.springframework.org/schema/p"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
<context:property-placeholder location="classpath:jdbc.properties" />
<bean id="sqlsrvDataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close" p:driverClassName="${jdbc.driverClassName}" p:url="${jdbc.url}" />
<bean id="sqlsrvSessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="sqlsrvDataSource"/>
<property name="mappingResources">
<list>
<value>SlipMnt.hbm.xml</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.generate_statistics">${hibernate.generate_statistics}</prop>
<prop key="hibernate.jdbc.use_scrollable_resultsets">${hibernate.jdbc.use_scrollable_resultsets}</prop>
</props>
</property>
</bean>
<bean id="dbread" class="DBRead">
<property name="sessionFactory">
<ref bean="derbySessionFactory" />
</property>
</bean>
</beans>
Code:
jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://CMC-8N1K8VZK5KC:1433;databaseName=factstest;selectMethod=cursor;user=sa;password=sa;
jdbc.username=sa
jdbc.password=sa
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.show_sql=false
hibernate.generate_statistics=false
hibernate.connection.pool_size=3
hibernate.jdbc.use_scrollable_resultsets=true
Code between sessionFactory.openSession() and session.close():Code:
import java.io.IOException;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
/**
* @author sridhar
*
*/
public class DBRead extends HibernateDaoSupport {
private SessionFactory sessionFactory = null;
@SuppressWarnings("unchecked")
public void readOrclDB() {
Timestamp start = new Timestamp(Calendar.getInstance()
.getTimeInMillis());
getHibernateTemplate().execute(new HibernateCallback(){
@Override
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
SlipMnt slip = null;
ScrollableResults results = session.createQuery("from SlipMnt").scroll(ScrollMode.SCROLL_INSENSITIVE);
while(results.next()){
slip = (SlipMnt)results.get(0);
}
return null;
}
});
Timestamp end = new Timestamp(Calendar.getInstance().getTimeInMillis());
System.out.println(start);
System.out.println(end);
System.out.println(end.getTime() - start.getTime());
}
public static void main(String[] args) throws IOException {
final ApplicationContext ctx = AppContext.getContext();
final DBRead dbread = (DBRead) ctx.getBean("dbread");
dbread.readOrclDB();
}
}
Full stack trace of any exception that occurs:Code:
log4j:WARN No appenders could be found for logger (org.springframework.context.support.ClassPathXmlApplicationContext).
log4j:WARN Please initialize the log4j system properly.
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at com.microsoft.sqlserver.jdbc.PLPInputStream.getBytes(Unknown Source)
at com.microsoft.sqlserver.jdbc.DDC.binaryPLPToObject(Unknown Source)
at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.DTV.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Column.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Column.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getBlob(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getBlob(Unknown Source)
at org.apache.commons.dbcp.DelegatingResultSet.getBlob(DelegatingResultSet.java:527)
at org.hibernate.type.BlobType.get(BlobType.java:57)
at org.hibernate.type.BlobType.nullSafeGet(BlobType.java:111)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2096)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1380)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1308)
at org.hibernate.loader.Loader.getRow(Loader.java:1206)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:580)
at org.hibernate.loader.Loader.loadSingleRow(Loader.java:268)
at org.hibernate.impl.ScrollableResultsImpl.prepareCurrentRow(ScrollableResultsImpl.java:231)
at org.hibernate.impl.ScrollableResultsImpl.next(ScrollableResultsImpl.java:100)
at DBRead$1.doInHibernate(DBRead.java:42)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:419)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
at DBRead.readOrclDB(DBRead.java:33)
at DBRead.main(DBRead.java:62)
Name and version of the database you are using: MS SQL Server 2005
Login
FAQ
Search
