Live and Learn

So I have been watching the site really closely today and the performance has just been terrible. I went ahead and turned off the Bragging Rights stats, and was almost amazed at the performance jump. It makes sense... those stats do three queries for every data hit... and since the data rarely changes, it doesn't make sense to do it that way.

So I will be building a cached model for that in my spare time. Hopefully I can get those stats back up and running in the next few days. For now, they are off line until I can complete the work.

I also noticed that the "watch lists" were running for every user, even when users didn't have an entry in the watch list table... so I put in a little hack to solve that. I will be working on that data model too over the next few days to make it perform better.

Comments

[Captain Betty]

Are you guys using stored procedures? That may be more efficient.

[Trapper Markelz]

Hi Captain... we are actually not using any stored procedures... most of our site is service driven... so all of the SQL is in the service tier... We then transform those service outputs using XSL... they are many of the same services that are published as our API... however we have a bunch of unpublished ones we use as well.

The trade off for stored procs performance wise really depends on what you are doing and honestly we are only doing a lot of simple selects... we aren't doing many data heavy things that require multiple queries, temp tables and such... where a compiled stored proc would get you a performance gain.

But you are right, I haven't looked at using stored procs in mySQL for sometime... maybe I should revisit it.