Delta Virtual Airlines Issue Report #4

DEVELOPMENT ISSUE #4 - Water Cooler Thread/Channel Lists slow
Reported by	Luke Kolin (DVA043) on 06/30/2005 23:08 ET
Assigned To	Luke Kolin (DVA043)
Issue Status	Fixed on 07/05/2005 00:02 ET
Issue Priority	Medium
Airlines	Air France Virtual, Delta Virtual Airlines
Security	Public
Area	Web Site
Issue Type	Enhancement
Target Version	1.0
Issue Description	This is probably because we are doing so many joins. See if you can store the views/post counts in the COOLER_THREADS table. When doing a new point, update the COOLER_THREADS table with a SELECT COUNT(*) FROM COOLER_POSTS WHERE THREADID=%id% so that each new response resets things. It's not entirely normalized, but it works well in Domino, and here you actually have locking. Investigate locking the tables so that you don't screw things up, but that might be overkill. Make sure you can unlock the table if the transaction needs to be rolled back. Does ROLLBACK() automatically release any locks?
4 ISSUE COMMENTS
Luke Kolin (DVA043) 07/03/2005 12:35 ET	I've updated the table schema to store data like the Domino database. This should dramatically simplify the SELECTs by reducing the number of joins from 3 or 4 to 1 or zero. When I reimport the data to golgotha from polaris, I'll need to redo the cooler. An idea - only purge the cooler.
Luke Kolin (DVA043) 07/03/2005 18:41 ET	I have updated the Water Cooler on polaris, and modified the DAOs to support the new schema. Now I need to create a new command to manually force a resync of a thread.
Luke Kolin (DVA043) 07/03/2005 19:15 ET	Got it running on polaris and it's a LOT faster. I'll dump the Cooler tonight and reimport.
Luke Kolin (DVA043) 07/04/2005 17:33 ET	Got the Cooler reimported and thread lists seem to be faster. I've also added an index on stickydate. We will still need to update the channel list query to pull the last update author/post info out of the threads, so we can avoid a join on COOLER_POSTS.

For flight simulation purposes only. Not a commercial website. In no way are we affiliated with Delta Air Lines, its affiliates, or any other airline. All logos, images, and trademarks remain the property of their respective owners. Delta Virtual Airlines is a non-profit entity engaged in providing an avenue for flight simulation enthusiasts.