1
\$\begingroup\$

So I have a query like this:

SELECT  data.Id,
        mr.mri AS Mri
FROM    ( SELECT    e.Id ,
                    dbo.GetLatestEntryId(e.Id, 2) AS G02 
          FROM      dbo.Estimations AS e
        ) AS data

        LEFT JOIN 
                ( SELECT  Id,
                            d.value('mri[1]', 'decimal(18,6)') AS mri
                    FROM    [dbo].[Entries]
                    CROSS APPLY [SerializedDataSet].nodes('/MechanicalRiskManager') AS DA(d)
                ) AS mr ON data.G02 = mr.Id

Xml columns that I am querying looks something like this:

<MechanicalRiskManager xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <EstimationDataEntryId>63dbfd37-41a6-48a1-b1f2-0480327408e1</EstimationDataEntryId>
  <mudWeight>0</mudWeight>
  <kdf>0</kdf>
  <mri>0</mri>
</MechanicalRiskManager>

Problem is that, it takes 4 seconds to generate 2000 rows using this exact query. Xml string besides mudWeight, kdf and mri has another 15 properties. I have another 2 left joins on xml columns (which are more or less the same complexity) and 5 left joins on other tables. [dbo].[Entries] has about 30000 rows. dbo.GetLatestEntryId returns guid of most recent record based on arguments. In total, this query takes about 15 secs to finish, and as far as I'he read this is a bit to long. If I strip querying to xml column, then it takes no time at all.

I am pretty new to querying xml, and I would appreciate any suggestion how to optimize querying.

\$\endgroup\$

Browse other questions tagged or ask your own question.