I need to join a table in MS-SQL with a Teradata table and store the results in MS-SQL. I only have read access to the Teradata database. The MS-SQL table contains ~20,000 records; the Teradata table has ~100 Million records. A simple test query that returns only a few rows works. But queries with larger record counts begin execution and never finish after waiting for hours. To eliminate the join I tried to select filtered data from the Teradata table that should return about 300,000 records and save the data in MS-SQL but even this doesn’t work after waiting for hours.
I am able to perform the join in MS-Access on the same server as the MS-SQL and it completes in about 15 minutes. The filtered select that returns ~300,000 rows also works in MS-Access in about 1 minute.
MS-Access uses the Teradata ODBC Driver. MS-SQL uses Microsoft OLE-DB for ODBC in addition to the ODBC driver which I suspect is the problem.
What can I do the determine the source of the problem and resolve it?
why don't you take the ~20,000 records table and get into Teradata? then perform the join in Teradata. Finally you can take the result where ever you want.