• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

Microsoft, SQL, 2005, SQL Job help

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

0
Jeff S
Asked:
Jeff S
1 Solution
 
SharathData EngineerCommented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now