Designing Cross Database Queries
-
View advocates should beware that views across servers (using linked servers, i.e. four-part object names) are extremely inefficient as they must be materialized (by importing the entire base table from the linked server into tempdb) every time they queried!
For more info, see my article at: http://www.sqlsavior.com/8HoursTo2.html
-
I have used similar approach for other scenarios. I always tend to create a view for all the base tables and have the stored procedure to refer to the view and apply permissions to only the stored procedure not on other objects in the database. This helped me to resolve an issue in one of my applicaton. We store files in a database table in an image column due to security restrictions on the file server. Most of the time the database size is very small. In this particular application the database size increased faster due to the files being added to the table. So backup time and transaction log backup time increased significantly. This is due to the table which contains the file. This table does not need to be backedup quite often. In order to decrease these maintenance time we created another database in the same server and transferred the files table to the new database and changed the view to point to db.owner.filetablename and with no other code change the application worked like a charm.
In fact I applied this technique in 2 other applications where the tables are not even referenced in a view. In this scenario I moved the table to another database in the same server and created a view in the existing database with same name as table and it worked too well.
Let me know if anyone have comments on this approach.
Thanks
Ramesh Thalluru
-
Fred, I think you may have seen this a) because importing entire tables, or b) importing very large subsets of tables, or c) because of something specific to your Oracle source?
But your stmt is not true in a general sense.
I just tested this out with profiler running on the linked SQL server that the data source, and the WHERE clause was implemented on that (the data source) end. The entire tables was NOT materialized locally, only the small subset of rows was brought over.
Saw same query plan on the linked server when I just did either
SELECT ... from 4-part name WHERE critieria apply
or this
create view vw_test as SELECT ... from 4-part name /* no WHERE */
SELECT ... from vw_test where criteria apply
Both servers are SQL 2000 Enterprise w/sp3a.
-
Question: Referential Integrity?
When you have these cross-database (and linked servers, etc), how do you maintain referential integrity?
eg, bbdpres: We have an environment for reporting here that needs to use dynamic aggregations on products. The product aggregations are on what we call market definitions and product groups (1-m-m; market def-prod grp-prod). Users have the ability to add and remove products from these groupings based on rules regarding attributes of the products.
How do you make sure you don't whack a prod grp that still has products in it (or, make sure that the product has a valid prod grp when it is inserted)?
I've played a little with triggers and such, but that seems like it makes the database ugly
(I'd rather keep triggers for "Business Logic Actions").Is this the "advantage" behind using CRUD sprocs?
-
Nice little article. I would expand this concept to include many different situations when I want to disconnect the logical and physical data storage.
Don't let anyone scare you against using views, underlying indexes are almost always used. In the case of you're using a view without Joins or Grouping, it's guaranteed.
As Fred Williams said, beware of Linked Server! Using Linked Server can have dramatic effects on the performance of your queries, especially when used with views. Using Linked Server when you don't have to is just dumb. And remember, always check the query plan and pay attention to expected row counts.
The only problem with your scheme is Referential Integrity, as thormj pointed out. This can be a big problem with cross-database designs. The only solution I know to this is to tighten down security. Make it so that data can be changed only through one procedure or a set of procedures. Put your data validation in the procedure. Make sure the procedure uses sets if possible (like loading from a staging table) and you're performance won't be to bad.
Cross-database design can be very effective, if done right.
Signature is NULL
-
Or you can keep the performance offered by the native SQL server option by putting the names/IP address information in the server's HOST file. We have one colocated SQL server for 5 different websites. I set up WWWSite1, WWWSite2, etc all with the same server IP address. If we ever do split the load to two colo SQL servers, I only change the HOST file. All queries are done against 4 part name using the Host file alias.
-
one year ago... so now that sql2005 is out would you not prefer to use Service Broker?
I've had to deal with the issue of pulling and pushing small amounts of data from other systems and its a hassle. linked servers I find awkward because of how permissions get replicated across (at least given our domain policies), but more because they have been a challenge when working on that same database on our beta and alpha servers. the direct queries is easy, and absolutely I'd go with a view, but having to manage permissions and accounts in multiple databases adds administrative overhead and increases the challenge in tracking security and access to information. inally I opted for creating and keeping current an operational data store which worked nicely for consolidating security rights and the where and how of sharing data, although only as far as read-only access.
so I'm now looking at Service Broker as being perhaps a lot more complex to deploy, but eliminating a lot of the shortcomings of the other solutions. thoughts?
Ryan
-----------------
www.quadrus.com -
In SQL 2005, an alternative is to define a synonym. They are very easy to create and provided you have a linked server in place, you the underlying table you are using can be on any server.
The advantage of using a synonym is that you can have a synonym for a table, view, function or stored procedure. Using the synonym requires no special coding considerations - your code "thinks" the object is in the same database. SQL Server sorts out where it actually is.
Obviously, you will still need to think about your architecture and the implications of running queries against linked servers.
-
I agree with happycat59. I use synonyms in SQL Server 2005 across multiple databases on the same server. Very easy and can use logical names too. Works well for me!
Best Regards
Terry -
Another technique is to give the secondary database name a synonym and then always reference the synonym from the querying database. Like using a view in the example, changes to the secondary database have minimal impact - just a single change to the synonym.
-
Ah, sorry, new to forum. Others had explained synonyms. Had been looking at thread sorted in reverse chronologically and didn't see earlier posts on same topic.
-
If you are crossing DBs just within the server, you might also want to think about using different schemas instead of different DBs. You can then split the data accross filegroups for performance.
One "downside" of synonyms is that they dont have a clearly defined interface(column list). But this can be overcome with creating a view in the source database that is then referenced by the synonym.
Best Regards,
Chris Büttner
-
Somewhat surprised that there has been no mention of collation conflicts when performing cross-database queries.
-
Hi all,
Nice discussion.
I was implementing similar solution so want to share my experience.
Link server should be created by functionality. After creating a SQL link server, change its physical name to real one by sp_setnetname @server = 'server', @netname = 'network_name'
Because it is possible always to change link server physical name, there is no need to use views.
Security:
For in server database access, I would prefer using cross database chaining. Although you should be aware, people having db_owner rights for db_chaining enabled database could create SP to run with SA authority and execute ….
For access to other servers I would use link server security options. I prefer disable wildcard use of security and setup only direct login mapping. So, in my configuration I would create some login with required permissions to access tables; configure login mapping to this login; execute all queries under local login configured in login mapping; to execute by other logins in SQL 2005 db_chaining with create proc xxx with execute as [mapped_local_login] whould be used.
Use of link servers should be:
It is good to use link servers to select small amount of data and specify parameters. Joining link servers to other tables is very inefficient. Joining will work OK for small amount of data, but with grows performance of query will dramatically degrade (you can see it using profiler on target server).
I would even suggest using of openquery, because you may omit specifying database name. Although, all parameters should be included inside openquery.
So, allowed syntax would be
Simple queries:
Select * from [linkserver].[db].[owner].
or
Select * from [linkserver].[db].[owner].
where xxx
Or
Select * from openquery([linkserver],’select * from [] where xxx’)
Queries with joins
Select * from openquery([linkserver], ‘select * from [owner].
’) join table on (some join expresss)
With regards,
Igor
-
Using Linked Server is always gonna slow down the process when data grow...i prefer to have a DTS written that transfers data to a table in the local server either daily or periodically and this local table is to be replaced in place of view.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply