Writing database queries across different services

Question

SELECT
    *
FROM
    tbl_order o
INNER JOIN
    tbl_contact c
ON
    c.ContactId = o.BillingContactId

The above query for an e-commerce store is nice and simple.

If I were to move all of my contacts data into a CRM such that its stored in a different database and the e-commerce database references contacts via a guid and fetches their data via a WCF service, how would I create an equivalent query?

Would this be too slow and ought I instead keep a copy of the contacts data in the e-commerce database?

score 1 · Answer 1 · answered Feb 28 '13 at 22:52

1

Without being very creative, there isn't going to be a way to query a WCF service in SQL Server. Plus you're right, it's going to be terribly slow.

I would recommend putting all the data in one place and then the queries are fast.

If you can copy the contact information into the e-commerce, that would be best. If the database technology is the same you could use replication to accomplish this.

If they are different, then you'll have to write some custom software to do it, but it will be worth your while.

answered Feb 28 '13 at 22:52

ryan1234

7,237
6
25
36

I think it would be quite interesting to see if its possibly to modularize infrastructure and applications such that queries, say, in an e-comm app only worked with objects specific to e-comm and references to items which were looked up at the end of a service. Does an e-comm app's queries really need to know *anything* about a billing contact? You might end up with a set of results that need the billing contact, but once a query is complete you would then fetch the list of billing contacts from a service. Could even cache them. This would result in great encapsulation and scalability. – Ian Warburton Mar 01 '13 at 12:13

Writing database queries across different services

1 Answers1