0

I am using JDBC to fetch data(Oracle Database) using Select Query in object accountdetailsList.I did read many post regarding performance issues with Result Set.Currently I am getting maximum 1000 Records in Development Environment so retrival is fast and I am not facing any performance issues.So to improve performance in PRODUCTION where there would be millions of Records I am using setFetchSize().As I am new to JDBC,I am looking for some expert suggestions if this is the correct way of handling large data.

 AccountDetailsList accountdetailsList = new AccountDetailsList();
    List<AccountDetails> accountDetails = new ArrayList();
    try {
        String sql_qry = "SELECT a.BILLING_ACCOUNT_ID,a.VALID_TO,a.STATUS_TYPE_KEY from tablename a where a.STATUS_TYPE_KEY='CLOSED' "
                        + "and a.VALID_FROM >'"
                         + formatStartDate+ "' and a.VALID_TO >'"+ formatToDate+ "'";
        this.preparedStatement = dbconnection.prepareStatement(sql_qry);
        preparedStatement.setFetchSize(100);
        ResultSet rs = this.preparedStatement.executeQuery();
        while (rs.next()) {
            AccountDetails detailsVo = new AccountDetails();
            detailsVo.setBillingAccountId(rs.getString("BILLING_ACCOUNT_ID"));
            detailsVo.setValidto(rs.getDate("VALID_TO"));
            detailsVo.setStatus(rs.getString("STATUS_TYPE_KEY"));

            accountDetails.add(detailsVo);

        }
        accountdetailsList.setAccountdetailsList(accountDetails);

So Is there any other way to improve performance.setFetchSize(100) is ok or setFetchSize(1000) can also be used? Thanks

Lisa14
  • 15
  • 5
  • Your query will return millions of results? – Kayaman Feb 26 '18 at 09:47
  • @Kayaman yes in production environment – Lisa14 Feb 26 '18 at 09:49
  • 3
    Fiddling with the fetch size won't help you. You're accumulating the results into an `ArrayList`, fetch size only helps you keep your memory management down. If you do use it, it will most likely lower your performance as most of the drivers set it to maximum by default. – Kayaman Feb 26 '18 at 10:01
  • @Kayaman ok.what could be done then to improve performance with million records? – Lisa14 Feb 26 '18 at 10:08
  • @Kayaman Fetch size can improve performance by transmitting more rows in a single network operation. On high(er) latency connections, larger fetch sizes therefor may result in better performance. – Mark Rotteveel Feb 26 '18 at 10:16
  • It is impossible to answer this question in a general sense. You'll need to test this and compare results. There is no silver bullet of a single value for fetch size. – Mark Rotteveel Feb 26 '18 at 10:18
  • @MarkRotteveel Thanks.Yes but wanted to know if setFetchsize() can be used here or not?I wanted to handle this as currently I will be testing on DEV and other environments which will have 1000 or lakhs of data.I wont be the one who will test on production so cant test right now with huge data :( – Lisa14 Feb 26 '18 at 10:23
  • Since you don't understand it, don't use it. You can only worsen things by trying to put your fingers into where they don't belong. – Kayaman Feb 26 '18 at 10:25
  • Given your current code, trying to retrieve millions of records will blow up in your face, whatever fetch size you use. – Mark Rotteveel Feb 26 '18 at 10:31
  • @Kayaman I just said I dont have access to higher environment to test.Your comment about worsening things??You could have said that in better way. – Lisa14 Feb 26 '18 at 10:35
  • 1
    No I couldn't. If you don't understand something, don't touch it. That's a basic rule **anywhere**. An inexperienced developer trying to fix imaginary performance problems is an invitation to chaos. – Kayaman Feb 26 '18 at 12:38
  • Potential duplicate of https://stackoverflow.com/q/20899977/1509264 – MT0 Feb 26 '18 at 12:44
  • @Lisa14 The question I'd be asking is - what is your plan for the millions of rows ? Its rare to shift those rows around in the middle tier - normally you'd use SQL etc to achieve the *function* desired (eg "Year to date total" or similar) and just return the *results* back to the middle tier. If the function is indeed to bring those millions of rows to the middle tier, then may well have to incur the performance overhead of doing so. – Connor McDonald Feb 27 '18 at 05:19
  • @ConnorMcDonald: I would be writing those data to CSV File.So I am passing the fianl object to write method. – Lisa14 Feb 27 '18 at 06:37
  • But you'll still load the whole dataset into your `ArrayList`, making it waste memory. If you were to directly write the data into a CSV straight from the `ResultSet`, then you could save memory with a lower fetch size. Now it's all about "hey, I'll pretend to be clever and 'tune' this code". Fell, at least with fetch size you can't break things, but it's still no use to change it here, with the code you've shown. – Kayaman Feb 27 '18 at 07:05
  • @Lisa14 In the case, you're unlikely to see much improvement with fetch sizes over 1000. If you are pulling the information over a network (which I presume you) then sorting the data will obtain some compression benefits which should speed things up. If you are just writing to CSV, it is probably more scalable to write each fetched batch, rather than getting the entire set into an array – Connor McDonald Feb 27 '18 at 07:20
  • @ConnorMcDonald thanks. – Lisa14 Feb 27 '18 at 08:34

0 Answers0