Clever Geek Handbook

Why is Teradata query faster in MS-Access than SQL Server

I need to join a Teradata table with approximately 0.5 billion records and a local table with approximately 10,000 records. I have it working in MS Access and it takes about 15 minutes to start. I would prefer to do this in SQL Server, but cannot even get a 1 record connection in the local SQL table to work.

Why is MS Access able to do this, albeit slowly, while SQL Server is choking? What does MS Access do differently than SQL Server?

SQL Server query with failed connection:

SELECT a.trk, a.wgt
FROM openquery(TERADATA, 'SELECT trk, wgt 
                          FROM SHIPMENT_DB.pkg') a
INNER JOIN  (Local_Tbl) b ON a.trk = b.Tracking_Number

Simple SQL Server query without a connection that works:

SELECT * 
FROM openquery(TERADATA,'SELECT trk, wgt 
                         FROM SHIPMENT_DB.pkg 
                         WHERE trk = ''773423067500''')
+3


source to share


2 answers


Not an answer, but I had a similar problem using OPENDATASOURCE. The performance was terrible, it took several hours to complete the request. The solution was to ensure that all conflicts involved in the WHERE clause are of mathematical data types. In my case, the remote column was INT, but in the query it was passed as varchar: ... 'WHERE remote_table.ID =' '4' '' ... Once I changed all the values ​​to their respective datatypes, the query took seconds.



0


source


Look at the execution plan in SQL Server. Since he knows very little about the dataset that is about to return from Teradata, he makes some assumptions.



Reordering tables in a join will help. Using an explicit one INNER HASH JOIN

might help (once you've changed the order).

0


source






More articles:


All Articles