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', '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
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.
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.