All Forums Connectivity
bmani 6 posts Joined 10/13
29 Mar 2014
Reading large table is slow

              I have a table that has 80,000 records i have to read all data and I cannot filter records. It takes over 90 seconds. This is way too slow. I tried with setFetchSize of 50,100,1000,10000. Nothing helped.
     Connection conn = getJdbcTemplate().getDataSource().getConnection();


    PreparedStatement stmt = conn.prepareStatement(sqlGetAllData);//, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);



What is the best way to retrieve large tables within 15-30 seconds.




Raja_KT 1246 posts Joined 07/09
30 Mar 2014

Did you check with your DBA ??????? if he probably can check with dbschk,syscheck. How about your TD system like amps, nodes etc. It can also be due to bad table defination.It maybe due to priority  and you run during peak time. Slow performance can be outside your table too, like stats of dictionary table not timely collected. For all databases that I know, reading that volume is as close as 1 minute.Big Data maybe different, like Aster,Hive, Pig etc.

Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

rksumner 2 posts Joined 04/09
01 Apr 2014

there can be a lot of things that will slow down your retrieval beyond the database, depending upon your network topology, server location, etc.  What is the priority that you are running at?  How heavily loaded is the system?  How long is your query waiting before execution? 
Once your query is executed and considering that you say you are unable to filter, that may mean that you are doing a simple select operation wherein you bring back all the data to spool and then consume it.  That would likely only take a few seconds while all the rest of the time is spent working with the retrieval logic and the network.  As Raja suggested earlier, I would check with the DBA but also with the network personnel to find out how much affect the network has on returning all of that data.  
There are many moving parts, any one of which may slow your retrieval. 


You must sign in to leave a comment.