喜欢运算符减慢查询速度

Question

有没有比使用LIKE运算符更有效的方法来过滤LEDGERACCOUNT列？当LEDGERACCOUNT列以每个帐户 # 开头时，我需要对TRANSACTIONCURRENCYAMOUNT列求和。这会减慢我查询的其余部分。我正在尝试与另外两个表连接。

LEDGERACCOUNT
213510-LTL
305100-CORP
441230-SAA
441230-GODP

select g.ACCOUNTINGDATE,
sum(case when g.LEDGERACCOUNT like '213510%'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0 end) as [113510-GL Transfer],
sum(case when g.LEDGERACCOUNT like '305100%'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0
end) as [205100-GL Transfer],
sum(case when g.LEDGERACCOUNT like '441230%'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0
end) as [241230-GL Transfer],
sum(case when g.LEDGERACCOUNT not like '213510%'
or g.LEDGERACCOUNT not like '305100%'
or g.LEDGERACCOUNT not like '441230%'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0
end) as [Other-GL Transfer]
from [AX2cTestStage].[dbo].[GENERALJOURNALACCOUNTENTRY_V] g
group by g.ACCOUNTINGDATE
order by g.ACCOUNTINGDATE

Answer 1

对于一般的壁架报告，您最好为 GL 代码的每个组件创建单独的列。例如，在我的上一家公司中，GL 代码是 [业务线]-[区域]-[部门]。通过单独存储每个报告，我可以轻松地按 LoB、区域、部门或任何组合运行报告。当然，这只有在您控制数据库并且可以添加列的情况下才有可能。另一种可能性是将它们添加为视图中的计算列，如果它是实例化视图，这将非常有帮助。

Answer 2

我唯一真正的想法是使用left(或者，我想，substring(而不是like。 我不太确定它会提高性能，但我认为值得一试。

一切都未经测试

select g.ACCOUNTINGDATE,
sum(case when left(g.LEDGERACCOUNT, 6) = '213510'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0 end) as [113510-GL Transfer],
sum(case when left(g.LEDGERACCOUNT, 6) = '305100'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0
end) as [205100-GL Transfer],
sum(case when left(g.LEDGERACCOUNT, 6) = '441230'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0
end) as [241230-GL Transfer],
sum(case when left(g.LEDGERACCOUNT, 6) not in ('213510', '305100', '441230')
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0
end) as [Other-GL Transfer]
from [AX2cTestStage].[dbo].[GENERALJOURNALACCOUNTENTRY_V] g
group by g.ACCOUNTINGDATE
order by g.ACCOUNTINGDATE

请注意，Other-GL Transfer逻辑与原始查询不同，但我认为这是您实际尝试完成的任务。

另一个想法是放入组中。

注意 -这以行而不是列的形式提供数据大多数情况下只是提供这个，以防它激发你的想法。

select case when left(g.LEDGERACCOUNT, 6) in ('213510', '305100', '441230')
then left(g.LEDGERACCOUNT, 6)
else 'Other'
end as ShortLedgerAcct,
g.ACCOUNTINGDATE,
sum(convert(float, g.TRANSACTIONCURRENCYAMOUNT) as TransferAmount
from [AX2cTestStage].[dbo].[GENERALJOURNALACCOUNTENTRY_V] g
group by g.ACCOUNTINGDATE,
case when left(g.LEDGERACCOUNT, 6) in ('213510', '305100', '441230')
then left(g.LEDGERACCOUNT, 6)
else 'Other'
end
order by g.ACCOUNTINGDATE

最后，您可以使用cross apply我怀疑它是否会提供显着不同的执行计划，但有些人觉得它更漂亮：

select g.ACCOUNTINGDATE,
sum(case when ca.ShortLedgerAcct = '213510'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0 end) as [113510-GL Transfer],
sum(case when ca.ShortLedgerAcct = '305100'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0
end) as [205100-GL Transfer],
sum(case when ca.ShortLedgerAcct = '441230'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0
end) as [241230-GL Transfer],
sum(case when ca.ShortLedgerAcct = 'Other'
then convert(float, g.TRANSACTIONCURRENCYAMOUNT)
else 0
end) as [Other-GL Transfer]
from [AX2cTestStage].[dbo].[GENERALJOURNALACCOUNTENTRY_V] g
cross apply (
select case when left(g.LEDGERACCOUNT, 6) in ('213510', '305100', '441230')
then left(g.LEDGERACCOUNT, 6)
else 'Other'
end ShortLedgerAcct
) ca
group by g.ACCOUNTINGDATE
order by g.ACCOUNTINGDATE