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屋！