anandmahajan
asked on
Complex SQL Query
Guys any Scope for Optimization!!
SELECT H1.USERNUM,
H1.HANDLE AS USERNAME,
H1.FIRSTNAME AS FIRST_NAME,
H1.MAIDENNAME AS MAIDEN_NAME,
H1.LASTNAME AS LAST_NAME,
H1.EMAIL,
H1.SCHOOL_ID,
H1.GRADYEAR AS GRAD_YEAR,
H1.GENDER,
H1.CITY,
H1.STATE,
H1.DATE_CREATED AS DATE_OF_REG,
H1.LAST_VISIT AS DATE_OF_LAST_VISIT,
H1.BIRTHDAY AS DATE_OF_BIRTH,
H1.DATING AS IS_DATING,
HEP.ACTIVITYWATCH_FREQ,
USS.IS_SUBSCRIBED,
USS.WLFY_COUNT AS WSFM_COUNT,
USS.WLFY_COUNT_THIS_WEEK AS WSFM_COUNT_LAST_WEEK,
USS.MSG_COUNT,
HAW.SCHOOL_ID AS WATCHED_SCHOOL_ID,
HAW.STARTYEAR AS WATCHED_START_YEAR,
HAW.ENDYEAR AS WATCHED_END_YEAR,
(SELECT NTP.THEN_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS THEN_PHOTO_ID,
(SELECT NTP.NOW_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS NOW_PHOTO_ID,
(SELECT COUNT (DISTINCT VPT.VIEWER_USERNUMBER)
FROM VIEW_PROFILE_TRACKING VPT, HSA H2
WHERE VPT.VIEWER_USERNUMBER = H2.USERNUM
AND H2.DELETED = 0
AND VPT.VIEW_DATE BETWEEN TRUNC(sysdate)-7 AND ?
AND VPT.VIEWEE_USERNUMBER <> VPT.VIEWER_USERNUMBER
AND VPT.VIEWEE_USERNUMBER = H1.USERNUM) AS WVMP_COUNT_LAST_WEEK,
(SELECT COUNT (DISTINCT VPT.VIEWER_USERNUMBER)
FROM VIEW_PROFILE_TRACKING VPT, HSA H2
WHERE VPT.VIEWER_USERNUMBER = H2.USERNUM
AND H2.DELETED = 0
AND VPT.VIEW_DATE BETWEEN TRUNC(?)-365 AND ?
AND VPT.VIEWEE_USERNUMBER <> VPT.VIEWER_USERNUMBER
AND VPT.VIEWEE_USERNUMBER = H1.USERNUM) AS WVMP_COUNT_PAST_YEAR,
(SELECT P.PROFILE_UPDATED FROM PROFILE.PROFILE P WHERE P.USERNUM = H1.USERNUM) AS LAST_PROFILE_MODIFY_DATE,
(SELECT COUNT(F.PROFILE_ID) FROM PROFILE.FAVORITES F WHERE F.PROFILE_ID = (SELECT ID FROM PROFILE.PROFILE WHERE USERNUM = H1
.USERNUM)) AS NUM_OF_INTERESTS,
(SELECT MAX(SEARCH_DATE) FROM PEOPLE_SEARCH_HISTORY PSH WHERE PSH.USERNUM = H1.USERNUM AND PSH.DELETED = 0) AS LAST_PEOPLE_S
EARCH_DATE,
(SELECT COUNT(SEARCH_ID) FROM PEOPLE_SEARCH_HISTORY PSH WHERE PSH.USERNUM = H1.USERNUM AND PSH.DELETED = 0) AS SAVED_SEARCHE
S_COUNT,
(SELECT LAST_ENTRY_DATE FROM BLOG.BLOG B WHERE B.USERNUM = H1.USERNUM) AS LAST_BLOG_ENTRY_DATE,
(SELECT MAX(P.CREATE_DATE) FROM ALBUM.PHOTO_ALBUM PA, ALBUM.PHOTO P WHERE PA.USERNUM = H1.USERNUM AND P.PHOTO_ALBUM_ID = PA
.PHOTO_ALBUM_ID) AS LAST_PHOTO_UPLOAD_DATE,
(SELECT COUNT(P.PHOTO_ID) FROM ALBUM.PHOTO_ALBUM PA, ALBUM.PHOTO P WHERE PA.USERNUM = H1.USERNUM AND P.PHOTO_ALBUM_ID = PA.
PHOTO_ALBUM_ID) AS NUM_OF_PHOTOS,
(SELECT COUNT(ABE.ID) FROM ADDRESSBOOK.ADDRESSBOOK AB, ADDRESSBOOK.ADDRESSBOOK_EN TRY ABE WHERE AB.USERNUM = H1.USERNUM AND A
BE.ADDRESSBOOK_ID = AB.ID AND NVL(ABE.USERNUM, 0) <> 25657194) AS NUM_OF_CONTACTS,
(SELECT COUNT(HSAAW.USERNUM) FROM HSAALUMNIWATCH HSAAW WHERE HSAAW.SCHOOL_ID = H1.SCHOOL_ID AND HSAAW.STARTYEAR <= H1.GRADYE
AR+2 AND HSAAW.ENDYEAR >= H1.GRADYEAR-2 AND HSAAW.DATE_CREATED BETWEEN TRUNC(?)-7 AND ?) AS NEW_STUDENTS_OF_PRIMARY_SC HOOL,
(SELECT REU.REUNIONDATE from RC_HSAREUNIONS REU WHERE REU.GROUPID = H1.SCHOOL_ID AND REU.GROUPYEAR = H1.GRADYEAR) AS REUNION
_DATE
FROM HSA H1
INNER JOIN HSA_EMAIL_PROFILE HEP ON HEP.USERNUM = H1.USERNUM
LEFT OUTER JOIN HSAALUMNIWATCH HAW ON HAW.USERNUM = H1.USERNUM
LEFT OUTER JOIN USER_SEARCH_STATS USS ON USS.USERNUM = H1.USERNUM
WHERE H1.USERNUM BETWEEN 0 AND 2000
AND H1.DELETED = 0
AND HEP.HARDBOUNCE = 0
AND HEP.ACTIVITYWATCH_FREQ > 0
AND HEP.VALIDATION_STATUS IN (1,2)
SELECT H1.USERNUM,
H1.HANDLE AS USERNAME,
H1.FIRSTNAME AS FIRST_NAME,
H1.MAIDENNAME AS MAIDEN_NAME,
H1.LASTNAME AS LAST_NAME,
H1.EMAIL,
H1.SCHOOL_ID,
H1.GRADYEAR AS GRAD_YEAR,
H1.GENDER,
H1.CITY,
H1.STATE,
H1.DATE_CREATED AS DATE_OF_REG,
H1.LAST_VISIT AS DATE_OF_LAST_VISIT,
H1.BIRTHDAY AS DATE_OF_BIRTH,
H1.DATING AS IS_DATING,
HEP.ACTIVITYWATCH_FREQ,
USS.IS_SUBSCRIBED,
USS.WLFY_COUNT AS WSFM_COUNT,
USS.WLFY_COUNT_THIS_WEEK AS WSFM_COUNT_LAST_WEEK,
USS.MSG_COUNT,
HAW.SCHOOL_ID AS WATCHED_SCHOOL_ID,
HAW.STARTYEAR AS WATCHED_START_YEAR,
HAW.ENDYEAR AS WATCHED_END_YEAR,
(SELECT NTP.THEN_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS THEN_PHOTO_ID,
(SELECT NTP.NOW_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS NOW_PHOTO_ID,
(SELECT COUNT (DISTINCT VPT.VIEWER_USERNUMBER)
FROM VIEW_PROFILE_TRACKING VPT, HSA H2
WHERE VPT.VIEWER_USERNUMBER = H2.USERNUM
AND H2.DELETED = 0
AND VPT.VIEW_DATE BETWEEN TRUNC(sysdate)-7 AND ?
AND VPT.VIEWEE_USERNUMBER <> VPT.VIEWER_USERNUMBER
AND VPT.VIEWEE_USERNUMBER = H1.USERNUM) AS WVMP_COUNT_LAST_WEEK,
(SELECT COUNT (DISTINCT VPT.VIEWER_USERNUMBER)
FROM VIEW_PROFILE_TRACKING VPT, HSA H2
WHERE VPT.VIEWER_USERNUMBER = H2.USERNUM
AND H2.DELETED = 0
AND VPT.VIEW_DATE BETWEEN TRUNC(?)-365 AND ?
AND VPT.VIEWEE_USERNUMBER <> VPT.VIEWER_USERNUMBER
AND VPT.VIEWEE_USERNUMBER = H1.USERNUM) AS WVMP_COUNT_PAST_YEAR,
(SELECT P.PROFILE_UPDATED FROM PROFILE.PROFILE P WHERE P.USERNUM = H1.USERNUM) AS LAST_PROFILE_MODIFY_DATE,
(SELECT COUNT(F.PROFILE_ID) FROM PROFILE.FAVORITES F WHERE F.PROFILE_ID = (SELECT ID FROM PROFILE.PROFILE WHERE USERNUM = H1
.USERNUM)) AS NUM_OF_INTERESTS,
(SELECT MAX(SEARCH_DATE) FROM PEOPLE_SEARCH_HISTORY PSH WHERE PSH.USERNUM = H1.USERNUM AND PSH.DELETED = 0) AS LAST_PEOPLE_S
EARCH_DATE,
(SELECT COUNT(SEARCH_ID) FROM PEOPLE_SEARCH_HISTORY PSH WHERE PSH.USERNUM = H1.USERNUM AND PSH.DELETED = 0) AS SAVED_SEARCHE
S_COUNT,
(SELECT LAST_ENTRY_DATE FROM BLOG.BLOG B WHERE B.USERNUM = H1.USERNUM) AS LAST_BLOG_ENTRY_DATE,
(SELECT MAX(P.CREATE_DATE) FROM ALBUM.PHOTO_ALBUM PA, ALBUM.PHOTO P WHERE PA.USERNUM = H1.USERNUM AND P.PHOTO_ALBUM_ID = PA
.PHOTO_ALBUM_ID) AS LAST_PHOTO_UPLOAD_DATE,
(SELECT COUNT(P.PHOTO_ID) FROM ALBUM.PHOTO_ALBUM PA, ALBUM.PHOTO P WHERE PA.USERNUM = H1.USERNUM AND P.PHOTO_ALBUM_ID = PA.
PHOTO_ALBUM_ID) AS NUM_OF_PHOTOS,
(SELECT COUNT(ABE.ID) FROM ADDRESSBOOK.ADDRESSBOOK AB, ADDRESSBOOK.ADDRESSBOOK_EN
BE.ADDRESSBOOK_ID = AB.ID AND NVL(ABE.USERNUM, 0) <> 25657194) AS NUM_OF_CONTACTS,
(SELECT COUNT(HSAAW.USERNUM) FROM HSAALUMNIWATCH HSAAW WHERE HSAAW.SCHOOL_ID = H1.SCHOOL_ID AND HSAAW.STARTYEAR <= H1.GRADYE
AR+2 AND HSAAW.ENDYEAR >= H1.GRADYEAR-2 AND HSAAW.DATE_CREATED BETWEEN TRUNC(?)-7 AND ?) AS NEW_STUDENTS_OF_PRIMARY_SC
(SELECT REU.REUNIONDATE from RC_HSAREUNIONS REU WHERE REU.GROUPID = H1.SCHOOL_ID AND REU.GROUPYEAR = H1.GRADYEAR) AS REUNION
_DATE
FROM HSA H1
INNER JOIN HSA_EMAIL_PROFILE HEP ON HEP.USERNUM = H1.USERNUM
LEFT OUTER JOIN HSAALUMNIWATCH HAW ON HAW.USERNUM = H1.USERNUM
LEFT OUTER JOIN USER_SEARCH_STATS USS ON USS.USERNUM = H1.USERNUM
WHERE H1.USERNUM BETWEEN 0 AND 2000
AND H1.DELETED = 0
AND HEP.HARDBOUNCE = 0
AND HEP.ACTIVITYWATCH_FREQ > 0
AND HEP.VALIDATION_STATUS IN (1,2)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
most better way to use stored procedure
Don't query the same table twice with the same criteria.
E.g.
< (SELECT NTP.THEN_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS THEN_PHOTO_ID,
(SELECT NTP.NOW_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS NOW_PHOTO_ID, >
Is better handled as a join:
Left Join (SELECT THEN_PHOTO_ID, NOW_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP) on NTP.USERNUM = H1.USERNUM)
E.g.
< (SELECT NTP.THEN_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS THEN_PHOTO_ID,
(SELECT NTP.NOW_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP WHERE NTP.USERNUM = H1.USERNUM) AS NOW_PHOTO_ID, >
Is better handled as a join:
Left Join (SELECT THEN_PHOTO_ID, NOW_PHOTO_ID FROM ALBUM.NOW_THEN_PHOTO NTP) on NTP.USERNUM = H1.USERNUM)
To jindalankush:
No, I disagree! Stored procedures do not have a "magic" way to execute inefficient SQL statements efficiently! If an SQL statement is inefficient, it will be inefficient whether that is run in SQL*Plus, TOAD,
some other program or utility or put into a stored procedure.
To MikeToole:
I agree that two separate selects from the same table with the same "where" clause should be avoided (and/or combined) if possible. But, if the only alternative is an outer join, that may not be a performance improvement.
No, I disagree! Stored procedures do not have a "magic" way to execute inefficient SQL statements efficiently! If an SQL statement is inefficient, it will be inefficient whether that is run in SQL*Plus, TOAD,
some other program or utility or put into a stored procedure.
To MikeToole:
I agree that two separate selects from the same table with the same "where" clause should be avoided (and/or combined) if possible. But, if the only alternative is an outer join, that may not be a performance improvement.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks ...lets me try it out!
Forced accept.
Computer101
EE Admin
Computer101
EE Admin