Using Table-Valued Parameter's column in stored proc -

Question

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.

will you throw in some realistic data? – TheVillageIdiot Aug 05 '11 at 11:02 — TheVillageIdiot, Aug 05 '11 at 11:02

score 3 · Answer 1 · answered Aug 05 '11 at 11:01

3

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))

answered Aug 05 '11 at 11:01

TheVillageIdiot

40,053
20
133
188

I have tested and it is indeed giving values in the range of from 2 minutes less to passed date. – TheVillageIdiot Aug 05 '11 at 11:18
This looks great, much simpler and more succinct than my query. However, it still has the problem that I would like to have the latest match from the `between` clause. – Alex Aug 05 '11 at 12:26
@Alex it will also be better performing as filtering is done at one level only in `ON` clause of `INNER JOIN` – TheVillageIdiot Aug 05 '11 at 15:54
What about SELECT MAX(S.AtTime), rest of query here. – Michael Riley - AKA Gunny Aug 06 '11 at 10:26

Using Table-Valued Parameter's column in stored proc -

1 Answers1