StoredProcedure, jdbcTemplate and large amount of data received

**Cerrebos** · Aug 31st, 2012, 10:05 AM

Hi,

I'm having some issues with the result of a StoredProcedure that return 50 000 lines, which are displayed in a grid in a flex(flash) client application all at once (no pagination).

The StoredProcedure itself is quite complex but optimized, and take 50 sec to run.
The result is done after 2 minutes. So basically I want to improve this as much as possible.

After that, the java layer starts retrieving the result and create object for each lines in my result set.

Here is the configuration I have (I use StoredProcedure, jdbcTemplate - one per dao) and a resultSetExtractor. The database is an SQLServer 2008:

datasource configuration:

HTML Code:

    <bean id="dataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
	    <property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
	    <property name="url" value="${database.url}"/>
    	<property name="username" value="${database.username}"/>
    	<property name="password" value="${database.password}"/>
    </bean>

DAO implementation (I removed some stuff and replace some variables name for confidentiality reason):

Code:

@Repository
public class JdbcObjectDAO implements ObjectDAO {

	
	//****************************************************//
	//**********CLASS ATTRIBUTES AND CONTRUCTOR **********//
	//****************************************************//
	
	private static final String STORED_PROCEDURE = "dbo.GUI_GET_OBJECT";
	private JdbcTemplate jdbcTemplate;

	@Autowired
	public JdbcObjectDAO(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	
	//********************************//
	//**********DAO METHODS **********//
	//********************************//

	@Override
	public MyObjectCollection getObjectInit(String xmlMessage, String userId) {
		CustomStoredProcedureWithRSExtractor proc = new CustomStoredProcedureWithRSExtractor(
				jdbcTemplate, STORED_PROCEDURE,
				new CustomResultSetExtractorGetListInit());
		Map<String, Object> result = proc.execute(xmlMessage, userId);
		return (MyObjectCollection) result.get("rs");
	}
	
	//*************************************************//
	//**********MAPPERS/RESULTSET EXTRACTORS **********//
	//*************************************************//
	
	private static final class CustomResultSetExtractorGetListInit implements
			ResultSetExtractor<MyObjectCollection> {

		@Override
		public MyObjectCollection extractData(ResultSet rs) throws SQLException,
				DataAccessException {
			MyObject myObject = null;
			MyObjectCollection objectCollection = new MyObjectCollection();
			//This starts after the storedProcedure is executed (almost 50 sec after service is called)
			if (rs != null)
				while (rs.next())
					if (rs.getString("QSDF").equalsIgnoreCase("QSDF")
							|| rs.getString("QSDF").equalsIgnoreCase("QSDF")) {
						myObject = new MyObject(rs.getString("QSDF"),rs.getString("QSDF"), rs.getString("QSDF"),rs.getString("QSDF"), rs.getString("QSDF"),rs.getString("QSDF"), 
						rs.getString("QSDF"),rs.getInt("QSDF"), rs.getInt("QSDF"),rs.getInt("QSDF"), rs.getInt("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),
						rs.getString("QSDF"),rs.getInt("QSDF"),rs.getString("QSDF"),rs.getInt("QSDF"),rs.getString("QSDF"),rs.getInt("QSDF"),rs.getString("QSDF"),
						rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"), rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),
						rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),
						rs.getString("QSDF"), rs.getDouble("QSDF"),rs.getDouble("QSDF"),rs.getDouble("QSDF"), rs.getString("QSDF"),rs.getDouble("QSDF"), rs.getDouble("QSDF"),
						rs.getDouble("QSDF"),rs.getDouble("QSDF"),rs.getDouble("QSDF"),rs.getDouble("QSDF"),rs.getDouble("QSDF"), rs.getString("QSDF"),rs.getString("QSDF"),
						rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"),
						rs.getString("QSDF"),rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"),
						rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"),
						rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"),
						rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), 
						rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"));
						
						objectCollection.addItem(myObject);
						
					} else if (rs.getString("QSDF").equalsIgnoreCase("QSDF"))
						myObject.addStuff(rs.getString("QSDF"),rs.getInt("QSDF"), rs.getInt("QSDF"),rs.getInt("QSDF"), rs.getInt("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),
						rs.getString("QSDF"),rs.getInt("QSDF"),rs.getString("QSDF"),rs.getInt("QSDF"),rs.getString("QSDF"),rs.getInt("QSDF"),rs.getString("QSDF"),
						rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"), rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),
						rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"),
						rs.getString("QSDF"), rs.getDouble("QSDF"),rs.getDouble("QSDF"),rs.getDouble("QSDF"), rs.getString("QSDF"),rs.getDouble("QSDF"), rs.getDouble("QSDF"),
						rs.getDouble("QSDF"),rs.getDouble("QSDF"),rs.getDouble("QSDF"),rs.getDouble("QSDF"),rs.getDouble("QSDF"), rs.getString("QSDF"),rs.getString("QSDF"),
						rs.getString("QSDF"),rs.getString("QSDF"),rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"),
						rs.getString("QSDF"),rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"),
						rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"),
						rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"),
						rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), 
						rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"), rs.getString("QSDF"));
						
			return objectCollection;
		}
	}
}

And here is the CustomStoredProcedureWithRSExtractor Class I use (I did it this way for architecture reasons):

Code:

public class CustomStoredProcedureWithRSExtractor extends StoredProcedure {

	private static final String USERID_PARAM = "@user_id";
	private static final String XML_MESSAGE = "@xmltext";


	/**
	 * @param jdbcTemplate
	 * 	the autowired jdbcTemplate from the caller class
	 * @param storedProcedureName
	 * 	the storedProcedure to call
	 * @param resultSetExtractor
	 * 	the custom resultSetExtractor to retrieve the result, associated with the resultSet "rs"
	 * Use execute method of this class to retrieve the mapped result function of the given the user id and xml message.
	 */
	public CustomStoredProcedureUserIdXmlMessageRSExtractor(JdbcTemplate jdbcTemplate, String storedProcedureName, ResultSetExtractor<?> resultSetExtractor) {
		super(jdbcTemplate, storedProcedureName);
		setFunction(false);
		declareParameter(new SqlParameter(USERID_PARAM, Types.VARCHAR));
		declareParameter(new SqlParameter(XML_MESSAGE, Types.VARCHAR));
		declareParameter(new SqlReturnResultSet("rs", resultSetExtractor));
		compile();
	}

	/**
	 * 
	 * @param xmlMessage
	 * 	the xml message needed by the Stored procedure
	 * @param userId
	 * 	the user Id
	 * @return
	 * 	An Object containing the mapped result for each row.
	 *  Use <strong>result.get("rs")</strong> to retrieve the mapped result set from the given RowMapper.
	 */
	public Map<String, Object> execute(String xmlMessage, String userId) {
		HashMap<String, String> input = new HashMap<String, String>();
		input.put(USERID_PARAM, userId);
		input.put(XML_MESSAGE, xmlMessage);
		return super.execute(input);
	}
}

My questions is:

- Is there any way to reduce the time of the phase "retrieve result > create object" considering this volume? (50k lines that create 50k objects with some treatment, and at least 30 fields)?
Note that I had to upgrade my JVM memory to 1500m in order to be able to test this dao locally.
Again, no pagination authorized :S.

Any help would be great ! :D

Thanks,

Cerrebos

Thread: StoredProcedure, jdbcTemplate and large amount of data received

Thread Tools

Display

Hybrid View

StoredProcedure, jdbcTemplate and large amount of data received

Tags for this Thread

Posting Permissions