我有一个数据加载场景,我在其中创建动态SQL查询以在我们的服务中提取数据和缓存.有1个表包含所有产品数据：ProductHistory(47列,200,000条记录并将继续增长)
我需要的：
使用最大ID,最大版本和最大更改来获取最新产品.
第一次尝试：
SELECT distinct Product.* FROM ProductHistory product WHERE product.version = (SELECT max(version) from ProductHistory p2 where product.Id = p2.Id and product.changeId = (SELECT max(changeid) from ProductHistory p3 where p2.changeId = p3.changeId))
这耗时超过2.51分钟.
其他失败的尝试：
select distinct product.* from ProductHistory product where CAST(CAST(id as nvarchar)+'0'+CAST(Version as nvarchar)+'0'+CAST(changeid as nvarchar) as decimal) = (select MAX(CAST(CAST(id as nvarchar)+'0'+CAST(Version as nvarchar)+'0'+CAST(changeid as nvarchar) as decimal)) from ProductHistory p2 where product.Id = p2.Id)
它基本上使用与订购日期时相同的原则,连接按相关性排序的数字.
For example 11 Jun 2007 = 20070711 And in our case: Id = 4,version = 127,changeid = 32 => 40127032 The zeros are there not to mix up the 3 different ids
但是这个需要3.10分钟！
