Performance issue with incremntal GetLastChangedFeedFiles query

[ranesh84]

Hi,

We are facing a performance issue on GetLastChangedFeedFiles query. it's taking around 5mins to give the result, do you have any ideas to change the way it's written to improve the performance?

declare @Table_Name varchar(200) = '@{pipeline().parameters.TABLE_NAME}'; declare @edsname varchar(200) = (select top 1 name from sys.external_data_sources where location like '%dfs.core.windows.net/dynamics365-financeandoperations%');

Declare @SQLQuery nvarchar(4000) = 'SELECT r.filepath(1) AS [TABLE_NAME] ,max(r.filepath(2)) AS [LAST_UPDATED_CDC_FILE] FROM OPENROWSET( BULK ''ChangeFeed//.csv'', DATA_SOURCE = ''' + @edsname+''', FORMAT = ''CSV'', PARSER_VERSION = ''2.0'', FIRSTROW = 1) With(FirstColum nvarchar(100)) as r WHERE (r.filepath(1) = '''+ @Table_Name +''' OR ''' + @Table_Name+ ''' = '''') GROUP BY r.filepath(1)';

EXECUTE sp_executesql @SQLQuery;

[jb-tech1999]

@ranesh84 Hi, since the Pipeline is already providing the table name I went and removed one of the wildcards (folder in data lake).

There are probably better ways to do this, but for now I was able to bring the query time for my table down from 7 min to less than 30s.

declare @Table_Name varchar(200) = '@{pipeline().parameters.TABLE_NAME}'; declare @edsname varchar(200) = (select top 1 name from sys.external_data_sources where location like '%dfs.core.windows.net/dynamics365-financeandoperations%');

Declare @SQLQuery nvarchar(4000) = 'SELECT '''+@Table_Name+''' AS [TABLE_NAME] ,max(r.filepath(1)) AS [LAST_UPDATED_CDC_FILE] FROM OPENROWSET( BULK ''ChangeFeed/'+ @Table_Name +'/*.csv'', DATA_SOURCE = ''' + @edsname+''', FORMAT = ''CSV'', PARSER_VERSION = ''2.0'', FIRSTROW = 1) With(FirstColum nvarchar(100)) as r GROUP BY
( SUBSTRING(r.filepath(), CHARINDEX(''/'', r.filepath()) + 1, CHARINDEX(''/'', r.filepath(), CHARINDEX(''/'', r.filepath()) + 1) - CHARINDEX(''/'', r.filepath()) - 1))';

EXECUTE sp_executesql @SQLQuery;