In SQL External table take a time for select and insert data into temp table

Question

I am using external table of main database to Datawarehouse database. While selecting data from external table to # table it take almost 9 min, sometime it take more time. How I can improve this performance of external table?

Could you please confirm if you are selecting every record or using some clauses to select the data. what is the size of select query. Could you please confirm if these 2 dbs are in same region or not? — IpsitaDash-MT, Sep 24 '21 at 06:08
this two db are in same region, and in select I have used where clause to filter data. The data in main table in main database is almost 22 lakh — S. Deshmukh, Sep 24 '21 at 06:18
Could you please accept the answer if it suffice as a relevant one & for the benefit of other community members. — IpsitaDash-MT, Sep 25 '21 at 15:00

score 2 · Accepted Answer · answered Sep 24 '21 at 09:10

Is to use the following TSQL to perform the query in the external database and get only the data required. The filter will be applied first in the external database, and then the data from the filter will be received by the database.

When you enable the query's Actual Execution Plan option, you can see that the query : Select * from PerformanceVarcharNVarchar, brings data from an external database to the temporal database, and then the engine applies the filter.

Here is the Official Microsoft Documents :EXECUTE (Transact-SQL) | Docs

Else you can use Azure Data Sync : SQL Data Sync is an Azure SQL Database-based service that allows you to synchronize selected data bidirectionally between multiple databases, both on-premises and in the cloud.

The Original Post has got detailed insights: Lesson Learned #56: External tables and performance issues | techcommunity

In SQL External table take a time for select and insert data into temp table

1 Answers1