none
run sql query on multiple databases

    Question

  • I need to run a same query on 15 different databases and union the results.

    Does any of the experts know how to do this with good performance?

    Any help is appreciated.

    Phil....the only reason i posted in T-Sql is because i will be using this query in reporting services :)

    Thanks

    Monday, February 05, 2007 8:49 PM

Answers

  • There is not an specific task for that. You would need to create an extra package; a master package that will call the child package that performs the extract/load. The trick would be to use a ForEach loop that iterates through the 15 set of connections and put the connection strings in variables (they can be in a table/file etc); inside of the ForEach Loop you will have an Execute Package task. Then the child package would need to use package configuration (from parent variable) to get the proper connection string on each iteration.

    Does this make sense?

    The only problem I see with this approach, it is that each iteration has to wait until the previous one is completed; so parallelization is not possible. I used a different approach to achieve parallel execution some time ago; but to be honest I did not like it at all; as you have to use a execute package task for every single execution you need (in your case would be 15!). I explained here in case you want to take a look:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=816000&SiteID=1

     

     

     

    Wednesday, February 07, 2007 1:26 PM

All replies

  • Nice to see you over here...

    The only reason I invited you over here is that using the power of SSIS may be better suited than doing a large cross-database query.  Here's what I'd suggest...

    Build a package in SSIS that connects to all of your various databases, and then using the union all component, you can store the results in a staging table to be used in SSRS.  Or do you need the results real-time?
    Monday, February 05, 2007 8:57 PM
  • The results will be updated every week.
    Monday, February 05, 2007 9:15 PM
  •  RookieDBA wrote:
    The results will be updated every week.


    There you go...  Using concepts from the data warehouse world, this is your best bet anyhow.  You're performance may be faster obtaining the results through SSIS (versus transact-sql -- only you can test that), but certainly when users run reports in SSRS against this "reporting" table, they'll be most appreciative that they aren't waiting for data to be assembled.
    Monday, February 05, 2007 9:20 PM
  • If all source tables have same estructure; you could have a dataflow with only one Source/destination component and then call the package 15 times; passing each time a difrent connection for the source component; that would help incase more sources are added down the road...
    Tuesday, February 06, 2007 1:47 PM
  • Yeah its fifteen different databases with five same tables and same sql code.

    I will try using SSIS and see the performance.

    Thanks

    Tuesday, February 06, 2007 2:24 PM
  • Rafael, could you tell me what toolbox item to use for "call the package 15 times, passing each time a different connection for the source..........."

     

    Thanks

    Tuesday, February 06, 2007 10:35 PM
  • There is not an specific task for that. You would need to create an extra package; a master package that will call the child package that performs the extract/load. The trick would be to use a ForEach loop that iterates through the 15 set of connections and put the connection strings in variables (they can be in a table/file etc); inside of the ForEach Loop you will have an Execute Package task. Then the child package would need to use package configuration (from parent variable) to get the proper connection string on each iteration.

    Does this make sense?

    The only problem I see with this approach, it is that each iteration has to wait until the previous one is completed; so parallelization is not possible. I used a different approach to achieve parallel execution some time ago; but to be honest I did not like it at all; as you have to use a execute package task for every single execution you need (in your case would be 15!). I explained here in case you want to take a look:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=816000&SiteID=1

     

     

     

    Wednesday, February 07, 2007 1:26 PM
  • One possible solution can be found here:

    Executing a set of SQL Script Files (*.sql) on a Group of SQL Server Databases


    Pradeep, Microsoft MVP (Visual Basic)
    http://pradeep1210.wordpress.com


    • Edited by pradeep1210 Tuesday, March 20, 2012 1:47 PM
    Tuesday, March 20, 2012 1:45 PM