I have a UI that allows a user to select certain data points they want to filter the results by. There are things such as location, department etc. Once they have selected all of the options, I generate an XML string that I pass to my stored procedure.
From there, for locations for example I create a temp table and dump all of the locations that were selected by the user into that table which I then join my data on later.
When running the execution plan, I noticed that each one of the 5 or so queries that are parsing the filter data are costing about 12% which is over 60% of the query just to determine the data we are going to be filtering by.
DECLARE @tmLocations TABLE (
location VARCHAR (100));
INSERT INTO @tmLocations
SELECT ParamValues.x1.value('location', 'VARCHAR(200)')
FROM @xml.nodes('data/teammateLocations/locations') AS ParamValues(x1);
Is there another method of extracting data from XML or ways to improve the query like above so that it doesn't cost as much to run? Setting up the data is costing more than the actual filtering of the data which is really slowing down performance.