在存储过程中使用表值参数的列 -

发布于 2024-11-27 23:50:42 字数 996 浏览 4 评论 0原文

我将带有 2 个字段（一个 datetime 和一个 varchar(3)）的 TVP 传递到存储过程中，并且我尝试返回所有表行，其中表的日期时间列要么等于 TVP 日期时间之一，要么提前几分钟（我不介意重复，但更喜欢没有重复）。

这就是我的想法 - 目前速度非常慢（约 5 秒！），而且我不知道如何改进它。索引表？哪一栏？ AtTime？

我知道这是一个繁琐的查询，因为它需要在尝试匹配行之前计算要查找的范围，因此如果有完全不同但更好的方法来执行此操作，请告诉我。另外，输入 TVP 有大约 300 行，表本身有超过 200k，因此有大量数据需要匹配和搜索。

CREATE PROCEDURE [dbo].[spGetPricesForDates]
    @tvp tvpType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT S.AtTime, S.Underlying, S.Price
    FROM SourceTable S, @tvp T
    WHERE S.Underlying = T.Underlying   
    AND S.AtTime in (select AtTime
                    from SourceTable 
                    where AtTime 
                    between DATEADD(mi, -2, T.MyDate)
                    and T.MyDate)
END

编辑我刚刚意识到我的in查询不会执行我想要的操作 - 我想返回最新的在 2 分钟范围内匹配每个 tvp 行的表日期/价格行，而目前它会为我提供该 Between 集中匹配的所有内容。我尝试使用 MAX(AtTime) 但这将其限制为一个可能的匹配，因此我回到了第 1 方。

原文

I'm passing a TVP with 2 fields (a datetime and a varchar(3)) into a stored proc, and I'm trying to return all the table rows where the table's datetime column is either equal to one of the TVP datetimes or up to a couple of minutes earlier (I don't mind duplicates but would prefer without).

This is what I've come up with - it's currently very slow (~5 seconds!) and I'm not sure what to do to improve it. Index the table? Which column? AtTime?

I know it's a fiddly query as it needs to compute a range to look for before trying to match a row, so if there's an entirely different but preferable way to do this please let me know. Plus the input TVP has ~300 rows and the table itself has over 200k, so there's a lot of data to match and search.

CREATE PROCEDURE [dbo].[spGetPricesForDates]
    @tvp tvpType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SELECT S.AtTime, S.Underlying, S.Price
    FROM SourceTable S, @tvp T
    WHERE S.Underlying = T.Underlying   
    AND S.AtTime in (select AtTime
                    from SourceTable 
                    where AtTime 
                    between DATEADD(mi, -2, T.MyDate)
                    and T.MyDate)
END

edit I just realised my in query won't do what I want - I want to return the latest
matching table date/price row per tvp row within a 2 minute range, whereas at the moment it'll give me everything that matches in that between set. I tried using MAX(AtTime) but that limits it to one possible match so I'm back to square 1.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

流绪微梦 2024-12-04 23:50:42

尝试使用此内部联接：

SELECT S.AtTime, S.Underlying, S.Price
FROM SourceTable S
INNER JOIN @tvp T ON (S.Underlying = T.Underlying 
        AND (S.AtTime BETWEEN DATEADD(mi, -2, T.MyDate) AND T.MyDate))

Try using this inner join:

SELECT S.AtTime, S.Underlying, S.Price
FROM SourceTable S
INNER JOIN @tvp T ON (S.Underlying = T.Underlying 
        AND (S.AtTime BETWEEN DATEADD(mi, -2, T.MyDate) AND T.MyDate))

回复收藏 0 原文

~没有更多了~