ArrayList performance with MySql database while retrieving records

Question

I have a LoginID database than can contain 1000's of users. Now to check if a user exist or not what am doing is am storing all the LoginID value of my database into an arraylist than checking if it exists or not using

Code:

while(result.next())
{
  String str = result.getString(1);
  LoginID_arraylist.add(str);
}

if(LoginID_arrayList.contains(loginid)
{
    // if exist --> than another query using loginid
}

Is this a good way to achieve my desired result and what are my alternatives.....will it effect my performance if my size grows further..?? I am using MySql and JDBC.

C. K. Young · Accepted Answer · 2011-06-08 20:52:48Z

4

Don't use ArrayList for doing contains searching. That has O(n) performance. Instead, use a HashSet, which has O(1) lookup.

However, even better is to not query all the rows in the first place. Just make your query:

SELECT COUNT(*) FROM users WHERE login_id = ?

then seeing if the result is 0 or not.

answered Jun 8, 2011 at 20:52

C. K. Young

224k47 gold badges394 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RanRag Over a year ago

if i use ResultSet result = statement.executequery("SELECT COUNT(*) FROM users WHERE login_id = ?") .....than if loginid doesn't exist what will result contain....??

C. K. Young Over a year ago

@RanRag: 0 if the ID doesn't exist; 1 or more if it does (1 if the ID column is unique).

Marcelo · Accepted Answer · 2011-06-08 20:52:39Z

1

Try using a HashSet instead of an ArrayList.

A set doesn't allow duplicates and is structured for much faster lookups.

answered Jun 8, 2011 at 20:52

Marcelo

11.3k1 gold badge40 silver badges53 bronze badges

Comments

Matthew Cox · Accepted Answer · 2011-06-08 20:53:38Z

1

The solution would not scale well with extremely large data sets because you still have Big O(N) time for using contains().

You could just as easily pass the current loginId to the stored procedure instead ... then return an additional data set with the goods needed from your second conditional query.

This would save you a trip to the db and save on iterating N times over the list looking for something you could be finding inside your query instead.

answered Jun 8, 2011 at 20:53

Matthew Cox

13.7k9 gold badges57 silver badges72 bronze badges

Comments

joekarl · Accepted Answer · 2011-06-08 20:55:00Z

1

I'd use a regular sql select and check if there are any results.

SELECT LOGIN_ID.ID FROM LOGIN_ID WHERE ID = 'loginid'

This way the database can optimize and cache queries as well as take advantage of any indexing on the columns. Plus you'll only be hitting the id column rather than every column in the loginID table.

answered Jun 8, 2011 at 20:55

joekarl

2,11814 silver badges23 bronze badges

4 Comments

RanRag Over a year ago

what will above statement return if loginid doesn't exist..??

Marcelo Over a year ago

@RanRag It would return no records.

joekarl Over a year ago

if you used the above sql for your jdbc call and the loginid did not exist, then your while loop would never be called. You can catch this case and handle accordingly.

RanRag Over a year ago

@Marcelo so how will i check that it is returning no records....can u plz show me the if/else condition to do that....

Collectives™ on Stack Overflow

ArrayList performance with MySql database while retrieving records

4 Answers 4

2 Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related