Cross apply a table valued function

Question

A real mind bender here guys!

I have a table which basically positions users in a league:

LeagueID Stake  League_EntryID  UserID  TotalPoints TotalBonusPoints    Prize
13028   2.00        58659        2812       15           5              NULL
13028   2.00        58662        3043       8            3              NULL
13029   5.00        58665        2812       8            3              NULL

The League_EntryID is the unique field here but you will see this query returns multiple leagues that user is entered for that day.

I also have a table value function which returns the current prize standings for the league and this accepts the LeagueID as a parameter and returns the people who qualify for prize money. This is a complex function which ideally I would like to keep as the function accepting the LeagueID. The result of this is as below:

UserID  Position    League_EntryID  WinPerc     Prize
2812    1               58659       36.000000   14.00
3043    6               58662       2.933333    4.40
3075    6               58664       2.933333    4.40

Essentially what I want to do is to join the table value function to the topmost query by passing in the LeagueID to essentially update the Prize Field for that League_EntryID i.e.

SELECT * FROM [League]
INNER JOIN [League_Entry] ON [League].[LeagueID] = [League_Entry].[LeagueID]
INNER JOIN [dbo].[GetPrizesForLeague]([League].[LeagueID]) ....

I'm not sure if a CROSS APPLY would work here but essentially I believe I need to JOIN on both the LeagueID and the League_EntryID to give me my value for the Prize. Not sure on the best way to do this without visiting a scalar function which will in turn call the table value function and obtain the Prize from that.

Speed is worrying me here.

P.S. Not all League_EntryID's will exist as a part of the table value function output so maybe an OUTER JOIN/APPLY can be used?

EDIT See the query below

SELECT DISTINCT [LeagueID],
    [CourseName],
    [Refunded],
   [EntryID],
   [Stake],
   d.[League_EntryID],
   d.[UserID],
   [TotalPoints],
   [TotalBonusPoints],
   [TotalPointsLastRace],
   [TotalBonusPointsLastRace],
   d.[Prize],
   [LeagueSizeID],
   [TotalPool],
   d.[Position],
   [PositionLastRace],
   t.Prize

FROM
(
SELECT [LeagueID],
   [EntryID],
   [Stake],
   [MeetingID],
   [Refunded],
   [UserID],
   [League_EntryID],
   [TotalPoints],
   [TotalBonusPoints],
   [TotalPointsLastRace],
   [TotalBonusPointsLastRace],
   [Prize],
   [LeagueSizeID],
   [dbo].[GetTotalPool]([LeagueID], 1) AS [TotalPool],
   RANK() OVER( PARTITION BY [LeagueID] ORDER BY [TotalPoints] DESC, [TotalBonusPoints] DESC) AS [Position],
   RANK() OVER( PARTITION BY [LeagueID] ORDER BY [TotalPointsLastRace] DESC, [TotalBonusPointsLastRace] DESC) AS [PositionLastRace],
   ROW_NUMBER() OVER (PARTITION BY [LeagueID]
                                ORDER BY [TotalPoints] DESC, [TotalBonusPoints] DESC
                               ) as [Position_Rownum]
FROM [DATA] ) AS d

INNER JOIN [Meeting] WITH (NOLOCK)  ON [d].[MeetingID] = [Meeting].[MeetingID]
INNER JOIN [Course] ON [Meeting].[CourseID] = [Course].[CourseID]
OUTER APPLY (SELECT * FROM [dbo].[GetLeaguePrizes](d.[LeagueID])) t
WHERE (
        ([LeagueSizeID] = 3 AND [Position_Rownum] <= 50)
        OR (d.[UserID] = @UserID AND [LeagueSizeID] = 3)
      )
      OR
      (
        [LeagueSizeID] in (1,2)
      )

ORDER BY [LeagueID], [Position]

Any direction would be appreciated.

If you need help improving the performance, you should post a question with all the details: table & index structure, the current code you have and example data + expected results, preferably in SQL Fiddle. If the function can be changed for example to an inline function, the performance should be quite a lot better, but of course it depends a lot on your case — James Z

Francisco Goldenstein Francisco Goldenstein · Accepted Answer · 2015-07-29T20:00:47

You need to use OUTER APPLY (a mix of CROSS APPLY and LEFT JOIN).

SELECT * FROM [League]
INNER JOIN [League_Entry] ON [League].[LeagueID] = [League_Entry].[LeagueID]
OUTER APPLY [dbo].[GetPrizesForLeague]([League].[LeagueID]) t

Performance is very good with CROSS APPLY/OUTER APPLY. It's great for replacing some inner queries and cursors.

Cross apply a table valued function

1 Answers