SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Table Partition Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

165 Posts

Posted - 11/27/2012 :  14:08:22  Show Profile  Reply with Quote
Hi guys,

I have questions. Please advice...
I have one table that has 6million rows.For good performance i am wondering if i do table Partition.
My understanding is (Please correct me if i am wrong) when we do table partitioins
TABLE1(ORIGINAL TABLE) = ONE YEAR OLD DATA
TABLE2 (CREATE AFTER PARTITION) = REST OF THE DATA

and if i want to use below statement that is NOT SURE IN WHICH TABLE

SELECT * FROM TABLENAME
WHERE DATE = '2010/10/2'

Did above statement going to scan table1 or table2 or i have to mention which table going to sear afer table partition.

Please let me know if my questin is not clear.

Thank you in advance.


jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/27/2012 :  14:28:58  Show Profile  Reply with Quote
That's not partitioning, that's archiving. If you partition a table, you tell sql that based on some function, put the rows that are captured by that function somewhere else where they don't bother anybody, but are still accessible to you.
For example, put the data that is <= '20101002' on a different filegroup because I rarely query it, but it's still the same table name

SELECT *
FROM yourTable
WHERE date > '20101002'

will work, but so will
SELECT *
FROM yourTable
WHERE date <= '20101002'


Jim

And 6 million rows isn't all that big, I'd look at indexing and query design for a performance boost first.


Everyday I learn something that somebody else already knew
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/27/2012 :  14:40:15  Show Profile  Reply with Quote
quote:
Originally posted by tooba

Hi guys,

I have questions. Please advice...
I have one table that has 6million rows.For good performance i am wondering if i do table Partition.
My understanding is (Please correct me if i am wrong) when we do table partitioins
TABLE1(ORIGINAL TABLE) = ONE YEAR OLD DATA
TABLE2 (CREATE AFTER PARTITION) = REST OF THE DATA

and if i want to use below statement that is NOT SURE IN WHICH TABLE

SELECT * FROM TABLENAME
WHERE DATE = '2010/10/2'

Did above statement going to scan table1 or table2 or i have to mention which table going to sear afer table partition.

Please let me know if my questin is not clear.

Thank you in advance.







Are you talking about Horizontal or Vertical Partitioning ...well you can do that but there is lot maintenance for it. As Jimf said, 6 million table is very small for Table Partitioning.
Go to Top of Page

tooba
Posting Yak Master

165 Posts

Posted - 11/27/2012 :  15:19:52  Show Profile  Reply with Quote
I am sorry it was typo mistake it was 30 million rows. I think i will try what Jimf said,
Jim could you please show me right path/tutorial where i can try Table Partiotining?

Thank in advance...
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/27/2012 :  15:32:17  Show Profile  Reply with Quote
30 million records isn't all that many either. I'd try a filtered index first
CREATE INDEX StuffIuse ON dbo.myTable
WHERE DATE >= '20101002'

BooksOnLine is the best place to start learning about partitioning
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/29946a77-345a-4527-967a-5fb598947122.htm

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

tooba
Posting Yak Master

165 Posts

Posted - 11/27/2012 :  15:58:47  Show Profile  Reply with Quote
Thank Jim, Quick question. Just want to clarefy
I have table TABLE1 has 30 m records and for sure already have some indexes (Clustered and non clustered index). If i understand right you are using 30m record is not many. Instead of Table Partition use Filtered Index.

If i use this query
CREATE INDEX StuffIuse ON dbo.myTable
WHERE DATE >= '20101002'

Its mean i am creating a filtered index on Date columns, right? and if use this query
SELECT * FROM TABLE1
WHERE DATE BETWEEN '20110101' AND '20110112' (Should be fast)

How about my query is
SELECT * FROM TABLE1
WHERE DATE BETWEEN '20090101' AND '20080101'


Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/27/2012 :  17:21:37  Show Profile  Reply with Quote
That's exactly right. I'm assuming you already have an index on date. There's no set rule for index creation, it all depends on what your query is. If you have queries that are mostly looking for data after '20101002' then a filtered index as above makes a lot of sense. If your queries have date ranges that are always a span of 1 year, then a filtered index for each year may make sense. The index basically tells SQL "Don't bother scanning thru 30 million records to find the right ones when you can find the right ones by looking at this index which is much, much smaller than the whole table".

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

tooba
Posting Yak Master

165 Posts

Posted - 11/27/2012 :  18:30:24  Show Profile  Reply with Quote
I really appreciate to clear my doubt!
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/27/2012 :  18:37:12  Show Profile  Reply with Quote
You're welcome!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000