Home » SQL & PL/SQL » SQL & PL/SQL » optimisation for view as it is time consuming (oracle 10g)
optimisation for view as it is time consuming [message #431528] Tue, 17 November 2009 23:12 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi my view is causing more time any optimisation ways will be highly appreciated .
CREATE OR REPLACE FORCE VIEW EMRVWTODAYSTASKLISTCOUNT (EMR_TRANSACTION_ID, TASK_ID, TASK_NAME, APPOINTMENT_DATE, PATIENT_FIRSTNAME, PATIENT_LASTNAME, CONSULTANT_ID, ROLE_NAME, LOCATION_ID, ID_VALUE) AS 
SELECT DISTINCT  EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 case when ET.TASK_ID IN (9,112) THEN TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') 
	       ELSE '1/1/1900' END AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL 
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID 
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID 
INNER JOIN EMRAppDetailsSuppHistory EA ON 
(
	ET.ID_VALUE = EA.APPOINTMENT_ID  and  ET.TASK_ID IN (9,112)
	OR
	ET.TASK_ID IN (4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 18, 19, 20, 21, 22, 23, 24,  
	29, 30, 31, 32, 33, 58, 59, 60, 63, 64, 65, 66, 67, 96, 110, 113, 115, 116, 117, 118, 119, 120, 121, 123, 125, 150)
)
WHERE   ET.TASK_ID IN (4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 18, 19, 20, 21, 22, 23, 24, 
29, 30, 31, 32, 33, 58, 59, 60, 63, 64, 65, 66, 67, 96, 110, 113, 115, 116, 117, 118, 119, 120, 121, 123, 125, 150,9,112)
UNION ALL
SELECT     ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, TO_CHAR(EED.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE,                       
                      EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EED.CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID,  ET.ID_VALUE
FROM        EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRTranscriptionMapping ETM, EMREncounterDetails EED, 
                      EMRTranscriptionData ETD
WHERE     ETL.TASK_ID = ET.TASK_ID AND ET.ID_VALUE = ETM.TRANSCRIPTION_ID AND ETM.ENCOUNTER_ID = EED.ENCOUNTER_ID AND 
          ETD.IS_VERIFIED = 0 AND ETD.TRANSCRIPTION_ID = ETM.TRANSCRIPTION_ID AND EED.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 15
UNION ALL
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME, 
        Case When Nvl(EED.APPOINTMENT_ID,'0')='0' then 
                      '1/1/1900'  
                      ELSE TO_CHAR(EED.APPOINTMENT_DATE,'DD/MM/YYYY')  
                      END AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL 
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID 
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRENCOUNTERDETAILS EED ON ET.ID_VALUE = EED.ENCOUNTER_ID
WHERE   ET.TASK_ID IN (16,17,25,26,27,153)
UNION ALL
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 '1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, NULL AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
WHERE  ET.TASK_ID IN (34, 61, 62, 73, 109)
OR (ET.TASK_ID IN (124, 126, 147, 154) AND ET.ROLE_NAME IS NOT NULL)
UNION ALL
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME, 
        TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY')  AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL 
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID 
INNER JOIN EMRPATIENTSMASTER EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
INNER JOIN EMRAppointmentDetails EA ON ET.ID_VALUE = EA.APPOINTMENT_ID  
WHERE   ET.TASK_ID IN (28)
UNION ALL
SELECT  ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME,
       '1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,  
       CON.CONSULTANT_ID AS CONSULTANT_ID, ET.ROLE_NAME ,  AL.LOCATION_ID AS LOCATION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE  
FROM  EMRTransactions ET inner join EMRTaskListLkup ETL on  ETL.TASK_ID = ET.TASK_ID
       INNER JOIN EMRDISEASEALERTS AL ON AL.DISEASE_ALERT_ID=ET.ID_VALUE
       LEFT JOIN EMRCONSULTANTDETAILS CON ON ET.USER_LOGIN = CON.CONSULTANT_ID  
       WHERE   ET.TASK_ID IN (122)
UNION ALL
SELECT  ET.EMR_TRANSACTION_ID, ET.TASK_ID, TASK_NAME,   
	TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,   
        ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM EMRTransactions ET
        INNER JOIN EMRTASKLISTLKUP ETL ON ET.TASK_ID = ETL.TASK_ID
	INNER JOIN EMRAppointmentDetails EA ON ET.ID_VALUE = EA.APPOINTMENT_ID 
	INNER JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
        INNER JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_STATUS_ID 
WHERE   ET.TASK_ID IN (1, 2, 3)
UNION ALL
SELECT     ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, 
        TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, 
	ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, EPM.LOCATION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE
FROM         EMRACTransactions ET 
	INNER JOIN EMRTaskListLkup ETL ON ETL.TASK_ID = ET.TASK_ID
	INNER JOIN EMRPatientsMaster EPM ON ET.PATIENT_ID = EPM.PATIENT_ID
        INNER JOIN EMRENCOUNTERDETAILS EED ON EED.ENCOUNTER_ID = ET.ID_VALUE
        INNER JOIN EMRCONSULTANTDETAILS ECD ON EED.CONSULTANT_ID = ECD.CONSULTANT_ID
	LEFT OUTER JOIN EMRAppointmentDetailsHistory EA ON EED.APPOINTMENT_ID = EA.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2
WHERE ET.TASK_ID IN (152)




[Updated on: Tue, 17 November 2009 23:14]

Report message to a moderator

Re: optimisation for view as it is time consuming [message #431530 is a reply to message #431528] Tue, 17 November 2009 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Coorectly format the query, make your lines no more than 80 characters, post the required information.

Regards
Michel
Re: optimisation for view as it is time consuming [message #431648 is a reply to message #431530] Wed, 18 November 2009 22:05 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Try these basic steps:

1) Since your view is based on a UNION ALL query with multiple parts, tuning this view is all about tuning each query one at a time. Get each piece running well, the query should run well. Indeed, it is likely that only one of the pieces is going slowly.

2) see if there is anything common between your sections and thus combine the sections. Eliminating multiple passes of the data is a great way to make things go faster.

Good luck, Kevin
Previous Topic: PL/SQL DWH features
Next Topic: execute immediate error
Goto Forum:
  


Current Time: Fri Dec 09 11:51:32 CST 2016

Total time taken to generate the page: 0.12825 seconds