Qerying several times.
I need to have different product information from multiple tables for different categories in the server cache or I need to fetch from Database every time. What will be the better way.
Solution 1: I can query upfront from DB and can crate objects in the server(10MB data). Over heads are these created objects takes lot of JVM memory and another one is at initial load going to connect/disconnect(db connections) DB several hundred times. Advantage is whenever I need certain information I can look for the object and can process things fast.
Solution 2: I can run the queries on demand, whenever needed. If transactions grows, performance goes down drastically. Because of querying DB each and every time.
Another though solution is using entity beans; appreciates your input on this.
[813 byte] By [
KVKVKV] at [2007-9-30 22:45:29]
I've used both methods, even in the same program. Depends on the details.
If the data can change (i.e. there are other programs updating the data concurrently), you need to keep it in the database. Must use solution 2.
If there are so many accesses that the database isn't fast enough, making the application totally unusable, you need to cache the data. Must use solution 1.
You could end up caching the most frequently used tables, while getting the rest from the database as needed.
Another trick I've used for read-only data is a least recently used cache in memory. Hashtable + timestamps + loader interface - a pleasant afternoon's programming exercise. Would probably work well with e.g. this forum: keep the few hundred least recently accessed articles in memory.
Is 10 MB a lot of memory? Depends on the application: if you are loading the product catalog of an online shop into the web server's memory, you can easily convince yourself that it is good use of your 500 MB memory budget. But for a small desktop application, 10 MB won't make your users happy.
What kind of data access is usual? Let's say users search products by name. How long does "select id from product where name like '%widget%'" take? How many accesses per second do you expect? How many $M will a db server with enough oomph end up costing?
> connect/disconnect DB several hundred times
You need to use a database connection pool; it'll take care of this.
Yes, I have very complicated queries. Combination of many things such as history of the product, ratings, ...etc., Thanks for sharing your ideas. However Instead keeping objects in the session SOLUTION1, I believe better to use Entity Beans, so that Application Server Container controls beans which are recently used in the application and dbconnections. What do you think?
> Yes, I have very complicated queries. Given only that requirement then the database solution is best.Without the database you would have to write your own query engine.
Hi,
If you are following Solution 1.
1) It the DB size is huge , you cannot cache in the memory.
If you are following Solution 2.
2) As you are doing only selects what ever be the size of the DB, if the query is properly tuned and if the table is indexed, then the query would execute in no time. Always use prepareStatement , it is much faster and if you are going for Vendor specific then you can write StoredProcedures where you can execute more than one query at a time and in no time.
I worked in a environment, where the data use to grow huge by hundreds of Mbs per day.
Think about this.
Ratan at 2007-7-7 13:15:21 >
Good Direction, Thank you Rathan, Do you agree if I use Entity beans will be more efficient instead of Solution1 & 2.
> Hi,
>
> If you are following Solution 1.
>
> 1) It the DB size is huge , you cannot cache in the
> memory.
Of course it depends on the size, but you can cache very large databases in memory.
Prevalyer is basically based on that exact idea... http://www.prevayler.org/wiki.jsp
If you search this forum you will find at least one mention of using 16 gig of memory with a cached data store.
> If you are following Solution 2.
> 2) As you are doing only selects what ever be the
> size of the DB, if the query is properly tuned and if
> the table is indexed, then the query would execute in
> no time. Always use prepareStatement , it is much
> faster and if you are going for Vendor specific then
> you can write StoredProcedures where you can execute
> more than one query at a time and in no time.
>
Technically for single requests, prepared statements are always slower. The only time that isn't true is if the database doesn't support prepared statements at all. There are of course other reasons for using prepared statements.
You can search the forum for benchmarks that demonstrate this.
Most systems will end up using single queries, even when the query is repeated due to the overhead and complexity of keeping and maintaining prepared statements. Normally the case where this isn't true is when processing a batch (many similar rows at once.)
But the other benefits of using prepared statements will offset the performance difference for most situations.
