Databases
StackOverflow2013
Postsdbo
POIssues with XML query - performance ok on one server, super slow on another
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIssues with XML query - performance ok on one server, super slow on another
    primarykey
    data
    text
    <p>I have this query that runs in 7 seconds producing 12,094 records on my local instance of SQL Server 2008 R2 (non express). However, it takes 38 seconds on an instance of Sql Server Express. It might just be the difference between a local 8gb Hyper-V machine and a remote GoGrid machine with half the memory.</p> <p>My question is am I doing anything obviously inefficient here?</p> <p>Query:</p> <pre><code>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) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USAaron Anodide
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIssues with XML query - performance ok on one server, super slow on another
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODid you compare the execution plans?
      singulars
      1. POIssues with XML query - performance ok on one server, super slow on another
      1. USAaron Bertrand
    2. COthanks for the suggestion. it looks like it's all in the xml.. nothing but 0% anywhere else. i also noticed the sql server express machine doesn't go over 25% cpu...
      singulars
      1. POIssues with XML query - performance ok on one server, super slow on another
      1. USAaron Anodide
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload