SQL Server Performance Forum – Threads Archive

huge performance difference between sp and query

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 PROCEDURE test AS [email protected] varchar(7) SELECT distinct dbo.AgPayHdr.prd_mth, dbo.AgPayHdr.agy_nam, dbo.AgPayHdr.unt_nam, dbo.AgPayHdr.agy_cd, dbo.AgPayHdr.unt_cd, dbo.AgPayHdr.agt_num, dbo.AgPayHdr.agt_nam, dbo.AgPayHdr.con_eff_dt, dbo.AgPayHdr.agt_rnk_cd, dbo.AgPayDtl.pol_num, CASE when ((dbo.PolMst.ins_clt_num = ”) or (dbo.PolMst.ins_clt_num = null)) THEN ‘Others (not available for enquiry on AgentNet)’ Else dbo.AgPayDtl.ins_nam END as ins_nam,dbo.AgPayDtl.rfyc, dbo.AgPayDtl.bonus, dbo.AgPayDtl.renewal, dbo.AgPayDtl.shortage, dbo.AgPayDtl.crd_dis_fee, dbo.AgPayDtl.mth_cov, dbo.AgPayDtl.unt_on_rec, dbo.AgPayDtl.old_blk, dbo.AgPayDtl.coo_off_whd, dbo.AgPayDtl.rmk, dbo.AgPayHdr.coo_off_rfyc, dbo.PolMst.pol_num AS record, isnull(dbo.PolMst.ins_clt_num, ‘999999’) as ins_clt_num,CASE WHEN ((dbo.PolMst.ins_clt_num = ”) or (dbo.PolMst.ins_clt_num = null)) THEN ‘Y’ Else ‘N’ END as capsil
FROM dbo.AgPayDtl LEFT OUTER JOIN dbo.PolMst ON dbo.AgPayDtl.agt_num = dbo.PolMst.cor_agt_num AND dbo.AgPayDtl.pol_num = dbo.PolMst.pol_num LEFT OUTER JOIN
dbo.AgPayHdr ON dbo.AgPayDtl.agt_num = dbo.AgPayHdr.agt_num AND dbo.AgPayDtl.prd_mth = dbo.AgPayHdr.prd_mth WHERE (dbo.AgPayHdr.agt_num = @agentnum) AND (dbo.AgPayHdr.prd_mth IN (SELECT MAX(prd_mth) FROM dbo.AgPayHdr WHERE dbo.AgPayHdr.agt_num = @agentnum)) order by capsil , dbo.AgPayDtl.ins_nam, dbo.AgPayDtl.pol_num, dbo.AgPayDtl.mth_cov
GO 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. sth like agent_num =’123456′ instead of agent_num = @agentnum 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

---

Here is one post on the topic:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1459 /Argyle

---

i’ll take a look on the article. thx a lot for your help

---

Make sure that:
– @agentnum is the same datatype as the column you are comparing it to
– @agentnum is a parameter and not a variable within the procedure, if necessary use a second level procedure to pass the variable into as a parameter also the query is getting all detail records optionally attaching a master record and a policy record. Would you have details that don’t have a master? Should the query perhaps be as follows…???

---

I run the sp_updatestats and the WITH RECOMPILE methods and added 2 other indexs, while the performance is a little bit faster. it is still a lot slower than running in query analyzer (9 seconds Vs within 1 sec).
Any more tips on this?

---

Did my earlier post help? Cheers
Twan

---

is there a hash join in the execution plan?
are there more than 10K rows involved in the hash join?
QA sends all calls to SQL as SQL Batch,
ADO sends some sp’s as RPC
in older version of sql (7 included), there is a difference between the set point where a hash join will spool to disk between Batch and RPC
if this is the cause, declare this particular as text, and send the explict string

---

i modified the sql statement and it now run a lot faster. the part i mod are: FROM dbo.AgPayDtl LEFT OUTER JOIN
dbo.PolMst ON dbo.AgPayDtl.agt_num = dbo.PolMst.cor_agt_num AND
dbo.AgPayDtl.pol_num = dbo.PolMst.pol_num LEFT OUTER JOIN
dbo.AgPayHdr ON dbo.AgPayDtl.agt_num = dbo.AgPayHdr.agt_num AND
dbo.AgPayDtl.prd_mth = dbo.AgPayHdr.prd_mth,
(SELECT MAX(prd_mth) as prd_mth
FROM dbo.AgPayHdr
WHERE dbo.AgPayHdr.agt_num = @AgentNum ) maxmth
WHERE (dbo.AgPayHdr.agt_num = @AgentNum) AND (dbo.AgPayHdr.prd_mth = maxmth.prd_mth) *****
thx a lot for all your kindly help.

---

]]>