Performance impact if I reference objects from other databases inside the same sql

Question

Post reply

Performance impact if I reference objects from other databases inside the same sql

APA0876

Hall of Fame

Points: 3853
More actions
December 5, 2013 at 1:43 pm

#301200

What is the performance impact if I reference objects from other databases inside the same sql server

example:

on database Test1 I have an stored procedure referencing a table on the database Test2

Test2..table1

, would it be better to create a schema inside the database Test2 or isolate objects on a different database. We want to Isolate objects in order for us to not create a mess on Database Test2.

But we are not sure performance wise schema vs referencing objects from other db

Thanks

APA

Viewing 5 posts - 1 through 4 (of 4 total)

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

dan-572483 SSCoach Points: 15870 More actions · Answer 1

If one or both of the database is very large you might get better performance by having two databases and putting them on separate drives. You can probably acheive the same boost within a database by having multiple files or file groups putting the 2nd schema in a different file or file group.

For me I'd also look at the needs of the applications that access the databases.

Lowell SSC Guru Points: 323482 More actions · Answer 2

As I understand it, there will be no significant impact at all;

if you look at the execution plan, all objects are extracted out to their four part naming convention anyway;

I believe from there, as long as the objects are on the same server, the typical actions for the query performance all apply.

Indexing, statistics, the execution plan, and whether the pages requested are in memory or not are the same conditions than if they were in the same database.

if the data was on a different server, that's when data gets copied over to temp, before joining or filtering the data against local resources.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!