When the 'Show' Button is pressed, the system performs a query to obtain a list of diary ID's that have entries within the timescale you asked for. Presently its finding around 220 or so, just for a day (out of around 21,000). It also gets the diary title and user name and such like.
Once it had that information, it then went and queried the database (for those SQL bods amongst you, using a large IN statement, ie where bert IN ( x, y, z)) to get the number of entries. This method worked fairly well when we were only getting 100 updates in a day, and there were less than 50,000 entries etc etc. You get the idea. Basically it worked OK when the database was small. But lately that method has been taking around 20 to 30 seconds to complete, EVERY TIME someone clicked the 'Show' button. Somewhat unnacceptable really... Of course, while the database is fetching this information, no-one can update the entries table (if they try, it locks the table), resulting in queries queueing up behind each other until eventually it hits the maximum number of outstanding requests and you get the dreaded 'Sorry we are too busy' message.
I have now changed the system so that when you add an entry to your diary, it recalculates how many entries you have and updates a seperate 'lookup' table. The 'Show' screen simply fetches the stored entry count and displays it. The result is that the query which took 30 seconds on a good day, now takes about 0.03 seconds. Mucho faster. There doesn't appear to be a noticeable slowdown in the adding of entries either, so its a win all round. The only downer is that until that table is properly populated, some diaries will show 0 entries, incorrectly. Also, the count is updated ONLY when you ADD entries, so if you delete any then the count will be incorrect until you next add an entry.
But hopefully, that should cure a couple of problems in one swoop :-)
Steve.