Forum Stats

  • 3,780,546 Users
  • 2,254,409 Discussions
  • 7,879,377 Comments

Discussions

Instead of Join, can we use Exists to imporve performance

Bindu K
Bindu K Member Posts: 18 Green Ribbon
edited May 1, 2019 10:25AM in MySQL Community Space

Team,

The With Temp1 is executing in 10 secs and it fetches - 700 records.

The FT_BASE table should fetch only the SUBSCRIBER_ID's that are there in Temp1 Table.

I have highlighted the statement which is causing the performance, please let me know if there is any alternate way of writing the query in Using 'Exists' OR IN. Please help me in changing the query.

The Final results should show only 700 Subscriber ID's

Thanks!

Bindu

With Temp1 AS

(

SELECT

            S.SUBSCRIBER_MDN,

            TO_CHAR(S.MASTER_SUBSCRIBER_ID) AS
SECONDARY_MASTER_SUBSCRIBER_ID,            TO_CHAR(SP.PRIMARY_MASTER_SSCR_ID) AS PRIMARY_MASTER_SSCR_ID,

            TO_CHAR(PRI.SUBSCRIBER_ID) AS PRIMARY_SSCR_ID,

          
NTWK_ACCESS_PLAN_OFFER_NM AS
PRIMARY_NAP,

            SUM(ADDN_GROSS_CNT) AS GA, SUM(DFCT_GROSS_CNT) AS GD

FROM

          
FT_SSCR_ADS A

            INNER JOIN DIM_CUSTOMER C ON A.CUSTOMER_KEY = C.CUSTOMER_KEY

            INNER JOIN DIM_CUSTOMER_STATUS CS

                 ON A.CUSTOMER_STATUS_KEY = CS.CUSTOMER_STATUS_KEY

            INNER JOIN DIM_SUBSCRIBER S ON A.SUBSCRIBER_KEY = S.SUBSCRIBER_KEY

            INNER JOIN DIM_EQUIPMENT E ON A.EQUIPMENT_KEY = E.EQUIPMENT_KEY

            INNER JOIN DIM_DATE D ON A.ADS_EVENT_DATE_KEY = D.DATE_KEY

            INNER JOIN DWWRK.STG_WRK_SSCR_PAIR_STATUS SP

                 ON S.MASTER_SUBSCRIBER_ID = SP.SEC_MASTER_SSCR_ID

                        AND D.DAY_DT BETWEEN SP.ROW_EFF_DT AND SP.ROW_EXP_DT

            INNER JOIN DIM_SUBSCRIBER PRI

                 ON PRI.MASTER_SUBSCRIBER_ID = SP.PRIMARY_MASTER_SSCR_ID

                        AND D.DAY_DT BETWEEN PRI.ROW_EFF_DT AND PRI.ROW_EXP_DT

            INNER JOIN BRDG_DIM_SSCR_DIM_SSCR_SA SA

                 ON PRI.SUBSCRIBER_KEY = SA.SUBSCRIBER_KEY

                        AND D.DAY_DT BETWEEN SA.ROW_EFF_DT AND SA.ROW_EXP_DT

            INNER JOIN DIM_SSCR_SERVICE_AGREEMENT
AGR

                 ON SA.SSCR_SERVICE_AGREEMENT_KEY = AGR.SSCR_SERVICE_AGREEMENT_KEY

WHERE

          
ADS_EVENT_DATE_KEY BETWEEN TO_CHAR(sysdate-6,'YYYYMMDD') AND TO_CHAR(sysdate,'YYYYMMDD')

            AND C.EMBEDDED_DEVICE_RESELLER_IND = 'N'

            AND CS.REVENUE_GENERATING_IND = 'Y'

            AND E.EQUIPMENT_CATEGORY1_DESC = 'Connected Device'

            AND E.EQUIPMENT_CATEGORY2_DESC = 'Wearable Solution'

            AND E.EQUIPMENT_CATEGORY3_DESC = 'Watch (Cellular)'

GROUP BY

            S.SUBSCRIBER_MDN,                                                                              
             S.MASTER_SUBSCRIBER_ID,

            SP.PRIMARY_MASTER_SSCR_ID,                    

          
PRI.SUBSCRIBER_ID,

          
NTWK_ACCESS_PLAN_OFFER_NM

)

select distinct S.SUBSCRIBER_ID from FT_BASE F

INNER JOIN DIM_SUBSCRIBER S ON

  1. F.SUBSCRIBER_KEY = S.SUBSCRIBER_KEY

INNER JOIN Temp1

ON S.SUBSCRIBER_ID = Temp1.PRIMARY_SSCR_ID

where F.SNAP_DATE_KEY BETWEEN 20180801 AND TO_CHAR(sysdate-7,'YYYYMMDD')

Best Answer

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Sep 19, 2018 9:36PM Accepted Answer

    If your question is related to mysql then yes. If it is related to Oracle database server then you should move your post to:

    AFTER first reading

    As a general rule of thumb, EXISTS() is usually faster than IN() due to the fact that with IN() all values are read in the in-list where as with EXISTS() the processing stops as soon as a match is found.

Answers