Most efficient way to query multiple identical tables in separate databases

Question

Hi,

I have a server (SQL Server 2005) with multiple archive databases (1 per quarter stretching back 8 years) that are all structurally identical.

I frequently need to query back over a certain date range that spans n databases, usually n is small 1-3 but it's possible I need to query the whole set.

Any thoughts n the most efficient way to do this both from a code cleanliness and a performance perspective?

Current solutions are rather ad-hoc, there are a collection of views that span all or just the most recent database, other solutions are to generate dynamic SQL that figures out which DB(s) contain the data sought.

Obviously the ideal solution would be to partition the tables but I can't do this because it's a 3rd party supplied database

Dave

EDIT: I can't combine the databases since they are 3rd party controlled, the total data size is about 50GB so not huge, the largest tables contain around 1.5m rows per quarter

EDIT2: A data warehouse is definitely the right solution long term (it's in the plan) but I can't do this today :(

Answer 1

Depending on the size of the databases, it might actually be better to consolidate them into one database and properly index them.

You can write your own SSIS package and schedule it to consolidate data periodically (daily / hourly / etc).

Answer 2

Here's something that's going to do it!

Declare
@Database varchar(8000),
@Sql varchar(8000)
BEGIN Declare DBName Cursor LOCAL FAST_FORWARD For Select name FROM sys.databases where name like 'Your_DB_Names%'

Open DBName WHILE (1=1) Begin Fetch Next From DBName into @Database

if @@Fetch_status = -1 Break
if @@Fetch_status = -2 Continue

Set @Sql = 'use '+@Database Print @Sql Execute (@Sql)

SELECT * FROM TABLE -- Your query here End Close DBName
Deallocate DBName END

Answer 3

I've done this often, and let me tell you, keeping separate databases is a PAIN IN THE ASS. It forces you to do all sorts of logic like this all over the place - it sort of breaks the encapsulation that is a database in the first place.

What you are looking at is a data warehouse. You should look into consolidating all of your databases into one, and making it read-only. Then you take nightly/hourly incremental backups of your live data, and restore that against your warehouse. Then your warehouse is always up to date, and you run your reporting against that, instead of the live data.

This has the upshot of keeping your reports from killing your live production databases, and I'd guess upwards of 90% of business needs don't require 100% accurate just-in-time numbers.

Do the hard stuff once - create a warehouse. :-)

EDIT

Something I've done in the past is to create a view of the tables I use, and using linked databases (if the dbs were on other machines)

Create view view_tale as 
    select * from activedb.dbo.table
    union
    select * from db1.dbo.table 
    union 
    select * from db2.dbo.table

Hideous, performance-wise, but solves the problem neatly. Then you still only have the one-time setup issue (creating a view per table you wsish to query), and a centralized place to modify to keep your database list up to date for ongoing maintenance, as opposed to keeping N number of reports up to date.

Answer 4

One way to do this: use sp_msForEachDb.

-- Round 1 -------

Call this system procedure with a varchar parameter. (It's actually a LOT messier than this, check the code in the master database if you want to know what it's really doing.) The parameter must be a chunk of dynamic code -- for example,

DECLARE @DemoParameter varchar(1000)
SET @DemoParameter = 'SELECT MyCol from MyTable where CreatedOn between ''Jan 1, 1980'' and ''Dec 21, 2012'''
EXECUTE sp_msForEachDb @DemoParameter

This would run the query against every database on the SQL instance, returning one set per database -- except for those databases that didn't have the necessary table(s), which would throw an error (particularly the system databases). This leads us to...

-- Round 2 ---------

Within the dynamic code, as databases are iterated over all instances of the question mark ? will be replaced with the name of the currently being processed database. You can use this to filter which databases are to be processed, and which aren't. Note also that the "current" database will not be changed by the routine, you have to do that yourself. This gives us code like:

SET @DemoParameter = '

IF ''?'' like  ''%Foo%''
 BEGIN
    USE ?
    SELECT MyCol from MyTable where CreatedOn between ''Jan 1, 1980'' and ''Dec 21, 2012''

'

This would run the query only against those databases whose names contain the characters "foo". Possibly you could check for the presence of the table within each database; other methods suggest themselves.

This will shotgun back one dataset for each database, which doesn't help too much if you need them all in one neat and orderly data set, and that gets us to...

-- Round 3 ------------

Briefly: create a temp table, and populate it from within the dynamic query. As I show below, you can include the name of the database, and ever the server name -- very useful when your questing for lost data across dozens of databases spread across a handful of servers.

Create (or clear) the temp table:

IF object_id('tempdb.dbo.##Foo') is null
    CREATE TABLE ##Foo
     (
       ServerName         varchar(100)  not null
      ,DBName             varchar(100)  not null

      --  Add your own columns here
      ,MyCol              int  not null
     )

ELSE
    --Option: Delete this line to not clear on each run
    TRUNCATE TABLE ##Foo

Run the code (this is my main template, you can easily work @DemoParameter back in there):

EXECUTE sp_msForEachDB '
IF ''?'' like  ''%Foo%''
 BEGIN
    USE ?

    INSERT ##Foo
     select @@servername, db_name()
       ,MyCol
      from MyTable
 END
'

...and that should produce a single temp table with your data. Test this out, I wrote this without actually testing the code, and typso will silp in. (#temp tables should work as well as ##temp, I generally do this with ad-hoc system support issues)