Home
%3CLINGO-SUB%20id%3D%22lingo-sub-369155%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%2356%3A%20External%20tables%20and%20performance%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-369155%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Jan%2013%2C%202019%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20Hello%20Team%2C%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Recently%20I%20closed%20a%20service%20request%20that%20had%20the%20following%20question%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EOur%20customer%20created%20external%20tables%20to%20perform%20SQL%20Query%20across%20Azure%20SQL%20Database.%20We%20suggested%20to%20use%20Azure%20SQL%20Managed%20Instance%20(because%20there%20is%20not%20needed%20to%20use%20external%20tables%20to%20access%20to%20other%20databases%20using%20the%20same%20connection)%20in%20order%20to%20avoid%20it%2C%20but%2C%20our%20customer%20wanted%20to%20continue%20working%20with%20Azure%20SQL%20Database.%20%3CBR%20%2F%3E%3CUL%3E%0A%20%20%20%20%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EIn%20this%20case%2C%20when%26nbsp%3Bour%20customer%20perform%26nbsp%3Bthe%20following%20query%26nbsp%3Busing%26nbsp%3Bexternal%26nbsp%3Btables%26nbsp%3Bthe%26nbsp%3Bperformance%26nbsp%3Bis%26nbsp%3Bslow%20and%20they%20need%20an%20explanation%26nbsp%3Bwhat%26nbsp%3Bcan%26nbsp%3Bbe%26nbsp%3Bwrong.%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EI%20explained%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EStep%201%3A%20The%20time%20spent%20is%20expected%2C%20mostly%2C%20when%20you%20executed%20the%20first%20time%20the%20query%20of%20an%20External%20Table%2C%20because%2C%20internally%2C%20a%26nbsp%3B%20component%20needs%20to%20retrieve%20the%20data.%20For%20this%20reason%2C%20I%20would%20like%20to%20suggest%20two%20alternatives%2C%20but%2C%20let%20me%20explain%20what%20is%20happening%3A%20%3CBR%20%2F%3E%20Suppose%20that%20I%E2%80%99m%20executing%20the%20following%20TSQL%20is%20an%20External%20Data%3A%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22SQL%22%5D%20Select%20*%20INTO%20jUAN1%20from%20PerformanceVarcharNVarchar%20Where%20texttosearch%20like%20'%2520000--2%25'%20where%20PerformanceVarcharNVarchar%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EEnabling%20the%20Actual%20Execution%20plan%20of%20the%20query%20option%2C%20you%20could%20see%20that%20the%20query%20(%20Select%20*%20from%20PerformanceVarcharNVarchar)%20bring%20the%20data%20to%20the%20temporal%20database%20from%20the%20external%20database%20and%20after%20the%20engine%20will%20be%20apply%20the%20filter.%20So%2C%20that%20means%20that%20if%20the%20table%20has%20700.000%20rows%2C%20the%20query%20will%20retrieve%20700.000%20to%20the%20temporal%20database%20and%20after%20it%20will%20apply%20the%20filter%20specified.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89096iCD67FE119A9A3C92%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EMy%20first%20suggestion%20is%20to%20execute%20the%20query%20in%20the%20external%20database%20and%20retrieve%20only%20the%20data%20needed%20using%20the%20following%20TSQL.%20The%20filter%20will%20be%20applied%20first%20in%20the%20external%20database%20and%20after%20the%20database%20will%20receive%20the%20data%20of%20this%20filter.%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fes-es%2Fsql%2Ft-sql%2Flanguage-elements%2Fexecute-transact-sql%3Fview%3Dsql-server-2017%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fes-es%2Fsql%2Ft-sql%2Flanguage-elements%2Fexecute-transact-sql%3Fview%3Dsql-server-2017%20%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fes-es%2Fsql%2Frelational-databases%2Fsystem-stored-procedures%2Fsp-execute-remote-azure-sql-database%3Fview%3Dazuresqldb-current%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fes-es%2Fsql%2Frelational-databases%2Fsystem-stored-procedures%2Fsp-execute-remote-azure-sql-database%3Fview%3Dazuresqldb-current%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%5Bcode%20language%3D%22SQL%22%5D%20%3CBR%20%2F%3E%20INSERT%20INTO%20JUAN%20(ID%2CTEXTTOSEARCH)%20EXEC%20sp_execute_remote%20%3CBR%20%2F%3E%20%40data_source_name%20%3D%20N'RemoteReferenceData'%2C%20%3CBR%20%2F%3E%20%40stmt%3D%20N'Select%20Id%20from%20PerformanceVarcharNVarchar%20Where%20texttosearch%20like%20%40Value'%2C%20%3CBR%20%2F%3E%20%40Params%20%3D%20N'%40Value%20varchar(200)'%2C%20%3CBR%20%2F%3E%20%40Value%3D'%2520000--3%25'%20%3CBR%20%2F%3E%20%5B%2Fcode%5D%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EThe%20second%20suggestion%20is%20to%20use%20DATA-SYNC%20-%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fes-es%2Fazure%2Fsql-database%2Fsql-database-sync-data%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2Fdocs.microsoft.com%2Fes-es%2Fazure%2Fsql-database%2Fsql-database-sync-data%20%3C%2FA%3E%20-%20in%20order%20to%20have%20synchronized%20the%20tables%20without%20using%20external%20tables.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20Enjoy!%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-369155%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2013%2C%202019%20Hello%20Team%2CRecently%20I%20closed%20a%20service%20request%20that%20had%20the%20following%20question%20Our%20customer%20created%20external%20tables%20to%20perform%20SQL%20Query%20across%20Azure%20SQL%20Database.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-369155%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eexternal%20tables%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPerformance%20Issues%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esp_execute_remote%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
First published on MSDN on Jan 13, 2019
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:





[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.








[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!