For an upcoming module, I will need to use different SQL queries to display data depending on what the user inputs. The queries might change frequently. I can have these mapped in a properties file (like HashMap) or store them in a database table and query the table to get the appropriate query.
My question is which one will be more efficient ? I have the following things in mind :
Properties file - Pros
1. Ease of maintenance.
2. Quicker access to read the query - one less hit to the database.
Properties file - Cons
1. The properties file might be be huge (say 10MB) after adding all the queries. So this will occupy 10 MB of memory on server startup (we are using spring + hibernate).
2. If the properties file is not to be loaded at startup, it might be loaded multiple times if two or more users access the same module.
Database - Pros
1. No extra memory occupied like the properties file.
Database - Cons
2. Might be time taking.
I seem to be inclined towards using Properties file and load it on server startup even if occupies extra memory. Can someone please throw more light as to what else should be considered here ? And which might work better ?
It really depends on the requirement. Your properties file with 10's of MB is not big in today's environments when servers have got typically 4 GB or more memory. So you can cache the properties and can query to cache which will be highly performing. You can refresh the cache intermittently so that after change in properties, those can be reflected into the system.
Data layer would be really a choice depending on various factors like -
1. Do you see database vendor changing in the future?
2. Are you using any OR mapping tool like hibernate
3. What is your server architecture? like clustered app server and clustered db
4. Do you want to give interface to user to modify the properties
love your job and not your company;
...because you never know when your company will stop loving you!
"Efficient" is often an odd primary concern. Yes its important, but much more important in my mind are:
Do you need your data store to be transactional? Properties files are not
Will other applications ever access the data? Databases can ensure the integrity of the data, property files rely on the applications that use them to play nice
Do you ever intend deploying this in an environment that doesn't support direct access of the file system (e.g. a distributed environment)
In a foot race between a file and a database, files will probably win, but I'm guessing not by much these days. If this is just a simple little application I'd use a file just because its simple too. But I'd put its access behind a DAO layer, so you can ramp it up to a full RDBMS if you need to.
Oracle was making the claim that they could serve up data faster than flat files as of about 11g. I've never seen hard stats for real-world scenarios, though.
In this particular case, I'd tend to vote against properties files. I like to edit properties file with a text editor and editing a 10MB file with Windows WordPad isn't something you'll want to do twice. Notepad had a 64KB limit, so that one was right out. Even vi and Emacs don't handle that much text with a lot of agility.
Properties files are typically read-only or read-mostly and the Java support mechanisms for them reflect that usage. you have to load and unload the entire file, not just individual items.
Also, if the app is already using a database, the overhead in both programming and performance for keeping properties in a database becomes mostly moot, since you've already paid the entrance fee.
An IDE is no substitute for an Intelligent Developer.