Azure : Very slow performance on WITH statement query

Question

我正在尝试了解 sql azure 中 WITH 语句的用法。我有一个初始查询，然后需要过滤 2 次。如果我只运行初始查询，它运行得很快。但是，一旦我添加了额外的过滤器，查询就会运行得很慢，永远不会完成，并且 azure force 会关闭连接。

所以，第一个 WITH 语句，它自己运行得非常快 ->

CREATE TYPE BrokerAccountAndTimeType as TABLE
(
    [BrokerAccountId] [bigint],
    [TimeUTC] [datetime]
);
GO

CREATE PROCEDURE [dbo].[GetLatestOpenBrokerAccountTradesByBrokerAccountAndTime]
@UserId [int],
@BrokerAccountAndTime BrokerAccountAndTimeType READONLY
AS
BEGIN
WITH trades as (SELECT bats.*
                FROM BrokerAccountTrades bats
                where bats.[TradeTimeUTC] = (SELECT MAX([TradeTimeUTC]) 
                           FROM BrokerAccountTrades bats2
                           inner join @BrokerAccountAndTime bat
                           on (bats.UserId = @UserId and bats2.BrokerAccountId = bat.BrokerAccountId)
                           WHERE bats2.UserId = bats.UserId
                           AND bats2.BrokerAccountId = bats.BrokerAccountId
                           AND bats2.SecurityId = bats.SecurityId
                           AND bats2.[TradeTimeUTC] < bat.TimeUTC))

    select *
    from trades

END

这不会造成问题 - 此查询在几秒钟内运行得足够快。但是，如果我随后添加额外的过滤，以从“交易”结果中获取我真正想要的记录，一切都会开始变得非常缓慢。这似乎违反直觉。如果 sql server 只是按顺序运行查询，就不会有问题，而且结果会很快返回。

CREATE PROCEDURE [dbo].[GetLatestOpenBrokerAccountTradesByBrokerAccountAndTime]
@UserId [int],
@BrokerAccountAndTime BrokerAccountAndTimeType READONLY
AS
BEGIN

--initial query
WITH trades as (SELECT bats.*
                FROM BrokerAccountTrades bats
                where bats.[TradeTimeUTC] = (SELECT MAX([TradeTimeUTC]) 
                           FROM BrokerAccountTrades bats2
                           inner join @BrokerAccountAndTime bat
                           on (bats.UserId = @UserId and bats2.BrokerAccountId = bat.BrokerAccountId)
                           WHERE bats2.UserId = bats.UserId
                           AND bats2.BrokerAccountId = bats.BrokerAccountId
                           AND bats2.SecurityId = bats.SecurityId
                           AND bats2.[TradeTimeUTC] < bat.TimeUTC)),

    --filter the results from the 'trades' query    
    trades2 as (select t.*
    FROM trades t
    where t.ExternalId = (select max(ExternalId)
                        from trades t2
                        where t.userid = t2.userid
                        and t.brokeraccountid = t2.brokeraccountid
                        and t.securityid = t2.securityid))

    --filter the results from the 'trades2' query
    select t3.*
    from trades2 t3
    where t3.OpenClose = (select max(CONVERT(int,OpenClose))
                        from trades2 t4
                        where t4.userid = t3.userid
                        and t4.brokeraccountid = t3.brokeraccountid
                        and t4.securityid = t3.securityid)
    and t3.NewPosition <> 0

END

有谁知道这里可能是什么问题？有没有办法强制查询按顺序运行？我可以只将第一个查询返回到我的代码并过滤掉代码中不需要的行，但这似乎是一个非常难看的修复。

仅供那些试图理解查询的人使用。这只是获取指定时间之前的最新记录，其中可以为正在查询的每个帐户提供不同的时间(因此是表值参数)。由于某些记录可能共享相同的时间戳，因此需要进一步过滤，因此有必要应用进一步过滤以确定共享相同时间戳的那些记录中哪些是“最新的”。

Answer 1

好的，所以我已经通过使用一个我觉得非常令人反感的表变量“解决”了这个问题。这意味着我必须在存储过程中重新定义现有表的模式，如果我需要修改它引用的表(例如添加新列等)，这会造成必须更新此存储过程的维护负担。呸……

CREATE PROCEDURE [dbo].[GetLatestOpenBrokerAccountTradesByBrokerAccountAndTime]
@UserId [int],
@BrokerAccountAndTime BrokerAccountAndTimeType READONLY
AS
BEGIN

DECLARE @tempTrades TABLE
(
[Id] [bigint] NOT NULL PRIMARY KEY,
[UserId] [int] NOT NULL,
[BrokerAccountId] [bigint] NOT NULL,
[SecurityId] [tinyint] NOT NULL,
[TradeTimeUTC] [datetime] NOT NULL,
[OpenClose] [bit] NOT NULL,
--many more columns...
)

insert into @tempTrades
SELECT bats.*
                FROM BrokerAccountTrades bats
                where bats.[TradeTimeUTC] = (SELECT MAX([TradeTimeUTC]) 
                           FROM BrokerAccountTrades bats2
                           inner join @BrokerAccountAndTime bat
                           on (bats.UserId = @UserId and bats2.BrokerAccountId = bat.BrokerAccountId)
                           WHERE bats2.UserId = bats.UserId
                           AND bats2.BrokerAccountId = bats.BrokerAccountId
                           AND bats2.SecurityId = bats.SecurityId
                           AND bats2.[TradeTimeUTC] < bat.TimeUTC);

    --filter the results from the 'trades' query    
    WITH trades2 as (select t.*
    FROM @tempTrades t
    where t.ExternalId = (select max(ExternalId)
                        from @tempTrades t2
                        where t.userid = t2.userid
                        and t.brokeraccountid = t2.brokeraccountid
                        and t.securityid = t2.securityid))

    --filter the results from the 'trades2' query
    select t3.*
    from trades2 t3
    where t3.OpenClose = (select max(CONVERT(int,OpenClose))
                        from trades2 t4
                        where t4.userid = t3.userid
                        and t4.brokeraccountid = t3.brokeraccountid
                        and t4.securityid = t3.securityid)
    and t3.NewPosition <> 0

END