March 26, 2009 at 6:36 am
Hi,
was wondering if anybody could help. The following query involves multiple sql blocks. Mainly two source tables are used VWVQ1_Inbox and VWLog_Custom. The VWVQ1_Inbox is basically a view and holds not more than 50k records, the VWLog_Custom on the other hand does house approx 3 Million records.
The processing involves use of table variable. The processing part of the batch takes no more than 30 secs. However, there is one simple SELECT Query (in bold) that takes up more than 6 minutes...it is a simple SELECT statement and that too from a Table variable......the no. of records are approx 51K......
Any suggestion please?
[font="Times New Roman"]SET NOCOUNT ON
DECLARE @time_stamp int, @sbu VARCHAR(25)
DECLARE @tmptbl TABLE (F_WorkFlowNumber BINARY(16),F_WobNum BINARY(16),PolicyNo VARCHAR(9),EndorsementNo INT, WorkflowClassification VARCHAR(200),WFInfo VARCHAR(120), Reassured VARCHAR(120), Assured VARCHAR(120), [Description] VARCHAR(140),WF_Name VARCHAR(100),DateWFCreated INT, StepName VARCHAR(100), DateStepCreated INT, CurrentUser VARCHAR(100), OldUser VARCHAR(100), ReassigningUser VARCHAR(100), DateReassigned INT,F_TimeStamp INT,F_EventType INT)
SET @sbu = '5_5_5'
--Selecting from VWV_Inbox
INSERT INTO @tmptbl
SELECT F_WorkFlowNumber,
F_WobNum,
PolicyNo,
EndorsementNo,
WorkflowClassification,
WFInfo,
Reassured,
Assured,
[Description],
F_Subject [WF_Name],
DateWorkflowCreated [DateWFCreated],
00:00:00.000') [DateWFCreated],
StepName,
DateCreated [DateCreated],
[DateCreated],
CurrentUser,
OldUser,
ReassigningUser,
augReassignedDate [augReassignedDate],
0 [F_TimeStamp],
0 [F_EventType]
FROM f_sw.VWVQ1_Inbox WITH (NOLOCK)
WHERE F_Subject LIKE 'Reinsurance%'
AND augSBU = @sbu
--Selecting the reassignment history for records selected vide previous query
INSERT INTO @tmptbl
SELECT a.F_WorkFlowNumber,
a.F_WobNum,
a.PolicyNo,
a.EndorsementNo,
a.WorkflowClassification,
a. [WFInfo],
a.Reassured,
a.Assured,
a. [Description],
a.F_Subject [WF_Name],
a.DateWorkflowCreated [DateWFCreated],
a.StepName,
DateCreated [DateCreated],
a.CurrentUser,
'OldUser' =
CASE a.F_EventType
WHEN 1010 THEN ''
ELSE a.OldUser
END,
'ReassigningUser' =
CASE a.F_EventType
WHEN 1010 THEN ''
ELSE a.ReassigningUser
END,
'DateReassigned' =
CASE a.F_EventType
WHEN 1010 THEN NULL
ELSE a.augReassignedDate
END,
a.F_TimeStamp,
a.F_EventType
FROM f_sw.VWLog_Custom a WITH (NOLOCK) INNER JOIN @tmptbl b ON a.F_WorkFlowNumber = b.F_WorkFlowNumber AND a.F_WobNum = b.F_WobNum
WHERE ((a.F_EventType IN (1010,1020)) OR (a.F_EventType = 125 AND a.F_Text LIKE 'The%in%%Reassigned from%'))
AND a.F_StartTime > 1199181562
AND a.augSBU = @sbu
--If any step is aleady completed, corresponding entries are removed
DELETE a
FROM @tmptbl a
INNER JOIN
(
SELECT F_WorkFlowNumber,F_WobNum,StepName
FROM @tmptbl WHERE F_EventType=1020) b
ON a.F_WorkFlowNumber = b.F_WorkFlowNumber
AND a.F_WobNum = b.F_WobNum
AND a.StepName = b.StepName
--Deleting those records where event_type = 1010 but there is no event type for 125
--(Restructured to reduce logica IO)
--SELECT a.F_WorkFlowNumber,a.F_WobNum,a.StepName,a.F_EventType,b.F_EventType
DELETE a
FROM @tmptbl a
INNER JOIN
(
SELECT F_WorkFlowNumber,F_WobNum, F_EventType
FROM @tmptbl WHERE F_EventType=1010) b
ON a.F_WorkFlowNumber = b.F_WorkFlowNumber
AND a.F_WobNum = b.F_WobNum
WHERE (a.F_EventType 125)
--Inbox records will not have a timestamp, to ensure that the inbox record
--will be the last (for a particular policy) SELECT @time_stamp = MAX(F_TimeStamp)+10 FROM @tmptbl
UPDATE @tmptbl SET F_TimeStamp = @time_stamp WHERE F_TimeStamp = 0
--In case more records with event_type = 1010 qualifies, to ensure that
--only the earliest one survives
DELETE a
FROM @tmptbl a INNER JOIN
(
SELECT F_WobNum, F_EventType, MIN(F_TimeStamp) MTS
FROM @tmptbl
WHERE F_EventType=1010
GROUP BY F_WobNum, F_EventType
HAVING COUNT(F_WobNum)>1
) b ON a.F_WobNum = b.F_WobNum AND a.F_EventType = b.F_EventType
AND a.F_TimeStamp > b.MTS
--When there are event_type= 1010, the record in Inbox and the last record in the VWVCustom the last qualified record are basically the same.
--This delete is to ensure that this duplication is removed
DELETE a
FROM @tmptbl a INNER JOIN
(
SELECT DISTINCT F_WorkFlowNumber, F_WobNum
FROM @tmptbl
WHERE F_EventType=125
) b ON a.F_WorkFlowNumber = b.F_WorkFlowNumber AND a.F_WobNum = b.F_WobNum
AND F_EventType = 0
DELETE @tmptbl WHERE F_EventType=1020
--Final Output
SELECT DISTINCT
PolicyNo [Policy Number],
EndorsementNo [Endorsement Number],
WorkflowClassification [Workflow Classification],
WFInfo [Workflow Info],
Reassured,
Assured,
[Description],
WF_Name [Workflow Name],
[Date Workflow Created] =
CASE DateWFCreated
WHEN 0 THEN NULL
ELSE CONVERT(VARCHAR,DATEADD(second,DateWFCreated,'1970-01-01 00:00:00.000') , 103)+' '+ CONVERT(VARCHAR,DATEADD(second,DateWFCreated,'1970-01-01 00:00:00.000') , 108)
END,
StepName [Step Name],
[Date Step Created] =
CASE DateStepCreated
WHEN 0 THEN NULL
ELSE CONVERT(VARCHAR,DATEADD(second,DateStepCreated,'1970-01-01 00:00:00.000') , 103)+' '+ CONVERT(VARCHAR,DATEADD(second,DateStepCreated,'1970-01-01 00:00:00.000') , 108)
END,
CurrentUser [Current User],
OldUser [Old User],
ReassigningUser [Reassigning User],
[Date Reassigned] =
CASE DateReassigned
WHEN 0 THEN NULL
ELSE CONVERT(VARCHAR,DATEADD(second,DateReassigned,'1970-01-01 00:00:00.000') , 103)+' '+ CONVERT(VARCHAR,DATEADD(second,DateReassigned,'1970-01-01 00:00:00.000') , 108)
END
FROM @tmptbl
Where F_EventType < 1020
ORDER BY PolicyNo, EndorsementNo, Description, StepName,[Date Reassigned]
GO
SET STATISTICS IO OFF[/font]
March 26, 2009 at 9:54 am
This may be one of those cases where you are kind of over the threshold of where a table variable is useful (there are no hard numbers on when that is). It would probably be worth your effort to try rewriting this to use a temp table instead of the table variable and then I would probably even index that (the f_eventtype columns seems like a good starting point for that). It seems to me that even though you are only returning 51,000 rows that your table variable is going through a lot more than that and it seems like a bit much for a table variable. Just a guess based on the information but I would think it would be worth a shot.
March 26, 2009 at 10:08 am
the rule of thimb I've heard around SSC is table variable is fine for 1000 rows or less, more than that it's recommended to use a temp table, and explicitly add a PK or index on that temp table for even better performance.
Lowell
March 26, 2009 at 11:27 pm
r u from mit
March 28, 2009 at 8:42 am
Thank you a lot Mike, Lowell.
Will try out the temporary table concept and update.
Richerds, sorry to disappoint, not from mit.
Regards
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply