Partitioned View Performance Question | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Partitioned View Performance Question

I’m fairly new to partitioned views, but am implementing one in test to see if it would be a worthy candidate for production. The test server is basically a workstation with 2 drives, one for data, one for logs. There is a fairly large table (87M rows) and a partitioned view written against multiple tables with columns matching those in the 87M row table. To mirror a typical day in production, I needed to insert about 2.5M rows into each (the big table, and the view). However, when doing so, I didn’t get the performance increase I’d expected. Inserting the 2.5M records into the 87M row table took approximately 6.5 minutes. The insert into the view took approximately 2 hours and 52 minutes. This seems absurd. The data added is heavily based on one particular date (ie; most of the data will be for one date, with maybe 1% of the data being of other dates). The tables behind the view are broken up by date. So, most of the data would have went into one table, with a small percentage going into other tables. Default Fill Factors were used, no out of the ordinary tuning done anywhere. There’s an index on the date field in the 87M row table, but that’s about it. I’m confused, any ideas?

I don’t think you can expect any performance improvement in your case. Partitioned views are best when underlying tables are distributed across multiple disk subsystems and under an SMP environment where reads/writes to each table can be processesd parallely. However, since 99% of the writes are going to the same table, I would assume it should be taking almost the same time as the inserts to the original table. Try inserting directly into the underlying table and see if the problem is in the view definition.
Good recomendation, Vkchakri.
I´m not sure yet, about same time inserting using views or not. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Thanks for the replies…
quote: don’t think you can expect any performance improvement in your case. Partitioned views are best when underlying tables are distributed across multiple disk subsystems and under an SMP environment where reads/writes to each table can be processesd parallely.

I don’t fully agree with that though. Think about it. Given that everything is apples to apples, meaning it’s all on the same drives, I’m either adding records to a table with 80M+ rows, or adding records to a table with at most 20M+ rows. The problem is, it’s checking all the tables, as opposed to realizing via the check constraint that it should only check particular tables. My question is, why is it doing that? When quering the view I have to add "top 100 percent" to the query to get it to only check the relevent tables because SQL Server has some sort of bug that requires me to do that. I’m wondering if there’s a comparable bug on the insert side.

If you have structured your table properly, viz. clustered index on the proper column and proper fill factor to avoid any page splits etc., I don’t think you see any noticeable difference inserting rows to an 80M table versus 20M rows. And sorry, I don’t have an answer for why it is checking all the tables. In fact, I did work on a project for partitioned views and later scrapped that, because it didn’t give me the benifits that I was looking for.
Note that I’m not being sarcastic here… I think the tables and views have to have been set up correctly, or I wouldn’t be able to do an insert through the view.
]]>