March 11, 2010 at 8:47 am
Howdy!
I have a stored procedure that sets the IsIgnored flag in a table to 1 (true) if the user ignores the respondent.
CREATE TABLE [dbo].[Respondent_Search](
[Respondent_Id] [nvarchar](10) NOT NULL,
[IsIgnored] [bit] NOT NULL)
GO
CREATE PROCEDURE [dbo].[IgnoreRespondent]
@Respondent_Id nvarchar(10)
AS
BEGIN
UPDATE Respondent_Search
SET IsIgnored = 1 where Respondent_Id = @Respondent_Id
-- added by Rick to undo change after ten minutes
--EXEC ResetIgnoredRespondent @Respondent_Id
END
There's a business rule that says that we can't leave respondents "ignored" like this for more than 10 minutes (somewhat flexible on this time frame). So I would like to create SQL code that will "flip" the IsIgnored column back to 0 after ten minutes.
One way to do this would be to create a SQL job that runs every ten minutes and updates the column. In fact, I thought I might add a column (IsIgnored_Time) that would be used to determine if the ten minutes had passed. But I don't want to use a job if I don't have to.
I examined WAITFOR, but that just holds everything up. I built a stored procedure (you can see the reference to it in the first SP, commented out) that uses WAITFOR, but it doesn't seem to work... after the designated time passes, the value is still not updated.
CREATE Procedure [dbo].[ResetIgnoredRespondent]
@Respondent_Id nvarchar(10)
AS
-- Resets IsIgnored flag ten minutes after Respondent_Search table is modified
WAITFOR DELAY '00:10:00';
UPDATE Respondent_Search
SET IsIgnored = 0 where Respondent_Id = @Respondent_Id
GO
Does anyone have any ideas about how to trigger a process that will "undo" itself after ten minutes?
Thanks!
Rick
March 11, 2010 at 8:59 am
Hi,
Personnaly I would store in a datetime when the respondent was ignored (you must store date AND time), then a simple stored procedure ran every minute by a SQL job could undo the operation.
Something like this would do it (not tested) :
UPDATE Respondent_Search
SET IsIgnored = 0 where IgnoredDateTime <= DATEADD(minute, -10, GETDATE())
Cheers,
Laurent
March 11, 2010 at 9:07 am
Is there a particular reason to not use a job? It's going to be the easiest and most efficient way to handle this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2010 at 9:17 am
Developers on this server do not have the ability to create/configure jobs, so I am trying to come up with a method that will avoid using a schedule, if possible. There is also a concern been raised that scanning the table every few minutes, all around the clock, might be undesirable. The application will only occasionally have ignored respondents set.
I agree with you that creating a simple job might be the most simple (and most maintainable) solution to this situation. But I'd like to see if there's another more elegant way to perform the task.
Thanks!
Rick
March 11, 2010 at 11:15 am
Another option would be using a computed column.
However, since this cannot be used as persisted column (getdate() is non-deterministic) and cannot be indexed either, the column will be recalculated every time the column is queried and probably will perform badly on a large table.
It's definitely not a "more elegant way" compared to a scheduled job. But it's another way how to do it....
CREATE TABLE #temp
(
status INT,
inserted DATETIME
)
ALTER TABLE #temp
ADD CONSTRAINT temp_expires
DEFAULT GETDATE() FOR inserted ;
ALTER TABLE #temp
ADD current_id_status AS
CASE
WHEN inserted > DATEADD(mi,-10,GETDATE())
THEN status
ELSE 0
END
INSERT INTO #temp(status,inserted)
SELECT 1,DATEADD(mi,-8,GETDATE()) UNION ALL
SELECT 2,DATEADD(mi,-9,GETDATE()) UNION ALL
SELECT 3,DATEADD(ss,-595 ,GETDATE()) UNION ALL
SELECT 4,DATEADD(mi,-11,GETDATE())
-- display data originally inserted
SELECT *
FROM #temp
-- wait 10 seconds to show the effect on status = 3
WAITFOR delay '00:00:06'
-- display the same data after status 3 has expired
SELECT *
FROM #temp
March 11, 2010 at 11:31 am
Instead of scanning that table, I'd create a sub-table with just the ID (or other PK reference), and an "Ignored" datetime value. It would only include records that have been ignored, and when they were ignored. Every minute, remove every row from that table which is more than 10 minutes old. The clustered index would be on the datetime column.
Will perform much better, probably take less space on the disks, etc., as well as being easier to manage. Also wouldn't lock rows in the main table while you manage it.
Then just join to that table when you want to query the rows that have been ignored.
If you want to avoid having a job, you just include "and IgnoredAt >= dateadd(minute, -10, getdate())" in your join statement. Then you don't need a job, but the table will grow over time and potentially become slower, depending on size and indexing. It's a tradeoff.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2010 at 11:45 am
Thank you, lmu92 and GSquared, for your thoughtful answers.
Rick
March 11, 2010 at 9:49 pm
I might do something like this:
USE tempdb;
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.Respondent_Search', N'U') IS NOT NULL
DROP TABLE dbo.Respondent_Search;
GO
CREATE TABLE dbo.Respondent_Search
(
respondent_id BIGINT
NOT NULL,
ignore_until_utc DATETIME
CONSTRAINT [DF dbo.Respondent_Search ignore_until_utc 19000101]
DEFAULT '19000101'
NOT NULL,
CONSTRAINT [PK dbo.Respondent_Search respondent_id]
PRIMARY KEY CLUSTERED (respondent_id)
WITH (FILLFACTOR = 100),
);
GO
-- Add 100,000 rows
INSERT dbo.Respondent_Search
(respondent_id)
SELECT TOP (100000)
ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master.sys.columns C1,
master.sys.columns C2,
master.sys.columns C3;
GO
-- Set 100 random rows to ignore for ten minutes
UPDATE R
SET ignore_until_utc = DATEADD(MINUTE, 10, GETUTCDATE())
FROM (
SELECT TOP (100) *
FROM dbo.Respondent_Search
TABLESAMPLE (5 PERCENT)
ORDER BY NEWID()
) R;
GO
-- Index the ignore column
CREATE INDEX [IX dbo.Respondent_Search ignore_until_utc]
ON dbo.Respondent_Search (ignore_until_utc);
GO
-- Index seek
SELECT respondent_id,
ignore_until_utc
FROM dbo.Respondent_Search
WHERE ignore_until_utc > GETUTCDATE();
GO
--
-- Example procedure
--
CREATE PROCEDURE dbo.IgnoreRespondent
@RespondentId BIGINT,
@Seconds INTEGER = 600 -- Default to ten minutes
AS
BEGIN
-- Check requested ignore duration
IF @Seconds < 10
BEGIN
RAISERROR('Minimum ignore duration is 10s', 16, 1);
RETURN -999;
END
-- Table variable to hold the updated key
DECLARE @Updated
TABLE (
respondent_id BIGINT NOT NULL
);
BEGIN TRY
-- Try the update
UPDATE dbo.Respondent_Search
SET ignore_until_utc = DATEADD(SECOND, @Seconds, GETUTCDATE())
-- Save the updated key
OUTPUT deleted.respondent_id
INTO @Updated (respondent_id)
WHERE respondent_id = @RespondentId
AND ignore_until_utc < DATEADD(SECOND, @Seconds, GETUTCDATE());
END TRY
BEGIN CATCH
DECLARE @Msg NVARCHAR(2048);
SET @Msg = ERROR_MESSAGE();
-- Rollback any uncommittable transaction
IF XACT_STATE() = -1 ROLLBACK TRANSACTION;
-- Re-throw the error
RAISERROR(@Msg, 16, 1);
RETURN -100
END CATCH;
-- Check the update succeeded
IF NOT EXISTS
(
SELECT *
FROM @Updated
WHERE respondent_id = @RespondentId
)
BEGIN
IF NOT EXISTS
(
SELECT *
FROM dbo.Respondent_Search
WHERE respondent_id = @RespondentId
)
BEGIN
-- Row not found
RAISERROR('UPDATE failed for respondent_id [%I64d]', 16, 1, @RespondentId);
RETURN -990;
END
ELSE
BEGIN
-- Already ignored for longer
RAISERROR('respondent_id [%I64d] is already ignored for longer', 16, 1, @RespondentId);
RETURN -980;
END;
END;
-- Success
RETURN 0;
END;
GO
-- ===============
-- PROCEDURE TESTS
-- ===============
DECLARE @rc INTEGER;
-- Ignore respondent 16384 for 60 seconds
EXECUTE @rc = dbo.IgnoreRespondent
@RespondentId = 16384,
@Seconds = 60;
SELECT result_code = @rc,
respondent_id,
ignore_until_utc
FROM dbo.Respondent_Search
WHERE respondent_id = 16384;
-- Ignore respondent 16384 for 10 seconds
-- (fails since already being ignored for longer)
EXECUTE @rc = dbo.IgnoreRespondent
@RespondentId = 16384,
@Seconds = 10;
-- Show the result code
SELECT result_code = @rc;
-- Fails: respondent_id does not exist
EXECUTE @rc = dbo.IgnoreRespondent
@RespondentId = 0,
@Seconds = 10;
-- Show the result code
SELECT result_code = @rc;
-- Show the final state of the row
SELECT respondent_id,
ignore_until_utc
FROM dbo.Respondent_Search
WHERE respondent_id = 16384;
GO
-- Tidy up
DROP TABLE
dbo.Respondent_Search;
DROP PROCEDURE
dbo.IgnoreRespondent;
GO
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 12, 2010 at 6:22 am
This is a very clever approach, Paul. This is a very flexible and robust solution.
Yours is a good insight: You know what time it will be (ten minutes from now), so you'll just insert that time into the respondent's row.
I'll check with the developer to see if he can modify the application in order to use the strategy you propose. Thank you for your idea!
Rick
March 12, 2010 at 9:09 am
No worries - it's a problem I have faced before, though I must say my natural inclination would be to use a queue table and a job to process stuff from the queue when ten minutes is up, but this should work too.
I nearly posted a Service Broker solution, but gave up when I realized it would be too long; and also if you aren't allowed to create a job, SB is probably out too 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply