I have a third party program that creates and populates a series of tables to hold some historical data:

data_33 -- contains data for 12/01/11 - 12/05/11
data_34 -- contains data for 12/05/11 - 12/14/11
data_35 -- contains data for 12/14/11 - 12/20/11
data_36 -- contains data for 12/20/11 - 12/22/11
data_37 -- contains data for 12/22/11 - 12/23/11
data_38 -- contains data for 12/24/11 - 12/28/11
data_39 -- contains data for 12/28/11 - 01/02/12
data_(you get the idea)

Unfortunately, while these tables are indexed, they are not created with a partitioning scheme or partition function so I'm left to manually iterate through the tables. Now I want to allow the user generation of reports, but I'm unsure of how to best write a stored procedure to query against these tables in an optimized manner.

My first idea was to just UNION ALL all the tables into one gigantic temporary table, but I'm guessing this is going to be costly as a single table has a large number of records and even doing a SELECT * FROM data_33 is taking about 10 minutes to resolve. On top of that, I'll have to maintain this (materialized) view as the software generates new tables.

My second idea was to programmatically UNION ALL everything by grabbing all the tables in the database matching the results of SELECT name FROM sys.tables WHERE name LIKE 'data_%' but without any other limit I'm afraid this would still be extremely slow.

What would be the best way to optimize presenting this mass of data in a somewhat optimized manner?

  • view / materialized view? – Eric Higgins Apr 30 '12 at 20:06
  • @EricHiggins Aye. A materialized view. – Greg Buehler Apr 30 '12 at 20:11
  • If you have frequently created new tables (which sounds like may be the case if your app is generating them), and if the dataset is big, it may not be worth materializing. So with what you've said I'd do a view or view + material. Other factors (outer joins=likely) may prevent you from going the materialized route. – Eric Higgins Apr 30 '12 at 20:14
  • @EricHiggins Outer joins are not likely, but calculated columns are (ie (ABS(value - @rangevalue)) as Delta)). All together I'm looking at ~14 million rows. – Greg Buehler Apr 30 '12 at 20:25

Before table partitioning was introduced the way of doing the very thing you asked was called Partitioned Views:

A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers, making the data appear as if from one table.

Partitioned Views are not only a UNION ALL over each table. By using appropriate check constraints on the underlying tables partitioning column the optimizer can do some fancy stuff, including partition elimination.

PS. Obviously you can generate the view pragmatically. You can even use Event Notifications to generate it automatically every time your 3rd party tool creates a new table, assuming you're running on SQL Server 2005 or later.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.