So I've inherited a SQL Server 2005 DB with a whole bunch of tables in the following format:
rank_xxxx, where xxxx is a date identifier.
Then they have stored procedures that use dynamic SQL to generate queries that will access the correct "partition". It's an awful mess.
Because I can't change the tables, I thought that a quick way to gain some performance improvements would be to create a partitioned view of these tables. In that way we could simplify the queries and speed things up.
1. There isn't a useful partitioning key available in these tables...
2. There are a lot of these rank_xxxx tables. And there is that 256 table limit...
What I did was create some subviews in this format:
SELECT 1 as period, * FROM dbo.rank_3425
SELECT 2 as period, * FROM dbo.rank_3426
So period is set up to be the partition key.
And then I create a master view like this:
SELECT * FROM vw_rank_all_part_1
SELECT * FROM vw_rank_all_part_2
This seems to work. Sort of.
If I query the vw_rank_all table and provide period as a static value like so:
period = 400
And then check my plan, I can see that partition elimination is working. It finds the base table and only looks at that one. So far so good.
The problem occurs when I drop the view into the stored procedure:
CREATE PROCEDURE [dbo].[a_test_rankpartitoning]
period = @period
The plan looks ok, and partition elimination is happening.
The compile time on first run is massive compared to the execution time. In the above SP it's not a huge amount, but when I use the view in a more complicated SP it drives the compile time through the roof (resulting in a total time of 50s on first run and then 2s on subsequent runs).
Any idea why this is happening? Is it just a side affect of using partitioned views? Is it because of my partitioning key? Any way to avoid this?
Thanks in advance for any comments.