CODEWITHSUNDEEP
sqlsybase
SouravD
SouravD

Reputation: 127

SQL Joins in Sybase

I have a table called mc_spot_customer_viewing_capped_thin4 which has 533,523,823 Row(s) of viewing data. Now I am to add some profiling attributes to each customer. I am running the following code:

SELECT A.*
,b.cb_key_household
,b.viewing_panel_id
,b.mirror_men
,b.mirror_women
,b.mirror_has_children
,b.demographic
,b.financial_outlook
,b.h_affluence
,b.homeowner
,b.h_lifestage
,b.region
,b.sky_go_reg
,b.value_segment
,b.sports_downgrade
,b.movies_downgrade
,b.household_composition
,b.current_package
,b.tenure
,b.social_class
,b.TV_PREMIUMS
,b.TV_PACKAGE
,b.TENURE_IN_MONTHS
,b.DOWNLOADS_CAMPAIGN_PERIOD
,b.ON_DEMAND_CAMPAIGN_PERIOD_FLAG
,b.MOSAIC_GROUP
,b.FINANCIAL_STRATEGY_SEGMENT
,b.ENGAGEMENT_SEGMENT
,b.PANELISTS_THAT_VIEWED_SPOTS
,b.NEW_SKY_GO_USERS
,b.SKY_GO_USERS_BEFORE_CAMPAIGN
,b.SKY_GO_USER_TYPE_BEFORE_CAMPAIGN
INTO #FINAL_ALL_SPOTS_PROFILE
FROM (SELECT * FROM mc_spot_customer_viewing_capped_thin4 WHERE SPOT_VIEWED_DURATION = SPOT_DURATION) AS A
LEFT JOIN PROFILING_LOOKUP_TABLE_V9 AS B
ON A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER

which is taking ages to run. Is there a more optimised way in which I can run the code to get the output faster. Please suggest, I am pretty much new to the field of SQL.

Thank you in advance, Regards,

SD

Upvotes: 0

Views: 5402

Answers (1)

D Stanley
D Stanley

Reputation: 152521

I don't know if it will change the performance, but you could remove the subquery:

SELECT A.*
...
INTO #FINAL_ALL_SPOTS_PROFILE
FROM mc_spot_customer_viewing_capped_thin4 AS A
LEFT JOIN PROFILING_LOOKUP_TABLE_V9 AS B
ON A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
WHERE A.SPOT_VIEWED_DURATION = A.SPOT_DURATION

You also may want to add an index on SPOT_VIEWED_DURATION = SPOT_DURATION

Another alternative would be:

FROM mc_spot_customer_viewing_capped_thin4 AS A
LEFT JOIN PROFILING_LOOKUP_TABLE_V9 AS B
ON A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
WHERE A.SPOT_VIEWED_DURATION - A.SPOT_DURATION = 0

with an index on SPOT_VIEWED_DURATION - SPOT_DURATION

Those two would be equivalent in your case, however the second would be faster in cases where you wanted a difference within a range, e.g. 

WHERE A.SPOT_VIEWED_DURATION - A.SPOT_DURATION BETWEEN -1 AND 1

Upvotes: 3

Related Questions