December 1, 2005 at 5:05 am
What overheads are involved in queries that join tables residing in seperate databases on the same Server? I have related data held in 2 databases. DatabaseA has client data, DatabaseB holds main Client table and other client data. So a typical query will be somehting like;
Select e.*, f.*, d.*, c.*
From DatabaseB.ClientMain as c
left join DatabaseA.Employment as e on e.clientid = c.clientid
left join DatabaseA.Financials as f on f.clientid = c.clientid
left join DatabaseA.Debts as d on d.clientid = c.clientid
How does sql server handle this? My experience (+5 yrs) with sql server tells me that it handles it very well. But there MUST be some overhead compared to all tables residing on the same database. I am contemplating createing a clients table on DatabaseA, this clients table holds ONLY the clientid and is updated via trigger from DatabaseB.Clients. So the query above can be;
Select e.*, f.*, d.*, c.*
From DatabaseB.ClientMain as c
join DatabaseA.ClientsCopy as cc on cc.clientid = c.clientid
left join DatabaseA.Employment as e on e.clientid = cc.clientid
left join DatabaseA.Financials as f on f.clientid = cc.clientid
left join DatabaseA.Debts as d on d.clientid = cc.clientid
Is this technique more efficient than the first query/technique.
Can somone please offer some advice or point me in the direction in BOL. Many thanks.
December 1, 2005 at 6:38 am
Oli,
I would suggest implementing the 2nd method within a test environment and inspect the resulting Execution plans within QA against those for the current solution to see how the query optimizer handles them, this should give you a clearer idea of the pros and cons of each.
Unfortunately I do not know enough about the inner workings of cross database queries to give you a Microsofty style statement on which would provide best performance, but as both databases reside on the same server I would assume no DTC overhead, I would also think that the less columns you retrieve from a remote database the better so as to reduce locking. I suggest you have a read of one of Steve's articles, it doesn't really address performance, but there are some handy points: http://qa.sqlservercentral.com/columnists/sjones/designingcrossdatabasequeries.asp
ll
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply