| Author |
 Topic  |
|
|
sbt1
Yak Posting Veteran
89 Posts |
 Posted - 10/04/2005 : 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
India
22461 Posts |
Posted - 10/04/2005 : 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
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 10/04/2005 : 07:38:08
|
yes you can and prepare yourself for the performance issue 
--------------------
keeping it simple... |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 10/04/2005 : 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 - 10/05/2005 : 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 - 09/02/2006 : 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
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 09/03/2006 : 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 |
Edited by - chiragkhabaria on 09/03/2006 02:36:33 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/04/2006 : 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
Slovenia
11741 Posts |
Posted - 09/04/2006 : 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
India
22461 Posts |
Posted - 09/04/2006 : 11:54:37
|
Is it Peso?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/04/2006 : 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
Slovenia
11741 Posts |
Posted - 09/04/2006 : 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
Sweden
3279 Posts |
Posted - 09/04/2006 : 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
Sweden
29138 Posts |
Posted - 09/04/2006 : 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
Sweden
3279 Posts |
Posted - 09/04/2006 : 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
Sweden
29138 Posts |
Posted - 09/04/2006 : 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
Sweden
3279 Posts |
Posted - 09/04/2006 : 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
India
22461 Posts |
Posted - 09/05/2006 : 12:08:07
|
>>is the original poster happy
Must be , after reading Peso's reply
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
Run query on multiple db's and combine results?
