List patternList=new ArrayList<String>();
ResultSet rs=stat.executeQuery("select file_pattern from Pattern_table");
Here I'm going through each record and add to a
list. It's taking too much time to traverse through the
ResultSet and adding the records in a
Is there another way to add all the records of
list at once?
Or any alternate approach I could follow to achieve better performance?
You could always use Commons DbUtils and the MapListHandler. From the doc:
ResultSetHandler implementation that converts a ResultSet into a List of Maps so it'll take a lot of boilerplate code out of your hands. And rest of it depends on the database transaction time.
Before trying to improve performance of Java code first try to run the same query from some SQL client. Check if the time taken from the SQL client is equivalent to Java.
SQL Clients have a default limit to the number of rows that they fetch at a time so you have to make sure that you have fetched all the rows that query returns to measure the time spent on running it.
If you find that SQL client is running significantly faster than the Java call, they try setting the
fetchSize() on the
Statement or the
ResultSet is not the entire data from the query but a just a fraction of it. As you go on running the
resultSet.next() it will keep on fetching rows once it runs out of the previously fetched rows.
For example if query returns 5000 rows and
fetchSize is 100 then
result.next() will fetch 100 rows at a time. Only after these 100 rows have been read next call to database will be made thus
JDBC will make
5000/100=50 network calls to database.
A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.
So in short, even if you find a way to get the list directly from the result set it will always use an iteration to go through it to convert it to List.
There are many utils for archive this, but the big cost in performance is all on the database connection and in the SQL statement you have executed so you need to optimize that.
You can get the column values from resultSet using method getArray(String columnLabel) and store it in array. Than convert it to the arrayList as per your requirement. Example:
List patternList=new ArrayList<String>(); ResultSet rs=stat.executeQuery("select file_pattern from Pattern_table"); String tempArr = rs.getArray(file_pattern); patternList = Arrays.asList(tempArr);