I have a table that has grown to over 1 million records... today (all valid)
I need to speed it up... would Table Partitioning be the answer? If so can i get some help on building the query?
The table has 4 bigint value keys and thats all with a primary key indexed and a index desc on userid the other values are at max 139 (there is just over 10,000 users now)
Any help or direction would be appreciated :)
you probably just need to tune your queries and/or indexes. 1 million records shouldn't be causing you problems. I have a table with several hundred million records & am able to maintain pretty high performance. I have found the SQL Server profiler to be pretty helpful with this stuff. It's available in SQL Server Management Studio (but not the express version, unfortunately). You can also do Query > Include Actual Execution Plan to see a diagram of where time is being spent during the query.
You should investigate your indexes and query workload before thinking about partitioning. If you have done a large number of inserts, your clustered index may be fragmented.
Even though you are using SQL Server Express you can still profile using this free tool: Profiler for Microsoft SQL Server 2005/2008 Express Edition
I agree with the other comments. With a reasonably small database (largest table 1MM records) it's unlikely that any activity in the database should provide a noticeable load if queries are optimized and the rest of the code isn't abusing the database with redundant queries. It's a good opportunity to get a feeling for the interplay between database queries and the rest of the code.
See my experiments on sql table partitioning here [http://faiz.kera.la/2009/08/02/does-partitioning-improve-performance-for-sql-tables/]. Hope this is helpful for you... And for your case, 1M is not a considerable figure. May be you need to fine tune the queries than going for partitioning.