XML查询问题 - 在一台服务器上性能不错，另一台服务器性能很差

Question

我有7秒钟内运行的查询，在本地SQL Server 2008 R2实例上（非快速）生成12,094条记录。但是，Sql Server Express实例需要38秒。这可能只是本地8GB Hyper-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中，因为这会显着加快速度。