Hi i have this query in my asp.net web form in vb.net with 3,500 data only on the Database but when executing it it takes 10 seconds i suspect it is on the query this is the query;
/* Formatted on 11/12/2019 2:37:37 PM (QP5 v5.318) */
SELECT C01.CAR_NO,
C01.REFNO,
NVL ((SELECT DEPT_DESC
FROM EMP_DEPT
WHERE DEPTCODE = C01.DEPTID),
' ')
AS DEPTID,
C01.ERR_PROOF,
NVL ((SELECT SECTIONNAME
FROM IQA_SECTION
WHERE SECTIONID = AREAID),
' ')
AS AREAID,
DECODE (AUDITDATE,
NULL, 'N',
AUDITDATE, TO_CHAR (AUDITDATE, 'YYYY-MM-DD'))
AUDITDATE,
ISSUEDTO
AS ISSUEDTOID,
NVL ((SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = ISSUEDTO),
NVL (ISSUEDTO, ' '))
AS ISSUEDTO,
DECODE (ISSUEDDATE,
NULL, 'N',
ISSUEDDATE, TO_CHAR (ISSUEDDATE, 'YYYY-MM-DD'))
ISSUEDDATE,
DECODE (CLOSEDDATE,
NULL, 'N',
CLOSEDDATE, TO_CHAR (CLOSEDDATE, 'YYYY-MM-DD'))
CLOSEDDATE,
CONFORMITY,
RFI,
FID,
NVL ((SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = AGUIDEID),
NVL (AGUIDEID, ' '))
AS AGUIDEID,
NVL ((SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = QMID),
' ')
AS QMID,
NVL ((SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = AUDITORID),
' ')
AS AUDITORID,
NVL ((SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = LOTHEADID),
NVL (LOTHEADID, ' '))
AS LOTHEADID,
NVL ((SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = ELOTHEADID),
' ')
AS ELOTHEADID,
NVL ((SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = VERIFIERID),
NVL (VERIFIERID, ''))
AS VERIFIERID,
NVL ((SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = EBFG),
' ')
AS EBFG,
DECODE (TARGET_DATE,
NULL, 'N',
TARGET_DATE, TO_CHAR (TARGET_DATE, 'YYYY-MM-DD'))
TARGET_DATE,
NVL (CAR_VERIFICATION1, ' ')
CAR_VERIFICATION1,
NVL (CAR_VERIFICATION2, ' ')
CAR_VERIFICATION2,
DECODE (CAR_D1_RECOMMITDATE,
NULL, 'N',
CAR_D1_RECOMMITDATE, CAR_D1_RECOMMITDATE)
CAR_D1_RECOMMITDATE,
DECODE (CAR_D2_RECOMMITDATE,
NULL, 'N',
CAR_D2_RECOMMITDATE, CAR_D2_RECOMMITDATE)
CAR_D2_RECOMMITDATE,
NVL (FINDINGSDESC, ' ')
FINDINGSDESC,
NVL (BFGSECTION, ' ')
BFGSECTION,
NVL (AREASECTION, ' ')
AREASECTION,
DECODE (NCA_DATE, NULL, 'N', NCA_DATE, NCA_DATE)
NCA_DATE,
DECODE (NCQ_DATE, NULL, 'N', NCQ_DATE, NCQ_DATE)
NCQ_DATE,
DECODE (PRL_DATE, NULL, 'N', PRL_DATE, PRL_DATE)
PRL_DATE,
DECODE (PRAO_DATE, NULL, 'N', PRAO_DATE, PRAO_DATE)
PRAO_DATE,
DECODE (PRQ_DATE, NULL, 'N', PRQ_DATE, PRQ_DATE)
PRQ_DATE,
DECODE (VCA_DATE, NULL, 'N', VCA_DATE, VCA_DATE)
VCA_DATE,
DECODE (VER_DATE, NULL, 'N', VER_DATE, VER_DATE)
VER_DATE,
NCA_TAG,
NCQ_TAG,
PRL_TAG,
PRAO_TAG,
PRQ_TAG,
PRA_TAG,
VCAF_TAG,
VER_TAG,
NVL ((SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = AOWNERID),
NVL (AOWNERID, ' '))
AS AOWNERID,
AOWNERID
AS OWNERID,
NVL (POINTS, ' ')
POINTS,
NVL (INTERIM, ' ')
INTERIM,
NVL (RCA, ' ')
RCA,
NVL (CORRECTIVE, ' ')
CORRECTIVE,
SHOW_TAG,
NVL(TempApprover.CURRENT_APPROVER, ' ')
CURRENT_APPROVER,
NVL (REMARKS, ' ')
REMARKS
FROM IQA_CAR C01,
(SELECT C02.CAR_NO,
DECODE (A01.APP_DESC,
'AUDITORID', (SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = C02.AUDITORID),
'QMID', (SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = C02.QMID),
'VERIFIERID', (SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = C02.VERIFIERID),
'LOTHEADID', (SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = C02.LOTHEADID),
'AOWNERID', (SELECT NAME
FROM TQM_VWEMPLOYEE
WHERE EMP_NUMBER = C02.AOWNERID),
NULL, 'N')
CURRENT_APPROVER
FROM IQA_CAR C02, IQA_APPROVAL A01, IQA_TRACKER T01
WHERE C02.CAR_NO = T01.REFNO AND A01.APP_NO = T01.APP_NO)
TempApprover
WHERE TempApprover.CAR_NO(+) = C01.CAR_NO
AND SHOW_TAG = 1
AND ab_getaudittypeid (deptid) = 11
ORDER BY C01.CAR_NO DESC
i try to optimize the query but nothing changes on the execution time and i uses data reader in my behind code part because they say DataReader works faster
here's my new query
SELECT c01.car_no, c01.refno,
NVL ((SELECT dept_desc
FROM emp_dept
WHERE deptcode = c01.deptid), ' ') AS deptid, c01.err_proof,
NVL ((SELECT sectionname
FROM iqa_section
WHERE sectionid = areaid), ' ') AS areaid,
NVL (TO_CHAR (auditdate, 'YYYY-MM-DD'), 'N') AS auditdate,
issuedto AS issuedtoid,
NVL ((SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = issuedto),
NVL (issuedto, ' ')) AS issuedto,
NVL (TO_CHAR (issueddate, 'YYYY-MM-DD'), 'N') AS issueddate,
NVL (TO_CHAR (closeddate, 'YYYY-MM-DD'), 'N') AS closeddate,
conformity, rfi, fid,
NVL ((SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = aguideid),
NVL (aguideid, ' ')) AS aguideid,
NVL ((SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = qmid), ' ') AS qmid,
NVL ((SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = auditorid), ' ') AS auditorid,
NVL ((SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = lotheadid),
NVL (lotheadid, ' ')
) AS lotheadid,
NVL ((SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = elotheadid), ' ') AS elotheadid,
NVL ((SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = verifierid),
NVL (verifierid, '')
) AS verifierid,
NVL ((SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = ebfg), ' ') AS ebfg,
NVL (TO_CHAR (target_date, 'YYYY-MM-DD'), 'N') AS target_date,
NVL (car_verification1, ' ') car_verification1,
NVL (car_verification2, ' ') car_verification2,
NVL (TO_CHAR (car_d1_recommitdate, 'YYYY-MM-DD'),
'N'
) AS car_d1_recommitdate,
NVL (TO_CHAR (car_d2_recommitdate, 'YYYY-MM-DD'),
'N'
) AS car_d2_recommitdate,
NVL (findingsdesc, ' ') findingsdesc,
NVL (bfgsection, ' ') bfgsection, NVL (areasection, ' ') areasection,
NVL (TO_CHAR (nca_date, 'YYYY-MM-DD'), 'N') AS nca_date,
NVL (TO_CHAR (ncq_date, 'YYYY-MM-DD'), 'N') AS ncq_date,
NVL (TO_CHAR (prl_date, 'YYYY-MM-DD'), 'N') AS prl_date,
NVL (TO_CHAR (prao_date, 'YYYY-MM-DD'), 'N') AS prao_date,
NVL (TO_CHAR (prq_date, 'YYYY-MM-DD'), 'N') AS prq_date,
NVL (TO_CHAR (vca_date, 'YYYY-MM-DD'), 'N') AS vca_date,
NVL (TO_CHAR (ver_date, 'YYYY-MM-DD'), 'N') AS ver_date, nca_tag,
ncq_tag, prl_tag, prao_tag, prq_tag, pra_tag, vcaf_tag, ver_tag,
NVL ((SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = aownerid),
NVL (aownerid, ' ')) AS aownerid, aownerid AS ownerid,
NVL (points, ' ') points, NVL (interim, ' ') interim,
NVL (rca, ' ') rca, NVL (corrective, ' ') corrective, show_tag,
tempapprover.current_approver current_approver,
NVL (remarks, ' ') remarks
FROM iqa_car c01,
(SELECT c02.car_no,
DECODE (a01.app_desc,
'AUDITORID', (SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = c02.auditorid),
'QMID', (SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = c02.qmid),
'VERIFIERID', (SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = c02.verifierid),
'LOTHEADID', (SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = c02.lotheadid),
'AOWNERID', (SELECT NAME
FROM tqm_vwemployee
WHERE emp_number = c02.aownerid),
NULL, 'N'
) current_approver
FROM iqa_car c02, iqa_approval a01, iqa_tracker t01
WHERE c02.car_no = t01.refno AND a01.app_no = t01.app_no) tempapprover
WHERE tempapprover.car_no(+) = c01.car_no AND show_tag = 1
AND ab_getaudittypeid (deptid) = 11
ORDER BY c01.car_no DESC
Is there any thing wrong with my query or may someone help me optimize it Thank you in advance
after many hours i able to solve my problem here's my latest query
SELECT iqa_car.car_no, iqa_car.refno, NVL (iqa_car.deptid, ' '),
NVL (iqa_car.bfgsection, ' '), iqa_car.err_proof,
NVL (iqa_section.sectionname, ' '), NVL (To_char(iqa_car.auditdate,'YYYY-MM-DD'), ' '),
NVL (iqa_car.issuedto, ' '), NVL (tqm_vwemployee.NAME, ' '),
NVL (To_Char(iqa_car.issueddate,'YYYY-MM-DD'), ' '), NVL (To_Char(iqa_car.closeddate,'YYYY-MM-DD'), 'N'),
NVL (vw_curr_approver.current_approver, ' ')
FROM iqa_car LEFT OUTER JOIN iqa_section
ON iqa_section.sectionid = iqa_car.areaid
LEFT OUTER JOIN tqm_vwemployee
ON tqm_vwemployee.emp_number = iqa_car.issuedto
FULL OUTER JOIN vw_curr_approver
ON vw_curr_approver.car_no = iqa_car.car_no
WHERE ab_getaudittypeid (deptid) = 11
AND TO_CHAR (iqa_car.issueddate, 'yyyy') = '2019'
ORDER BY iqa_car.car_no DESC
Thanks for @Tejash for the idea