The query has been canceled because the estimated cost of this query exceeds the ....

Question

Hi Experts,
I am getting below error while executing the stored procedure.

The query has been canceled because the estimated cost of this query (3258) exceeds the configured threshold of 2500. Contact the system administrator.

I am importing the XML into the table. Actually, I am uploading CSV contacts in my application & then push the XML to sql server. Here, I extract the contacts using xpath query, & save into a table. In my local machine, this code works fine while I upload this code on production server, it raises the above error. below is the sample code which I am using:

INSERT INTO @TempTable(FullName, FirstName, LastName, Email, DateOfBirth, CountryCode)
SELECT FullName = T.Item.query('fullname').value('.', 'VARCHAR(256)'),
FirstName = T.Item.query('firstname').value('.', 'VARCHAR(256)'),
LastName = T.Item.query('lastname').value('.', 'VARCHAR(256)'),
Email = T.Item.query('email').value('.', 'VARCHAR(256)'),
DOB = CASE WHEN T.Item.query('dateofbirth').value('.', 'VARCHAR(10)') = '' THEN NULL
ELSE CONVERT(DATE, T.Item.query('dateofbirth').value('.', 'VARCHAR(10)'), 103)
END,
CC = T.Item.query('country').value('.', 'CHAR(2)')
FROM @xmlData.nodes('contacts/contact') AS T(Item);

above code works fine & takes only few milliseconds on my local system but throws an error on production server.

I have tried to search the reason & found that this error is due to long execution time. so, either I will have to increase the query governor execution time or I will have to optimize the query.

But I am not able to understand what can I do to optimize this query. This is basic XQuery syntax. Please guide me how can I fix this issue ?

Answer 1

But I am not able to understand what can I do to optimize this query. This is basic XQuery syntax. Please guide me how can I fix this issue ?

Try removing the query method for each column and instead invoke the value method with a singleton path:

SELECT 
	FullName = T.Item.value('fullname[1]', 'VARCHAR(256)')
	, FirstName = T.Item.value('firstname[1]', 'VARCHAR(256)')
	, LastName = T.Item.value('lastname[1]', 'VARCHAR(256)')
	, Email = T.Item.value('email[1]', 'VARCHAR(256)')
	, DOB = 
		CASE WHEN T.Item.value('dateofbirth[1]', 'VARCHAR(10)') = '' THEN NULL
		ELSE CONVERT(DATE, T.Item.value('dateofbirth[1]', 'VARCHAR(10)'), 103)
		END
	, CC = T.Item.value('country[1]', 'CHAR(2)')
FROM @xmlData.nodes('contacts/contact') AS T(Item);

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 2

Hey dan,

thank you very much.

It works.

thanks a ton mate !!!!

Answer 3

Hi Anuj,

           After tuning the query like Dan Guzman suggested,you can also try creating primary & secondary XML indexes on XML column based on your requirement.

"XML instances are stored in xml type columns as large binary objects (BLOBs). These XML instances can be large, and the stored binary representation of xml data type instances can be up to 2 GB. Without an index, these binary large objects are shredded at run time to evaluate a query. This shredding can be time-consuming."

     Primary XML index helps in shredding . Secondary XML index helps in querying XML instance

     For better understanding - http://msdn.microsoft.com/en-in/library/ms191497.aspx

---

Thanks & Regards, sathya

Answer 4

Hi Sathya,

I was really searching for a solution like this. This link is very useful & now this is the time when I will have to sacrifice  my week end to study XML again.

Again thank you mate.

Answer 5

Hi Anuj,

      Try my blog for basic understanding about handling XML in SQL Server - http://sathyadb.blogspot.in/2012/09/all-about-xml-in-sql-server.html

      For more & complex examples ,try this link -  http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

---

Thanks & Regards, sathya

Answer 6

Hi Anuj,

           After tuning the query like Dan Guzman suggested,you can also try creating primary & secondary XML indexes on XML column based on your requirement.

"XML instances are stored in xml type columns as large binary objects (BLOBs). These XML instances can be large, and the stored binary representation of xml data type instances can be up to 2 GB. Without an index, these binary large objects are shredded at run time to evaluate a query. This shredding can be time-consuming."

     Primary XML index helps in shredding . Secondary XML index helps in querying XML instance

     For better understanding - http://msdn.microsoft.com/en-in/library/ms191497.aspx

---

Thanks & Regards, sathya

Ok... let's ask a question here.  How many people think that storing a CSV in a column is a good idea? ;-)  Even if you could index it, would it be a good idea? 

I certainly hope no one thinks so.  So why do people allow what turns out to be some highly denormalized data with HUGE and (many times) unnecessary delimiters that will screw up their online indexing because of the blob content to be stored in their databases???

Forget about the XML indexes.  Normalize the data and store it in the database correctly! 

---

--Jeff Moden

Answer 7

Ok... let's ask a question here.  How many people think that storing a CSV in a column is a good idea? ;-)  Even if you could index it, would it be a good idea? 

I certainly hope no one thinks so.  So why do people allow what turns out to be some highly denormalized data with HUGE and (many times) unnecessary delimiters that will screw up their online indexing because of the blob content to be stored in their databases???

Forget about the XML indexes.  Normalize the data and store it in the database correctly! 

Well, but that is what OP is doing, shredding the XML, and the question is whether building an index on it first will help.  I never found it so.

Also, there may be some confusion here between XPath and XQuery, if indeed there still is any difference between them.

In regards to the proper care and handling of XML in and around relational databases, it's problematic.  CJ Date once raved against it then had a change of heart.  Microsoft has seen fit to build XPath/XQuery capabilities into SQL Server.  In my own experience, when XML is your ETL protocol then there are often reasons to keep the XML data around in its original format, and if you do that then also having it in relational format may in some cases seem redundant, and for rare and limited cases it may make sense to index and XQuery it.  I agree it's crufty, but sometimes so is the world.

Josh

Answer 8

Ok... let's ask a question here.  How many people think that storing a CSV in a column is a good idea? ;-)  Even if you could index it, would it be a good idea? 

I certainly hope no one thinks so.  So why do people allow what turns out to be some highly denormalized data with HUGE and (many times) unnecessary delimiters that will screw up their online indexing because of the blob content to be stored in their databases???

XML and other large value types may pose problems for database maintenance but I wouldn't go so far as to throw the baby out with the bathwater.  Storing a repeating group as a delimited list isn't really denormalized in the sense that it can still be queried like a normalized one-to-many relationship if one treats the values as separate logical rows.  XML and SQLCLR also provide the capability for strict data type/schema conformance, although it's not technically required.

I'm not suggesting this sort of approach as a best practice and it certainly for newbies.  I would first go down the traditional normalization path and introduce non-standard approaches line XML, SQLCLR types and delimited lists only when it makes good business sense and the downsides are fully considered.

But in Anuj's situation, it seems XML is only used to pass multiple rows to a stored procedure. Although a TVP might be a better choice in SQL 2008 and later, I've found XML is a great way to pass multiple rows to a proc in SQL 2005 or when the client API doesn't support TVPs (like the current versions of JDBC and ODBC for Linux).  Believe me, a single proc call beats the heck out of 1 millions calls from a performance perspective :-)

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 9

Hi Dan, Jeff, Josh,
Sorry, for the late response. I was away & was not able to use my system.

Dan is right. I am not saving XML in database. I used XML only to pass multiple rows to stored procedure.

Dan, as you said that TVP may be a better choice. But I was afraid because I thought that TVP may cause more load as compare to XML while sending data from application(.NET) to database server.
So, I used XML.

But now i will check this option also.

Answer 10

Dan, as you said that TVP may be a better choice. But I was afraid because I thought that TVP may cause more load as compare to XML while sending data from application(.NET) to database server.
So, I used XML.

But now i will check this option also.

A TVP is definitely something to look into since it will require less processing on the database server than XML.  You might also look into using SqlBulkCopy.  In the case of a TVP, I suggest passing the TVP as an IEnumerable<SqlDataRecord> and specifying meta data that matches the target data types and column lengths.  In my experience, that method performs better than passing a DataTable. 

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com