First published on MSDN on Jan 13, 2019
Hello Team,
Recently I closed a service request that had the following question
[code language="SQL"] Select * INTO jUAN1 from PerformanceVarcharNVarchar Where texttosearch like '%20000--2%' where PerformanceVarcharNVarchar[/code]
[code language="SQL"]
INSERT INTO JUAN (ID,TEXTTOSEARCH) EXEC sp_execute_remote
@data_source_name = N'RemoteReferenceData',
@stmt= N'Select Id from PerformanceVarcharNVarchar Where texttosearch like @Value',
@Params = N'@Value varchar(200)',
@Value='%20000--3%'
[/code]
Enjoy!
Hello Team,
Recently I closed a service request that had the following question
-
Our customer created external tables to perform SQL Query across Azure SQL Database. We suggested to use Azure SQL Managed Instance (because there is not needed to use external tables to access to other databases using the same connection) in order to avoid it, but, our customer wanted to continue working with Azure SQL Database.
- In this case, when our customer perform the following query using external tables the performance is slow and they need an explanation what can be wrong.
-
I explained:
-
Step 1: The time spent is expected, mostly, when you executed the first time the query of an External Table, because, internally, a component needs to retrieve the data. For this reason, I would like to suggest two alternatives, but, let me explain what is happening:
Suppose that I’m executing the following TSQL is an External Data:
-
Step 1: The time spent is expected, mostly, when you executed the first time the query of an External Table, because, internally, a component needs to retrieve the data. For this reason, I would like to suggest two alternatives, but, let me explain what is happening:
[code language="SQL"] Select * INTO jUAN1 from PerformanceVarcharNVarchar Where texttosearch like '%20000--2%' where PerformanceVarcharNVarchar[/code]
-
- Enabling the Actual Execution plan of the query option, you could see that the query ( Select * from PerformanceVarcharNVarchar) bring the data to the temporal database from the external database and after the engine will be apply the filter. So, that means that if the table has 700.000 rows, the query will retrieve 700.000 to the temporal database and after it will apply the filter specified.
-
- My first suggestion is to execute the query in the external database and retrieve only the data needed using the following TSQL. The filter will be applied first in the external database and after the database will receive the data of this filter. https://docs.microsoft.com/es-es/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017 and https://docs.microsoft.com/es-es/sql/relational-databases/system-stored-procedures/sp-execute-remote-azure-sql-database?view=azuresqldb-current
[code language="SQL"]
INSERT INTO JUAN (ID,TEXTTOSEARCH) EXEC sp_execute_remote
@data_source_name = N'RemoteReferenceData',
@stmt= N'Select Id from PerformanceVarcharNVarchar Where texttosearch like @Value',
@Params = N'@Value varchar(200)',
@Value='%20000--3%'
[/code]
-
- The second suggestion is to use DATA-SYNC - https://docs.microsoft.com/es-es/azure/sql-database/sql-database-sync-data - in order to have synchronized the tables without using external tables.
Enjoy!
Updated Mar 14, 2019
Version 2.0Jose_Manuel_Jurado
Microsoft
Joined November 29, 2018
Azure Database Support Blog
Follow this blog board to get notified when there's new activity