Reading large table is slow

Connectivity
Enthusiast

Reading large table is slow

Hi,

              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();

    conn.setAutoCommit(false);

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

    stmt.setFetchSize(50);

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

Thanks,

Bharani

2 REPLIES
Enthusiast

Re: Reading large table is slow

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.

Teradata Employee

Re: Reading large table is slow

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.