April 23, 2007 at 2:51 pm
what are some of the pitfalls when using joins on distributed queries?
distributed queries. ie; queries between 2 different databases under
the same server.
_________________________
April 26, 2007 at 11:31 am
when i ask.. i mean what to look out for? any thing i should consider?
for example... what if there are say a dozen of inner joins... does
that affect performance in how quickly those values are returned even
if it has the proper indexes?
_________________________
May 3, 2007 at 9:57 am
I thought distributed queries were multi-server queries?
I've been wondering how the joins work across servers also....
Just an FYI: Proper table design will do more for performance than any index could.
May 4, 2007 at 4:30 am
I have seen no performance impact between joins across DBs within the same server v joins within the same DB.
I have seen severe performance problems with joins involving multiple servers. (I saw it particularly within MS Access; but since I understood it was due to the limits of the ODBC protocol, it may happen with SQL Server as well.)
May 4, 2007 at 5:24 am
I really have to take exception to that statement.
May 4, 2007 at 8:19 am
Why?
May 4, 2007 at 8:57 am
probably due to the design vs index comment.
even a poorly designed table would benefit from an index.
_________________________
May 4, 2007 at 9:55 am
You should not find significant performance differences in cross-database queries assuming you fully qualify all of your object names. You do have to watch out for cross-database security chains. This has been vastly improved in SQL 2005, but you still have to give appropriate access to all tables and views being accessed and this can be a headache.
The real issue is that you end up with database names somewhat hard-coded in your queries. Using schemas in a single database is often a better approach. Otherwise, if this issue concerns you, creating views from one database to another and just accessing a single database gives you logical seperation that is easy to find.
May 4, 2007 at 10:02 am
yes, it is "possible" to improve query performance for a poorly designed table with an index; but nothing will compare to a properly designed table, utilizing good normalization techniques and data-typing, by keeping the 8k pages sql has to read to an absolute minimum, one can take a poorly designed table and improve its queryability performance even hundreds of times.
cross server joins, are a must at times, in some cases, utilizing openquery verses the 4 part name, can yield better performance. that is if you can get the remote server to do as much work as possible, rather than returning all the rows to satisfy the join and where clause. although sql attempts to do as much of this as possible, one with a dynamic sql statement and openquery, defined well, can also yield some incredible throughput, compared to 4 part name joins.
May 4, 2007 at 11:39 am
you mention "...by keeping 8k pages"
by this do you mean keeping the default 80% fill factor
because this is what it maintains of course when you do
not specifically adjust it upon creating indexes?
_________________________
May 4, 2007 at 12:38 pm
Simply put there is no difference between interdb and intradb queries, the only issue is administration to assure a query with a join across databases will work without error.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply