Perhaps I should explain more about how my rewrite sans JOINs works: The reports read from a log table with this schema: PK | ref_PK1 | ref_PK2 | ref_PK3 | start_time | stop_time
Where ref_PK# is the primary key of another table. These 3 other tables are very shallow (row count < 100). The log table is very deep (row count > 100,000). The web app, when started, reads the 3 shallow tables into memory (3 hash maps). (And btw: the database code is NOT intertwined with the Serlvet code, thank you very much :) To run a report: the user enters the start and stop times, along with some other options. The report sends the options to db accessor class which runs a query that only references the log table. The results (an array of Log objects) are rendered as an HTML table (it also does Excel). The ref_PK# values are matched against their respective hashmaps to produce human readable data. This approach proved much faster than using the JOINs. I'm not saying that there is no place for using JOINs, just that you need to think about where to place the processing load. Our database is very busy with writes, all of which have priority over reads, hence the desire to move as much load off of it as possible. Furthermore, the data in 3 shallow tables doesn't change once inserted. But in other projects where I employ caching, the caches Timeout after certain periods of time. Also these other caches load incremently (as needed) instead of preloading, which is what the report app does. Finally, I am very busy with other responisibilites at my job, and do not have time to dig into heiroglyphics of database optimization which is very vender specific and as such further binds your organization to that vendor. thanks On Fri, Jan 23, 2009 at 12:52 PM, Christopher Schultz < ch...@christopherschultz.net> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Chris, > > Chris Wareham wrote: > > By the way, if it's not static data your caching, > > how's your messaging system? Without one how are you going to maintain > > the integrity of your caches? Even with one, can you tolerate a race > > condition between the data being modified in the database and the > > notifications causing all your caches to be updated? > > Er... write-through cache? Presumably, Leon is talking about a one-box > wonder. > > Before everyone starts yelling about how stupid everyone else's ideas > are, maybe we should just let this one go in the interests of civility. > > - -chris > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.9 (MingW32) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iEYEARECAAYFAkl6A+UACgkQ9CaO5/Lv0PCWdACdH0d9og7mrxwSzfZyn5hmzzo+ > 9AsAoKmNOfAlMiX6jkRcbUof72goMM9T > =nOJX > -----END PGP SIGNATURE----- > > --------------------------------------------------------------------- > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > >
