Improving Performance of Dynamic Sql

[indian143]

Hi All, I am using Dynamic SQL to load values from Audit tables into Flat-Table, for that I am taking all the tables and looping through their columns and inserting the values into Temp Table then I am putting the values from that Temp Table into a Flat Table. Below is one of the insert into a temp Table, but like this there are 4 more Dynamic Sqls are there execute in that SP. But I saw these Dynamic Sqls are taking lot of time to execute. Can anybody please let me know if I can execute the Dynamic Sql as fast as normal SQL? Any suggestion is appreciated. Thanks in advance I am also searching as well.

	SET @sql =N'INSERT INTO #TrackUpdatedColumnNamesFlatTable\_ForTemp (TableName, ColumnName, PrimaryKeyColumnName, PrimaryKeyValue, ChangeSetId
		, TransactionId, TransactionType, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate, IsSoftDelete, IsCmsActive, ColumnValueAfter)
		select ''' + @TblName + ''', ''' +  @ColName + ''', ''' +  @PKColName + ''' 
		, CASE WHEN ' +  @PKColName + ' IS NULL THEN '''' ELSE ' +  @PKColName + ' END, ChangeSetId, \_\_$start\_lsn, CASE WHEN (\_\_$operation=1) THEN ''Delete''
		WHEN \_\_$operation=2 THEN ''Insert''
		WHEN \_\_$operation=4 THEN ''Update''
		END	TransactionType, ' + @CreatedBy + ', '+ @ModifiedBy + ', '+ @CreatedDate + ', '+ @ModifiedDate + '
		, ' + CAST(@IsDeleted AS varchar(max)) + ' 
		, ' + CAST(@IsCmsActive AS varchar(max)) + ' 
		, CASE WHEN \_\_$operation=1 THEN NULL ELSE CAST(' + @ColName + ' AS nvarchar(max)) END 			
		FROM \[cdc\].' + @cdcinstancename 
		+ ' WHERE CAST(sys.fn\_cdc\_map\_lsn\_to\_time(\_\_$start\_lsn) AS DATE)=CAST(''' + @DateToTrack + ''' AS DATE)
		AND sys.fn\_cdc\_is\_bit\_set(sys.fn\_cdc\_get\_column\_ordinal(''' + @captureinstance +
		''',''' + '' + @ColName + '''), \_\_$update\_mask) = 1 
		AND \_\_$operation IN (1, 2, 4) OPTION(OPTIMIZE FOR UNKNOWN)';		
	
		exec sp\_executesql @sql, N'@IsColumnModified int out', @IsColumnModified out

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

[CHill60]

Try reducing the number of CASTs in the query. For example have @DateToTrack as type Date to avoid

CAST(''' + @DateToTrack + ''' AS DATE)

Similarly if ColumnValueAfter is declared as a nvarchar(max) and @ColName is an nvarchar I don't think you need the

CAST(' + @ColName + ' AS nvarchar(max))

I also doubt very much that you need to loop their columns in this way but I'd need to see some sample table schemas, and I'm not sure what you mean by a "flat table"

[indian143]

Yeah I did man thank you.

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

[Oscar Salgado]

Maybe should try OPTION(RECOMPILE), so the execution plan is different every time.