Post reply

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

  • 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

  • December 5, 2013 at 2:00 pm

    #1671314

    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.

  • December 5, 2013 at 2:02 pm

    #1671315

    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!

  • December 5, 2013 at 3:00 pm

    #1671328

    Hello,

    Yes I completely agree with you!!

    Thanks that is what I wanted to confirm

  • December 5, 2013 at 6:29 pm

    #1671350

    Completely agree Lowell..

    CEWII

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

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