Cross Database Queries...Performance Impact?
-
Currently I have one database with a $H*T TON of tables (It embarasses me to say the actual number). Having this number of tables makes it hard to do a lot of things administratively but mainly trying to compare structure against our other environments is near impossible using the tools I would like to use (RedGate). 95% of the tables do not need to be compared and it would be great if I could move these off into their own database.
So my question is, will I take a performance hit by moving these tables off into another database on the same server? I'll need to perform all standard queries against them, INSERT, UPDATE, DELETE, JOIN, UNION, etc. Is there any reason I shouldn't separate this 95% of the database from the other 5%?
Please Note: The database was poorly designed by the original developers I'm here to make things better and moving these tables is just one step to hopefully merging the tables into a handful of tables down the road.
Thank you in advance for any help.
Brian
-
bkothe (6/28/2010)
Currently I have one database with a $H*T TON of tables (It embarasses me to say the actual number). Having this number of tables makes it hard to do a lot of things administratively but mainly trying to compare structure against our other environments is near impossible using the tools I would like to use (RedGate). 95% of the tables do not need to be compared and it would be great if I could move these off into their own database.So my question is, will I take a performance hit by moving these tables off into another database on the same server? I'll need to perform all standard queries against them, INSERT, UPDATE, DELETE, JOIN, UNION, etc. Is there any reason I shouldn't separate this 95% of the database from the other 5%?
Please Note: The database was poorly designed by the original developers I'm here to make things better and moving these tables is just one step to hopefully merging the tables into a handful of tables down the road.
Thank you in advance for any help.
Brian
Heh.... that a very common unit of measure. But I don't know... Is it metric?
There's usually no problems with cross database queries so long as they are on the same instance. Referential integrity gets pretty weird but no unrelated performance problems to speak of. Also, if you make a "Synonym" for each displaced table, you won't have to suffer through any code changes either in the GUI or any of the procs, views, functions, etc.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the reply Jeff. Based on what I have read and from your response it doesn't seem like there should be any performance impact, at least nothing negative. I read that it might actually help performance slightly. I might give it a shot and see what happens.
By the way, I think "$h*t ton" is a Metric and US Standard unit of measure.
-
bkothe (6/29/2010)
Thanks for the reply Jeff. Based on what I have read and from your response it doesn't seem like there should be any performance impact, at least nothing negative. I read that it might actually help performance slightly. I might give it a shot and see what happens.By the way, I think "$h*t ton" is a Metric and US Standard unit of measure.
Heh... I figured. We used it a lot on U.S. Submarines and I had a friend about the H.M.S. Subs that would also use it as a unit of measurement. It was preceded in order by a "pot wad full" and a "pant load full" all of which seem both Metric and SAE.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply