Run query on multiple db's and combine results? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Run query on multiple db's and combine results?

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2005-10-04 : 06:54:08

This may be an elemental question but I've never tried this.

Is it possible to run a query on multiple databases (identical structure) and assemble the results into a single recordset?

Here's what I want to do. I have 4 sites each with their own databases (again, structured identically). Can I run a query on table X in each of the 4 databases, and merge the results into a single resulting recordset?

Thanks for your thoughts.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-04 : 06:57:03

Select Columns from DB1..TableX
Union all
Select Columns from DB2..TableX
Union all
Select Columns from DB3..TableX
Union all
Select Columns from DB4..TableX

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-04 : 07:38:08

yes you can and prepare yourself for the performance issue

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-04 : 07:55:04

Or possibly


DECLARE @MyTable TABLE
(
... appropriate columns ...
)

INSERT INTO @MyTable
EXEC DB1.dbo.MySProc @Parameter1, @Parameter1

INSERT INTO @MyTable
EXEC DB2.dbo.MySProc @Parameter1, @Parameter1

...

which requires that you have the SProc in every DB, but should run pretty quickly as each may well have a cached query plan for its own DB

Kristen

sbt1
Yak Posting Veteran

89 Posts

Posted - 2005-10-05 : 12:08:02

Yes, I do anticipate a performance issue, but these queries will not be run very often, I just need to provide the ability to run them if/as needed.

Thanks for the help!

sbt1
Yak Posting Veteran

89 Posts

Posted - 2006-09-02 : 23:30:54

Sanity question... is this something that's even done in production environments? I have multiple offices each with its own database.. and have been unsuccessful at getting them to merge. Yet users in any location need to look at data in any other location. Currently, my user apps allow the users to quickly switch between databases, but it would be really nice to be able to manipulate data between all of them.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-03 : 02:22:04

Are all the databases on the Same Server? if no then you need to link them using Sp_addlinkedserver

Its not a good idea, to give the direct access of the databases to the users, since then you need to built all your bussiness validations in the databases.

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 08:59:33

I use Madhivanans approach when gathering records from 4 different servers. Each has a total of 7 million records (28 million records fetched total), and the fetch time is just about a second.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-04 : 10:39:11

let me get this straight, peter... you retreive 20 million rows in 1 second????

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-04 : 11:54:37

Is it Peso?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 12:11:15

Yes, I do. Not on my laptop, but at the server at my current client.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-09-04 : 13:05:27

what do you have there? a 1 Thz server?

that's awfully quick...

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-09-04 : 15:14:50

Is this partitioned views over federated database serves we are talking about?

retreiving 20 million rows in 1 second!
I'd like to see that, what do you mean retreiving Peso?
All of them?, to where?

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 15:29:45

There is an 10 gig backbone network with 5 servers (4 processor 8 gig ram each).
For a specific task, I collect primary key column values from the other four servers (all identical tables, each 7 million rows) the way madhivanan wrote.

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-09-04 : 16:21:17

Yes, but, I have to ask:
You collect the 28M rows, and then what do you do with them?
Use them in a query, put in a table, what?

PS.
I'm just jealous of your performance

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 16:38:11

I do an INNER JOIN with the result from the 4 remote servers to see if the data in the "master" server is duplicated somewhere on the 4 "slave" servers.
There is some logic that prohibits two "slave" servers to keep same value. So instead of checking against each server one at a time (4 checks), I do as Madhivanan wrote and check one time.

Peter Larsson
Helsingborg, Sweden

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-09-04 : 16:57:13

Hm, yes, damn good performance scanning all that data.
So what about check constraints on the slaves ensuring it's not duplicated?

Is this thread hijacking, or is the original poster happy

rockmoose

Unedited:
Post the query plan, maybe we can make it run slower.. LOL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-05 : 12:08:07

>>is the original poster happy

Must be , after reading Peso's reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources