April 20, 2009 at 11:19 am
On a single 2005 server (or on single instances of 2008), are joins or queries against tables in other databases slower than if all tables are contained in the same db? If so, by how much and why?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 23, 2009 at 3:17 pm
(looking at watch.... then calendar....)
sigh....
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 23, 2009 at 3:45 pm
I looked at this post the other day, then thought for a while, then started to type, then decided that I'd be guessing anyway. But since you reminded me ..... I wouldn't have thought that there would be too much difference, barring additional permission checks. Any data would need to be brought into memory regardless of which database it is in.
April 23, 2009 at 3:54 pm
That's kind of where my DBA and I are at. But we were hoping someone knew of some reliable authority to that effect. Thanks 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 23, 2009 at 4:23 pm
Heh, I am like Matt.
I started typing and stopped; because when I wrote an answer like "I expect", "I think". Someone told me not to reply to post if I don't know (and I remember making a small remark so unless I know 100% I stopped writing now).
But I have 3-4 applications running in which the application accesses objects from other databases. These databases are in SQL 6.5, 2000 and 2005. I have not seen any performance impact on doing that approach; I think the main reason you don't want to do that is if you have referential integrity across databases then it becomes impossible to sync the backups.
There is an issue with ownership chaining (security) and such; I have not looked into it too deeply but here is are some articles I just found ...
http://qa.sqlservercentral.com/articles/Security/2633/
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1217061,00.html
So any how this is just what I have noticed; but don't know the hardcore answer behind the black box (SQL Engine, I wish I did heh). I also checked the "Inside Microsoft SQL Server 2005: QUery Tuning and Optimization" by Kalen Delaney; but did not find anything to help you sorry.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 23, 2009 at 4:48 pm
Thanks Mohit.
I've got the Delaney books for 2005. Just finished reading "Internals" but never ran across this.
I appreciate the help 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 24, 2009 at 7:06 am
It might be faster?
If the databases are on different disks you might actually have better performance.
How does the query plan look? If the joins are using proper indices and you're not table/index scanning the query should perform fine. You'll want to use fully-qualified names.
If you were using a "linked server", then you would have worse performance especially if using transactions(DTC).
steve
April 24, 2009 at 12:19 pm
We're not talking about linked servers... I KNOW the answer to that question.
Not sure the table sizes justify separate disk resources, but they could live where the load is lightest.
Query plans SHOULD be the same, but this is all speculative at this point. We don't want to move these tables and change all the procs if there is going to be unanticipated overhead. Probably the only way we can be sure is to do a side by side comparison.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 24, 2009 at 3:38 pm
Even if being on different drives made a significant difference, it would presumably be as easy to move the tables to a separate filegroup in the same database rather than to a different database, whilst keeping the intra-database benefits.
April 24, 2009 at 4:01 pm
agreed
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply