none
Cross DB query performance RRS feed

  • Question

  • Hi Experts,

    We are going to move some tables to another DB, I wonder if it will impact the performance.

    Select* from DB1.dbo.t1 t1 join DB1.dbo.t2  t2 on t1.c1=t2.c1

    VS

    Select* from DB1.dbo.t1 t1 join DB2.dbo.t2  t2 on t1.c1=t2.c1

    Thanks for your help!

    Friday, August 24, 2012 9:29 AM

Answers

  • If they are on the same server, there is no real problem pulling from separate database. In fact, you may want to separate them for good reasons. For instance if you have a combination of transactional tables and lookup tables that are imported from files. The transactional data needs full recovery and frequent transactional log backups to be able to properly restore, the lookup data does not and can benefit from being in a daatbase in simple recovery mode.

    We have many different databases our applications use and we cross databases in queries all the time. As long as the indexing is done properly, there has been no noticable performance difference. The biggest potential issue is for data integrity as you can't set up foreign keys across databases. This can be handled in triggers if need be though.

    Now when the databases are on different servers, there can be a performance problem and getting the data is more complicated.


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by SSAS_user Sunday, August 26, 2012 10:33 AM
    Friday, August 24, 2012 9:31 AM
  • No, it shouldn't be. The optimizer has the same information as if you were inside a single database. But it is better to keep query within same database. what happens if DB2 is not accessible then the whole query fails .. can you accept this

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by SSAS_user Sunday, August 26, 2012 10:33 AM
    Friday, August 24, 2012 9:45 AM
  • As mentioned by others, if both databases are on the same server you don't have to expect any performance problems.

    Doesn't mean that it is always a good idea. If you have (or can have) foreign key constraints between two tables, then typically you don't want to separate these tables over two databases. You don't want this from an integrity point of view, but also from a performance point of view. See no-columns-from-autojoined-table.html


    Gert-Jan

    • Marked as answer by SSAS_user Sunday, August 26, 2012 10:33 AM
    Sunday, August 26, 2012 9:19 AM

All replies

  • If they are on the same server, there is no real problem pulling from separate database. In fact, you may want to separate them for good reasons. For instance if you have a combination of transactional tables and lookup tables that are imported from files. The transactional data needs full recovery and frequent transactional log backups to be able to properly restore, the lookup data does not and can benefit from being in a daatbase in simple recovery mode.

    We have many different databases our applications use and we cross databases in queries all the time. As long as the indexing is done properly, there has been no noticable performance difference. The biggest potential issue is for data integrity as you can't set up foreign keys across databases. This can be handled in triggers if need be though.

    Now when the databases are on different servers, there can be a performance problem and getting the data is more complicated.


    Many Thanks & Best Regards, Hua Min

    • Marked as answer by SSAS_user Sunday, August 26, 2012 10:33 AM
    Friday, August 24, 2012 9:31 AM
  • No, it shouldn't be. The optimizer has the same information as if you were inside a single database. But it is better to keep query within same database. what happens if DB2 is not accessible then the whole query fails .. can you accept this

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by SSAS_user Sunday, August 26, 2012 10:33 AM
    Friday, August 24, 2012 9:45 AM
  • As mentioned by others, if both databases are on the same server you don't have to expect any performance problems.

    Doesn't mean that it is always a good idea. If you have (or can have) foreign key constraints between two tables, then typically you don't want to separate these tables over two databases. You don't want this from an integrity point of view, but also from a performance point of view. See no-columns-from-autojoined-table.html


    Gert-Jan

    • Marked as answer by SSAS_user Sunday, August 26, 2012 10:33 AM
    Sunday, August 26, 2012 9:19 AM
  •  As long as the indexing is done properly, there has been no noticable performance difference. The biggest potential issue is for data integrity as you

    Thanks for your reply. Take this as an example, how to build avaiable index for this?

    Select DB1.c1, DB1.c2, DB2.c3  from DB1.dbo.t1 t1 join DB2.dbo.t2  t2 on t1.c1=t2.c1

    Sunday, August 26, 2012 10:35 AM