Microsoft, SQL, 2005, SQL Job help

Jeff S
Jeff S used Ask the Experts™
on
I wrote multiple Update Scripts and set them to SQL Jobs .... however when they run, its bogging down the server quite a bit. I am a novice when it comes to writing Scripts and SQL Jobs. Can someone help me refine the coding and make this run better on the server?
update patientprofile set SignatureOnFile = '1' where SignatureOnFile IS NULL OR SignatureOnFile = '0'
update patientprofile set BenefitAssignmentMId = '762' where BenefitAssignmentMId IS NULL
update patientprofile set ReleaseOfInformationIndicatorMId = '773' where ReleaseOfInformationIndicatorMId IS NULL
update patientprofile set PrivacyPolicyAcknowledgementMId = '766' where PrivacyPolicyAcknowledgementMId IS NULL
update patientprofile set SignatureSourceMId = '153' where SignatureSourceMId IS NULL
update guarantor set BillCodeMId = '22' where BillCodeMId IS NULL
update guarantor set StatementNotesPrintStatus = '1' where StatementNotesPrintStatus IS NULL OR StatementNotesPrintStatus = '0'
 
update pp
Set BenefitAssignmentDate = IsNull(MaxVisit, GETDATE())
from PatientProfile pp
left join 
    (SELECT PatientProfileId, MAX(visit) AS MaxVisit FROM patientvisit GROUP BY PatientProfileID) pv 
    on pp.PatientProfileId = pv.PatientProfileId
 
update pp
Set PrivacyPolicyAcknowledgementDate = IsNull(MaxVisit, GETDATE())
from PatientProfile pp
left join 
    (SELECT PatientProfileId, MAX(visit) AS MaxVisit FROM patientvisit GROUP BY PatientProfileID) pv 
    on pp.PatientProfileId = pv.PatientProfileId
 
update pp
Set ReleaseOfInformationIndicatorDate = IsNull(MaxVisit, GETDATE())
from PatientProfile pp
left join 
    (SELECT PatientProfileId, MAX(visit) AS MaxVisit FROM patientvisit GROUP BY PatientProfileID) pv 
    on pp.PatientProfileId = pv.PatientProfileId

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Data Engineer
Commented:
You can combine the last three UPDATE statements into one.
update patientprofile set SignatureOnFile = '1' where SignatureOnFile IS NULL OR SignatureOnFile = '0'
update patientprofile set BenefitAssignmentMId = '762' where BenefitAssignmentMId IS NULL
update patientprofile set ReleaseOfInformationIndicatorMId = '773' where ReleaseOfInformationIndicatorMId IS NULL
update patientprofile set PrivacyPolicyAcknowledgementMId = '766' where PrivacyPolicyAcknowledgementMId IS NULL
update patientprofile set SignatureSourceMId = '153' where SignatureSourceMId IS NULL
update guarantor set BillCodeMId = '22' where BillCodeMId IS NULL
update guarantor set StatementNotesPrintStatus = '1' where StatementNotesPrintStatus IS NULL OR StatementNotesPrintStatus = '0'
 
update pp
Set BenefitAssignmentDate = IsNull(MaxVisit, GETDATE()),
    PrivacyPolicyAcknowledgementDate = IsNull(MaxVisit, GETDATE()),
    ReleaseOfInformationIndicatorDate = IsNull(MaxVisit, GETDATE())
from PatientProfile pp
left join
    (SELECT PatientProfileId, MAX(visit) AS MaxVisit FROM patientvisit GROUP BY PatientProfileID) pv
    on pp.PatientProfileId = pv.PatientProfileId
Create a SQL job with the above code and check the performance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial