November 12, 2002 at 9:53 pm
I'm facing a very strange problem with one of our stored procedures. Here is what is happening.
The stored procedure is executing withing 3 seconds from the application which is hosted on a different server and it is taking almost 60 seconds to execute the same procedure from Query Analyzer. The stored proc. was running fast from the query analyzer before and slow from the application and after clearing the procedure cache and rebuilding, it started behaving the other way round. Any tips would be appreciated.
I ran the profiler and what i saw was the procedure which had source from query analyzer had more reads, almost 6 million as compared to the one which ran from the application. The one from application had just 80,000 reads. The paramters accepted are the same in both cases.
What i suspect is the MDAC version. The MDAC version from all the boxes where i tested was 2.6 and above while from the server where the application is hosted was 2.5.SP2. The stored procedure is using a view and the record count for the view is 11 million records. It seems to me that the execution plan is different in both the cases. Is it possible that views are queried differently with MDAC 2.6 and 2.5 ?
Any other ideas to trouble-shoot would be appreciated.
November 13, 2002 at 4:05 am
MDAC should not have this kind of impact, the server should be keeping an execution plan and when either interface runs the same query you should get the same results. However, I hva eseen a few instances where a GUI such as QA will cause a slow performance while it is outputing all the data to the screen in the lower half, could be somewhat the situation here. The reads thou are what concern me, why are they higher for one than the other if the SP and it's submitted values are the same. I would sugget looking at the execution plan with profiler for each and see if anything unusually is occurring.
November 13, 2002 at 12:09 pm
Antares,
We have monitors set up from another server and i monitored the stored procedure, and it showed me high execution time.
I agree that MDAC might not be related but i came down to MDAC version 2.6 no sp's on one of my test server, restored the database and the procedure was running fine on this server and then restored the database on another server with MDAC version 2.6 SP2 and the procedure was taking long(60 Seconds). but what concerns me was that a week back it was executing the other way which makes me rethink about MDAC version. Still unsure. Regarding your question, only 18 rows are returned by the stored procedure and i think this should not be a problem with the output.
I will run the profiler and check for the execution plan and i would appreciate if you also have any other tips.
Thanks for your time amd help
November 13, 2002 at 1:57 pm
Can you post the query. I find that if the query is a poor performer then it can be pot luck on whether it is quick or slow.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 13, 2002 at 8:07 pm
Simon,
I agree with you. That's the first thing which came to my mind when i saw the query and the query is not one of the best queries and it has lot of room for improvement. But what i'm seeing is different execution plan for the same stored procedure. I backed up the same database to two different servers and the execution plan is totally different on both of the servers. I can e-mail you the stored procedure if you want.
November 14, 2002 at 2:49 am
If its not too large can you post it
Do you have diferent collations, sort orders on the servers this might affect things. Do the servers have the same memory, processor?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 14, 2002 at 9:27 pm
Here is the code for the procedure:
The view view1 which is used joins 8 tables.
Again, i didn't write this procedure and i definetely agree that it can be improved.
CREATE PROCEDURE dbo.Procedure1
@USC varchar(50),
@std varchar(255),
@SPI varchar(255),
@MI int,
@Normal int,
@TIN varchar(255),
@DTI varchar(255),
@LangPref varchar(5),
@ML varchar(50),
@year int,
@cy varchar(255),
@FL varchar(255),
@Country char(2)
AS
Set NoCount ON
DECLARE @VAR1 varchar(50), @VAR12 varchar(50), @XModel varchar(50)
IF @Country != 'US'
Set @USC = 'default'
ELSE IF (SELECT COUNT(*) FROM QTY_Location WHERE Country = @Country AND State = @USC) < 1
Set @USC = 'default'
--
Set @VAR1 = 'xxxx'
Set @VAR12 = 'xxxx'
IF @FL = 'Other'
BEGIN
Set @FL = '%'
Set @VAR1 = 'ABC'
Set @VAR12 = 'EFG'
END
-- Get the translated model name if needed.
SELECT @XModel = m.ModelDesc
FROM TABLE1 x
INNER JOIN QTY_Model m
ON m.ModelID = x.ModelID
WHERE TranslateModelDesc = @ML
IF @XModel IS NOT NULL
BEGIN
SET @ML = @XModel
END
--
IF @Normal = 1
BEGIN
SELECT DISTINCT
[MOK] =
Case MOK
When ((@MI + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal
Then ((@MI + NormalInterval / 2) / NormalInterval * NormalInterval)
When ((@MI + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal + MAX_Normal
Then ((@MI + NormalInterval / 2) / NormalInterval * NormalInterval)
When ((@MI + NormalInterval + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal
Then ((@MI + NormalInterval + NormalInterval / 2) / NormalInterval * NormalInterval)
When ((@MI + NormalInterval + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal + MAX_Normal
Then ((@MI + NormalInterval + NormalInterval / 2) / NormalInterval * NormalInterval)
Else (((@MI - MOK) / MAX_Normal) * MAX_Normal) + MOK
End,
--(Q_V_P.MOK + (MAX_M * (@MI/(MAX_M +(SevereInterval / 2))))) as [MOK],
MaintenanceDesc,
Report =
Case MOK
When ((@MI + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal Then 1
When ((@MI + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal + MAX_Normal Then 1
When ((@MI + NormalInterval + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal Then 2
When ((@MI + NormalInterval + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal + MAX_Normal Then 2
Else 3
End
FROM VIEW1
WHERE MY = @year
AND State = @USC
AND Country = @Country
AND CD = @cy
AND ModelDesc = @ML
AND bNormalCondition = 1
AND SPD = @SPI
AND DTD = @DTI
AND TD = @TIN
AND LanguageID = @LangPref
AND (FD like @FL or FD2 like @FL)
AND FD Not In (@VAR1, @VAR12)
AND STD = @std
AND (MOK = ((@MI + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal----------------------------------------- OR (MOK = ((@MI + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal + MAX_Normal AND 0 != ((@MI + NormalInterval / 2) / NormalInterval * NormalInterval))--------------Current Mile
OR MOK = ((@MI + NormalInterval + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal----------------------------------------- OR (MOK = ((@MI + NormalInterval + NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal + MAX_Normal AND 0 != ((@MI + NormalInterval + NormalInterval / 2) / NormalInterval * NormalInterval))--------------Next Mile
OR MOK in
(
SELECT
CASE max(MOK)
WHEN NULL THEN MAX_Normal
ELSE max(MOK)
END
FROM VIEW1
WHERE MY = @year
AND State = @USC
AND Country = @Country
AND CD = @cy
AND ModelDesc = @ML
AND bNormalCondition = 1
AND SPD = @SPI
AND DTD = @DTI
AND TD = @TIN
AND LanguageID = @LangPref
AND (FD like @FL or FD2 like @FL)
AND FD Not In (@VAR1, @VAR12)
AND STD = @std
AND (
MOK <= ((@MI - NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal
OR MOK = ((@MI - NormalInterval / 2) / NormalInterval * NormalInterval) % MAX_Normal + MAX_Normal
)
AND MOK <= ((@MI - NormalInterval / 2) / NormalInterval * NormalInterval)
AND MOK % NormalInterval = 0
AND MOK % SevereInterval = 0
GROUP BY MAX_Normal
))
/*ORDER BY Report*/
END
ELSE
BEGIN
SELECT DISTINCT
[MOK] =
Case MOK
When ((@MI + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M
Then ((@MI + SevereInterval / 2) / SevereInterval * SevereInterval)
When ((@MI + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M + MAX_M
Then ((@MI + SevereInterval / 2) / SevereInterval * SevereInterval)
When ((@MI + SevereInterval + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M
Then ((@MI + SevereInterval + SevereInterval / 2) / SevereInterval * SevereInterval)
When ((@MI + SevereInterval + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M + MAX_M
Then ((@MI + SevereInterval + SevereInterval / 2) / SevereInterval * SevereInterval)
Else (((@MI - MOK) / MAX_M) * MAX_M) + MOK
End,
--(Q_V_P.MOK + (MAX_M * (@MI/(MAX_M +(SevereInterval / 2))))) as [MOK],
MaintenanceDesc,
Report =
Case MOK
When ((@MI + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M Then 1
When ((@MI + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M + MAX_M Then 1
When ((@MI + SevereInterval + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M Then 2
When ((@MI + SevereInterval + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M + MAX_M Then 2
Else 3
End
FROM VIEW1
WHERE MY = @year
AND State = @USC
AND Country = @Country
AND CD = @cy
AND ModelDesc = @ML
AND bNormalCondition = 0
AND SPD = @SPI
AND DTD = @DTI
AND TD = @TIN
AND LanguageID = @LangPref
AND (FD like @FL OR FD2 like @FL)
AND FD Not In (@VAR1, @VAR12)
AND FD2 Not In (@VAR1, @VAR12)
AND STD = @std
AND (MOK = ((@MI + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M----------------------------------------- OR (MOK = ((@MI + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M + MAX_M AND 0 != ((@MI + SevereInterval / 2) / SevereInterval * SevereInterval))--------------Current Mile
OR MOK = ((@MI + SevereInterval + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M----------------------------------------- OR (MOK = ((@MI + SevereInterval + SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M + MAX_M AND 0 != ((@MI + SevereInterval + SevereInterval / 2) / SevereInterval * SevereInterval))--------------Next Mile
OR MOK in
(
SELECT max(MOK)
FROM VIEW1
WHERE MY = @year
AND State = @USC
AND Country = @Country
AND CD = @cy
AND ModelDesc = @ML
AND bNormalCondition = 0
AND SPD = @SPI
AND DTD = @DTI
AND TD = @TIN
AND LanguageID = @LangPref
AND (FD like @FL OR FD2 like @FL)
AND FD Not In (@VAR1, @VAR12)
AND FD2 Not In (@VAR1, @VAR12)
AND STD = @std
AND (
MOK <= ((@MI - SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M
OR MOK = ((@MI - SevereInterval / 2) / SevereInterval * SevereInterval) % MAX_M + MAX_M
)
AND MOK <= ((@MI - SevereInterval / 2) / SevereInterval * SevereInterval)
AND MOK % NormalInterval = 0
AND MOK % SevereInterval = 0
))
/*ORDER BY Report*/
END
GO
November 15, 2002 at 5:46 pm
Nice! Will look at it over the weekend, if beer doesn't get in the way
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 15, 2002 at 8:15 pm
Simon,
Let me know if you would like to view the two different execution plans. It would be difficult to post as they are big but let me know whatever is convenient to you.
Thanks,
November 16, 2002 at 5:22 am
If you could send me a schema creation script, it would make it easier.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 17, 2002 at 10:50 pm
Can you let me know what email id i can send it to?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply