Re: What is a good strategy for joining data from separate databases
- From: "David Browne" <davidbaxterbrowne no potted meat@xxxxxxxxxxx>
- Date: Wed, 22 Nov 2006 17:59:27 -0600
"Eric" <someone@xxxxxxxxxx> wrote in message news:#$dGffoDHHA.3660@xxxxxxxxxxxxxxxxxxxxxxx
I have a situation where I must restructure my data access, and was hoping the community could give me pointers or links to articles that would help.
My situation is that I need to query data from two separate databases. One is my application's own database, and the other is a Data Mart created for me with a view I access. The way I do it currently requires that the SQL-Servers be linked, and, given our security setup, on the same SQL-Server.
Currently the cross database queries take two forms:
SELECT field FROM MyAppDataMart..vwAppData vw
JOIN MyOwnDatabaseTable tbl ON vw.Field = tbl.Field
OR
SELECT field FROM MyAppDataMart..vwAppData vw
WHERE NOT EXISTS(SELECT field FROM MyOwnDatabaseTable tbl WHERE vw.Field = tbl.Field)
My current .NET business object merely makes a single stored procedure call to retrieve a resultset, with any joins required handled by the stored procedure. It is now considered unacceptable to have a dependency like that in the stored procedure and I need to re-structure the data access such that the .NET business object retrieves data separately from each database eliminiating the need for one database to be able to be linked or even aware of the other.
What you have is a reasonable strategy, and I would push back on the notion that it's "unacceptable". Your alternatives are not really better.
This means, however, that I must handle any JOIN or EXISTS logic in my business object, and I am looking for strategies to accomplish that. From what I have read so far, I'm not sure the DataRelation in ADO.NET can enforce logic more complex than a INNER JOIN.
In short, what I need to go to is from
Existing:
-Call stored procedure which bridges Database1 and Database2 and returns a resultset.
Desired:
-Retrieve info from Database1
-Retrieve info from Database2
-Apply any logic such as JOIN or EXISTS or NOT EXISTS to filter data
-Return resultset.
I think these are your options:
1) "JOIN" in client code.
Slow, expensive an requires a lot of code: avoid if at all possible.
2) Replicate data into your local database.
Possible, depending on the latency, size etc. Requires setup and monitoring on the server.
3) Construct the SQL for a cross-database JOIN in client code
Basically the same as your current solution, except the dependency is removed from the stored procedure.
David
.
- References:
- Prev by Date: Re: What is a good strategy for joining data from separate databases
- Next by Date: Re: I Think I Found The Problem
- Previous by thread: Re: What is a good strategy for joining data from separate databases
- Index(es):
Relevant Pages
|
