CODEWITHSUNDEEP
javaperformancehibernatebulk-load
M.J.
M.J.

Reputation: 16666

Bulk Fetching from a table in hibernate

I have a table and from that I am fetching records somewhere around 250,000 records and this is taking some 25 mins, is there a way to decrease the fetch time. I am using some code like below :-

query.setParameterList("studentId", StudentIdList);

Is there a way to optimize it?

Upvotes: 0

Views: 2677

Answers (2)

Peter Lawrey
Peter Lawrey

Reputation: 533670

Is there a way to optimize it?

If you want to make it faster you can do a bulk export and load from a file. Or just not use the data base ;)

You can go much faster with other approaches to storing data. You might get performance like this with a database, but sometimes the simplest approaches are best.

public static void main(String... args) throws IOException {
    File file = new File("students.csv");

    PrintWriter pw = new PrintWriter(file);
    for (int i = 0; i < 250 * 1000; i++)
        pw.println("student " + i + ", last " + i + ", email.address" + i + "@my-school.com," + i);
    pw.close();

    long start = System.nanoTime();
    BufferedReader br = new BufferedReader(new FileReader(file));
    List<Student> students = new ArrayList<Student>();
    for (String line; ((line = br.readLine()) != null);) {
        int pos = line.indexOf(',');
        int pos2 = line.indexOf(',', pos + 1);
        int pos3 = line.indexOf(',', pos2 + 1);
        students.add(new Student(line.substring(0, pos), line.substring(pos + 1, pos2), line.substring(pos2 + 1, pos3), Integer.parseInt(line.substring(pos3 + 1))));
    }
    br.close();
    long time = System.nanoTime() - start;
    System.out.printf("Time to load %,d students was %.1f ms.%n", students.size(), time / 1e6);
}

I used Student from http://introcs.cs.princeton.edu/java/32class/Student.java.html

prints

Time to load 250,000 students was 228.7 ms.

Upvotes: -1

Pace
Pace

Reputation: 43867

If it takes less than 10 minutes from the SQL command line and more than 25 minutes from Hibernate then you may want to look into the information on batching. In particular you might benefit from a stateless session (especially if you're not doing much work). If you're really not doing much work you may be able to use a DML statement (also in that URL).

Upvotes: 4

Related Questions