Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  How to instantly Commit?

Author  Topic  gouri Starting Member 7 Posts Posted - 2008-06-11 : 05:07:21 I have a server application which is working as a simulator for data generation. It generates data and immediately sends It to a queue in service broker. The messages in service broker queue need to be read every one minute (one minute push to database) and sent to the table in database. The data has various patient ids and bed numbers. In table if there is no record with the same patient id and bed number a new record gets added. If the patient id and bed number exists then it is checked if the difference between the time in the existing record and new data is less than 10 minutes. If so the same record is updated. Otherwise a new record gets added. I am having each record with 10 minutes of data. What will be the optimum way to achieve this? This is the code I am using --There is a queue named ReceiverQueue --table structure is bedno char(10),pid char(10), --starttime datetime, hrtype char(10), v text while (1=1) BEGIN DECLARE @message XML DECLARE @PKey nchar(10) DECLARE @BKey nchar(10) DECLARE @StartTime datetime DECLARE @HKey nchar(10) DECLARE @vKey nvarchar(12) begin transaction; waitfor (RECEIVE TOP(1) @message=CONVERT(xml, message_body) FROM ReceiverQueue), timeout 100; if (@@ROWCOUNT =0) begin commit break end select @message --separate all tags from data SET @PKey= @message.value ('(v/@PID)[1]','nchar(10)') SET @BKey=@message.value('(v/@BedID)[1]','nchar(10)') SET @StartTime=GETDATE() SET @HKey =@message.value('(v/@HR)[1]','nchar(10)') SET @vKey=@message.value('(/v)[1]','nvarchar(12)') SELECT @PKey,@BKey ,@StartTime,@HKey,@vKey if ((SELECT COUNT(*) FROM dbo.Table1 WHERE pid=@PKey and bedno=@BKey)=0 ) BEGIN set transaction isolation level read uncommitted BEgin transaction t1 -- add new record INSERT INTO dbo.Table1 (bedno,pid,starttime,hrtype,v) VALUES (@BKey,@PKey,@StartTime,@HKey,@vKey) commit transaction t1 END ELSE -- find out if current time and previous time have a difference of 10 min --upto 10 min data should go to same record --patient id same, bed name same and difference upto 10 min if (SELECT COUNT(*) FROM dbo.Table1 WHERE (DATEDIFF(mi,starttime,@StartTime) <= 10 AND DATEDIFF(mi,starttime,@StartTime) > 0 AND pid=@PKey and bedno=@BKey))=1 BEGIN set transaction isolation level read uncommitted begin transaction t2 --now add v tag declare @l int select @l=datalength(Table1.v) from table1 where (DATEDIFF(mi,starttime,@StartTime) <= 10 AND DATEDIFF(mi,starttime,@StartTime) > 0 AND pid=@PKey and bedno=@BKey) DECLARE @ptr varbinary(16) SELECT @ptr = TEXTPTR(v) FROM dbo.Table1 WHERE (DATEDIFF(mi,starttime,@StartTime) <= 10 AND DATEDIFF(mi,starttime,@StartTime) > 0 AND pid=@PKey and bedno=@BKey) UPDATETEXT dbo.Table1.v @ptr @l 0 @vKey commit transaction t2 END ELSE IF -- in else the new record need to be added (SELECT COUNT(*) FROM dbo.Table1 WHERE (DATEDIFF(mi,starttime,@StartTime) > 10 AND pid=@PKey and bedno=@BKey))>1 BEGIN set transaction isolation level read uncommitted begin transaction t3 INSERT INTO dbo.Table1 (bedno,pid,starttime,hrtype,v) VALUES (@BKey,@PKey,@StartTime,@HKey,@vKey) commit transaction t3 END END Thanks