加速glacial sql语句？

debugcn 发表于 Dev

鬼魂

我正在编写一个在三个表上执行联接的报告。

PartitionCode大约有127行。AcctListLocal大约有17,000行。TrialBal有70,000,000多行

DBMS是Sybase 15

这需要永久执行-超过45分钟。它是其他团队使用的开发服务器，但是我仍然认为执行时间是不可接受的。

所有表都有复合PK-

AcctListLocal (PK and Index)
=============
acct_local
lv1
lv2
entity_code

PartitonCode (PK and Index)
============
entity_code
partition_code

TrialBal (PK + 3 indexes)
========
**PK/Index 1**
ac_cp
ac_gl
ac_gl_ctrl
ac_taps
code
ccy
id
company
co_ta
cc
entity_code
partition_code
pl_date
pro_num
src

**Index 2**
pl_date
entity_code
pro_num

**Index 3**
pl_date
entity_code
company
ac_gl

**Index 4**
pnl_date
entity_code
partition_code

我要加入不完整索引的问题的一部分吗？也就是说，所有索引都是“复合”字段，但是我只匹配其中几个？我如何在由实体_代码和分区_代码组成的试验余额上创建索引，以匹配实体_代码和ac_gl上的PartitonCode和AccListLocal以匹配对RegalTrialBal的查找

还是CASE语句令人发指？

SQL如下：

INSERT INTO #VolumesAndValues 
    SELECT 
        ahl.lv1 AS base, 
        ahl.lv2 AS ap, 
        ahl.lv3 AS mc1, 
        sum(tb.us) as total,
        SUM(CASE WHEN pc.partition_lv2 = 'MA' THEN tb.us ELSE 0 END) AS base,
        SUM(CASE WHEN pc.partition_lv2 = 'AJ' THEN tb.us ELSE 0 END) AS batch,
        SUM(CASE WHEN pc.partition_lv2 in('ADCG','ADIG') AND 1=1 THEN rtb.us ELSE 0 END) AS net,                    
        SUM(CASE WHEN pc.partition_lv2 = 'FR' THEN tb.us ELSE 0 END) AS fr,
        SUM(CASE WHEN pc.partition_lv2 = 'PA' THEN tb.us ELSE 0 END) AS pa,
        SUM(CASE WHEN pc.partition_lv2 = 'RE' THEN tb.us ELSE 0 END) AS re,
        SUM(CASE WHEN pc.partition_lv2 = 'OF' THEN tb.us ELSE 0 END) AS of,
        SUM(CASE WHEN pc.partition_lv2 = 'PR' THEN tb.us ELSE 0 END) AS pr,
        '1 Table Data' as rowType
FROM TrialBal tb 
    LEFT OUTER JOIN AcctListLocal al ON tb.entity_code = al.entity_code
        and tb.ac_gl_c = al.ac_local
    LEFT OUTER JOIN PartitionCode pc ON pc.partition_code = tb.partition_code
GROUP BY al.lv1, al.lv2, al.lv3

kyzen

如果数据结构允许，请在所有case语句之前执行汇总，例如：

INSERT INTO #VolumesAndValues 
SELECT
    base
    ,ap
    ,mc1
    ,plv2
    ,sum(subtotal) as total
    ,SUM(CASE WHEN plv2 = 'MA' THEN subtotal ELSE 0 END) AS base
    ,SUM(CASE WHEN plv2 = 'AJ' THEN subtotal ELSE 0 END) AS batch
    ,SUM(CASE WHEN plv2 in('ADCG','ADIG') AND 1=1 THEN othersubtotal ELSE 0 END) AS net                    
    ,SUM(CASE WHEN plv2 = 'FR' THEN subtotal ELSE 0 END) AS fr
    ,SUM(CASE WHEN plv2 = 'PA' THEN subtotal ELSE 0 END) AS pa
    ,SUM(CASE WHEN plv2 = 'RE' THEN subtotal ELSE 0 END) AS re
    ,SUM(CASE WHEN plv2 = 'OF' THEN subtotal ELSE 0 END) AS of
    ,SUM(CASE WHEN plv2 = 'PR' THEN subtotal ELSE 0 END) AS pr
    ,'1 Table Data' as rowType
FROM (
    SELECT 
        ahl.lv1 AS base 
        ,ahl.lv2 AS ap
        ,ahl.lv3 AS mc1 
        ,pc.partition_lv2 as plv2
        ,sum(tb.us) as subtotal
        ,sum(rtb.us) as othersubtotal
    FROM TrialBal tb 
    LEFT OUTER JOIN AcctListLocal al ON tb.entity_code = al.entity_code
        and tb.ac_gl_c = al.ac_local
    LEFT OUTER JOIN PartitionCode pc ON pc.partition_code = tb.partition_code
    GROUP BY 
            al.lv1
            ,al.lv2
            ,al.lv3 
            ,pc.partition_lv2
) subq
GROUP BY
    base
    ,ap
    ,mc1
    ,plv2

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。