performance diff between SP and query analyzer

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

performance diff between SP and query analyzer

Author

Topic

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2003-10-23 : 23:45:11

Hi guys,
I am running a web app which the web page pass the parameters to the sp and return the recordset to display.

Sth like:

CREATE proc Sample
@IDNum varchar(7),
@Month char(6)
as
select .....

Every things run fine until recently the database become bigger and bigger. It now contains about 1.5 m records and i found that the performance is greatly degraded.

I tried the codes in the query analyzer and find that the performance is a lot faster than the one running in the sp (the same code)

What's most strange is that if i hardcoded the variables in the sp then the performance will become faster again.

I checked the estimated execution plan and find that it's a huge difference between them.

Do anyone know some tips in tunning this?

Thx in advance.

Yu

p.s. the sql is just a select statement with several outer join and sub-query

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-24 : 00:14:24

Without the code and some idea of the available indexes it will be difficult for us to help. It could be due to an old cached execution plan that is ineffective for the data you want to retrieve. Running a normal query in query analyzer will generate a new plan that is likely to be more effective. Recompiling the stored procedure may help.

yipchunyu
Yak Posting Veteran

80 Posts

Posted - 2003-10-24 : 02:17:25

quote:
Originally posted by robvolk

Without the code and some idea of the available indexes it will be difficult for us to help. It could be due to an old cached execution plan that is ineffective for the data you want to retrieve. Running a normal query in query analyzer will generate a new plan that is likely to be more effective. Recompiling the stored procedure may help.

I don't think it's appropiate to post the orginial code here and so i modified the code as it simplied as :

CREATE PROCEDURE test AS

Declare @ID varchar(7)

SELECT distinct a.prd_mth, a.id_cd, a.id_num, a.name, b.case_num, CASE when c.clt_num = null THEN 'Others (not available for enquiry)' Else b.nam END as clt_nam
FROM dbo.AgPayDtl b LEFT OUTER JOIN
dbo.PolMst c ON b.id_num = c.id_num AND
b.cse_num = c.cse_num LEFT OUTER JOIN
dbo.PayHdr a ON b.id_num = a.id_num AND
b.prd_mth = a.prd_mth
WHERE (a.id_num = @ID) AND (a.prd_mth IN
(SELECT MAX(prd_mth)
FROM dbo.PayHdr
WHERE dbo.PayHdr.id_num = @ID))
order by a.prd_mth, a.id_num

Hope U may help.

p.s. i modified the code and so hope it contains no syntax error. >_<

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources