Best way to combine records across databases

Question

Post reply

Best way to combine records across databases

JuniperJun

SSC Enthusiast

Points: 183
More actions
February 9, 2023 at 7:59 pm

#4147752

Data which is distributed over multiple databases needs to be gathered and then queried/filtered.
Currenttly a temporary table in which the records are inserted from the different databases is used however it
is really slow, is there a faster way to handle it?
February 10, 2023 at 7:44 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

If all the databases are on the same instance, why do you need to use a temporary table?

Also, does your temporary table actually have the correct indexes on it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JuniperJun SSC Enthusiast Points: 183 More actions · Answer 2

I guess you mean selecting them with union all, that's even worse performance wise.

It does.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

oh god no don't use UNION ALL unless you really need to.

Why not just something like this to get all the records you need from each source?

INSERT INTO tempdb.TableName(col1, col2, col3)

SELECT colA, colB, colC

FROM SomeDb.schemaA.T1

WHERE x=1;

Or

INSERT INTO tempdb.TableName

EXEC db.schema.StoredProcedureName @param1=1...

and then if the table in tempdb is indexed, you should be ready to go, right?

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

JuniperJun wrote:

I guess you mean selecting them with union all, that's even worse performance wise.
It does.

No... and no where did I come close to even inferring that. I mean use the tables like normal tables by adding synonyms in the "current" database to the tables in those other databases so that you can also follow the "2 part naming convention". You might even want to consider a "Partitioned View" if all the databases are on the same instance (they can be "distributed" to other instances but that's both a pain and a risk).

Also, you've given us practically zero information for use to help you with performance. Please see the article at the 2nd link in my signature line below for how to seriously increase your chances of getting help for performance issues.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JuniperJun SSC Enthusiast Points: 183 More actions · Answer 5

As I said second option is still used but it's slow. @pietlinden

@jeff Moden: which requires a union all? 😅 , No thx

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

JuniperJun wrote:

As I said second option is still used but it's slow. @pietlinden
@jeff Moden: which requires a union all? 😅 , No thx

As opposed to what??? The (in your own words) SLOW Temp Table you currently have? Contrary to what's been stated, UNION ALL , especially when properly used in Partitioned Views, doesn't slow anything down. What slows things down is the code that queries against it.

But, you seem like you know it all. Do what you think you need to. 😉

p.s. It's actually UNION and not UNION ALL that has the performance issue because it has a built in form of DISTINCT. Other than a post on this thread, I'm not sure where you came up with the incorrect notion that UNION ALL is slow when used PROPERLY. 😉

To be sure, though, you haven't posted anything of particular use for us to actually help you with your performance issue. You might not need your SLOW TEMP table (and it shouldn't be slow but we have no info on that or your code) and you might not need UNION ALL (again, no way for us to know)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

pietlinden wrote:

oh god no don't use UNION ALL unless you really need to. Why not just something like this to get all the records you need from each source?
INSERT INTO tempdb.TableName(col1, col2, col3) SELECT colA, colB, colC FROM SomeDb.schemaA.T1 WHERE x=1;
Or INSERT INTO tempdb.TableName EXEC db.schema.StoredProcedureName @param1=1...
and then if the table in tempdb is indexed, you should be ready to go, right?

Why do YOU think UNION ALL is a sin of some sort here? Done correctly, it can be used to make a VERY high speed minimally logged insert into a Temp Table. And, used in a properly constructed Partitioned View, it will eliminate the need for the Temp Table altogether.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael L John One Orange Chip Points: 27055 More actions · Answer 8

JuniperJun wrote:

Data which is distributed over multiple databases needs to be gathered and then queried/filtered.

Are these databases on the same instance, or different instances or servers?

JuniperJun wrote:

Currenttly a temporary table in which the records are inserted from the different databases is used however it

What method are you using to gather this data, and put it into a temp table?

JuniperJun wrote:

is really slow, is there a faster way to handle it?

WHAT is slow? Loading the temp table, or querying the temp table?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27055 More actions · Answer 9

JuniperJun wrote:

Data which is distributed over multiple databases needs to be gathered and then queried/filtered.

Are these databases on the same instance, or different instances or servers?

JuniperJun wrote:

Currenttly a temporary table in which the records are inserted from the different databases is used however it

What method are you using to gather this data, and put it into a temp table?

JuniperJun wrote:

is really slow, is there a faster way to handle it?

WHAT is slow? Loading the temp table, or querying the temp table?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/