Как оптимизировать запрос, который занимает слишком много времени

Question

В этом запросе я вызываю функцию "fnGetPoolWinner" в приведенном ниже запросе 2-3 раза, что, по-моему, замедляет ее производительность, или причиной является использование нескольких операторов case.

Это занимает время около 00:01:39.

Я пробовал варианты, такие как создание индексов в таблице и создание общего выражения таблицы, а затем с помощью этого запроса, но не нашел никакого решения, чтобы сократить его время.

DECLARE @TournamentId INT = 1

DECLARE @TournamentName AS NVARCHAR(MAX)

SELECT @TournamentName = TournamentName FROM Tournaments WHERE  TournamentId = @TournamentId


    SELECT 
    (SELECT CustomerIds FROM DBO.fnGetPoolWinner(SET_1.BracketBettingAmount,@TournamentId,
    (CASE
        WHEN SET_1.GAMES= 63
        THEN 1
        WHEN SET_1.GAMES= 31
        THEN 2
        WHEN SET_1.GAMES= 15
        THEN 3
        ELSE 0
    END))) AS [AccountNumber],
    @TournamentName AS TournamentName,
    (CASE
        WHEN SET_1.GAMES= 63
        THEN 'GENERAL POOL ROUND OF 64 $'+CAST(CAST(SET_1.BracketBettingAmount AS INT) AS NVARCHAR)+''
        WHEN SET_1.GAMES= 31
        THEN 'GENERAL POOL ROUND OF 32 $'+CAST(CAST(SET_1.BracketBettingAmount AS INT) AS NVARCHAR)+''
        WHEN SET_1.GAMES= 15
        THEN 'GENERAL POOL SWEET 16 $'+CAST(CAST(SET_1.BracketBettingAmount AS INT) AS NVARCHAR)+''
        ELSE ''
    END) AS [PoolName],
    (SELECT BracketNames FROM DBO.fnGetPoolWinner(SET_1.BracketBettingAmount,@TournamentId,
    (CASE
        WHEN SET_1.GAMES= 63
        THEN 1
        WHEN SET_1.GAMES= 31
        THEN 2
        WHEN SET_1.GAMES= 15
        THEN 3
        ELSE 0
    END))) AS GroupEntries,
    'Public' AS Access,
    COUNT(SET_1.BracketId) AS Members, 
    COUNT(SET_1.BracketId)*SET_1.BracketBettingAmount AS CurrentPoolPrize, 
    (SELECT WinnerNames FROM DBO.fnGetPoolWinner(SET_1.BracketBettingAmount,@TournamentId,
    (CASE
        WHEN SET_1.GAMES= 63
        THEN 1
        WHEN SET_1.GAMES= 31
        THEN 2
        WHEN SET_1.GAMES= 15
        THEN 3
        ELSE 0
    END))) AS WinnersName,
    (CASE
        WHEN SET_1.GAMES= 63
        THEN 1
        WHEN SET_1.GAMES= 31
        THEN 2
        WHEN SET_1.GAMES= 15
        THEN 3
        ELSE 0
    END) AS RoundId,
    SET_1.BracketBettingAmount AS BettingAmount
    FROM
        (SELECT BR.BracketId,
            BracketBettingAmount,
            (SELECT COUNT(1) FROM BracketPredictions AS BP WHERE BP.BracketPredictionBracketId =BR.BracketId) AS GAMES
        FROM Brackets AS BR WHERE BR.BracketTournamentId = @TournamentId AND BR.IsDeleted = 0) SET_1 WHERE SET_1.GAMES > 0
    GROUP BY SET_1.BracketBettingAmount, SET_1.GAMES HAVING SET_1.BracketBettingAmount IN (1,5,10,25)

Answer 1

Попробуй это, не проверял

DECLARE @TournamentId INT = 1

    with SET_1 as (

      SELECT BR.BracketId, BracketBettingAmount as BettingAmount, W.GAMES, TN.TournamentName,
             case W.GAMES 
             WHEN 63 THEN 1
             WHEN 31 THEN 2
             WHEN 15 THEN 3
             ELSE 0 END as IDGAME,

             CASE W.GAMES
             WHEN 63 THEN 'GENERAL POOL ROUND OF 64 $'+CAST(CAST(BracketBettingAmount AS INT) AS NVARCHAR)
             WHEN 31 THEN 'GENERAL POOL ROUND OF 32 $'+CAST(CAST(BracketBettingAmount AS INT) AS NVARCHAR)
             WHEN 15 THEN 'GENERAL POOL SWEET 16 $'   +CAST(CAST(BracketBettingAmount AS INT) AS NVARCHAR)
             ELSE '' END AS PoolName,

             TN.TournamentId

             FROM Brackets AS BR 
             cross apply
             (
                SELECT COUNT(*) GAMES FROM BracketPredictions AS BP WHERE BP.BracketPredictionBracketId =BR.BracketId
             ) W

             inner join Tournaments TN on BR.BracketTournamentId=TN.TournamentId

             WHERE BR.IsDeleted = 0 and BracketBettingAmount IN (1,5,10,25) and W.GAMES>0 and TN.TournamentId = @TournamentId


    ),

    NbBracketId as ( select NbBracketId, count(*) Nb from SET_1 group by NbBracketId) 


    SELECT 
    Y.CustomerIds AccountNumber, X.TournamentName, X.PoolName, Y.BracketNames AS GroupEntries, 'Public' AS Access,
    Z.NB AS Members, Z.NB*X.BettingAmount AS CurrentPoolPrize, Y.WinnerNames, X.IDGAME AS RoundId, X.BettingAmount
    FROM SET_1 X 
    outer apply DBO.fnGetPoolWinner(SET_1.BracketBettingAmount, SET_1.TournamentId, SET_1.IDGAME) as Y
    inner join NbBracketId Z on X.BracketId=Z.BracketId

Answer 2

DBO.fnGetPoolWinner можно оптимизировать самостоятельно.

попробуй этот подход,

DECLARE @TournamentId INT = 1
DECLARE @TournamentName AS NVARCHAR(MAX)
SELECT @TournamentName = TournamentName FROM Tournaments WHERE  TournamentId = @TournamentId
select @TournamentName TournamentName,
CustomerIds AS [AccountNumber]
,(CASE
        WHEN SET_1.GAMES= 63
        THEN 'GENERAL POOL ROUND OF 64 $'+CAST(CAST(SET_1.BracketBettingAmount AS INT) AS NVARCHAR)+''
        WHEN SET_1.GAMES= 31
        THEN 'GENERAL POOL ROUND OF 32 $'+CAST(CAST(SET_1.BracketBettingAmount AS INT) AS NVARCHAR)+''
        WHEN SET_1.GAMES= 15
        THEN 'GENERAL POOL SWEET 16 $'+CAST(CAST(SET_1.BracketBettingAmount AS INT) AS NVARCHAR)+''
        ELSE ''
    END) AS [PoolName]
    ,ca.BracketNames as GroupEntries
     'Public' AS Access,
    COUNT(SET_1.BracketId) AS Members, 
    COUNT(SET_1.BracketId)*SET_1.BracketBettingAmount AS CurrentPoolPrize
    ,WinnerNames  as WinnersName
    , (CASE
        WHEN SET_1.GAMES= 63
        THEN 1
        WHEN SET_1.GAMES= 31
        THEN 2
        WHEN SET_1.GAMES= 15
        THEN 3
        ELSE 0
    END) AS RoundId,
    SET_1.BracketBettingAmount AS BettingAmount
 FROM
       (SELECT BR.BracketId,
            BracketBettingAmount,
            (SELECT COUNT(1) FROM BracketPredictions AS BP WHERE BP.BracketPredictionBracketId =BR.BracketId) AS GAMES
        FROM Brackets AS BR WHERE BR.BracketTournamentId = @TournamentId AND BR.IsDeleted = 0) SET_1 
        WHERE SET_1.GAMES > 0
    GROUP BY SET_1.BracketBettingAmount, SET_1.GAMES 
    HAVING SET_1.BracketBettingAmount IN (1,5,10,25)

    cross apply(SELECT CustomerIds,BracketNames,WinnerNames
     FROM DBO.fnGetPoolWinner(SET_1.BracketBettingAmount,@TournamentId,
    (CASE
        WHEN SET_1.GAMES= 63
        THEN 1
        WHEN SET_1.GAMES= 31
        THEN 2
        WHEN SET_1.GAMES= 15
        THEN 3
        ELSE 0
    END)) )ca