i'm facing a performance issue with the ArrayList specificly in adding to it , briefly i'm reading about 10000 to 40000 records from the DB and putting them in the some ArrayLists to make some processing on them later , i thought may be the delay from the query itself i tried it outside my code it was very fast , so i made a counter and i found it is incremented slowly it takes about nearly 15 mins to fill all the Arraylists with around 9000 records ! .. here is a snippet of my code ..
I can't figure why this delay is happing , Any recommendation ?
Matthew Brown wrote:A couple of things you could try:
- Create the ArrayList with a specified capacity (there's a constructor that does that) big enough for what you need. That stops the ArrayList having to constantly reallocate a new internal array.
i cant do this because the number of records i'm fetching i variable , and i wanna use the ArrayList feature that i dont have to define a size for it in creation time
- Or, switch to a LinkedList, which is faster to modify in general than an ArrayList.
Either of those any help?
i tried this and the still the problem exits , but i noticed something when i'm using the Arraylist that the arrayLists were populated with around 55 records then stop for 3 to 5 mins then continue and so on till it's done !!
I am surprised by that. ArrayList guarantees amortised constant time for add() or similar. Agreed it is quicker to add to the middle of a LinkedList, but finding the nth element of a linked list runs in linear time. I suspect you only require a few seconds for adding 40000 elements to that List, and your delays are occurring elsewhere.
Campbell Ritchie wrote:I am surprised by that. ArrayList guarantees amortised constant time for add() or similar. Agreed it is quicker to add to the middle of a LinkedList, but finding the nth element of a linked list runs in linear time. I suspect you only require a few seconds for adding 40000 elements to that List, and your delays are occurring elsewhere.
this is also what i 'm surprised for why is the ArrayList is behaving like this ?! ! also it's a very simple code for adding
Sherif Shehab wrote:i thought may be the delay from the query itself i tried it outside my code it was very fast , so i made a counter and i found it is incremented slowly it takes about nearly 15 mins to fill all the Arraylists with around 9000 records !
I still suspect the problem has nothing to do with the ArrayList. How exactly did you try it outside your code? I'm guessing you got a ResultSet right away - but if you didn't iterate through every element in the set, that means nothing. A ResultSet can be returned long before all the data has been retrieved - it will just block on rs.next() whenever you need to wait while it gets more data.
To test this theory, try removing the ArrayList, but looping through the ResultSet anyway (doing nothing with the results).
Sherif Shehab wrote:i tried it and found the delay from it , but what may cause this delay in the ResultSet ?
Well, it could be a problem with the network, or with the database. But I think the most likely culprit is an inefficient SQL query combined with a lot of rows in the database that need to be searched. Is it possible that there's a lot of data in the DB, and your query is looking for a small subset of that data? That 3-5 minute pause makes me think maybe it's looking through a portion of the data where nothing matches your query - and then later it gets to a part of the table where there are more records.
Do you have any DBAs who are more familiar with how this database is set up? It may be helpful to talk to them, if there are any. It's possible that you could add an index that would make it more efficient to search on a particular field. Or perhaps there's a way to reorganize the query to be more efficient. Hard to say without more information, and there are many people who know much more than me about how to optimize this sort of thing.
If there's no qualified DBA to ask, you could try asking again here. You'll probably get better results if you post in the JDBC forum and give it a title that's more descriptive of the problem, as we currently understand it. Something like "help optimizing a SQL query" perhaps.
Thanks Mike , but the weired thing i tried that i took this sql statement outside my code and run it alone in the sql query analyzer , i found it took about 10 seconds to finish !! so i think it's some issue with the ResultSet itself ..
Joined: Mar 05, 2008
Hmmm, OK, that's valuable information. In that case the problem must be elsewhere.
Are you using the same connection information in the Java code as in the query analyzer? The same DB server, same user, same password, etc?
Perhaps there's a problem with the JDBC driver. How do you obtain the connection object?
Actually, at this point, these questions would probably be better handled in the JDBC forum, in a new topic with a better descriptive topic. I don't know much about the problems you're having; maybe others there do.