Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cross apply a table valued function

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.

like image 287
CR41G14 Avatar asked Jul 29 '15 19:07

CR41G14


People also ask

How do you use cross apply with table valued functions?

CROSS APPLY in SQL ServerCROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. It other words, result of CROSS APPLY doesn't contain any row of left side table expression for which no result is obtained from right side table expression.

What is a cross apply in SQL?

The CROSS APPLY operator is semantically similar to INNER JOIN. It retrieves all the records from the table where there are corresponding matching rows in the output returned by the table valued function.

What is cross apply and outer apply?

Thus, the CROSS APPLY is similar to an INNER JOIN, or, more precisely, like a CROSS JOIN with a correlated sub-query with an implicit join condition of 1=1. The OUTER APPLY operator returns all the rows from the left table expression regardless of its match with the right table expression.


1 Answers

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.

like image 143
Francisco Goldenstein Avatar answered Sep 21 '22 17:09

Francisco Goldenstein