Hi
I had 2 tables in Cache in web server. The size of tables is considerable huge. Each time to retrieve data I have to do a join on these 2 tables.
Other option I have is have nothing in cache and making a db call each time that will do a join on the db tables.
So what I want to know is doing a join on webserver is it costlier than making a db call if performance is on high priority. Suggestions please....
I would suggest that you create a view with the join and store the view in memory. In MySQL this is done by using the engine called MEMORY. This should give you the best of both worlds as you still have a transactional system behind your calls which ensures the consistency of your data and you have high performance as the view is being maintained in memory, thus giving really good response times.
Another very important advantage you get from creating the view is optimized joins which you would need to program yourself on the web-server. However, this advantage is only relevant if your joins reach a certain degree of complexity.
The simple rule is Let the database be the database
Databases are designed to do joins. They are written by very clever people who spend all their time worrying about such things. There are many ways that you can optimise the database to join faster.
If you have done that and you are still having performance issues, then look at optimisation but look at the whole system, as it is highly unlikely that you will be able to do the databases job better than the database.