i具有一个函数,该功能必须根据从具有历史值的表格中检索的转换率转换货币。它采用四个参数(@to_curr,@from_curr, @trans_date,@gl_cmp)adn返回汇率。
表结构是
-
currencypk int primary key
-
gl_crcnv_bdate datetime
-
gl_crcnv_edate datetime
-
gl_crcnv_rate float
-
gl_cmp_key char(30)
(我们有多家公司确定每个公司) -
gl_CRNCY_TO char(30)
-
gl_CRNCY_FROM char(30)
以下是我的代码,当我将其插入功能时,它会导致执行时间大大增加。我有一个很好的主意,我的功能中的瓶颈在哪里,但我坚持如何重写它。我之所以有一个丑陋的原因,即(和)或(和)设置的子句是因为并非每个关系都颠倒了。
例如,有从美元到GBP的转换率的记录,但根本没有GBP到USD的记录。该事实说明了SELECT
中的案例语句,以获取表中不存在的实际转换。 isnull
是,以防万一它没有返回,因此使用1
任何帮助都非常感谢。
SELECT
isnull((SELECT convert(decimal(5,4),
case
when @to_curr <> gl_CRNCY_TO
then (1/gl_crcnv_rate)
else gl_crcnv_rate
end)
FROM
[TABLE]
WHERE
gl_cmp_key = @gl_cmp
AND ((gl_CRNCY_TO = @from_curr AND gl_CRNCY_FROM = @to_curr)
OR (gl_CRNCY_TO = @to_curr AND gl_CRNCY_FROM = @from_curr))
AND @trans_date BETWEEN gl_crcnv_bdate AND gl_crcnv_edate), 1)
您可以尝试:
SELECT isnull(SELECT convert(decimal(5,4), gl_crcnv_rate)
FROM (select 1/gl_crcnv_rate as gl_crcnv_rate
from [TABLE]
WHERE gl_cmp_key = @gl_cmp AND
gl_CRNCY_TO = @from_curr AND
gl_CRNCY_FROM = @to_curr AND
@trans_date BETWEEN gl_crcnv_bdate AND gl_crcnv_edate
UNION
select gl_crcnv_rate
from [TABLE]
WHERE gl_cmp_key = @gl_cmp AND
gl_CRNCY_TO = @to_curr AND
gl_CRNCY_FROM = @from_curr AND
@trans_date BETWEEN gl_crcnv_bdate AND gl_crcnv_edate
) sq,
1)
这假设存在两个可能的转换记录,则转换因子将以相同的值进行评估,并且有一个查询可以使用的索引。
这就是我能根据私人信息想到的一切
创建计算的列,以便我们不必对其进行计算。
ALTER TABLE [TABLE] ADD gl_crcnv_rate_invert AS (1/gl_crcnv_rate) PERSISTED;
Craete索引
CREATE NONCLUSTERED INDEX ix_cmpkey_crncyto_crncyfrom_bdate_edate ON [TABLE] (gl_CRNCY_TO, gl_CRNCY_FROM, gl_crcnv_bdate, gl_crcnv_edate)
马克·班尼斯特(Mark Bannisters)的回答已经是向前迈出的一步,但是我会进一步将代码更改为:
SELECT convert(decimal(5,4), Coalesce( -- direct lookup
(SELECT TOP 1 gl_crcnv_rate
FROM [TABLE]
WHERE gl_cmp_key = @gl_cmp
AND gl_CRNCY_FROM = @from_curr
AND gl_CRNCY_TO = @to_curr
AND @trans_date BETWEEN gl_crcnv_bdate AND gl_crcnv_edate),
-- reverse lookup
(SELECT TOP 1 1/gl_crcnv_rate
FROM [TABLE]
WHERE gl_cmp_key = @gl_cmp
AND gl_CRNCY_TO = @from_curr
AND gl_CRNCY_FROM = @to_curr
AND @trans_date BETWEEN gl_crcnv_bdate AND gl_crcnv_edate),
-- fallback
1))
COALESE()
足够聪明,如果发现第一个已经返回了匹配项,则不执行第二个块。它还为您提供UNION
(首先应该是UNION ALL
!)。
此外,您还需要确保具有这样的索引:
CREATE INDEX idx ON [TABLE] (gl_cmp_key, gl_CRNCY_FROM, gl_CRNCY_TO, gl_crcnv_edate, gl_crcnv_bdate)
坦率地说,我会使当前的PK非聚类,并聚集,这样您可以在书签查找中节省很多;唯一的缺点是,您可能需要偶尔重建/碎片索引,因为添加新记录会随着时间的推移而导致碎片化,因为新值在现有记录之间被挤压...(Side-Mark:您甚至需要currencypk?如果不是,只需制作`gl_cmp_key,gl_crncy_from,gl_crncy_to,gl_crcnv_edate,gl_crcnv_bdate,currencypk'the new pk'the new pk ...相当丑陋,相当丑陋,但在功能上同样有效,并且在空间上保存了很多)。
您想在bdate
之前拥有edate
的原因是,无论如何您都可能正在寻找最新的速率。假设您有20个率坐在那里,他们都将在今天之前拥有一个开始日期,但是只有少数几个(否则您可以预测未来,只有一个)将拥有今天之后的末日。
一些进一步的建议:如果您想要性能,则应摆脱标量功能。众所周知,它们在MSSQL =(易于使用,但是在基于设定的操作中使用时会导致大型开销;例如,一次更新几个记录。
我假设您现在有一些内容:
UPDATE [INVOICE]
SET target_amount = base_amount * dbo.fn_get_rate(gl_cmp, base_ccy, target_ccy, invoice_date)
WHERE blah = blah
如果将其更改为下方,它的速度会更快,尽管我同意重复使用要少得多...我猜=)
UPDATE [INVOICE]
SET target_amount = Coalesce( -- direct lookup
(SELECT TOP 1 [INVOICE].base_amount * d.gl_crcnv_rate
FROM [TABLE] d
WHERE d.gl_cmp_key = [INVOICE].gl_cmp
AND d.gl_CRNCY_FROM = [INVOICE].base_ccy
AND d.gl_CRNCY_TO = [INVOICE].target_ccy
AND [INVOICE].invoice_date BETWEEN d.gl_crcnv_bdate AND d.gl_crcnv_edate),
-- reverse lookup
(SELECT TOP 1 [INVOICE].base_amount * r.gl_crcnv_rate
FROM [TABLE] r
WHERE r.gl_cmp_key = [INVOICE].gl_cmp
AND r.gl_CRNCY_TO = [INVOICE].target_ccy
AND r.gl_CRNCY_FROM = [INVOICE].base_ccy
AND [INVOICE].invoice_dateBETWEEN r.gl_crcnv_bdate AND r.gl_crcnv_edate),
-- fallback
[INVOICE].base_amount))
WHERE blah = blah