Amazon Interview Question
Needless to say that 5 Reads should use only one single connection. If the database server runs on a single cpu box, it will still takes 35 seconds. So the result should be cached if the data do not change that often. Two levels of caching can be used. They are in memory and on disk. Well it becomes very complicated if it is in a clustered enviroment and cache coherence is a must.
Several ways to improve performance...
No connectors are specified, so no assumption can be made about the type of connection(s).
1) DB indexing on most frequent tables(tuning)
2) Cache results on app server
3) Cache results on web server
4) Cache results on user-machine(if small, use cookies).
5) Use AJAX for asynchronous loading
Needless to say that 5 Reads should use only one single connection. If the database server runs on a single cpu box, it will still takes 35 seconds. So the result should be cached if the data do not change that often. Two levels of caching can be used. They are in memory and on disk. Well it becomes very complicated if it is in a clustered enviroment and cache coherence is a must.
1. Optimize the database reads and reduce the time from 7 secs to lesser
2. Do lazy reading where by you read only the minimum amount of data and so reduce the number of database reads.
3. Denormalize your data and so that you dont do multiple joins
4. Build a cache and load the data eagerly.
5. Use AJAX like mechanism to load in the background what is not critical.
6. Use Materialized Views
Need clarification on these 'reads' :
- These reads are basically sql queries ? (JDBC statements)
- are these reads related ?
- is each read by itself optimized ? .. as in 7 sec is unavoidable ? or query to be tuned
Two levels of tuning :
1. See if each read can be optimized independently . SQL tuning check explain plan, total cost,FTS, buffer gets etc. Also does each read leak any connection object
Can there be caching to cater for each read
2. Can reads be combined to avoid multiple calls. This has a trade off. If each read gets lot of data in memory, combining may cause memory hog. Otherwise better to combine
Need clarification on these 'reads' :
1 These reads are basically sql queries ? (JDBC statements)
2 are these reads related ?
3 is each read by itself optimized ? .. as in 7 sec is unavoidable ? or query to be tuned
Two levels of tuning :
1. See if each read can be optimized independently . SQL tuning check explain plan, total cost,FTS, buffer gets etc. Also does each read leak any connection object
Can there be caching to cater for each read
2. Can reads be combined to avoid multiple calls. This has a trade off. If each read gets lot of data in memory, combining may cause memory hog. Otherwise better to combine
For the database part:
- WHIZKID November 28, 2005This seems more like db tuning excercise, but I might first check that if I can do all the 5 calls to db in a single connection instead opening and closing db connection everytime I need it. This will reduce the time way below 35 seconds.
If possible materialized views can also be of some help.