• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

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 (count = 40000)
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

Open in new window

0
sqlagent007
Asked:
sqlagent007
1 Solution
 
SharathData EngineerCommented:
>> The following query should not return more than 40000 rows, since the TRANSACTION_HISTORY table is only 40K rows.

This is wrong. You may get more records when you join the tables.
DM_SLS_TRANSACTION_HISTORY (count = 40000)
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.
0
 
jfmadorCommented:
Hello you only use simple cross join in your query, you need to use inner join between every table in order to bet only 40000rows,

Cross join are join without any condition and link between two table, they will list every combinason of row on each side,

I don't know why but your join are defined in your query but commented

FROM        
DM_SLS_TRANSACTION_HISTORY 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

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now