Sql Azure: Very slow performance on WITH statement query

Question

I'm trying to get my head around the usage of the WITH statement in sql azure. I have an initial query, which then needs to be filtered 2 times. If I just run the initial query, it runs fast. But as soon as I add the additional filters, the query runs so slow it never finishes and azure force closes the connection.

So, the first WITH statement, which on it's own runs very quick ->

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

This doesn't cause a problem - this query runs fast enough in a few seconds. However, if I then add the additional filtering, to get the records I actually want out of the 'trades' result, everything just starts going really slow. This seems counter-intuitive. If sql server simply ran the queries sequentially, there wouldn't be a problem and the result would come back really fast.

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

Does anyone know what the problem might be here? And is there a way to force the queries to be run sequentially? I could just return the first query to my code and filter out the unwanted rows in code, but that seems a really ugly fix.

And just for those trying to understand the query. This is just getting the latest records before a specified time, where a different time may be provided for each account that is being queried (thus the table valued parameter). The further filtering is required as certain records may share the same timestamp, so it is necessary to apply the further filtering to determine which is the 'latest' of those records sharing the same timestamp.

Answer 1

Ok, so i have 'solved' this by using a table variable which I find really distasteful. It means I have to re-define the schema of an existing table, in a stored procedure, which creates a maintenance burden of having to update this stored proc should I ever need to modify the table it is referencing such as adding new columns etc. YUK...

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