Most efficient way to select records in one database based on result set from totally different database

Question

I have 2 totally separate databases - one MSSQL and one Pervasive. Due to the way our product data is stored in the Pervasive database, you cannot easily run a query to get a products's information and features to display on our website.

So, using a DTS package I take the product data from Pervasive and process it so it is one MSSQL table with the product item # (primary key for both databases) and all of the columns for the product features. Very easy and fast to query for our website.

The Pervasive database is the one used by the ERP system, so it always has the most up-to-date inventory totals for each product. Now, I need to find the best way to most efficiently pull the inventory information from the Pervasive database based on the records retrieved from the MSSQL database on a real-time basis.

Getting just one product's inventory information is no big deal. My main concern is how to pull inventory data for a list of items returned from a query on the MSSQL product table and have the inventory data match up with the correct items.

Caching all of the inventory data from the Pervasive db won't work because I need it to be real-time.

What are the most efficient options for me to pull this data, besides generating SELECT statements for each item in the list? I would like it to only be one database call if possible.

Thanks.

Answer 1

Could you keep a cache in MSSQL and have Pervasive update it using a trigger when the inventory level changes?

Answer 2

In order to access data to a different database from SQL Server, you'd have to first create a linked server instance on SQL Server pointing to the other db (Pervasive in this example) with appropriate credentials/permissions. This article is for linking MySQL to SQL Server 2008, but the steps are the same.

Once that's in place, you can write queries in SQL Server as though you were dealing with a single database.

Answer 3

I'm not quite sure if this is applicable, but in MySQL, I've used the IN operator for pulling significant sets of data (up to thousands) at once.

SELECT a,b,c FROM table WHERE id IN (123,234,345,456,...)

It can at times really reduce the number of queries that need sent.