Hi All,
I have a big stored procedure with 20 intermediate tables and combining all of them at the end. This stored procedure taking 5 mins to run.
Could you please let me know how to improve the performanance
DELETE FROM ActivePatientDetails
INSERT INTO ActivePatientDetails(PatientID,PatientName,PatientNumber)
SELECT DISTINCT
pat.PatientID,
pat.PatientLName +', '+ pat.PatientFName +' '+ ISNULL(pat.PatientMName, '') as PatientName,
pat.PatientNumber
FROM Patient pat
WHERE pat.Active = 1
----------------------------------------------------------------------------------------------------
DELETE FROM ActivePatientInsurance
INSERT INTO ActivePatientInsurance(FK_PatientID,FK_InsuranceID,PolicyNumber)
SELECT DISTINCT
pin.FK_PatientID,
pin.FK_InsuranceID,
pin.PolicyNumber
FROM ActivePatientDetails pat_det
LEFT JOIN PatientInsurance pin ON pat_det.PatientID = pin.FK_PatientID
----------------------------------------------------------------------------------------------------
INSERT INTO ActivePatientDataSet(PatientID,PatientName,PatientNumber,FK_InsuranceID,PolicyNumber)
SELECT DISTINCT
pat_det.PatientID,
pat_det.PatientName,
pat_det.PatientNumber,
ins.FK_InsuranceID,
ins.PolicyNumber
FROM ActivePatientDetails pat_det
LEFT JOIN ActivePatientInsurance ins ON pat_det.PatientID = ins.FK_PatientID
ORDER BY pat_det.PatientName
--------------------------------------------------------------------------------------------------------------------
Thanks
Babu