December 5, 2007 at 8:15 am
I have been tasked with finding a way to import a subset of records from a table on a remote Oracle database. The table has 80 to 90 million records so I don't want to import the entire table because I will only need approximately 10,000 records. I have a table on SQL Server that has the same fields that the Oracle table has. This SQL table has the primary key values which match it's corresponding record in the oracle database. I will be wanting to import from oracle based on the primary key in the SQL table. The only way I know to do this is to use lookups to retrieve the Oracle data for each field as I transfer the records from the SQL table to another SQL staging table. I'm thinking this will take too long to run since there are around 50 lookups for each field in each record. Is there a faster way to do this? If I could just perform an inner join it would be great but I don't think thats a possible option. Thanks in advance.
December 6, 2007 at 2:46 pm
Hello Katherine,
If you want do do a join, why not just set up a linked server?
Best Regards,
Chris Büttner
December 7, 2007 at 4:47 am
If I’m following this correctly, you are trying to select specific records from an Oracle database based on key information in your SQL Server database. Due to the size of the Oracle table, you don’t want to bring back all the data.
I had to do something similar to this four years ago in SQL Server 2000. I’ll share what I did. Maybe someone else can improve on this, or this may spark additional thoughts and approaches.
I had the same situation. Based on certain criteria in my SQL Server database, retrieve certain data from the Oracle database. So I set up a linked server to the Oracle database and did the following:
[font="Courier New"]SELECT o.Name
FROM IDList i
INNER JOIN OPENQUERY(OrclSvr, [/font][font="Courier New"] 'SELECT Name, ID FROM NameInfo'[/font][font="Courier New"]) o ON i.ID = o.ID[/font]
It worked, but I found this to run very slowly because the OPENQUERY statement was passing ALL of the results of the query across the network (that is, the entire set of Names and IDs from the Oracle table) before joining to the IDList table. The key, as I found out, was to narrow the scope of the query within the OPENQUERY statement. This allows Oracle to pull out and pass back only the data desired, which is then joined to your other table.
So then I tried this:
[font="Courier New"]DECLARE @i int
SET @i = 5 --Hardcode an ID for example purposes
SELECT o.Name
FROM IDList i
INNER JOIN OPENQUERY(OrclSvr, [/font][font="Courier New"]'SELECT Name, ID FROM NameInfo WHERE ID = '[/font][font="Courier New"] + CONVERT(varchar, @i))[/font]
The OPENQUERY worked if I hardcoded a specific value. [font="Courier New"]SELECT * FROM OPENQUERY(OrclSvr, [/font][font="Courier New"]'Select Name, ID FROM NameInfo WHERE ID = 5'[/font][font="Courier New"])[/font] However, THIS didn’t work because the OPENQUERY statement wouldn’t accept dynamic SQL! We ultimately ended up developing our process using dynamic SQL that could build the selects with OPENQUERY statements, like this:
[font="Courier New"]DECLARE @i int, @SQL varchar(500)
SET @i = 5
SET @SQL = [/font][font="Courier New"]'SELECT o.Name
FROM IDList i
INNER JOIN OPENQUERY(OrclSvr, ''SELECT Name, ID FROM NameInfo WHERE ID = '[/font][font="Courier New"] + CONVERT(varchar, ID) + [/font][font="Courier New"]''')'[/font][font="Courier New"]
EXEC (@SQL)[/font]
In our case, our ID list tended to be very short, often only one item, generally less than 20, very rarely would be as many as 100. If there could be more than one item, then we built up the list in a variable and used an IN () statement in the WHERE clause in the OPENQUERY rather than equality.
December 7, 2007 at 5:53 am
From my experience (which is not too big), a linked table is the only viable option when you want to download by joining to 10.000 local rows.
SELECT D.x, D.y ...
FROM ORASRV..SCHEMA.TABLE D
JOIN dbo.FilterTable F ON F.FilterCol = D.FilterCol
How many columns do you have in your table? You cannot avoid a table scan on the oracle side so I hope the table is not too big (despite the row count).
Best Regards,
Chris Büttner
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply