| Author |
Topic |
|
chih
Posting Yak Master
154 Posts |
 Posted - 2007-08-27 : 08:57:54
|
| Hi everyone,I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?Can you please give some suggestions? It will be very helpful if you can post some references or examples.Thank you! |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-27 : 11:35:10
|
| 1 mill (or even few mill rows) is nothing for SQL server, if you have the right indexes. We have over 900 mill rows in over half a dozen tables and did not need to partition yet. I would look into indexes first before moving on to Partitions.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
chih
Posting Yak Master
154 Posts |
Posted - 2007-08-27 : 19:41:57
|
We have couple tables having more than 150 million rows. Due to some query we need to use like '% %' statement, it will slow the speed even setting the index.Any good suggestion for this situation?quote:
Originally posted by dinakar
1 mill (or even few mill rows) is nothing for SQL server, if you have the right indexes. We have over 900 mill rows in over half a dozen tables and did not need to partition yet. I would look into indexes first before moving on to Partitions.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
|
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-27 : 19:48:47
|
| Your best bet is to convince your Mgmt that no matter what your queries will be slo wunless you put in some filters other than wild card search. At least few starting characters. like.LIKE 'A%' will be better than LIKE '%A%'.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-27 : 21:30:50
|
| Tried with full text index? |
|
|
|
chih
Posting Yak Master
154 Posts |
Posted - 2007-08-28 : 00:19:32
|
| So normally the best practice to deal with large table will be using Index. how about if table row has more than 150 million? any suggestion? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-28 : 00:45:20
|
| Indexing is not just for large tables. Did you ever check full text index? |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-08-28 : 01:28:02
|
| Hi ,We have a huge table with around 250 million records and have implemented SQL server 2005's new table partitioning feature. Now the data seems to be evenly spread across 20 different filegroups ( each 5 GB approx ) for the same table that was occupying 100 GB itself in the PRIMARY filegroup earlier.Still the query response times have not come down drastically but we could see a good improvement in the execution plans now.Any thoughts / suggestions would be appreciated.Thanks,Hariarul |
|
|
|
chih
Posting Yak Master
154 Posts |
Posted - 2007-08-28 : 19:24:47
|
Hi Rmiao,Thanks for the reply. I will test full text index and see how it goes.One more question. If I only perform normal select statement,what is the best practice for dealing with large table? We are thinking about1. seperate one table into several tables2. table partition.Are there other methods? What are advantages and disadvantages? Is that necessary to do it for the table has about 150million rows?Thanksquote:
Originally posted by rmiao
Indexing is not just for large tables. Did you ever check full text index?
|
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-28 : 19:33:19
|
| Dont do partition just because the table has 150 mill rows. Do it if it makes sense. You can have tables with over 5-600 mill rows working perfectly fine, if you have the right indexes and your queries are properly optimized.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-08-28 : 19:35:26
|
| Here's an excellent article by Kimberly Trip on Partitioning: http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htmDinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-28 : 21:00:39
|
| chih, it depends on queries you run against the table. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 07:41:08
|
The only things that I know that will makeLIKE '%A%'faster are:1) Don't do it  2) Use Free Text Search instead3) Use carefully constructed parametrised dynamic SQL to maintain appropriately cached query plans - e.g. using sp_ExecuteSQL4) Partition but only query ONE partitioned tablei.e. instead of doing SELECT ... FROM MyBigTable WHERE MyCol1 LIKE '%A%'you doSELECT ... FROM MyPartitionOneTable WHERE MyCol1 LIKE '%A%'and to do that you will have to KNOW that the user's data is in MyPartitionOneTable - and not MyPartitionTwoTable etc - in which case you will know something else, such as the Date, and in that case SQL Server will be fast enough with your original MyBigTable and an index on the Date Column.Partitioning is good if you have lots of date-related data, such as accounts transactions, and most of the queries happen in, say, the current month; but you want all the historical transactions available too.OTOH partitioning means that you have to create new partition tables frequent, move the now-stale data into the new table, and so on.Kristen |
|
|
|
|
Large table-Table Partition, View or other method?