SQL联接以60个匹配项永久占据表 [英] SQL Joins Taking forever on table with 60 matches
问题描述
我有一个查询,可以在4个表之间进行联接.
I have a query that makes joins between 4 tables.
逐个表匹配我的输入参数将产生以下结果
Matching my input params on a table by table basis yeilds the following
TrialBal-800万条匹配实体和pl_date的记录
TrialBal - 8 million records matching entity and pl_date
加入ActDetail-执行时间约为85秒,行数为8672175(使用group by时,行数为1 ...目前). ActDetail在内部联接上将返回零行.
Join to ActDetail - Execution is about 85 secs, the row count is 8672175 (with group by, the row count is 1...for now). ActDetail would return zero rows on an inner join.
加入CalendarEngine-该表(pl_date&实体)中只有60个matchig记录,但是将其引入到SQL中后,查询就会运行. 有什么特殊原因吗?
Join to CalendarEngine - there are only 60 matchig records in this table (pl_date & entity), but when this is introduced to the SQL, the query just runs and runs. Is there any particular reason for this?
CalendarEngine在实体和一个查询中未使用的其他字段上有一个复合索引,因此我应该仍然可以使用它吗?
CalendarEngine has a composite index on entity and one other field not used in my query, so I should still be able to use it?
为什么TrailBal和ActDetail之间的联接会相对较快(大概是外部联接正在所有800万条记录中运行,而ActDetail中只有168k条目计数),但是在CalendarEngine中联接60条记录会导致看似无休止的循环.
Why would the join between TrailBal and ActDetail be relatively quick (presumably the Outer join is running through all 8 million records against the count of 168k entries in ActDetail), but joining on 60 records in CalendarEngine leads to a seemingly endless loop.
下面的SQL(我需要在最终查询中注释掉的字段).还尝试用2个外部联接替换OR语句
SQL below (I need the commented out fields in the final query). Also tried Replacing the OR statement with 2 Outer Joins
SELECT
ad.acct_lv2 AS IFRS_lvl2,
ad.acct_lv3 AS IFRS_lvl3,
ad.acct_lv4 AS IFRS_lvl4,
ad.acct_lv6 AS IFRS_lvl6,
--(CASE WHEN tb.pl_date = cal.curr_me_date THEN cal.date END) as Reporting_Cycle_This,
--(CASE WHEN tb.pl_date = cal.prev_me_date THEN cal.date END) as Reporting_Cycle_Last,
--(CASE WHEN tb.pl_date = cal.prev_me_date THEN cal.date END) as Reporting_Cycle_Previous,
null as Total,
null as Balance_default_dim,
null as Balance_proxy_dim,
0 as percentage_bal_proxy_applied,
'1 Table Data' as rowType
FROM TrialBal tb
INNER JOIN Partition p ON p.partition_code = tb.partition_code
AND p.entity = tb.entity
LEFT OUTER JOIN ActDetail ah ON tb.actNum = ad.actNum
AND tb.cpny = ad.cpny
AND tb.pl_date = ad.pl_date
AND ad.source = 'Ground0'
--LEFT OUTER JOIN CalendarEngine cal on tb.pl_date = cal.curr_me_date OR tb.pl_date = cal.prev_me_date
--AND tb.entity = cal.entity
WHERE tb.pl_date = '2014-04-30'
AND tb.entity = 'My_entity'
GROUP BY ad.acct_lv2, ad.acct_lv3, ad.acct_lv4, ad.acct_lv6, --tb.actNum, tb.cpny,
CASE WHEN tb.pl_date = cal.curr_me_date THEN cal.date END,
CASE WHEN tb.pl_date = cal.prev_me_date THEN cal.date END,
CASE WHEN tb.pl_date = cal.prev_me_date THEN cal.date END
还尝试用2个外部联接替换OR语句:
Also tried Replacing the OR statement with 2 Outer Joins:
LEFT OUTER JOIN sCalendar cal_cur on tb.pnl_date = cal_cur.curr_me_date
AND tb.entity_code = cal_cur.entity_code
LEFT OUTER JOIN sCalendar cal_pre on tb.pnl_date = cal_pre.prev_me_date
AND tb.entity_code = cal_pre.entity_code
推荐答案
您为什么要进行此加入
LEFT OUTER JOIN ActDetail ah ON tb.actNum = ad.actNum
AND tb.cpny = ad.cpny
AND tb.pl_date = ad.pl_date
AND ad.source = 'Ground0'
我看不到该列的任何用处,删除不必要的联接.
I dont see any use, of the column, remove unnecesary joins.
如果这部分是日期
tb.pl_date = '2014-04-30'
我建议使用
pl_date >= dateadd(d,0,datediff(d,0,pl_date)) and pl_date < dateadd(d,0,datediff(d,0,pl_date+1))
致谢
这篇关于SQL联接以60个匹配项永久占据表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!