5 table join returns way too many rows, DISTINCT not working either and DISTINCT takes way too long to run
The following query should not return more than 40000 rows, since the TRANSACTION_HISTORY table is only 40K rows. However when I let this run for more than 1 minute, i end up getting 1 million or so rows. When I try to use DISTINCT, the query just takes over 5 minutes then I manually kill it. I will be able to filter by date once I get everything loaded once, however I need to get all history loaded first. Here are the row counts for each table:
DM_SLS_TRANSACTION_HISTORY
V_WHOLESALER (count = 16)
CONTACT_AGG (count = 544160
ACCOUNT_AGG (COUNT = 317)
DM_ACCOUNT_VALUE (COUNT = 45339)
Basically without a transaction the data is useless, so everything is based off the TRANSACTION_HISTORY table. Attached is what I have so far.
DM_SLS_TRANSACTION_HISTORY
V_WHOLESALER (count = 16)
CONTACT_AGG (count = 544160
ACCOUNT_AGG (COUNT = 317)
DM_ACCOUNT_VALUE (COUNT = 45339)
Basically without a transaction the data is useless, so everything is based off the TRANSACTION_HISTORY table. Attached is what I have so far.
select
DM_SLS_TRANSACTION_HISTORY.CONTACT_ID,
DM_SLS_TRANSACTION_HISTORY.FIRM_ID,
DM_SLS_TRANSACTION_HISTORY.TRADE_DATE,
DM_SLS_TRANSACTION_HISTORY.POSTING_DATE,
DM_SLS_TRANSACTION_HISTORY.CUST_ACCT_NBR,
DM_SLS_TRANSACTION_HISTORY.GROSS_AMOUNT,
DM_SLS_TRANSACTION_HISTORY.TRADE_REP,
DM_SLS_TRANSACTION_HISTORY.TRADE_REP_ORIG,
DM_SLS_TRANSACTION_HISTORY.GROSS_AMOUNT_ORIG,
V_WHOLESALER.FULL_NAME as WHOLESALER_NAME,
CONTACT_AGG.LAST_NAME as REP_CONTACT_LNAME,
CONTACT_AGG.FIRST_NAME as REP_CONTACT_FNAME,
CONTACT_AGG.MIDDLE_NAME as REP_CONTAT_MNAME,
ACCOUNT_AGG.NAME as ORIGINAL_BROKER,
DM_SLS_TRANSACTION_HISTORY.FUND,
DM_ACCOUNT_VALUE.REP_NAME AS ORIGINAL_REP
--into INVESTMENT_DETAIL_AGG
FROM
DM_SLS_TRANSACTION_HISTORY , V_WHOLESALER, CONTACT_AGG, ACCOUNT_AGG, DM_ACCOUNT_VALUE
/*
INNER JOIN
V_WHOLESALER ON DM_SLS_TRANSACTION_HISTORY.TERR1 = V_WHOLESALER.TERR INNER JOIN
CONTACT_AGG ON DM_SLS_TRANSACTION_HISTORY.CONTACT_ID = CONTACT_AGG.CONTACT_ID INNER JOIN
ACCOUNT_AGG ON DM_SLS_TRANSACTION_HISTORY.FIRM_ID = ACCOUNT_AGG.FIRM_ID INNER JOIN
DM_ACCOUNT_VALUE ON DM_SLS_TRANSACTION_HISTORY.FIRM_ID = DM_ACCOUNT_VALUE.FIRM_ID
*/
where
DM_SLS_TRANSACTION_HISTORY.fund = '3701'
and DM_SLS_TRANSACTION_HISTORY.CONTACT_ID = CONTACT_AGG.CONTACT_ID
and DM_SLS_TRANSACTION_HISTORY.FIRM_ID = ACCOUNT_AGG.FIRM_ID
and DM_SLS_TRANSACTION_HISTORY.FIRM_ID = DM_ACCOUNT_VALUE.FIRM_ID
and DM_SLS_TRANSACTION_HISTORY.TERR1 = V_WHOLESALER.TERR
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This is wrong. You may get more records when you join the tables.
DM_SLS_TRANSACTION_HISTORY
CONTACT_AGG (count = 544160)
You are joining both these tables on Contact_ID. If you have contact_id repeated in CONTACT_AGG table you will have more records in your output.
For example, Table A has following 3 records.
EmpId
1
2
3
Table B has below reocrds
DeptID EMpId
10 1
10 2
10 3
20 1
If you join both the tables on EMpID, how many record you will get? 4 records more than the records in Table A.