Stored Procedure modification
-
Hi,
We have existing Stored Procedure and we would like to modify to add some interwal logic to check that if it runs more than 45 minutes then quit and run at next schedule otherwise run it.
We have sql 2008 R2 and this is a high availability database and we can't afford any lock.
Could you please guide how to add this logic to add parameter to run until 45 minutes and if running more than 45 minutes then quit the job.
We have current job set up to run procedure every 30 minutes as a schedule job so stored procedure runs every thirty 30 minutes and delete the duplicate records.
but sometimes job runs for an hour or more so we need to limit the job to just runs for 45 minutes and if it runs more than 45 minutes then quit it.
We have a very big table (millions records) and lots of duplicates recorda keep adding so we need to delete the records.
If you guide to add this parameters to handle if job runs more than 45 minutes than quit so it will run at next schedule.
Procedure:
=========
CREATE PROCEDURE [dbo].[Purge_DupsRecords]
(@NumberOfRows int=20000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @minCnt int
, @Cnt int
, @i int
, @PK_ID int
DECLARE @PK_IDs TABLE
(PK_ID int NOT NULL
)
-- if records is not exists populate stage table
IF NOT EXISTS (SELECT 1 FROM #stgTable WHERE IsDeleted = 0 )
BEGIN
TRUNCATE TABLE #stgTable
INSERT INTO #stgTable (MinPK_ID, KEY_WORD, A_ID, Title, IsDeleted )
SELECT MinPK_ID = MIN(PK_ID), KEY_WORD, A_ID, Title, 0
FROM AR_Table WITH (NOLOCK)
GROUP BY A_ID, Title, KEY_WORD
HAVING COUNT(*) > 1
END
SELECT @MinCnt = MIN(ID)
FROM #stgTable
WHERE IsDeleted = 0
SELECT @Cnt = @MinCnt + @NumberOfRows
, @i = @MinCnt-1
INSERT INTO @PK_IDs
SELECT MinPK_ID
FROM #stgTable
WHERE ID >= @MinCnt
AND ID < @Cnt
ORDER BY MinPK_ID
WHILE @i<@Cnt
BEGIN
SET @i=@i+1
SELECT @PK_ID = MinPK_ID
FROM #stgTable
WHERE ID = @i
DELETE
FROM AR_Table
WHERE PK_ID = @PK_ID
END
UPDATE #stgTable
SET IsDeleted = 1
WHERE ID >= @MinCnt
AND ID < @Cnt
END
GO
Thanks;
-
If you just want to stop processing at a certain point in time add a start time at the top of your code and check it on each pass through the loop.
As a side note, you should be very careful using NOLOCK. At the very least you need to understand the potential issues with it.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thanks.
We don't need to stop at certain time clock, we need to quit the job (stored procedure) in it's running more than 45 minutes as currently it's runnning from 20 minutes to 1:30 minutes to delete the duplicate records.
So we need to stop the job if it's running more than 50 minutes.
-
poratips (1/21/2013)
Thanks.We don't need to stop at certain time clock, we need to quit the job (stored procedure) in it's running more than 45 minutes as currently it's runnning from 20 minutes to 1:30 minutes to delete the duplicate records.
So we need to stop the job if it's running more than 50 minutes.
Right that is why I said have a variable at the beginning so you know when you started and check it on each iteration of the loop.
declare @StartTime datetime = getdate()
....
while (some condition AND datediff(n, @StartTime, getdate()) < 30) begin
It will fall out of your loop after 30 minutes regardless if the loop is complete or not. Then it will just run that single update and exit.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thanks Sean.
If you don't mind could you please look at my code and correct me if i have mistaken as i am not as i am not programmer.
appreciate your help so much!
CREATE PROCEDURE [dbo].[Purge_DupsRecords]
(@NumberOfRows int=20000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @minCnt int
, @Cnt int
, @i int
, @PK_ID int
, @StartTime datetime = getdate() ==> here right?
DECLARE @PK_IDs TABLE
(PK_ID int NOT NULL
)
...
...
...
WHILE @i<@Cnt and datediff(n, @StartTime, getdate()) < 30) -- I need to stop if it runs more than 45 minutes
BEGIN
SET @i=@i+1
SELECT @PK_ID = MinPK_ID
FROM #stgTable
WHERE ID = @i
DELETE
FROM AR_Table
WHERE PK_ID = @PK_ID
END
UPDATE #stgTable
SET IsDeleted = 1
WHERE ID >= @MinCnt
AND ID < @Cnt
END
-
That looks pretty close. You said that you want to stop if the process runs for 45 minutes but your check is < 30. You can change that to 45 if that is how long you want it to run. I suspect that you could get rid of the loop entirely and the whole process would end up in less than a minute. Before I try to code something like that, do you actually need the rows in #stgTable? From your description and the code I am guessing that table is used to hold rows between runs or something like that? If I am correct that the temp table is only used to help the loop processing this entire process could be changed to be a single statement.
CREATE PROCEDURE [dbo].[Purge_DupsRecords]
(
@NumberOfRows int=20000
) AS
BEGIN
SET NOCOUNT ON;
;with cte as
(
SELECT PK_ID, KEY_WORD, A_ID, Title, ROW_NUMBER() over(partition by A_ID, Title, KEY_WORD order by PK_ID) as RowNum
FROM AR_Table --removed nolock hint
)
delete cte
where RowNum > 1
end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thanks Sean once again for your resposnse.
I need for 45 minutes check but i can also set up as 30 minutes.
I was trying to implement in one shot like you suggested and i can definately try that oen but we don't have any DEV/STG enviornment and this is running
over our website constantly and it's locking.
My boss told me that if we do in a one shot then it will block for long time until it's done as table is very Big and regularly inserting data and no control
for duplicate data which we are running this job to delete duplicate records.
So my procedure modification as you have suggested is fine, right?
I will implement this one first and then i will try to implement CTE sql.
any other solution?
Regards,
-
poratips (1/22/2013)
Thanks Sean once again for your resposnse.I need for 45 minutes check but i can also set up as 30 minutes.
I was trying to implement in one shot like you suggested and i can definately try that oen but we don't have any DEV/STG enviornment and this is running
over our website constantly and it's locking.
My boss told me that if we do in a one shot then it will block for long time until it's done as table is very Big and regularly inserting data and no control
for duplicate data which we are running this job to delete duplicate records.
So my procedure modification as you have suggested is fine, right?
I will implement this one first and then i will try to implement CTE sql.
any other solution?
Regards,
I certainly do have another suggestion. Get a test environment up and running immediately.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I do completely agree with you but i have just started and suggested to create DEV but company is small and i don't when they will do?
My procedure modification is correct, right?
Do you think that if i eleminate the while loop and try with your CTE, it will lock the table for long or run fast and won't hold the lock as this table accessed constantly as people entering key words.
Thanks once again!
-
poratips (1/22/2013)
I do completely agree with you but i have just started and suggested to create DEV but company is small and i don't when they will do?My procedure modification is correct, right?
Do you think that if i eleminate the while loop and try with your CTE, it will lock the table for long or run fast and won't hold the lock as this table accessed constantly as people entering key words.
Thanks once again!
I can't begin to know how fast that will run because I don't know the table structure or what the indexes are like. I can say with certainty that any locks should be shorter because the code will run LOTS faster than a nasty cursor. It really seems like there are some major design issues if you have to constantly delete thousands and thousands of duplicates from a table. Unfortunately your code is attacking the symptom instead of fixing the underlying issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
You are right, Design is major issue and i don't know what's the plan here but i can definayely suggest them.
Due to avoid locking, they have implemented looping and going one by one records and that's why we are adding time limit as if procedure runs more than 45 minutes then let it run it next schedule as this job runs every 30 minutes and kickking of the procedure and delete any duplicate records.
I would like to know that procedure modification is correct, right?
CREATE PROCEDURE [dbo].[Purge_DupsRecords]
(@NumberOfRows int=20000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @minCnt int
, @Cnt int
, @i int
, @PK_ID int
, @StartTime datetime = getdate()
DECLARE @PK_IDs TABLE
(PK_ID int NOT NULL
)
-- if records is not exists populate stage table
IF NOT EXISTS (SELECT 1 FROM #stgTable WHERE IsDeleted = 0 )
BEGIN
TRUNCATE TABLE #stgTable
INSERT INTO #stgTable (MinPK_ID, KEY_WORD, A_ID, Title, IsDeleted )
SELECT MinPK_ID = MIN(PK_ID), KEY_WORD, A_ID, Title, 0
FROM AR_Table WITH (NOLOCK)
GROUP BY A_ID, Title, KEY_WORD
HAVING COUNT(*) > 1
END
SELECT @MinCnt = MIN(ID)
FROM #stgTable
WHERE IsDeleted = 0
SELECT @Cnt = @MinCnt + @NumberOfRows
, @i = @MinCnt-1
INSERT INTO @PK_IDs
SELECT MinPK_ID
FROM #stgTable
WHERE ID >= @MinCnt
AND ID < @Cnt
ORDER BY MinPK_ID
WHILE @i<@Cnt and datediff(n, @StartTime, getdate()) < 30 -- WHILE @i<@Cnt
BEGIN
SET @i=@i+1
SELECT @PK_ID = MinPK_ID
FROM #stgTable
WHERE ID = @i
DELETE
FROM AR_Table
WHERE PK_ID = @PK_ID
END
UPDATE #stgTable
SET IsDeleted = 1
WHERE ID >= @MinCnt
AND ID < @Cnt
-
So my procedure modification as you have suggested is fine, right?
I will implement this one first and then i will try to implement CTE sql.
any other solution?
CREATE PROCEDURE [dbo].[Purge_DupsRecords]
(@NumberOfRows int=20000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @minCnt int
, @Cnt int
, @i int
, @PK_ID int
, @StartTime datetime = getdate()
DECLARE @PK_IDs TABLE
(PK_ID int NOT NULL
)
-- if records is not exists populate stage table
IF NOT EXISTS (SELECT 1 FROM #stgTable WHERE IsDeleted = 0 )
BEGIN
TRUNCATE TABLE #stgTable
INSERT INTO #stgTable (MinPK_ID, KEY_WORD, A_ID, Title, IsDeleted )
SELECT MinPK_ID = MIN(PK_ID), KEY_WORD, A_ID, Title, 0
FROM AR_Table WITH (NOLOCK)
GROUP BY A_ID, Title, KEY_WORD
HAVING COUNT(*) > 1
END
SELECT @MinCnt = MIN(ID)
FROM #stgTable
WHERE IsDeleted = 0
SELECT @Cnt = @MinCnt + @NumberOfRows
, @i = @MinCnt-1
INSERT INTO @PK_IDs
SELECT MinPK_ID
FROM #stgTable
WHERE ID >= @MinCnt
AND ID < @Cnt
ORDER BY MinPK_ID
WHILE @i<@Cnt and datediff(n, @StartTime, getdate()) < 45
-- WHILE @i<@Cnt
BEGIN
SET @i=@i+1
SELECT @PK_ID = MinPK_ID
FROM #stgTable
WHERE ID = @i
DELETE
FROM AR_Table
WHERE PK_ID = @PK_ID
END
UPDATE #stgTable
SET IsDeleted = 1
WHERE ID >= @MinCnt
AND ID < @Cnt
END
-
That looks like it will do what you want. I don't know that I would say it is "right". 😉
You might try to suggest to the powers that be that because they are a small company setting up a dev/test environment if even more important. Ask them what the plan is if the entire system comes crashing down because nothing can be tested. Most small businesses would find it disastrous to their well being if such a thing happened. Not being able to test stuff in a controlled environment means it is not a case of IF it happens, it is a matter of when it happens, because it will at some point.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thanks Sean, appreciate your help!
-
You're welcome. I will cross my fingers for you as you test in production. Post back and let me know how it goes if you get the chance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply