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..TableXUnion allSelect Columns from DB2..TableXUnion allSelect Columns from DB3..TableXUnion allSelect Columns from DB4..TableXMadhivananFailing 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 possiblyDECLARE @MyTable TABLE(... appropriate columns ...)INSERT INTO @MyTableEXEC DB1.dbo.MySProc @Parameter1, @Parameter1INSERT INTO @MyTableEXEC 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 DBKristen |
|
|
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_addlinkedserverIts 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 LarssonHelsingborg, 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?MadhivananFailing 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 LarssonHelsingborg, 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 LarssonHelsingborg, 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 LarssonHelsingborg, 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 rockmooseUnedited: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 MadhivananFailing to plan is Planning to fail |
|
|
|