Recreate table with partition

[Welshbird]

Morning all.
Whilst I have been writing queries and messing with SQL data for a while, I'm certainly no DBA...
But I find myself in a position where I don't have a DBA, so I'm going to need to head up a fairly steep learning curve.

I have a table which has data added once each month. The data is dfor a whole month each time and I import to a temporary table and then convert into the main data table (converting the text 'date' into a smalldatetime on the way).

So far - so good.

I have indexes on the fields I query by and join to other tables, but it is still slow to do anything.

There are just over 4 million rows each month (ish). It strikes me I might ought to be having partitions on this table - so how do I start?

I've googled, and it seems I must create new file groups? Do I relly need to start with a fresh database and a new partitioned table and import everything?

Am clearly clueless so any and all help much appreciated!


Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Am currently reading this article in the hope it makes sense!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Boo.

I've discovered that I don't have enterprise edition after all.

So please to ignore this question then!

Unless of course anyone has any other bright ideas to make this more efficient......

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[litton1]

I also am no dba.. So blind leading blind etc..
Have you checked to see if the indexes are fragmented?


Age is a consequence of experience

 

[gmmastros]

You could always go old school, the SQL 2000 method.

Basically, you create separate tables for your data (for example, each month could get it's own table). You then create a partitioned view to combine the data. When you run a query against the view, the SQL Query Optimizer will know that it can complete ignore some of the tables, which will make the query run quicker.

The problem with this approach is that you have to maintain the partitioned view each time you add another month's data.

Take a look at this page, starting at the section (to combine partitioned data):

http://msdn.microsoft.com/en-us/library/aa214282(v=sql.80).aspx

And also this page:

http://msdn.microsoft.com/en-us/library/aa933141(v=sql.80).aspx

The key here involves a check constraint on whatever you decide to partition the data on. For example, if you decide to create separate tables for each month, you will need to create a check constraint on the "date" column for:

MyDateColumn >= '20120701' And MyDateColumn < '20120801'

I'm curious, how many rows do you currently have in this table?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Welshbird]

At present about 49 million. But that will increase as I get more back-data so I will have 24 months (minimum) of over 4 million per month.

I'm not even sure that partitioning was the right way to think of this - as typically I'd be running a query that would look at every month each time, so having a partition for the month is probably pointless. (Am I right in that?)

As this is product based, should I have been thinking about way of partitioning by market segment or something similar? Queries would tend to be against specific segments rather than over the whole set.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[gmmastros]

The proper way to do this is based on your queries. If your queries are filtering on date, then partitioning on date makes sense. If you are filtering on something else, then that would be the appropriate thing to partition on. It all depends on the where clause.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLScholar]

Fee,

Few different ideas.

- Check your indexes and excecution plan. Whats the slow bit? Is there a missing index? Is there any recursion?
- If for instance you have a query that summises all the data for the each month, then create a table after the data populate that has the data at this level. Then query this table instead.

I was going to suggest indexed views - but apparently thats enterprise too....

I would post your table definition and example slow queries here to see if any of the gurus can make it faster.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[jrbarnett]

Fee

Filegroups let you split storage within a particular database across different volumes on the server, so you could have your temporary working area in a local DB, your main database on a fast SAN drive etc. They are not an Enterprise only feature (you can use them in standard edition).

With that volume of data and growth I would assume you are running a modern 64 bit server with Windows and SQL version with lots of RAM, powerful processors and large quantities of disk space and that this server is dedicated to hosting this database, not for running other things as well.

I would follow SQLScholar's advice in posting table definitions and SQL code - but would also look at estimated/actual execution plans and identify where the bottlenecks are.

John