May 10, 2010 at 11:28 am
I could really use some help with trying to trace this execution problem with a script.
I created a short script that prematurely terminates when called from within the application I am running. I am using System.Data.SqlClient for the call and I have set the connection string with a timeout of 10000 (or 166 minutes). Here's the script.
ALTER PROCEDURE dbo.EPP_UpdateHeaderID
AS
BEGIN
SET NOCOUNT ON
-- UPDATE EPP_Scan
BEGIN
UPDATE EPP_Scan
SET EPP_Scan.HeaderID = EPP_ReportStatus.ReportID
FROM EPP_Scan, EPP_ReportStatus
WHERE EPP_Scan.ReportName=EPP_ReportStatus.ReportName
END
-- UPDATE EPP_CheckTrailer
BEGIN
UPDATE EPP_CheckTrailer
SET EPP_CheckTrailer.HeaderID = EPP_ReportStatus.ReportID
FROM EPP_CheckTrailer, EPP_ReportStatus
WHERE EPP_CheckTrailer.ReportName=EPP_ReportStatus.ReportName
END
-- UPDATE EPP_CheckHeader
BEGIN
UPDATE EPP_CheckHeader
SET EPP_CheckHeader.HeaderID = EPP_ReportStatus.ReportID
FROM EPP_CheckHeader, EPP_ReportStatus
WHERE EPP_CheckHeader.ReportName=EPP_ReportStatus.ReportName
END
-- UPDATE EPP_CheckDetail
BEGIN
UPDATE EPP_CheckDetail
SET HeaderID = EPP_ReportStatus.ReportID
FROM EPP_CheckDetail INNER JOIN
EPP_ReportStatus ON EPP_CheckDetail.ReportName = EPP_ReportStatus.ReportName
WHERE (EPP_CheckDetail.HeaderID IS NULL)
END
-- UPDATE EPP_ReportStatus to True
BEGIN
UPDATE EPP_ReportStatus
SET HeaderFilled = 1, DetailFilled = 1, TrailerFilled = 1, ScanFilled = 1, Status = 'FTPCREATE'
WHERE (HeaderFilled = 0) AND (DetailFilled = 0) AND (TrailerFilled = 0) AND (ScanFilled = 0)
END
END
When the script is called it executes all the way down to --Update EPP_CheckDetail and then times out-- in a lot shorter period that 166 minutes. If I go through the Sql Editor is Visual Studio 10 and select the Update EPP_CheckDetail Portion and "Design SQL", then click Execute SQL, it executes without fault.
I am very confused.
If anyone has got any ideas, please let me know.
Thanks.
May 10, 2010 at 12:53 pm
It's hard to know for sure, but I'd check the default ANSI settings on your connections. If they're different that could affect how that procedure is processed.
My next guess is the possibility of contention with other processes.
Finally, it could be that since you're manipulating all this data all at once, the log file is growing and that's taking longer and longer to execute. Usually this is because you have the default setting of allowing the log to grow by percentages.
Other than that, I'd more details to be sure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2010 at 10:43 am
Hey Grant,
Thanks for the comeback!
I took some your advice...
Finally, it could be that since you're manipulating all this data all at once, the log file is growing and that's taking longer and longer to execute. Usually this is because you have the default setting of allowing the log to grow by percentages.
... and broke up the stored procedure into two. The EPP_Scan table and the EPP_CheckDetail contain almost a million records. Unfortunately, it didn't do a darn thing. It stops right at that Update EPP_CheckDetail procedure.
Let me ask another question...
Does it matter if none of the fields mentioned in the Update EPP_CheckDetail routine reference the primary key? Keep in mind, EPP_CheckHeader and EPP_CheckTrailer don't even have Primary keys. EPP_Scan also doesn't refer to its primary key and yet it works.
You also mentioned your ANSI settings. I not even sure how to find them. This is the only connection settings I am aware of
ADFConn1 = New System.Data.SqlClient.SqlConnection("Data Source=STPISSQ01ADF;Initial Catalog=ADF;User Id=XXXXXXXX;Password=xxxxxxxxxxx;Persist Security Info=True;connect timeout=10000;packet size=4096")
Is there a quick way to view the ANSI settings?
Thanks for your help!:-)
May 11, 2010 at 11:13 am
frankG-689606 (5/11/2010)
Hey Grant,Thanks for the comeback!
I took some your advice...
Finally, it could be that since you're manipulating all this data all at once, the log file is growing and that's taking longer and longer to execute. Usually this is because you have the default setting of allowing the log to grow by percentages.
... and broke up the stored procedure into two. The EPP_Scan table and the EPP_CheckDetail contain almost a million records. Unfortunately, it didn't do a darn thing. It stops right at that Update EPP_CheckDetail procedure.
Sorry, it was a guess. And as most guesses go, it was wrong.
Let me ask another question...
Does it matter if none of the fields mentioned in the Update EPP_CheckDetail routine reference the primary key? Keep in mind, EPP_CheckHeader and EPP_CheckTrailer don't even have Primary keys. EPP_Scan also doesn't refer to its primary key and yet it works.
That gets into the issue of whether or not this query is have performance problems because of missing indexes, table scans, etc. That's certainly possible. Without the execution plans for the queries though, it's hard to know.
You also mentioned your ANSI settings. I not even sure how to find them. This is the only connection settings I am aware of
ADFConn1 = New System.Data.SqlClient.SqlConnection("Data Source=STPISSQ01ADF;Initial Catalog=ADF;User Id=XXXXXXXX;Password=xxxxxxxxxxx;Persist Security Info=True;connect timeout=10000;packet size=4096")Is there a quick way to view the ANSI settings?
Thanks for your help!:-)
So that connection isn't changing any of the ANSI settings, so it will use the defaults that have been set on the server. If you look at the server Properties and the Connections page you can see what's there. But I don't think that's the issue here either. Again, another guess gone wrong.
I'm back to where I was, it's hard to know without more information. What do the execution plans look like, are there other processes running on the server, can you look at sys.dm_exec_requests to see what the process is waiting on, have you looked to see if it's blocked by another process... lots & lots of questions. More information about what's happening on the system will make it easier to diagnose.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2010 at 11:57 am
Grant,
You are not going to guess what the solution was...
Do I feel dumb!!:crazy:
Someone brought it to my attention. They asked me, "You do know that the connection timeout is not the same thing as the command timeout right?"
All that 10000 does is tell it how long to take to make the connection. I still had to give the time to the command.
After I changed it, it ran through all those records without even stopping.
Man, I guess we do learn something new everyday.
Thanks again for your help.
Frank
May 11, 2010 at 12:08 pm
frankG-689606 (5/11/2010)
Grant,You are not going to guess what the solution was...
Do I feel dumb!!:crazy:
Someone brought it to my attention. They asked me, "You do know that the connection timeout is not the same thing as the command timeout right?"
All that 10000 does is tell it how long to take to make the connection. I still had to give the time to the command.
After I changed it, it ran through all those records without even stopping.
Man, I guess we do learn something new everyday.
Thanks again for your help.
Frank
I didn't even notice that in the connection string. Well, all's well that ends well. I'm glad you found the problem & fixed it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply