pattern ideas?

[Michael J. Makunas]

I have a situation where due to db performance issues, we are trying to limit the number of queries in our code. For the sake of argument, let say I am constantly creating "User" ojects, that represent a user on the site. There are cases where I intatiate 12 of them, one after another, each with it's own db hit. What I would like to do is create an object structure that enables me to create an array of objects (using one db query) when I want and still be able to instantiate a single object, too.

Are there any patterns that fit this?

Thanks....

[Stan James]

Some place you have something that executes SQL and populates a User object from the result set, right? Can you modify the SQL to select for userid "in" a set of ids? When the set has only one id the query fetches only one row. When the set has mulitple userids the single query will return multiple rows. Then you'd just build a User object per row and return a collection of some kind.

Is that the kind of thing you had in mind? I really don't know what kind of speed advantage a single query will have over multiples, but it might be worth doing.

Are you caching so things are not retrieved again? That's a good strategy sometimes depending on the fetch vs update ratio on things. Be sure to include a way to clear the cache and get the latest database data again.

[Lasse Koskela]

Don't know about a pattern, but the usual way to implement this would be to have two methods:
public User findUser(String uid);
public Collection findUsers(String[] uid);

Are you using a persistence framework like Hibernate/JDO/Castor/TopLink?

[Jeanne Boyarsky]

Michael,
Are you trying to create a bunch of users in the database or retrieve them?

If creating, JDBC has an executeBatch() statement that does just that.

If retrieving, search the JDBC forum for my posts on batching. It's not a formal pattern, but it helps limit database roundtrips. Basically, you have an in clause with predefined numbers of ?'s to take advantage of the preparedness part. It is a middle ground for many roundtrips (network issues) and doing everything at once (forcing the database to prepare the statement each time.) A few other people here have tried it and noticed a performance boost.

[Michael J. Makunas]

Originally posted by Stan James:
Some place you have something that executes SQL and populates a User object from the result set, right? Can you modify the SQL to select for userid "in" a set of ids? When the set has only one id the query fetches only one row. When the set has mulitple userids the single query will return multiple rows. Then you'd just build a User object per row and return a collection of some kind.

Yes, my plan was to modify the SQL to use an "IN" clause. I'm more interested in figuring out how to model this so that the data layer stays suficiently decoupled.

[Michael J. Makunas]

Originally posted by Lasse Koskela:
Don't know about a pattern, but the usual way to implement this would be to have two methods:
public User findUser(String uid);
public Collection findUsers(String[] uid);

Are you using a persistence framework like Hibernate/JDO/Castor/TopLink?

I'm not using any kind of persistence framework. But when you mention having two methods, findUser and findUsers, you made me think of something. I think I need to completely seperate the SQL and db code from the user class, and have another layer that hits the db and then creates a collection of users.

[Michael J. Makunas]

Originally posted by Jeanne Boyarsky:

If retrieving, search the JDBC forum for my posts on batching. It's not a formal pattern, but it helps limit database roundtrips. Basically, you have an in clause with predefined numbers of ?'s to take advantage of the preparedness part. It is a middle ground for many roundtrips (network issues) and doing everything at once (forcing the database to prepare the statement each time.) A few other people here have tried it and noticed a performance boost.

Thanks. I'm not sure if a prepared statement will be work in my particular case but I think your post will help in some other areas where we are having performance issues.

[Ilja Preuss]

Originally posted by Michael J. Makunas:
I'm not using any kind of persistence framework. But when you mention having two methods, findUser and findUsers, you made me think of something. I think I need to completely seperate the SQL and db code from the user class, and have another layer that hits the db and then creates a collection of users.

Yes, that's definitely a good idea!

[Stan James]

Yes, that specialized IO layer sounds good!

I'm just refactoring my Wiki (for entertainment value). I used to have a Repository layer that did all the IO. I had a simple flat file implementation and an archiving implementation that saved all revisions.

This time around I have the Repository layer that does many common file manipulations and a DataStore layer that does physical IO. In real life I use a FileSystemDataStore, but for unit testing I have an InMemoryDataStore.

Keeps me off the street at night.