Improving Performance of my SP which is looping using While

indian143

Hi, I have written a stored Procedure which fills data in to a Flat table, but all it needs to do is to check if a particular row is there if not insert the record to avoid duplication of the values. And I am making this check against the same table that I am inserting. I am not sure what went wrong after this check the SP Performance has drastically dropped from 4 minutes to 20 minutes. Can anybody please help me how to improve performance of the SP in these situations. Here is the check that I am doing on the table, all those columns that I am checking against are must, I have check this to avoid duplicate data in the table.

IF (@ChangeSetId IS NOT NULL)
BEGIN
IF NOT EXISTS (select top 1 * from TrackUpdatedColumnNamesFlatTable where
ChangeSetId=@ChangeSetId AND TransactionType=@TransactionType and TableName=@TableName
AND ColumnName=@ColumnName and PrimaryKeyColumnName=@PrimaryKeyColumnName and
PrimaryKeyValue=@PrimaryKeyValue)
begin
INSERT INTO TrackUpdatedColumnNamesFlatTable
(
ApplicationId
,ApplicationComponentId
,ApplicationName
,ApplicationFriendlyName
,SubApplicationName
,SubApplicationFriendlyName
,UserId
,SamAccountName
,FullName
,DefaultDisplayValue
,ChangeSetId
,TransactionType
,TableName
,ColumnName
,ColumnNameForDisplayValue
,FriendlyColumnName
,ColumnValueBefore
,ColumnDisplayValueBefore
,ColumnValueAfter
,ColumnDisplayValueAfter
,PrimaryKeyColumnName
,PrimaryKeyValue
,ImageBefore
,ImageAfter
,ChangedBy
,ChangedDate
,IsSoftDelete
)
select
@ApplicationId,
@ApplicationComponentId,
@ApplicationName,
@ApplicationFriendlyName,
@SubApplicationName,
@SubApplicationFriendlyName,
@UserId,
@SamAccountName,
@FullName,
@DefaultDisplayValue,
@ChangeSetId,
TransactionType,
TableName,
ColumnName,
@ColumnNameForDisplayValue,
@FriendlyColumnName,
ColumnValueBefore,
@ColumnDisplayValueBefore,
ColumnValueAfter,
@ColumnDisplayValueAfter,
PrimaryKeyColumnName,
PrimaryKeyValue,
ImageBefore,
ImageAfter,
@FullName,
@AuditDate,
IsSoftDelete
from #TrackUpdatedColumnNamesFlatTable where ID=@MinId
end
END

Unfortunately th

Chris Quinn

You should be able to convert this to a single insert query with a correlated WHERE NOT EXISTS query, removing the loop entirely Something like this:

INSERT INTO destination_table (
id
,data_col_1
,data_col_2
)
SELECT id
,data_col_1
,data_col_2
FROM #tempdata TD
WHERE NOT EXISTS (
SELECT 1
FROM destination_table
WHERE id = TD.id
)

========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================