SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 The query has been canceled because the ......
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anujkrathi
Starting Member

India
9 Posts

Posted - 01/25/2013 :  22:51:18  Show Profile  Reply with Quote
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.

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/26/2013 :  05:34:41  Show Profile  Reply with Quote
Ask your DBA to increase the query governor cost limit. Ask them to change it to something like 5000 or so, based on your error message. Show them this article if they are not familiar with it, or don't know how to change it: http://technet.microsoft.com/en-us/magazine/dd421653.aspx

Edited by - James K on 01/26/2013 05:35:34
Go to Top of Page

anujkrathi
Starting Member

India
9 Posts

Posted - 01/26/2013 :  06:28:57  Show Profile  Reply with Quote
Hi James,
Thanks for your reply.
But mate, i think that increasing the query governor cost is not a good solution. Because this is not a very bog or complex query which can consume so much resource.

If I execute my stored procedure, which contains above code, from query analyzer, it is running fine but when my web application calls that stored procedure, sql server throws an exception.
You can see that this is not a complex query. This is a simple query using Xquery.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/26/2013 :  07:10:56  Show Profile  Reply with Quote
The query governor cost is specified in seconds - I was making that suggestion because I had in my mind that it is to be specified in milli-seconds. So scratch that.

Another possibility is that the poor performance may be because of the use of a bad query plan caused by parameter sniffing or some other problem. For testing purposes, if you are calling a stored procedure, you can force a recompile by adding the WITH RECOMPILE option to the stored procedure definition. Or you can use OPTION (RECOMPILE) to force recompilation at a statement level.

I am assuming that it is the same or similar XML fragment that you are testing with in production and in development environments.
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 01/26/2013 :  08:42:41  Show Profile  Visit russell's Homepage  Reply with Quote
Show us the entire stored procedure. I doubt that is the part it's choking on.
Go to Top of Page

anujkrathi
Starting Member

India
9 Posts

Posted - 01/26/2013 :  08:42:53  Show Profile  Reply with Quote
still not working. I have tried both options.
Go to Top of Page

anujkrathi
Starting Member

India
9 Posts

Posted - 01/26/2013 :  09:20:12  Show Profile  Reply with Quote
Hi Russel,
I am posting entire SP. Please check below code. I am importing the contacts CSV after converting to XML.
I have just changed the exact stored procedure & table names. Rest is same.
I have also tried by commenting the XML block. In that case procedure executes successfully & doesn't throw any error. Means issue is in xquery.

CREATE PROCEDURE [prc_INSERT_XML]
@intCategoryID INT,
@xmlData XML
AS
DECLARE @intContactCount INT;

DECLARE @TempTable TABLE (RowNum INT NOT NULL IDENTITY(1,1), FullName VARCHAR(256), FirstName VARCHAR(256), LastName VARCHAR(256), Email VARCHAR(256), DateOfBirth DATE, CountryCode CHAR(2));
DECLARE @ExistingContactTable TABLE (RowNum INT, FullName VARCHAR(256), FirstName VARCHAR(256), LastName VARCHAR(256), Email VARCHAR(256), DateOfBirth DATE, CountryCode CHAR(2));
DECLARE @InvalidContactTable TABLE (RowNum INT, FullName VARCHAR(256), FirstName VARCHAR(256), LastName VARCHAR(256), Email VARCHAR(256), DateOfBirth DATE, CountryCode CHAR(2));

BEGIN
SET NOCOUNT ON;
BEGIN TRY
/* XML block start */
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);

/* XML block end */

SELECT @intContactCount = COUNT(tt.RowNum) FROM @TempTable tt;

IF @intContactCount > 0
BEGIN

INSERT INTO @InvalidContactTable(RowNum, FullName, FirstName, LastName, Email, DateOfBirth, CountryCode)
SELECT tt.RowNum, tt.FullName, tt.FirstName, tt.LastName, tt.Email, tt.DateOfBirth, tt.CountryCode
FROM @TempTable tt
WHERE /*tt.FirstName IS NULL OR tt.FirstName = '' OR*/ tt.Email IS NULL OR tt.Email = '';

DELETE FROM @TempTable
WHERE /*FirstName IS NULL OR FirstName = '' OR */ Email IS NULL OR Email = '';

IF (SELECT COUNT(*) FROM @TempTable ) > 0
BEGIN

INSERT INTO @ExistingContactTable(RowNum, FullName, FirstName, LastName, Email, DateOfBirth, CountryCode)
SELECT tt.RowNum, tt.FullName, tt.FirstName, tt.LastName, tt.Email, tt.DateOfBirth, tt.CountryCode
FROM @TempTable tt
JOIN Catalogue.vwMailingListContacts vmlc ON vmlc.Email = tt.Email AND vmlc.CategoryID = @intCategoryID

DELETE FROM @TempTable
WHERE RowNum IN (SELECT ect.RowNum FROM @ExistingContactTable ect);
END

IF (SELECT COUNT(*) FROM @TempTable ) > 0
BEGIN
INSERT INTO Contacts(CategoryID, FullName, FirstName, LastName, Email, DOB, CCode)
SELECT @intCategoryID, tt.FullName, tt.FirstName, tt.LastName, tt.Email, tt.DateOfBirth, tt.CountryCode
FROM @TempTable tt;
END
END

SELECT tt.FullName, tt.FirstName, tt.LastName, tt.Email, tt.DateOfBirth,tt.CountryCode AS Country, 0 AS Invalid
FROM @TempTable tt;

SELECT idt.FullName, idt.FirstName, idt.LastName, idt.Email, idt.DateOfBirth,idt.CountryCode AS Country,
CASE
WHEN idt.Email IS NULL OR idt.Email = '' THEN 1
ELSE 0
END AS Invalid
FROM @InvalidContactTable idt;

SELECT ect.FullName, ect.FirstName, ect.LastName, ect.Email, ect.DateOfBirth, ect.CountryCode AS Country,
CASE
WHEN ect.Email IS NULL OR ect.Email = '' THEN 1
ELSE 0
END AS Invalid
FROM @ExistingContactTable ect;

RETURN 1;

END TRY
BEGIN CATCH

INSERT INTO ApplicationError(AerENo, AerESeverity, AerEState, AerEProcedure, AerELine, AerEMessage)
VALUES(ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE());

RETURN -1;
END CATCH

END
Go to Top of Page

anujkrathi
Starting Member

India
9 Posts

Posted - 01/26/2013 :  11:04:44  Show Profile  Reply with Quote
Hey james, Russell,
I have found a solution. I also want to share with you guys.

http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/31f80c1f-6285-4966-b7ed-9d0f93816f52

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/27/2013 :  17:00:51  Show Profile  Reply with Quote
Anuj, thank you for the link - glad you got it fixed.

I am still curious as to why the query plan is substantially different when invoked from ASP.Net. Rewriting the query obviously changed the query plan for the better, but why a query that ran in milliseconds from SSMS came back with a query plan that is so expensive when invoked from ASP.Net puzzles me.
Go to Top of Page

anujkrathi
Starting Member

India
9 Posts

Posted - 01/28/2013 :  08:16:07  Show Profile  Reply with Quote
Hi James,
I am also surprised why sql server generating different query plan.
I have also tried with recompile.

I think this is due to the xquery which I used for every column.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000