optimize stored proc

-- CREATE INDEX #IDX_TMP ON #TMP (CORE_ID_MaterialLocationId)

Index at line 33 most likely slows the proc down. In return it speeds up the DELETE that follows. Could you remove CREATE INDEX... and test it to see if it improves the performance?

Mike

Ok is it possible not to use sp_executesql because it doesn't return error. Thanks

The INDEX won't slow down the execution and actually it may speed it up.
You're using sp_executesql because the DELETE command is being created from a dynamic sql. If you get rid of that you'll need to build a DELETE command from each available schema and if in the future you'll add a schema you need to remember to come back to this SP and add the code for the new schema. Something like:

IF @p_schema = 'test'
    BEGIN
        WITH CTE AS (
             SELECT  ML.CORE_ID_MaterialLocationId
        (...)
       Delete  From TEST.CORE_TEST_MaterialLocation 
       (...)
   END
ELSE
    IF @p_schema = 'test2'
    BEGIN
        WITH CTE AS (
             SELECT  ML.CORE_ID_MaterialLocationId
        (...)
       Delete  From TEST2.CORE_TEST_MaterialLocation 
       (...)
   END
ELSE
(...)
 

Select allOpen in new window

You can create your procedure in master database without any dynamic SQL or sp_executesql:

USE [master]
GO

CREATE PROCEDURE [dbo].[sp_Delete]
AS

-- Delete the temporary table #TMP if existe
IF  OBJECT_ID('tempdb..#TMP') IS NOT NULL
    DROP TABLE #TMP;

--Lister les doublons 
;WITH CTE AS (
    SELECT
    ML.CORE_ID_MaterialLocationId
	,ML.CORE_ID_MaterialId
	,ML.CORE_ID_LocationId
	,COUNT(*) OVER (PARTITION BY  ML.CORE_ID_MaterialId, ML.CORE_ID_LocationId) AS CountML
	,ROW_NUMBER() OVER (PARTITION BY  ML.CORE_ID_MaterialId, ML.CORE_ID_LocationId ORDER BY ML.CORE_ID_MaterialLocationId) AS RankML
FROM CORE_TEST_MaterialLocation AS ML  WITH (NOLOCK)
)
SELECT CORE_ID_MaterialLocationId
	,CORE_ID_MaterialId
	,CORE_ID_LocationId
INTO #TMP
FROM CTE
WHERE  RankML > 1 and CountML > 1;

CREATE INDEX #IDX_TMP ON #TMP (CORE_ID_MaterialLocationId);

--Supprimer et Inserer 
Delete  From CORE_TEST_MaterialLocation
FROM  CORE_TEST_MaterialLocation ML
INNER JOIN #TMP T
ON T.CORE_ID_MaterialLocationId=ML.CORE_ID_MaterialLocationId

GO

EXEC sys.sp_MS_marksystemobject sp_Delete

Select allOpen in new window

Then you can call this procedure this way:

EXEC Test.dbo.sp_Delete

Select allOpen in new window

OR

EXEC Test..sp_Delete

Select allOpen in new window

OR

USE Test
EXEC sp_Delete

Select allOpen in new window

Máté, I think the 'test' is the schema name and not the database name.

Thanks but I can't catch error within begin try
regards

You don't have any TRY..CATCH code block.

how can I add it thanks

thanks regards

@Vitor,

re:> "The INDEX won't slow down the execution and actually it may speed it up."
When an index is being made, which is the case here every time the proc is executed, it takes time and it is not good for performance. But, after an index is created once, it definitely improves seek operation.

In this case every time we run this proc, we are creating the index via line:
CREATE INDEX #IDX_TMP ON #TMP (CORE_ID_MaterialLocationId)

Mike, this is for a temporary table so it needed to be created every time the temporary table is created.
Index creation time comparing to the select time can be irrelevant when we are talking with large amount of data. In fact this kind of solution is quite used.
I wouldn't recommend it if the temporary table will handle few records. But the author didn't tell us nothing about the amount of data so I'll assume that the creation of index is in the code because it handles large amount of data.

Thanks Vitor,
Perhaps it is better. instead of index, we use WHERE clause (instead of JOIN) like: WHERE Col1 IN(Select....)

Thanks regards