What is a good strategy for joining data from separate databases

[Eric]

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.

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.

Any pointers or links would be greatly appreciated, thanks.

 

[William \(Bill\) Vaughn]

I might try to mirror or replicate a subset of the second database to a
central DBMS where the join/filtering can take place more efficiently.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)

 

[David Browne]

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

 

[Adrian Moore]

Eric,

You might try www.queryadataset.com for joining data on the client.

Ad.