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.ex:rank_3425rank_3426etc.

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.

Starting issues: 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:

CREATE VIEW [dbo].[vw_rank_all_part_1] AS SELECT 1 as period, * FROM dbo.rank_3425UNION ALL SELECT 2 as period, * FROM dbo.rank_3426....

If I query the vw_rank_all table and provide period as a static value like so:

SELECT count(*)FROMdbo.vw_rank_all whereperiod = 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 intASBEGIN

SELECT top 100*FROMdbo.vw_rank_all whereperiod = @periodEND

The plan looks ok, and partition elimination is happening. But.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?