XML查询的问题在一台服务器上性能还可以，在另一台服务器上性能却非常慢。

Question

我有一个在7秒内运行的查询，它在我的SQL Server2008 R2 (非express)的本地实例上产生了12,094条记录。但是，在Sql Server Express实例上需要38秒。这可能只是本地8 GoGrid V机器和具有一半内存的远程GoGrid机器之间的区别。

我的问题是，我在做什么明显效率低下的事情吗？

查询：

declare @FromTimestamp datetime = '2012-02-01'
declare @ToTimestamp datetime = '2012-02-22'
DECLARE @to datetime = DATEADD(dd, 1, @ToTimestamp)
SELECT
    Data.value('(/LendingTreeAffiliateRequest/Request/@AppID)[1]', 'varchar(50)') AS AppID,
    Data.value('(/LendingTreeAffiliateRequest/Request/@type)[1]', 'varchar(50)') AS [Type], 
    [Timestamp], 
    Data.value('(/LendingTreeAffiliateRequest/@affid)[1]', 'varchar(50)') AS CDNumber, 
    Data.value('(/LendingTreeAffiliateRequest/Request/SourceOfRequest/VisitorIPAddress)[1]', 'varchar(50)') AS IP, 
    Data.value('(/LendingTreeAffiliateRequest/Request/SourceOfRequest/LendingTreeAffiliateEsourceID)[1]', 'varchar(50)') AS ESourceID,
    Data.value('(/LendingTreeAffiliateRequest/Request/Applicant/State)[1]', 'char(2)') AS [State],
    Data.value('(/LendingTreeAffiliateRequest/Request/Applicant/DateOfBirth)[1]', 'varchar(20)') AS DateOfBirth,
    Data.value('(/LendingTreeAffiliateRequest/Request/Applicant/EmailAddress)[1]', 'varchar(255)') AS Email,
    Data.value('(/LendingTreeAffiliateRequest/Request/Applicant/IsVeteran)[1]', 'char(1)') AS IsVeteran,
    Data.value('(/LendingTreeAffiliateRequest/Request/Applicant/CreditHistory/CreditSelfRating)[1]', 'varchar(50)') AS Credit
FROM 
    WebLogEntry wle
    INNER JOIN EventType et on wle.EventTypeId=et.Id
    INNER JOIN [EventData] ed on wle.EventDataId=ed.Id
WHERE
    (et.Id=2)
        AND (Data.exist('/LendingTreeAffiliateRequest/Request/@AppID')=1)
        AND ([Timestamp] BETWEEN @FromTimestamp AND @to)

Answer 1

除了SQL服务器将不得不处理大量xquery之外，我看不到任何明显的错误。您可能希望尝试完整地返回数据字段，然后在代码中解析它，看看您的特定实现是否更快。测量两种方法的时间，并选择速度更快的方法。

如果您有一个可预测的数据结构，最好将其存储在表中，而不是存储在XML blob中，因为这将显着提高速度。