Partitioning for performance

Question

I have a table with following columns:

Place .......................... varchar(60)

PumpStatus ............... varchar(15)

PumpStatus holds values: Success and Failure. 

I execute a query to display Pump Status grouped by Place at regular intervals. This query displays real-time information.

My worry is that new records are continuously being entered and there are about 2.5 million records. At present there are only 13000 records but it will soon reach 2.5 million.

I want to speed up this real-time information performance. Is there a way to create partition on Place and PumpStatus so that the partition automatically gets updated as new records are entered or old ones updated?

Answer 1

How many distinct values does pumpstatus has .I presume quite less .So it does not make sense to have it partitioned.Place can be a a paritition candidate and looks like more unique than the pumpststus .But Partitioning will only increase performance in terms of Index maintenance and partition level Lock escalation (in 2008)  ...Apart from that you need to make sure that there are proper indexes and the SPs hitting this tables are well tuned ..

Do you have only these 2 columns in a table ? If yes then I doubt if this is a good design ..

Regards

---

Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/

Answer 2

There are many columns but the query I mentioned is using those two columns. I have indexes on both the columns. 

There are a total 16 places and all the records are for any of these places.

Answer 3

I execute a query to display Pump Status grouped by Place at regular intervals. This query displays real-time information.

I agree with Abhay that indexes rather than partitioning may better address performance objectives.

Can you post your actual query? My understanding is that you have 16 distinct Place values and 2 distinct PumpStatus values and group by Place.  How is PumpStatus used?  Is it returned using an aggregate function like MIN or MAX?

You might consider a composite index on Place and PumpStatus to improve aggregations on those columns.  Perhaps index views are appropriate.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

My worry is that new records are continuously being entered and there are about 2.5 million records. At present there are only 13000 records but it will soon reach 2.5 million.

Let's put this as good news and bad news.

The good news is that, assuming your system is current hardware and moderately loaded, it should be able to handle 2.5m row calculations pretty quickly.  Recalculating it every five seconds or so will keep it loaded in buffers.   Check your CPU usage with set statistics or profiler to see what your real cost is.

The bad news is, this isn't really something you want to do.  If you want to display an aggregate in real time, then CALCULATE it in real time - have each transaction that writes a row, add or subtract to a row in a total table!  Or, I suppose, you could do that with a trigger.  That's if you want to let the database do this at all, frankly it might be better yet to have some middle-tier component keep the running total for you outside the database.

I can't see partitioning helping at all here, unless as some side-effect of partitioned indexing already knowing their total count or something.  I really think the middle-tier solution is better architecture. 

Josh