Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
anujkrathi
Starting Member
9 Posts |
Posted - 2013-01-25 : 22:51:18
|
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 NULLELSE 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-26 : 05:34:41
|
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 |
|
|
anujkrathi
Starting Member
9 Posts |
Posted - 2013-01-26 : 06:28:57
|
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. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-26 : 07:10:56
|
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. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-01-26 : 08:42:41
|
Show us the entire stored procedure. I doubt that is the part it's choking on. |
|
|
anujkrathi
Starting Member
9 Posts |
Posted - 2013-01-26 : 08:42:53
|
still not working. I have tried both options. |
|
|
anujkrathi
Starting Member
9 Posts |
Posted - 2013-01-26 : 09:20:12
|
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 XMLASDECLARE @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 |
|
|
anujkrathi
Starting Member
9 Posts |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 17:00:51
|
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. |
|
|
anujkrathi
Starting Member
9 Posts |
Posted - 2013-01-28 : 08:16:07
|
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. |
|
|
|
|
|
|
|