July 24, 2008 at 10:10 am
Hello All,
I am having some issues with performance on some of my tables and I was wondering if there was any advice for how to handle the problems that I am experiencing.
I have tables, Reports, ReportsGrades, ReportsComments (there are more but you can just expand the given example)
Report holds information about students (name, year etc) and ReportsGrades holds the grade written on the given report.
Within the Report table the information is broken into cycles, so there may be 3 cycles per year - where each student will have a report written.
The original design that I inherited(!) was that all the data is stored in the one table - and when displaying the front end we are now required to show quite a lot of information that usually is counting various information such as how many green reports have been written for a specific student in cycle 5.
Some of our clients have now been using our product for quite a while and have accumulated a lot of information, I would hazard calling it a large table with in the largest ~5,000,000 rows.
I have added indexes and written all sql statements into stored procedures - but sometimes I am still waiting 8-12 seconds - my target is < 1s if possible.
The module is now finished and we are faced with potentially taking features out (the lesser of two evils) as everyone experiences the slow loads where as about (30% of clients) will complain about the features being lost.
We are now speccing V3 of this module, and I am looking at partitioning the tables (SQL 2005 Ent) - would this be advantageous?, is there any way that we can effectively tell sql that we are only interested in data with cycleID IN (5,6,7,8) or similar? (bare in mind we can guarantee 2005 standard at the mo)
Kind Regards,
Scott
July 24, 2008 at 11:47 am
Partitioning might help in this situation. What I'd really recommend, as a full solution, is hire someone (a contractor) to come in, look at the database, run some performance traces, etc., and recommend modifications. In the long-run, that'll be cheaper and more effective than trying to half-guess through a web forum.
There are good, skilled people who do that kind of work. (I don't know any personally, but I run into them pretty regularly on this forum.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 2:53 am
Certainly partitioning table will help you in this regard but don;t forget to partition your indexes too.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply