SQL Server Performance Forum – Threads Archive
partitioned view
We have a table that has about 160 million rows. One of the fields is PAYMENT_DATE. We are trying to break it up into a table for each year based on the PAYMENT_DATE and have a partitioned view that has union alls that combine all tables. I made the check constraint on datepart(year,PAYMENT_DATE). I also put all of the same indexes on each of the tables that are on our huge table that we are trying to replace. I ran some queries using PAYMENT_DATE on the partitioned view and on the huge table and it is actually slower on the partitioned view, even though when I look at the query plan, it is only looking at the one table that it needs to. It seems to be working correctly, but it is slower than our current massive table. There has to be something that I’m doing wrong or some way to tweak it to make it faster. Anyone have a similar problem? ChrisDid you run Update Statistics before partition?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
No, I haven’t run that. Can I just do that now and it should improve the performance?
Yes, you can do that, but if query still slower, at least is one thing less to think.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Hi ya, you may need to post the query so that we can guess at what might be good candidates for indexes…? Since your big table was much uhm bigger, SQL would be less inclined to do table scans or even index scans, whereas now that the tables are much smaller it could be that SQL is opting to table scan or index scan rather than use what it might regard as a less than ideal index…? Cheers
Twan
Are all the involved tables will be local server or any remote (linked) server is involved? http://www.sql-server-performance.com/q&a82.asp for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
My constraints look like this:
([PAYMENT_DATE] >= ‘1/1/1996′ and [PAYMENT_DATE] <= ’12/31/1996’)
I have a table for each year.
I ran update stats. I also added PAYMENT_DATE to the primary key. I read in a microsoft article that the constraint column needed to be a part of the primary key. It’s still slower than my table that has 160 million rows. Each of the partitioned tables have about 13 million. They each have identical indexes as the larger table. Any suggestions? Chris
One thing, I would start with is to rewrite your constraint using an ANSI format for the date
([PAYMENT_DATE] >= ‘19960101’ and [PAYMENT_DATE] <= ‘19961231’) Another thing is to have a look to the execution plan.
You can see then if SQL Server is using the small table or the big table Med
]]>