April 26, 2006 at 3:57 pm
I have two stored procedures that update a table. The 1st updated a column
called PingReceivedCount. The 2nd proc then kicks off and updates either the
ResponseTrueCount or ResponseFalseCount count depending on results.
The ResponseTrueCount and ResponseFalseCount should be less or equal to the
PingReceivedCount as they are executed within the same session and if there
is a timeout that causes the 1st proc not to execute, the 2nd shouldn't
execute either.
Now we are seeing that the 1st proc is timing out, but the 2nd is still
executing. So the response count is greater than the ping count.
What's happening? If a timeout occur's the 2nd proc shouldn't fire...
Do I need to add additional code to the proc to handle this?
1st proc -
---------------------------------------------------
CREATE Procedure [dbo].usp_LogPingReceived
@affiliateID int
AS
declare @pingDate smalldatetime
BEGIN
set @pingDate = cast(getDate() as smalldatetime)
if (not Exists
(select pingDate from LogPingResponse
where Year(pingDate) = Year(@pingDate)
and Month(pingDate) = Month(@pingDate)
and Day(pingDate) = Day(@pingDate)
and AffiliateID = @affiliateID))
BEGIN
insert into LogPingResponse (pingDate, PingReceivedCount,
ResponseTrueCount, ResponseFalseCount, AffiliateID)
values
(@pingDate, 0,0,0,@affiliateID)
END
update LogPingResponse set PingReceivedCount = PingReceivedCount + 1
where Year(pingDate) = Year(@pingDate)
and Month(pingDate) = Month(@pingDate)
and Day(pingDate) = Day(@pingDate)
and AffiliateID = @affiliateID
END
GO
2nd proc
-----------------------------------------------------------
CREATE Procedure [dbo].usp_LogPingResponse
@affiliateID int,
@pingResult bit
AS
declare @pingDate smalldatetime
BEGIN
set @pingDate = cast(getDate() as smalldatetime)
if (@pingResult = 1)
BEGIN
update LogPingResponse
set ResponseTrueCount = ResponseTrueCount + 1
where Year(pingDate) = Year(@pingDate)
and Month(pingDate) = Month(@pingDate)
and Day(pingDate) = Day(@pingDate)
and AffiliateID = @affiliateID
END
else
BEGIN
update LogPingResponse
set ResponseFalseCount = ResponseFalseCount + 1
where Year(pingDate) = Year(@pingDate)
and Month(pingDate) = Month(@pingDate)
and Day(pingDate) = Day(@pingDate)
and AffiliateID = @affiliateID
END
END
GO
table schema
-----------------------------------------
CREATE TABLE [LogPingResponse] (
[pingDate] [smalldatetime] NULL ,
[PingReceivedCount] [bigint] NULL ,
[ResponseTrueCount] [bigint] NULL ,
[ResponseFalseCount] [bigint] NULL ,
[AffiliateID] [int] NULL
) ON [PRIMARY]
GO
data in tables
--------------------------------
pingDate PingReceivedCount
ResponseTrueCount ResponseFalseCount AffiliateID
------------------------------------------------------ --------------------
-------------------- -------------------- -----------
2006-04-14 00:00:00 973293
18793 954491 0
2006-04-13 00:00:00 986257
18928 967339 0
2006-04-12 00:00:00 1276019
22623 1253405 0
2006-04-11 00:00:00 1356228
25690 1330501 0
2006-04-10 00:00:00 1385917
27641 1358281 0
2006-04-09 00:00:00 1050967
22968 1027937 0
2006-04-08 00:00:00 1128418
25973 1102454 0
2006-04-07 00:00:00 1142605
26279 1114977 0
2006-04-06 00:00:00 1155900
28804 1127096 0
2006-04-05 00:00:00 1183278
28708 1154584 0
2006-04-04 00:00:00 1348119
33810 1313838 0
2006-04-03 00:00:00 1634575
43551 1590727 0
2006-04-02 00:00:00 1420976
31687 1389328 0
2006-04-01 00:00:00 1717251
38353 1677763 0
2006-03-31 00:00:00 1848262
22266 1825971 0
2006-03-30 00:00:00 1661936
23229 1638698 0
2006-03-29 00:00:00 1344536
22405 1321432 0
2006-03-28 00:00:00 1522253
17237 1505002 0
2006-03-27 00:00:00 1493650
18846 1474783 0
April 26, 2006 at 5:25 pm
Everything here is wrong: from initial design to the way SP is written.
If this the the way your system is developed you better hire SQL developer, otherwise you will never make it out of several thousand rows limit.
_____________
Code for TallyGenerator
April 26, 2006 at 5:29 pm
This is my second week here and believe me it's worse than it looks.
We are in the process of re-designing our entire site, but until then, I've been asked to band aid any issues that arise.
Any suggestions would be greatly appreciated.
April 26, 2006 at 6:14 pm
I don't know where to start.
Everything is wrong. Really.
For the beginning, LogPingResponse must be a view, not a table.
Select dbo.DateOnly(RingTime) as pingDate,
-- dbo.DateOnly is a UDF returning only date portion of DATETIME value
COUNT(*) as PingReceivedCount,
COUNT(case when PingResult = 1 then PingResult else NULL end) as ResponseTrueCount,
COUNT(case when PingResult = 0 then PingResult else NULL end) as ResponseFalseCount,
AffiliateId
FROM RingLog -- the table where the actual calls are recorded
Group by dbo.DateOnly(RingTime), AffiliateId
Then you may index this view to make it table. But sometimes it's better not to do it.
And then you can go on.
The thing you posted is a dead end. Trying to make it work you will just waste your time. It's wrong by design.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply