More efficient?

Question

Post reply

More efficient?

evaleah

Ten Centuries

Points: 1372
More actions
July 30, 2005 at 6:42 am

#88224

Can anyone help me find a way to make the following query more effecient?  I can't think of any other way to do this combination of so many tables and cases.  Maybe someone can look at it and give me some suggestions?
If you need clarification, please feel free to ask me for it.
Just as a note I do an insert into a temp table and not a union because I need the total figured at the end.
CREATE PROCEDURE spReportCoordinatorTimeTracking
@coorid bigint = NULL,
@begin datetime = '1/1/1900',
@end datetime = '12/31/3000'
AS
CREATE TABLE #Final (
Protocol varchar(41),
PIName varchar(75),
Status varchar(50),
PatientName varchar(75),
ByWhom varchar(75),
Title varchar(200),
Category varchar(200),
TimeSpent smallmoney,
EventDate datetime
)
INSERT #Final
SELECT
Protocol =
  (SELECT Name FROM vwProtocolNumber
  WHERE vwProtocolNumber.ProtocolID = CASE ML.ModSpecificID
   WHEN 7 THEN (SELECT min(ProtocolID) FROM tb_PatientEnrollment
    WHERE tb_PatientEnrollment.PatientID = ML.MajorAreaID)
   WHEN 50 THEN (SELECT ProtocolID FROM tb_PatientEnrollment
    INNER JOIN tb_PatientQuery ON tb_PatientQuery.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID
    WHERE tb_PatientQuery.PatientQueryID = ML.FieldID)
   WHEN 42 THEN (SELECT ProtocolID FROM tb_PatientEnrollment
    INNER JOIN tb_PatientTreatment ON tb_PatientTreatment.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID
    WHERE tb_PatientTreatment.PatientTreatmentID = ML.FieldID)
   WHEN 41 THEN (SELECT ProtocolID FROM tb_ProtocolArm
    INNER JOIN tb_ProtocolArmProcedure ON tb_ProtocolArmProcedure.ProtocolArmID = tb_ProtocolArm.ProtocolArmID
    WHERE tb_ProtocolArmProcedure.ProtocolArmProcedureID = ML.FieldID)
   WHEN 44 THEN (SELECT ProtocolID FROM tb_PatientContact
    WHERE tb_PatientContact.PatientContactID = ML.FieldID)
   ELSE ML.MajorAreaID
   END),
PIName =
  (SELECT MemberName FROM vwMemberNames
  INNER JOIN tb_Protocol ON tb_Protocol.PIMemberID = vwMemberNames.MemberID
  WHERE tb_Protocol.ProtocolID = CASE ML.ModSpecificID
   WHEN 7 THEN (SELECT min(ProtocolID) FROM tb_PatientEnrollment
    WHERE tb_PatientEnrollment.PatientID = ML.MajorAreaID)
   WHEN 50 THEN (SELECT ProtocolID FROM tb_PatientEnrollment
    INNER JOIN tb_PatientQuery ON tb_PatientQuery.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID
    WHERE tb_PatientQuery.PatientQueryID = ML.FieldID)
   WHEN 42 THEN (SELECT ProtocolID FROM tb_PatientEnrollment
    INNER JOIN tb_PatientTreatment ON tb_PatientTreatment.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID
    WHERE tb_PatientTreatment.PatientTreatmentID = ML.FieldID)
   WHEN 41 THEN (SELECT ProtocolID FROM tb_ProtocolArm
    INNER JOIN tb_ProtocolArmProcedure ON tb_ProtocolArmProcedure.ProtocolArmID = tb_ProtocolArm.ProtocolArmID
    WHERE tb_ProtocolArmProcedure.ProtocolArmProcedureID = ML.FieldID)
   WHEN 44 THEN (SELECT ProtocolID FROM tb_PatientContact
    WHERE tb_PatientContact.PatientContactID = ML.FieldID)
   ELSE ML.MajorAreaID
   END),
Status =
  (SELECT Status FROM vwProtocol
  WHERE vwProtocol.ProtocolID = CASE ML.ModSpecificID
   WHEN 7 THEN (SELECT min(ProtocolID) FROM tb_PatientEnrollment
    WHERE tb_PatientEnrollment.PatientID = ML.MajorAreaID)
   WHEN 50 THEN (SELECT ProtocolID FROM tb_PatientEnrollment
    INNER JOIN tb_PatientQuery ON tb_PatientQuery.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID
    WHERE tb_PatientQuery.PatientQueryID = ML.FieldID)
   WHEN 42 THEN (SELECT ProtocolID FROM tb_PatientEnrollment
    INNER JOIN tb_PatientTreatment ON tb_PatientTreatment.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID
    WHERE tb_PatientTreatment.PatientTreatmentID = ML.FieldID)
   WHEN 41 THEN (SELECT ProtocolID FROM tb_ProtocolArm
    INNER JOIN tb_ProtocolArmProcedure ON tb_ProtocolArmProcedure.ProtocolArmID = tb_ProtocolArm.ProtocolArmID
    WHERE tb_ProtocolArmProcedure.ProtocolArmProcedureID = ML.FieldID)
   WHEN 44 THEN (SELECT ProtocolID FROM tb_PatientContact
    WHERE tb_PatientContact.PatientContactID = ML.FieldID)
   ELSE ML.MajorAreaID
   END),
PatientName =
  (SELECT PatientName FROM vwPatientNames
  WHERE vwPatientNames.PatientID = CASE ML.ModSpecificID
   WHEN 7 THEN ML.MajorAreaID
   WHEN 50 THEN ML.MajorAreaID
   WHEN 19 THEN (SELECT PatientID FROM tb_PatientEnrollment
    WHERE tb_PatientEnrollment.PatientEnrollmentID = ML.FieldID)
   WHEN 42 THEN ML.MajorAreaID
   WHEN 44 THEN ML.MajorAreaID
   WHEN 2 THEN (SELECT PatientID FROM tb_SAETracking
    WHERE tb_SAETracking.SAEID = ML.FieldID) END),
ByWhom =
  (SELECT MemberName FROM vwMemberNames
  INNER JOIN tb_Membership ON
  tb_Membership.MemberID = vwMemberNames.MemberID
  WHERE tb_Membership.UserName = ML.ModifiedBy),
Title = CASE ML.ModSpecificID
  WHEN 44 THEN (SELECT Title FROM tb_PatientContact
    WHERE tb_PatientContact.PatientContactID = ML.FieldID)
  ELSE
  (SELECT (SELECT ModAction FROM tb_ModAction WHERE tb_ModAction.ModActionID = ML.ModActionID) + ' ' +
  (SELECT ModSpecific FROM tb_ModSpecific WHERE tb_ModSpecific.ModSpecificID = ML.ModSpecificID))
  END,
Category = CASE ModSpecificID
  WHEN 9 THEN 'New Study Creation'
  WHEN 6 THEN 'New Study Creation'
  WHEN 1 THEN 'New Study Creation'
  WHEN 5 THEN 'New Study Creation'
  WHEN 7 THEN 'Registration'
  WHEN 50 THEN 'Data Processing'
  WHEN 19 THEN 'Registration'
  WHEN 42 THEN 'Intervention'
  WHEN 40 THEN 'New Study Creation'
  WHEN 41 THEN 'New Study Creation'
  WHEN 37 THEN 'New Study Creation'
  WHEN 47 THEN 'New Study Creation'
  WHEN 48 THEN 'New Study Creation'
  WHEN 49 THEN 'New Study Creation'
  WHEN 44 THEN (SELECT PatientContactCategory FROM tb_PatientContactCategory PCC
   INNER JOIN tb_PatientContact ON PCC.PatientContactCategoryID = tb_PatientContact.CategoryID
   WHERE tb_PatientContact.PatientContactID = ML.FieldID)
  WHEN 2 THEN 'SAE'
  ELSE 'Misc' END,
TimeSpent = CASE ModSpecificID
  WHEN 44 THEN (SELECT TimeSpent FROM tb_PatientContact
   WHERE tb_PatientContact.PatientContactID = ML.FieldID)
  ELSE CASE ModActionID
  WHEN 1 THEN (SELECT CreateTime FROM tb_ModSpecific
   WHERE tb_ModSpecific.ModSpecificID = ML.ModSpecificID)
  WHEN 2 THEN (SELECT UpdateTime FROM tb_ModSpecific
   WHERE tb_ModSpecific.ModSpecificID = ML.ModSpecificID)
  END END,
EventDate = CASE ModSpecificID
  WHEN 44 THEN (SELECT ContactDate FROM tb_PatientContact
   WHERE tb_PatientContact.PatientContactID = ML.FieldID)
  ELSE ML.ModificationDate END
FROM
tb_ModificationLog ML
INNER JOIN tb_Membership ON
tb_Membership.UserName = ML.ModifiedBy
WHERE
tb_Membership.MemberID = CASE
  WHEN @coorid is NULL THEN tb_Membership.MemberID
  ELSE @coorid END
AND
ML.ModificationDate >= CASE
  WHEN @begin is NULL THEN '1/1/1950'
  ELSE @begin END
AND
ML.ModificationDate <= CASE
  WHEN @end is NULL THEN '12/31/2049'
  ELSE @end END
AND
ML.ModSpecificID <> 46
AND
ML.ModSpecificID <> 39
AND
ML.ModSpecificID <> 1
AND
ML.ModActionID <> 4
INSERT #Final
SELECT
Protocol =
  (SELECT Name FROM vwProtocolNumber
  WHERE vwProtocolNumber.ProtocolID =
   (SELECT ProtocolID FROM tb_PatientEnrollment
   INNER JOIN tb_PatientDataTracking ON tb_PatientDataTracking.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID
   WHERE tb_PatientDataTracking.PatientDataTrackingID = ML.FieldID)),
PIName =
  (SELECT MemberName FROM vwMemberNames
  INNER JOIN tb_Protocol ON tb_Protocol.PIMemberID = vwMemberNames.MemberID
  WHERE tb_Protocol.ProtocolID =
   (SELECT ProtocolID FROM tb_PatientEnrollment
   INNER JOIN tb_PatientDataTracking ON tb_PatientDataTracking.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID
   WHERE tb_PatientDataTracking.PatientDataTrackingID = ML.FieldID)),
Status =
  (SELECT Status FROM vwProtocol
  WHERE vwProtocol.ProtocolID =
   (SELECT ProtocolID FROM tb_PatientEnrollment
   INNER JOIN tb_PatientDataTracking ON tb_PatientDataTracking.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID
   WHERE tb_PatientDataTracking.PatientDataTrackingID = ML.FieldID)),
PatientName =
  (SELECT PatientName FROM vwPatientNames
  WHERE vwPatientNames.PatientID = ML.MajorAreaID),
ByWhom =
  (SELECT MemberName FROM vwMemberNames
  INNER JOIN tb_Membership ON
  tb_Membership.MemberID = vwMemberNames.MemberID
  WHERE tb_Membership.UserName = ML.ModifiedBy),
Title =
  (SELECT (SELECT ModAction FROM tb_ModAction WHERE tb_ModAction.ModActionID = ML.ModActionID) + ' ' +
  (SELECT ModSpecific FROM tb_ModSpecific WHERE tb_ModSpecific.ModSpecificID = ML.ModSpecificID)),
Category = 'Data Processing',
TimeSpent = CASE ModActionID
  WHEN 1 THEN ((SELECT Pages FROM tb_ProtocolForm
   INNER JOIN tb_PatientDataTracking PDT ON
   PDT.ProtocolFormID = tb_ProtocolForm.ProtocolFormID
   WHERE PDT.PatientDataTrackingID = ML.FieldID) *
   (SELECT CreateTime FROM tb_ModSpecific
   WHERE tb_ModSpecific.ModSpecificID = 46))
  WHEN 2 THEN (SELECT UpdateTime FROM tb_ModSpecific
   WHERE tb_ModSpecific.ModSpecificID = 46)
  END,
EventDate =
  (SELECT DateComplete FROM tb_PatientDataTracking
  WHERE tb_PatientDataTracking.PatientDataTrackingID = ML.FieldID)
FROM
tb_ModificationLog ML
INNER JOIN tb_Membership ON
tb_Membership.UserName = ML.ModifiedBy
WHERE
tb_Membership.MemberID = CASE
  WHEN @coorid is NULL THEN tb_Membership.MemberID
  ELSE @coorid END
AND
(SELECT DateComplete FROM tb_PatientDataTracking
WHERE tb_PatientDataTracking.PatientDataTrackingID = ML.FieldID) >= CASE
  WHEN @begin is NULL THEN '1/1/1950'
  ELSE @begin END
AND

(SELECT DateComplete FROM tb_PatientDataTracking
WHERE tb_PatientDataTracking.PatientDataTrackingID = ML.FieldID) <= CASE
  WHEN @end is NULL THEN '12/31/2049'
  ELSE @end END
AND
ML.ModSpecificID = 46
AND
ML.ModActionID <> 4
INSERT #Final
SELECT
Protocol =
  (SELECT Name FROM vwProtocolNumber
  WHERE vwProtocolNumber.ProtocolID = tb_RegTask.ProtocolID),
PIName =
  (SELECT MemberName FROM vwMemberNames
  INNER JOIN tb_Protocol ON tb_Protocol.PIMemberID = vwMemberNames.MemberID
  WHERE tb_Protocol.ProtocolID = tb_RegTask.ProtocolID),
Status =
  (SELECT Status FROM vwProtocol
  WHERE vwProtocol.ProtocolID = tb_RegTask.ProtocolID),
PatientName = NULL,
ByWhom =
  (SELECT MemberName FROM vwMemberNames
  INNER JOIN tb_Membership ON
  tb_Membership.MemberID = vwMemberNames.MemberID
  WHERE tb_Membership.UserName =
  (SELECT ML.ModifiedBy FROM tb_ModificationLog ML
  WHERE ML.FieldID = tb_RegTask.RegTaskID
  AND ModSpecificID = 39 and ModActionID = 1)),
Title,
Category =
  (SELECT RegCategory FROM tb_RegCategory
  WHERE tb_RegCategory.RegCategoryID = tb_RegTask.RegCategoryID),
TimeSpent,
EventDate = ActionDate
FROM
tb_RegTask
WHERE
tb_RegTask.RegTaskID in
  (SELECT ML.FieldID FROM tb_ModificationLog ML
  WHERE ML.ModSpecificID = 39 and
  ML.ModifiedBy =
  (SELECT UserName FROM tb_Membership
  WHERE tb_Membership.MemberID in
   (SELECT MemberID FROM tb_Membership
   WHERE tb_Membership.MemberID = CASE
   WHEN @coorid is NULL THEN tb_Membership.MemberID
   ELSE @coorid END)))
AND
tb_RegTask.ActionDate >= CASE
  WHEN @begin is NULL THEN '1/1/1950'
  ELSE @begin END
AND
tb_RegTask.ActionDate <= CASE
  WHEN @end is NULL THEN '12/31/2049'
  ELSE @end END
SELECT begindate = @begin, enddate = @end,
total = (select sum(timespent) from #Final F
where F.ByWhom = #Final.ByWhom),
* FROM #Final
ORDER BY ByWhom, Protocol, PatientName, EventDate
DROP TABLE #Final
Thanks!
Eva

Viewing 3 posts - 1 through 2 (of 2 total)

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

venku009 SSC-Addicted Points: 493 More actions · Answer 1

You can try something of this sort using exec, the code will reduce.

CREATE PROCEDURE spReportCoordinatorTimeTracking

@coorid bigint = NULL,

@begin datetime = '1/1/1900',

@end datetime = '12/31/3000'

AS

CREATE TABLE #Final (

Protocol varchar(41),

PIName varchar(75),

Status varchar(50),

PatientName varchar(75),

ByWhom varchar(75),

Title varchar(200),

Category varchar(200),

TimeSpent smallmoney,

EventDate datetime

)

declare @Str Varchar(5000)

Select @STR = ' WHERE vwProtocolNumber.ProtocolID = CASE ML.ModSpecificID

WHEN 7 THEN (SELECT min(ProtocolID) FROM tb_PatientEnrollment

WHERE tb_PatientEnrollment.PatientID = ML.MajorAreaID)

WHEN 50 THEN (SELECT ProtocolID FROM tb_PatientEnrollment

INNER JOIN tb_PatientQuery ON tb_PatientQuery.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID

WHERE tb_PatientQuery.PatientQueryID = ML.FieldID)

WHEN 42 THEN (SELECT ProtocolID FROM tb_PatientEnrollment

INNER JOIN tb_PatientTreatment ON tb_PatientTreatment.PatientEnrollmentID = tb_PatientEnrollment.PatientEnrollmentID

WHERE tb_PatientTreatment.PatientTreatmentID = ML.FieldID)

WHEN 41 THEN (SELECT ProtocolID FROM tb_ProtocolArm

INNER JOIN tb_ProtocolArmProcedure ON tb_ProtocolArmProcedure.ProtocolArmID = tb_ProtocolArm.ProtocolArmID

WHERE tb_ProtocolArmProcedure.ProtocolArmProcedureID = ML.FieldID)

WHEN 44 THEN (SELECT ProtocolID FROM tb_PatientContact

WHERE tb_PatientContact.PatientContactID = ML.FieldID)

ELSE ML.MajorAreaID

END '

INSERT #Final

Exec

'

SELECT

Protocol = ('

+ @STR,

') PIName =

(SELECT MemberName FROM vwMemberNames

INNER JOIN tb_Protocol ON tb_Protocol.PIMemberID = vwMemberNames.MemberID '

@STR

+ ' ),

Status =

(SELECT Status FROM vwProtocol '

+ @STR

.

'

.

evaleah Ten Centuries Points: 1372 More actions · Answer 2

Thanks for the suggestion. Actually, I got it to be MUCH more efficient just by removing the optional features on the parameters. I have made them required. This enabled me to remove the where case statements and it increased run time on the procedure from 16.5 minutes down to 46 seconds!