Link to home
Start Free TrialLog in
Avatar of anandmahajan
anandmahajanFlag for United States of America

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_ENTRY 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_SCHOOL,
        (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
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)


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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anandmahajan

ASKER

Thanks ...lets me try it out!
Forced accept.

Computer101
EE Admin