QlikView脚本中SQL CASE语句性能优化咨询

问题背景 #

QlikView脚本中嵌入的SQL CASE语句导致数据重载耗时超2小时，移除该语句后仅需4分钟。该语句针对recip为client和reClient的场景，重复对x.MESSAGE_DATA执行多次字符串替换、截取、XML转换及查询操作，以此生成[Percent Share]字段。考虑过将表存储为QVD后在QlikView脚本中计算，但不确定性能提升效果，寻求优化方案。

原CASE语句代码：

case when recip = 'client' 
        then 
            cast(cast(cast(replace(replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac'),
            substring(replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac'),
            charindex('<Jv-Ins',
            replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac'),1),
                        charindex('<Claim',replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac'),1)-    charindex('<Jv-Ins',
            replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac'),1)),'<Jv-Ins>') as xml).query('data(/Jv-Ins/Claim/Contract/SharePercentage/Rate)') as varchar(6)) as float)
            
     when recip = 'reClient' 
        then 
            cast(cast(cast(replace(replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac'),
            substring(replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac'),
            charindex('<Jv-Ins',
            replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac'),1),
            charindex('<Claim',replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac'),1)-charindex('<Jv-Ins',
            replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac'),1)),'<Jv-Ins>') as xml).query('data(/Jv-Ins/Claim/Contract/SharePercentage/Rate)') as varchar(6)) as float)  end  [Percent Share],

优化方案 #

1. 消除重复计算，预处理中间结果 #

原代码重复执行了多次replace(cast(x.MESSAGE_DATA as varchar(12)),'ac:','ac')，每次计算都会消耗资源。可以通过CTE（公共表表达式）预处理该字段，后续直接引用处理后的结果，同时合并client和reClient的分支逻辑：

WITH PreprocessedData AS (
    SELECT 
        recip,
        MESSAGE_DATA,
        -- 预计算清理后的字符串
        replace(cast(MESSAGE_DATA as varchar(12)),'ac:','ac') AS CleanedMessage
    FROM your_table
)
SELECT 
    recip,
    CASE 
        WHEN recip IN ('client', 'reClient') THEN
            cast(cast(cast(replace(CleanedMessage,
                substring(CleanedMessage,
                    charindex('<Jv-Ins', CleanedMessage, 1),
                    charindex('<Claim', CleanedMessage, 1) - charindex('<Jv-Ins', CleanedMessage, 1)
                ), '<Jv-Ins>') as xml).query('data(/Jv-Ins/Claim/Contract/SharePercentage/Rate)') as varchar(6)) as float)
        ELSE NULL
    END AS [Percent Share]
FROM PreprocessedData

2. 简化XML解析逻辑 #

原代码通过截取字符串片段再转换XML的方式效率较低，若MESSAGE_DATA清理后可成为完整XML，可直接使用XML的value()方法替代query()，value()更直接获取指定节点的值，性能更优：

-- 基于预处理的CleanedMessage
cast(cast(CleanedMessage as xml).value('(/Jv-Ins/Claim/Contract/SharePercentage/Rate)[1]', 'varchar(6)') as float)

如果必须截取片段，也可以在预处理步骤中完成截取，避免重复计算截取逻辑。

3. 转移计算到QlikView脚本（QVD方案） #

将未计算[Percent Share]的原始数据导出为QVD，在QlikView本地脚本中处理字符串和XML解析，分散数据库端的计算压力：

-- 第一步：导出原始数据到QVD
LOAD
    recip,
    MESSAGE_DATA
FROM [your_sql_connection] (sql, ...)
STORE INTO [RawData.qvd];

-- 第二步：从QVD加载并计算目标字段
LOAD
    recip,
    MESSAGE_DATA,
    If(recip='client' or recip='reClient',
        // 模拟SQL中的字符串处理逻辑
        Let cleanedMsg = Replace(Replace(MESSAGE_DATA, 'ac:', 'ac'), SubString(Replace(MESSAGE_DATA, 'ac:', 'ac'), Index(Replace(MESSAGE_DATA, 'ac:', 'ac'), '<Jv-Ins'), Index(Replace(MESSAGE_DATA, 'ac:', 'ac'), '<Claim') - Index(Replace(MESSAGE_DATA, 'ac:', 'ac'), '<Jv-Ins')), '<Jv-Ins>');
        Num(XMLGet(XMLParse(cleanedMsg), '/Jv-Ins/Claim/Contract/SharePercentage/Rate'))
    ) as [Percent Share]
FROM [RawData.qvd] (qvd);

QlikView的本地计算可利用客户端资源，避免数据库端的密集计算瓶颈，尤其适合数据量较大的场景。

4. 数据库端持久化计算（若允许修改数据库） #

如果有权限修改数据库，可添加持久化计算列，提前完成MESSAGE_DATA的清理、XML解析和值提取，查询时直接引用该列：

ALTER TABLE your_table
ADD [Percent Share] AS 
    CASE 
        WHEN recip IN ('client', 'reClient') THEN
            cast(cast(cast(replace(replace(cast(MESSAGE_DATA as varchar(12)),'ac:','ac'),
                substring(replace(cast(MESSAGE_DATA as varchar(12)),'ac:','ac'),
                    charindex('<Jv-Ins', replace(cast(MESSAGE_DATA as varchar(12)),'ac:','ac'),1),
                    charindex('<Claim',replace(cast(MESSAGE_DATA as varchar(12)),'ac:','ac'),1)-charindex('<Jv-Ins',replace(cast(MESSAGE_DATA as varchar(12)),'ac:','ac'),1)
                ),'<Jv-Ins>') as xml).query('data(/Jv-Ins/Claim/Contract/SharePercentage/Rate)') as varchar(6)) as float)
        ELSE NULL
    END PERSISTED;

持久化计算列会在数据插入/更新时自动计算，查询时无需重复运算，大幅提升查询速度。

内容的提问来源于stack exchange，提问作者Paulina P