While Loop 100% CPU

Question

While Loop 100% CPU

CrazyMan

SSChampion

Points: 12560
More actions
August 18, 2008 at 3:57 am

#125730

Hi All
I have got a problem with one of my SP on the server which i recently took charge. The job os the SP is to take one record from Table A, then process this record against 4 tables across the same database, and then store the result on Table B, the process takes 4 to 5 secs( which is faster) for a record from start to finish, this is on a while loop, once it completed it checks on table A wether there are any more record, if yes then it takes and process it and if not it exit SP, when this SP runs it takes 100% cpu, thus not allowing any other process to use that server giving timeout,
How can i avoid this?? please post your messages..
Thanks in advance
Cheers
🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

As a quick workaround you can always add a WAITFOR DELAY command in your loop, that would free resources for other sessions to use in each iteration of your WHILE command.

By the way... I'm not sure if I would call good performance a five seconds processing time for a single row.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Sounds about right for a while loop.

Could you post the code here and we'll help you do whatever it is without a while loop. It's usually possible and almost always far faster.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason Crider SSCertifiable Points: 6019 More actions · Answer 3

Would adding the option(maxdop 1) help in this case?

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

CrazyMan SSChampion Points: 12560 More actions · Answer 4

Hi Gial

Sorry about the delay,please find the code attached

CREATE PROCEDURE [dbo].[GetMatch] (@TypeID INT = 0, @Priority INT = 1)
AS
DECLARE @userid BIGINT -- the one requesting matches
DECLARE @service INT -- the service in question
DECLARE @noDates TINYINT -- how many person-to-person good matches are needed
DECLARE @noChats TINYINT -- how many person-to-person chatters are needed
DECLARE @noOps TINYINT -- how many operator matches are needed
DECLARE @opPurpose TINYINT -- operator purpose ---HardCoded!!!!
DECLARE @SendBilled TINYINT -- Used to send free or billed matches
DECLARE @MoreQueueP2PApprovedID INT
DECLARE @SendTrigger TINYINT
DECLARE @TotalMatches TINYINT
DECLARE @UserID2 Varchar(20)
DECLARE @Service2 Varchar(20)
DECLARE @IsTrigger TINYINT
--DECLARE @Priority TINYINT
--Type 0 = normal matches
--Type 1 = MoneyButtons
--Type 2 = Forced Reg Match
--Type 3 = Broadcast MMS no trigger sent
--NB table GenerateMMS column PriceyMT should be renamed to Mtroute as this is set to FreeMT is the SMS triggers to go with the MMS are to be sent Free
WHILE (select count(*) from MoreQueueP2PApproved
WHERE STATUS = 0 AND
TypeID = @TypeID AND
Priority = @Priority AND
Retries < 2 --AND
--Service = '5045'
) > 2
BEGIN
---------------
--Grab first Msg to process
---------------
select TOP 1 @MoreQueueP2PApprovedID = MoreQueueP2PApprovedID, @user-id = UserID, @Service = Service, @NoDates = NoDates,
@noChats = NoChats, @noOps = noOps, @opPurpose = opPurpose, @SendBilled = SendBilled, @SendTrigger = SendTrigger, @TypeID = TypeID,
@Priority = Priority, @IsTrigger = IsTrigger
FROM
MoreQueueP2PApproved
WHERE STATUS = 0 AND
TypeID = @TypeID AND
Retries < 2
Order By Priority, Datestamp desc --Changed 20/12/2007 to reduce backlog...
SET @TotalMatches = ISNULL(@NoDates,0) + ISNULL(@noChats,0) + ISNULL(@noOps,0)
SET @TOTALMatches = CASE WHEN @TOTALMatches = 0 THEN 3 ELSE @TOTALMatches END
SELECT @TotalMatches AS Matches
UPDATE a
SET Status = 2, DateProcessed = GetDate()
from MoreQueueP2PApproved a
WHERE @MoreQueueP2PApprovedID = MoreQueueP2PApprovedID
--------------------------------
--reset Triggers if a more is sent... for MBType = 7
----------------------------------
--clear for specific user
----------------------------
UPDATE a
SET TriggerStateNo = CASE WHEN MBTypeID = 7 AND TriggerStateno > 1 THEN 1
ELSE TriggerStateNo END
--select *
FROM Services a
INNER JOIN defineserviceMB b
ON a.service = b.service
WHERE b.MBTypeID IN (7) AND
(MBTypeID = 7 AND TriggerStateno > 1) AND
@TypeID = 0 AND
a.UserID = @user-id AND
a.Service = @Service AND
a.Registeredb = 1 AND
a.notstoppedb = 1 AND
@IsTrigger = 0
-------------------------
--Clear Cache
-------------------------
insert into Users (Userid, service, status)
select @user-id, @Service, 0
EXEC PrClearCachePLTablePhoneService
---------------------------
--Pull out User
---------------------------
--UserType = 1 -- UserImage
--UserType = 2 -- Operator
SELECT DISTINCT a.userid,Phone,a.service,keyword, a.KeyID,
CASE WHEN b.intvalue BETWEEN 0 AND 23 THEN 1
WHEN ISNULL(b.intvalue,24) BETWEEN 24 AND 25 THEN 2
WHEN b.intvalue BETWEEN 26 AND 30 THEN 3
WHEN b.intvalue BETWEEN 31 AND 40 THEN 4
WHEN b.intvalue BETWEEN 41 AND 100 THEN 5 END AS AgeRange,
ISNULL(c.region,206) AS Region,
CASE WHEN d.intvalue IS NULL THEN i.gender
-- WHEN i.gay <> 1 AND e.Intvalue = 1 AND d.intvalue = 1 THEN 2 -- If Gay Female then pretend they are Male for matching
-- WHEN i.gay <> 1 AND e.Intvalue = 2 AND d.intvalue = 2 THEN 1 -- If Gay Males then pretend they are Female for matching
ELSE d.intvalue END AS gender,
CASE WHEN e.intvalue IS NULL AND i.gender = 1 THEN 2
WHEN e.intvalue IS NULL AND i.gender = 2 THEN 1
ELSE e.intvalue END AS genderpref, i.Aggregator, i.Name, i.Shortcode, 0 AS spent, 0 AS Bill, i.Price,FreeMT, FreeMMS,
CASE WHEN i.smsmatch = 1 OR @SendBilled = 0 THEN 1 ELSE ISNULL(a.SMSMatch,0) END AS SMSMatch, -- Change to send SMS to free matches 31/03/08
CASE WHEN @SendBilled = 0 THEN ISNULL(FreeMT,88) ELSE ISNULL(PriceyMT,89) END AS PriceyMT, i.Country
INTO #UserToMatch
FROM services a (NOLOCK)
LEFT OUTER JOIN CharNumberHas b (NOLOCK)
ON a.userid = b.userid AND b.charistic = 206
LEFT OUTER JOIN Characters2 c (NOLOCK)
ON a.userid = c.userid
LEFT OUTER JOIN CharKeyhas d (NOLOCK)
ON a.userid = d.userid AND d.charistic = 1
LEFT OUTER JOIN (SELECT MAX(Intvalue) AS Intvalue, UserID, Charistic FROM CharKeyPrefer (NoLock)
WHERE Charistic = 1 GROUp BY userid, Charistic) e
ON a.UserID = e.UserID
INNER JOIN Phonelist g (NOLOCK)
ON a.userid = g.userid
INNER JOIN DefineMTRoutes h (NOLOCK)
ON g.aggregator = h.aggregator AND ISNULL(g.network,0) = h.network
INNER JOIN defineAggregators j (NOLOCK)
ON g.aggregator = j.aggregator
INNER JOIN Agsrv i (NOLOCK)
ON a.service = i.service
WHERE a.Registeredb = 1 AND --Changed from activeb to registeredb in order to account for the wap reg service 20/11/07
((@TypeID = 3 AND a.NotStoppedb = 0) OR (@TypeID <> 3 AND a.Notstoppedb = 1)) AND
a.UserID = @user-id AND
a.Service = @Service
------------------
-- fix missing genders
------------------
UPDATE a
SET a.Gender = CASE WHEN Gender = 0 OR Gender IS NULL THEN 2 ELSE Gender END,
a.GenderPref = CASE WHEN GenderPref = 0 OR GenderPref IS NULL THEN 1 ELSE GenderPref END
from
#UserToMatch a
DECLARE @Keyword INT
DECLARE @Phone Varchar(15)
DECLARE @MMSRoute INT
DECLARE @FreeMT INT
DECLARE @FreeMMS INT
DECLARE @SMSMatch INT
DECLARE @KeyID INT
DECLARE @Price INT
DECLARE @Aggregator INT
DECLARE @Name Varchar(255)
DECLARE @Shortcode Varchar(20)
DECLARE @PriceyMT INT
DECLARE @Country Varchar(2)
SELECT @SMSMatch = SMSMatch,@Name = Name, @Shortcode = Shortcode, @Price = Price,
@Aggregator = Aggregator, @Service = Service, @Keyword = Keyword,@KeyID = KeyID,
@Phone = Phone, @MMSRoute = 12471,@PriceyMT = PriceyMT, @FreeMT = FreeMT, @FREEMMS = FreeMMS,
@Country = Country
FROM #UserToMatch
DECLARE @dateStamp AS DateTime
SET @DateStamp = CASE WHEN @Priority = 1 AND @TypeID <> 3 THEN Getdate() --Send now if an inreg trigger else send after 4:30pm if a MoneyButton
WHEN (@Priority <> 1 OR @TypeID = 3) AND @Country <> 'UK' THEN DATEADD(n,CAST(RAND(LEFT(ISNULL(5677,123456),6) * DATEPART(ms, GETDATE()))*1000 AS BIGINT) % 60, CONVERT(datetime,CONVERT(VARCHAR(50), getdate(), 106) + ' 10:00:00:00
0', 113))
ELSE DATEADD(n,CAST(RAND(LEFT(ISNULL(5677,123456),6) * DATEPART(ms, GETDATE()))*1000 AS BIGINT) % 270, CONVERT(datetime,CONVERT(VARCHAR(50), getdate(), 106) + ' 16:30:00:000', 113))
END
---------------------------
--Pull out Real Matches
---------------------------
--ALTER PROCEDURE PrCalcMatchesValidP2PApproved AS
---------------------------
--Remove inactive Matches
---------------------------
DELETE A
--select count(*)
FROM MatchesValidP2PApproved a (NOLOCK)
LEFT OUTER JOIN Services b ON a.USERID = b.USERID AND a.SErvice = b.Service
WHERE b.Activeb = 0 OR
b.UserID IS NULL OR
b.p2pprofileapproved = 0
---------------------------
--load in matches
---------------------------
SELECT a.userid AS Matchee, a.service, b.userid AS Matcher, a.lastcall, Type AS UserType,
CASE WHEN a.Actualregion = b.Region THEN 2
WHEN a.Region > 200 and a.ActualRegion BETWEEN 200 AND 207 THEN 1
ELSE 0 END AS Sort
INTO #MatchesValid
FROM MatchesValidP2PApproved a (NOLOCK)
INNER JOIN #UserToMatch b
ON a.age BETWEEN (CASE WHEN AgeRange = 0 THEN 18
WHEN AgeRange = 1 THEN 18
WHEN AgeRange = 2 THEN 18
WHEN AgeRange = 3 THEN 22
WHEN AgeRange = 4 THEN 23
WHEN AgeRange = 5 THEN 40 ELSE 0 END)
AND (CASE WHEN AgeRange = 0 THEN 100
WHEN AgeRange = 1 THEN 25
WHEN AgeRange = 2 THEN 35
WHEN AgeRange = 3 THEN 35
WHEN AgeRange = 4 THEN 45
WHEN AgeRange = 5 THEN 100 ELSE 0 END)
AND
((a.genderpref = 3 AND b.gender IN (1,2)) OR (
a.genderpref = b.gender AND a.gender = b.genderpref )) AND
a.userid <> b.userid AND
((b.region BETWEEN
(CASE WHEN a.Region >= 200 THEN 200 ELSE a.region END) AND
(CASE WHEN a.Region >= 200 THEN 207 ELSE a.region END)) OR (Type = 2))
WHERE (a.service IN ( SELECT a.service
FROM DefineService a (NOLOCK) -- Valid services of Receiver
CROSS JOIN DefineService b (NOLOCK) -- Definition of Sender's service(s)
WHERE (b.service = @Service) AND
( (a.service = b.service) OR -- In the same service
((a.pooling > 0) AND (b.pooling = a.pooling) ) -- In the same pool
) )
OR
(a.userid IN ( SELECT userid
FROM DefineRobots (NOLOCK)
WHERE ((@TypeID IN (1,2) AND TriggerMsg IS NOT NULL)OR
(@TypeID IN (0,3))) AND
((@OpPurpose <> 42 AND purpose = @OpPurpose) OR
(@OpPurpose = 42 AND Purpose IN (43,44))) -- Removed to include all pools
) ))
--AND c.Matchee IS NULL
ORDER BY NEWID()
--Load Backup Matches
INSERT INTO #MatchesValid
SELECT a.userid AS Matchee, a.service, b.userid AS Matcher, a.lastcall, 3 AS UserType, 0 AS Sort
FROM MatchesValidP2PApproved a (NOLOCK)
INNER JOIN #UserToMatch b
ON ((a.genderpref = 3 AND b.gender IN (1,2)) OR
(a.genderpref = b.gender AND a.gender = b.genderpref)
) AND
a.userid <> b.userid
LEFT OUTER JOIN #MatchesValid c
ON a.userid = c.Matchee AND
b.UserID = c.Matcher
-- LEFT OUTER JOIN P2PApprovedMatchesSeen c ON b.userid = c.Matcher AND a.userid = c.Matchee --- Make sure match not already sent
WHERE (a.userid IN ( SELECT userid
FROM DefineRobots (NOLOCK)
WHERE ((@TypeID IN (1,2) AND TriggerMsg IS NOT NULL)OR
(@TypeID = 0)) AND
((@OpPurpose <> 42 AND purpose = @OpPurpose) OR
(@OpPurpose = 42 AND Purpose IN (43,44)))))
AND c.Matchee IS NULL
ORDER BY NEWID()
--36 -----------------------------------------
--Choose Matches
-----------------------------------------
CREATE TABLE [dbo].#MatchesToUse(
[Matchee] [bigint] NOT NULL,
[service] [int] NOT NULL,
[Matcher] [bigint] NOT NULL,
[lastcall] [int] NULL,
[UserType] [int] NOT NULL,
[sort] [int] NOT NULL
) ON [PRIMARY]
SELECT d.*
INTO #P2PApprovedMatchesSeen
FROM #MatchesValid a
INNER JOIN P2PApprovedMatchesSeen d ON a.Matcher = d.Matcher AND a.Matchee = d.Matchee
-----------------------------------------
--Insert Dates
-----------------------------------------
SET ROWCOUNT @noDates
IF @NoDates > 0
BEGIN
INSERT INTO #MatchesToUse
SELECT a.*
FROM #MatchesValid a
LEFT OUTER JOIN #MatchesToUse b ON a.Matchee = b.Matchee
LEFT OUTER JOIN #P2PApprovedMatchesSeen d ON a.Matcher = d.Matcher AND a.Matchee = d.Matchee --- Make sure match not already sent
WHERE b.Matchee IS NULL AND
a.UserType = 1 AND
d.Matchee IS NULL
ORDER BY Sort DESC, NewID()
END
--SELECT @NoDates AS dates
-----------------------------------------
--Insert Chatters
-----------------------------------------
SET ROWCOUNT @noChats
IF @noChats > 0
BEGIN
INSERT INTO #MatchesToUse
SELECT a.*
FROM #MatchesValid a
LEFT OUTER JOIN #MatchesToUse b ON a.Matchee = b.Matchee
LEFT OUTER JOIN #P2PApprovedMatchesSeen d ON a.Matcher = d.Matcher AND a.Matchee = d.Matchee --- Make sure match not already sent
WHERE b.Matchee IS NULL AND
a.UserType = 1 AND
d.Matchee IS NULL
ORDER BY Sort DESC, a.LASTCALL
END
--SELECT * FROM #MatchesToUse
--SELECT @noChats AS Chats
-----------------------------------------
--Insert Ops
-----------------------------------------
SET ROWCOUNT @noOps
IF @noOps > 0
BEGIN
INSERT INTO #MatchesToUse
SELECT a.*
FROM #MatchesValid a
LEFT OUTER JOIN #MatchesToUse b ON a.Matchee = b.Matchee
LEFT OUTER JOIN #P2PApprovedMatchesSeen d ON a.Matcher = d.Matcher AND a.Matchee = d.Matchee --- Make sure match not already sent
WHERE b.Matchee IS NULL AND
a.UserType = 2 AND
d.Matchee IS NULL
ORDER BY NEWID()
END
DECLARE @ROW INT
SET ROWCOUNT 0
IF (SELECT @TotalMatches-COUNT(*) FROM #MatchesToUse) > 0
BEGIN
SET @ROW = 0
SET @ROW = (SELECT @TotalMatches-COUNT(*) FROM #MatchesToUse)
SET ROWCOUNT @ROW
INSERT INTO #MatchesToUse
SELECT a.*
FROM #MatchesValid a
LEFT OUTER JOIN #MatchesToUse b ON a.Matchee = b.Matchee
LEFT OUTER JOIN #P2PApprovedMatchesSeen d ON a.Matcher = d.Matcher AND a.Matchee = d.Matchee --- Make sure match not already sent
WHERE b.Matchee IS NULL AND
a.UserType = 2 AND
d.Matchee IS NULL
ORDER BY NEWID()
END
SET ROWCOUNT 0
-----------------------------------------------
-- If still no matches send random op profiles
-----------------------------------------------
IF (SELECT @TotalMatches-COUNT(*) FROM #MatchesToUse) > 0
BEGIN
SET @ROW = 0
SET @ROW = (SELECT @TotalMatches-COUNT(*) FROM #MatchesToUse)
SET ROWCOUNT @ROW
INSERT INTO #MatchesToUse
SELECT a.*
FROM #MatchesValid a
LEFT OUTER JOIN #MatchesToUse b ON a.Matchee = b.Matchee
LEFT OUTER JOIN #P2PApprovedMatchesSeen d ON a.Matcher = d.Matcher AND a.Matchee = d.Matchee --- Make sure match not already sent
WHERE b.Matchee IS NULL AND
a.UserType = 3 AND
d.Matchee IS NULL
ORDER BY NEWID()
END
-------------------------------------------------------------------
-----------------------------------------------------
--If there are no matches because the users seen them all
-----------------------------------------------------
-------------------------------------------------------------------
SET ROWCOUNT 0
IF (SELECT COUNT(*) FROM #MatchesToUse) < @TotalMatches
BEGIN
--------------------------
--Find oldest TriggerMatch
--------------------------
SET @ROW = 0
SELECT @ROW = @TotalMatches-COUNT(*) FROM #MatchesToUse
SET ROWCOUNT @ROW
SELECT P2PMatchesSeenApprovedID
INTO #MatchesToRemove
FROM #MatchesValid a
INNER JOIN #P2PApprovedMatchesSeen b ON a.Matchee = b.Matchee
INNER JOIN DefineRobots c (NOLOCK) ON a.Matchee = c.UserID
LEFT OUTER JOIN #MatchesToUse d ON a.Matchee = d.Matchee
WHERE a.UserType IN (2,3) AND
d.Matchee IS NULL
ORDER BY b.Datestamp
--------------------------
--Remove oldest trigger match for resending
--------------------------
SET ROWCOUNT 0
DELETE a
FROM P2PApprovedMatchesSeen a
INNER JOIN #MatchesToRemove b
ON a.P2PMatchesSeenApprovedID = b.P2PMatchesSeenApprovedID
DROP TABLE #MatchesToRemove
TRUNCATE TABLE #P2PApprovedMatchesSeen
INSERT INTO #P2PApprovedMatchesSeen
SELECT DISTINCT d.* FROM #MatchesValid a
INNER JOIN P2PApprovedMatchesSeen d ON a.Matcher = d.Matcher AND a.Matchee = d.Matchee
--------------------------
--Grab deleted Trigger Match
--------------------------
SET @ROW = 0
SELECT @ROW = @TotalMatches-COUNT(*) FROM #MatchesToUse
SET ROWCOUNT @ROW
INSERT INTO #MatchesToUse
SELECT a.*
FROM #MatchesValid a
LEFT OUTER JOIN #MatchesToUse b ON a.Matchee = b.Matchee
LEFT OUTER JOIN #P2PApprovedMatchesSeen d ON a.Matcher = d.Matcher AND a.Matchee = d.Matchee --- Make sure match not already sent
WHERE b.Matchee IS NULL AND
a.UserType IN (2,3) AND
d.Matchee IS NULL
ORDER BY NEWID()
END
SET ROWCOUNT 0
--SELECT * FROM #MatchesToUse
-----------------------------------------
--Store Used Matches
-----------------------------------------
INSERT INTO P2PApprovedMatchesSeen (Matchee, Matcher)
SELECT a.Matchee, a.Matcher AS Matcher
FROM #MatchesToUse a
GROUP BY a.Matchee, a.Matcher
-----------------------------
--Bill them for the alert MMS Matches
-----------------------------
--DECLARE @noDates INT
--DECLARE @noChats INT
--DECLARE @noOps INT
--
--set @noDates = 1
--set @noChats = 1
--set @noOps = 1
-----------------------------
--Send them a Billed Message - 3 mins delay
-----------------------------
IF (@SendTrigger = 0 AND @TypeID = 0) --OR (@TypeID = 0 AND @Smsmatch = 1)) Removed 050308
BEGIN
INSERT INTO DBSRVR2.Queues.DBO.Outqueuepre (Phone, Aggregator, Service, Keyword,KeyID, Result, WhichQueue,
Msg, Price, Billable)
SELECT DISTINCT @Phone AS Phone, @Aggregator, @Service AS Service, ISNULL(@Keyword,2) AS Keyword,@KeyId AS KeyID,
80 AS Result,1 AS WhichQueue,
ISNULL(REPLACE(REPLACE(dbo.DefineMsgsFullString (@Service, 80, 1), 'VVV',@Shortcode),'ZZZ',@Name),
'To chat to any of your matches, start ur msg with their nickname: eg M12345 U sound great, want to chat? To get new matches, text MORE.')
AS Msg, @Price,@SendBilled
END
-----------------------------
--Store the LASTMT
-----------------------------
UPDATE b
SET LASTMT = DATEADD(Minute, 3,Getdate())
FROM
#UserToMatch a
INNER JOIN services b
ON a.UserID = b.userid AND
a.service = b.service
----------------
--Clear Services table Cache
--------------------------------------------------------------
SET @UserID2 = CAST(@UserID AS Varchar(20))
SET @Service2 = CAST(@Service AS Varchar(20))
EXEC [PrClearCacheServicePL] @UserID2, @Service2
---------------------------------------------------------------
----------------
----------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
--If MMS matches are required
------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
IF (SELECT SMSMatch FROM #UserToMatch) = 0
BEGIN
-----------------------------
--Generate MMS XML
-----------------------------
DECLARE @ID INT
-----------------------------
--Create MMS Receiver
-----------------------------
-----------------------------
--SET MMS Logo based on SubserviceTypeID
-----------------------------
DECLARE @DefineServiceMMSID INT
SELECT @DefineServiceMMSID = ISNULL(DefineServiceMMSID,1)
FROM DefineSErvice a (NOLOCK)
LEFT OUTER JOIN DefineServiceMMS b (NOLOCK)
ON a.SubserviceTypeID = b.SubserviceTypeID AND
b.TypeID = @TypeID
WHERE a.service = @Service
--------------------------------
--Set send date
---------------------------------
SET @DateStamp = CASE WHEN @Priority = 1 AND @TypeID <> 3 THEN Getdate() --Send now if an inreg trigger else send after 4:30pm if a MoneyButton
WHEN (@Priority <> 1 OR @TypeID = 3) AND @Country <> 'UK' THEN DATEADD(n,CAST(RAND(LEFT(ISNULL(5677,123456),6) * DATEPART(ms, GETDATE()))*1000 AS BIGINT) % 60, CONVERT(datetime,CONVERT(VARCHAR(50), getdate(), 106) + ' 10:00:00:000'
, 113))
ELSE DATEADD(n,CAST(RAND(LEFT(ISNULL(5677,123456),6) * DATEPART(ms, GETDATE()))*1000 AS BIGINT) % 270, CONVERT(datetime,CONVERT(VARCHAR(50), getdate(), 106) + ' 16:30:00:000', 113))
END
EXEC @ID = DBSRVR3.FileServer.dbo.PrInsertGenerateMMS @DefineServiceMMSID, @Service, @Keyword, @Phone,@PriceyMT, @TypeID, @datestamp, @FreeMMS, @userid
--PriceyMT was removed...
DECLARE @matches INT
SELECT @matches = COUNT(*) FROM #MatchesToUse
-----------------------------
--Insert Body of MMS
-----------------------------
INSERT INTO DBSRVR3.FileServer.dbo.GenerateMMSProfile (GenerateMMSID, UserID, ServiceID)
SELECT @ID, Matchee,Service
FROM #MatchesToUse
ORDER BY NEWID()
--Charge for MMS Matches only for service 3206
IF @@RowCount > 0 AND @matches > 1 AND @Service = 3206
BEGIN
SET @matches = @matches * @Price
INSERT INTO dbsrvr2.queues.dbo.outqueuepre (Phone, Aggregator, service, Keyword,KeyID, result, msg, price, billable, send)
select @Phone,@Aggregator, @Service, @Keyword, @KeyID, 80, 'MMS Matches', @matches, 1,0
WHERE @Service = 3206
END
IF (SELECT COUNT(*) FROM #MatchesToUse) >= 1
BEGIN
UPDATE a
SET Status = 3, DateProcessed = GetDate(), WasSMSMatch = @Smsmatch
from MoreQueueP2PApproved a
WHERE @MoreQueueP2PApprovedID = MoreQueueP2PApprovedID AND
Status = 2
END
UPDATE a
SET Status = 95, DateProcessed = GetDate(), WasSMSMatch = @Smsmatch
from MoreQueueP2PApproved a
WHERE @MoreQueueP2PApprovedID = MoreQueueP2PApprovedID AND
Status = 2
END
----------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
--If SMS matches are required
------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
IF (SELECT SMSMatch FROM #UserToMatch) = 1
BEGIN
-- temp fix to add profile if its missing...
INSERT INTO charstringinfo (userid, Charistic, Strvalue)
select a.userid, 300, Profile
from definerobots a
inner join services b on a.userid = b.userid and b.P2PProfileApproved = 1
left outer join charstringinfo c on b.userid = c.userid and c.charistic = 300
where c.userid is null
------------------------------------
SELECT DISTINCT TOP 7 LEFT(RTRIM(b.Nickname) + ' : ' + e.StrValue,160) AS Msg
INTO #OutMsgs
FROM #MatchesToUse a
INNER JOIN Services b (NOLOCK)
ON a.Matchee = b.UserID AND
a.Service = b.Service
INNER JOIN CharStringInfo e (NOLOCK)
ON b.USerID = e.UserID AND
e.Charistic = 300
DECLARE @INSERTED INT
INSERT INTO dbsrvr2.queues.dbo.OutqueuePre
(Phone, Aggregator, Service, Keyword,KeyID, Result, WhichQueue,
Msg, Price, datestamp,billable)
SELECT @Phone,@Aggregator, @Service, @Keyword, @KeyID, 80 AS Result, CASE WHEN @TypeID = 0 THEN 0 ELSE 1 END AS WhichQueue, Msg, @Price, @DateStamp,@SendBilled
FROM #OutMsgs
SET @Inserted = @@RowCount
select @Inserted AS MatchesAdded
-------------------------------------------------
--Mark as dealt with
-------------------------------------------------
IF (SELECT COUNT(*) FROM #MatchesToUse) >= 1
BEGIN
UPDATE a
SET Status = 3, DateProcessed = GetDate(), WasSMSMatch = @Smsmatch
from MoreQueueP2PApproved a
WHERE @MoreQueueP2PApprovedID = MoreQueueP2PApprovedID AND
Status = 2
END
UPDATE a
SET Status = 95, DateProcessed = GetDate(), WasSMSMatch = @Smsmatch
from MoreQueueP2PApproved a
WHERE @MoreQueueP2PApprovedID = MoreQueueP2PApprovedID AND
Status = 2
DROP TABLE #OutMsgs
END
-------------------------------------------------
--Send Type 0's --taken out 8/3/2008 added as new job to increase speed
-------------------------------------------------
--
--IF @TypeID = 0
--
--BEGIN
--
--EXEC DBSRVR3.Fileserver.dbo.PrGenerateMMSMTXML @TypeID
--
--END
-----------------------------
--Send Trigger Match if required
-----------------------------
IF (@SendTrigger = 1) OR (@SMSMatch > 0 AND @TypeID = 1) --If an SMS MButton match send trigger now, MMS triggers sent after mms is generated
BEGIN
-----------------------------
--Send them a Trigger Message
-----------------------------
CREATE TABLE #MMSTriggerProfiles(
[P2PMatchesSeenApprovedID] [int],
[Matchee] [bigint] NOT NULL,
[Matcher] [bigint] NOT NULL,
[DateStamp] [smalldatetime] NOT NULL,
[LastChat] [smalldatetime] NULL,
[SentTrigger] [tinyint] NULL
)
INSERT INTO #MMSTriggerProfiles
SELECT TOP 1 b.*
FROM P2PApprovedMatchesSeen b (NOLOCK)
INNER JOIN #UserToMatch c ON b.Matcher = c.UserID
WHERE senttrigger = 0
ORDER BY P2PMatchesSeenApprovedID desc
-----------------------------
--MArk trigger as sent
-----------------------------
UPDATE a
SET senttrigger = 1
FROM P2PApprovedMatchesSeen a
INNER JOIN #MMSTriggerProfiles b ON a.P2PMatchesSeenApprovedID = b.P2PMatchesSeenApprovedID
------------------------------------
--Grab the Profile for that match
--If no trigger exists pick a random one...
------------------------------------
--SELECT DISTINCT
-- RTRIM(Nickname) + ' - ' + TriggerMsg AS String
----select b.*
--INTO #trigger
--FROM #MMSTriggerProfiles a
-- INNER JOIN Outnow.dbo.DefineRobots b (NOLOCK)
-- ON a.Matchee = b.USerID
--WHERE b.triggerMsg <> '' AND b.triggermsg is not null
SELECT DISTINCT
LEFT(RTRIM(Nickname) + ' - ' + b.TriggerMsg + ' ' + ISNULL(c.Tip,''),160) AS String
--select b.*
INTO #trigger
FROM #MMSTriggerProfiles a
INNER JOIN Outnow.dbo.DefineRobots b (NOLOCK)
ON a.Matchee = b.USerID
LEFT OUTER JOIN DoggingTips c (NOLOCK)
ON b.Purpose = c.purpose AND
c.ID = CAST(49*RAND() AS INT)+1
WHERE b.triggerMsg <> '' AND b.triggermsg is not null
------------------------------------
--Send Triggers
------------------------------------
DECLARE @SendDate Datetime
SET @SendDate = (CASE WHEN @TypeID = 0 THEN DATEADD(Minute, 3,getdate())
ELSE @DateStamp END)
INSERT INTO DBSRVR2.Queues.DBO.OutqueuePre (Phone, Aggregator, Service, Keyword,KeyID, Result, WhichQueue,
Msg,Datestamp, Price,billable)
SELECT DISTINCT @Phone,@Aggregator, @Service, @Keyword, @KeyID, 80 AS Result,1 AS WhichQueue, String AS Msg, @SendDate, @Price,@SendBilled
FROM #trigger
WHERE String is not null
--Add Trigger Msgs to system messages
INSERT INTO DBSRVR5.virtualcallcentre.dbo.SystemMessage (inqueueId, serviceId, userId, msgDate, messageText)
SELECT 0 AS msgid, @service, @user-id, @SendDate, String
FROM #trigger
WHERE String is not null
DROP TABLE #MMSTriggerProfiles
DROP TABLE #Trigger
END
DECLARE @TotalMsgs INT
SET @TotalMsgs = @noDates + @noChats + @noOps
---------------------------
--Drop temp tables
---------------------------
drop table #UserToMatch
drop table #MatchesToUse
drop table #MatchesValid
DROP TABLE #P2PApprovedMatchesSeen
END
-----------------------------------------------
--Send Type 1's where it's a bulk Trigger
-----------------------------------------------
IF @TypeID = 1 AND @Priority = 2
BEGIN
---------------------
----Record those already generated ---ADd in service
---------------------
EXEC DBSRVR3.Fileserver.dbo.PRMMSUseExisting
END