Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
robbie79
Starting Member
4 Posts |
 Posted - 2004-11-24 : 14:57:38
|
| I realise this has been posted before but I couldnt see an answer that resolved the issue I am seeing.I have a table >10000000 rows so tried a partitioned view. For testing the sub tables were placed in a seperate DB on the same server.The table contains;customer_id (int), date, product_id (int)..other columnsThe primary key of the table is on cust_id, date and product_id so this is the clustered unique group.There are also non_clustered on each of the columns individually.Queries on the table typically look only at one cust_id then search across dates etc.So I created 10 sub tables of similar size using a 'check' on cust_id e.g. CHECK cust_ID between 1 and 100. Each sub table had the same indexing as the parent table.To test this I ran the following against the original table and the view:SELECT * FROM table WHERE cust_ID = x AND date = 'y'I find this is slower with the partioned view than the original table!! There is a >2% scan cost for each sub table - this seems high why is so long spent checking inappropriate tables.Although I dont see why could it be reated to the primary key having to be across three columns rather than just the partitioning column?Any suggestions/assictance really really appreciated! |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-11-24 : 15:07:59
|
| Have you tried to make more "sub tables"?If you have just ten tables, that might not be enough to see the gains of Partitioned views.Also, how many rows per table? If one table has more than it's share of rows, selecting from that table will slow you down.Your select statement above, did it cause a Clustered Index Seek or Scan?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
robbie79
Starting Member
4 Posts |
Posted - 2004-11-25 : 05:16:18
|
| Yes it performs a clustered index seek using the primary key set.I have tried creating 160 tables (1 per cust_id) and although the seek cost of each table goes down the total is still higher.I have since tried splitting a smaller table into 3 and again the table scan on tables not containing the desired cust_id is high. To check I queried the partitioned view the original table and one of the sub tables. The query on the sub table and the original table had the same cost but the partitioned view was significantly slower than both.Although I did this on a small table I cant see how I will get a performance benefit from a partitioned view while it will has to seek across all the member tables. The original table is fairly narrow could it be the (large) original table is still not large enough to benefit from partitioning?? |
|
|
|
robbie79
Starting Member
4 Posts |
Posted - 2004-11-25 : 05:19:35
|
| Just had another thought. Given the number of cust_ids is low and so rows per id is high does this reduce the effectiveness of the parition - though the way this table is queried this is the only column that makes sense to partition on?Thanks. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-11-26 : 15:52:50
|
| Do you always search by date as well? If so, partition on date. I've had great success partitioning on month. I read an article that showed how to partition on date, but by using an INT (4) instead of a DateTime(8) It made things faster since the field was half as large and thus the index was smaller etc. I create a DateKey field which was in the format at YYYYMMDDso, today would be 20041126, which is an int, but that can be easily converted to a DateTime.I'd try partitioning on year / month. You may have to put Date as the first thing in your where clause to make it seek things using the index.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-26 : 23:27:17
|
| Probably daft question, but does your view adhere to the requirements of partitioned VIEW, which (if I've cut&pasted all the right bits!) are:Each base table has a partitioning column whose key values are enforced by CHECK constraints.The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table.Any given value of the partitioning column must map to only one table.The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.The partitioning column cannot be an identity, default or timestamp column.The partitioning column must be in the same ordinal location in the select list of each SELECT statement in the view.Partitioning columns cannot allow nulls.Partitioning columns must be a part of the primary key of the table.Partitioning columns cannot be computed columns.There must be only one constraint on the partitioning column.There are no restrictions on the updatability of the partitioning columns. and you are using UNION ALL (rather than UNION)Is there something about using SCHEMABINDING too?Kristen |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-11-27 : 05:17:56
|
| Yes, if things are not PERFECT, it will SCAN all of the tables, giving you much worse performance than if you had it all in one table.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
robbie79
Starting Member
4 Posts |
Posted - 2004-11-29 : 13:39:24
|
| I've been looking around for other info on this and found some stuff seen on partitions on tables with to compound primary keys.The partition is on 'int' values and yet maybe checked with to smallint vlaues (or tinyint etc) and it seems this causes the problem when SQL creates the execution plan.I found the partitioned view behaved as expected if I performed the followingselect * from partitioned_View where cust_id = CAST(int AS 371) A bit more hunting around found the following articlehttp://support.microsoft.com/?scid=http%3a%2f%2fsupport.microsoft.com%2fservicedesks%2fbin%2fkbsearch.asp%3farticle%3d308643which suggests the problem I was having was the result of a known issue. This seems to resolve, describe the problem I was seeing.Thanks to everyone for their assistance please let me know if this article isnt describing the problem I was having. |
|
|
|
|
|
|
|
|
Slow partitioned view (again)...