SQL Azure:WITH语句查询的性能非常慢 [英] Sql Azure: Very slow performance on WITH statement query

问题描述

我正试图弄清sql azure中WITH语句的用法. 我有一个初始查询，然后需要过滤2次. 如果我只运行初始查询，则运行速度很快.但是，一旦我添加了其他过滤器，查询就会运行得如此之慢，以至于它永远不会完成，并且天蓝色会强制关闭连接.

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.

因此，第一个WITH语句(它自己运行)非常快->

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

这不会引起问题-此查询在几秒钟内运行得足够快. 但是，如果我随后添加了额外的筛选，以使我真正想要的记录从交易"结果中剔除，则一切开始都会非常缓慢. 这似乎违反直觉.如果sql server只是简单地按顺序运行查询，就不会有问题，并且结果会很快返回.

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.

推荐答案

好，所以我使用表变量解决了"这个问题，我发现这确实令人讨厌. 这意味着我必须在存储过程中重新定义现有表的架构，这会增加维护负担，如果我需要修改其引用的表(例如添加新列等)，则必须更新此存储过程. K ...

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

这篇关于SQL Azure:WITH语句查询的性能非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！