| Author |
 Topic  |
|
|
tooba
Posting Yak Master
107 Posts |
 Posted - 11/27/2012 : 14:08:22
|
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
2868 Posts |
Posted - 11/27/2012 : 14:28:58
|
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 |
 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 11/27/2012 : 14:40:15
|
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. |
|
|
|
tooba
Posting Yak Master
107 Posts |
Posted - 11/27/2012 : 15:19:52
|
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... |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/27/2012 : 15:32:17
|
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 |
|
|
|
tooba
Posting Yak Master
107 Posts |
Posted - 11/27/2012 : 15:58:47
|
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'
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/27/2012 : 17:21:37
|
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 |
|
|
|
tooba
Posting Yak Master
107 Posts |
Posted - 11/27/2012 : 18:30:24
|
| I really appreciate to clear my doubt! |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/27/2012 : 18:37:12
|
You're welcome!
Jim
Everyday I learn something that somebody else already knew |
|
|
| |
Topic |
|
Table Partition Help
