Parameter sniffing - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Parameter sniffing

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2007-02-26 : 04:09:42

Hi,
Is somebody will give me some idea about the parameter sniffing?
What is parameter sniffing??
How to overcome with this problem???
I have one procedure that is causing this problem.
Plz give me some tips to modify this proc to overcome this problem

dbo.cm_test (
@fromdate datetime = '01/01/1900',
@thrudate datetime = '12/31/9999'
)
As
Begin
SET ANSI_WARNINGS OFF
set nocount on
declare @minln smallint,
@CID char(24),
@EID numeric(12,0),
@extractdate datetime

/* Begin gathering data */
select @extractdate = getdate()
/* Begin gathering data */
if exists (select * from tempdb..sysobjects where name like '#tmpWTClm%')
drop table #tmpWTClm

create table #tmpWTClm (
ClaimID char(24) not null,
EventID numeric(12,0) not null)

insert into #tmpWTClm
select Rem.ClaimID, Rem.EID
from Rem, RemOut2
where Rem.CID= RemOut2.CIDand
Rem.EID= RemOut2.EIDand
(ReimProcessDate between @fromdate and @thrudate)

insert into #tmpWTClm
select distinct ClaimID, EID
from PaymentsDetail
where ProcessDate between @fromdate and @thrudate

insert into #tmpWTClm
select distinct ClaimID, EID
from ClaimDenials
where DenialProcessDate between @fromdate and @thrudate

insert into #tmpWTClm
select distinct ClaimID, EID
from ClaimAppeals
where AppealProcessDate between @fromdate and @thrudate

declare ClmDataCursor cursor for
select distinct ClaimID, EventID
from #tmpWTClm

open ClmDataCursor
fetch ClmDataCursor into @claimid, @EID

while (@@fetch_status = 0)
begin
insert into WTDenialAppealExtract
select distinct
from Rem, Rem1
where Rem.CID= Rem1.CIDand
Rem.EID= Rem1.EIDand
Rem.CID= @CIDand
Rem.EID= @eventid

fetch ClmDataCursor into @CID , @EID
end

close ClmDataCursor
Deallocate ClmDataCursor

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-26 : 06:01:37

If you don't know what parameter sniffing is, how can you be so sure that your procedure is facing from the same problem?

If your SP is performing badly, it could be well because of that <shudder> cursor </shudder> stuff which is not necessary.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-02-26 : 06:04:11

Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79651

Kristen
Test

22859 Posts

Posted - 2007-02-26 : 06:04:32

(Sorry Harsh, you are the deadlock victim

)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-26 : 06:10:16

I think your algo is flawed, Kristen !

Why me? According to time priority, this is the original post and the other one is duplicate?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-02-26 : 06:28:08

You deadlock victims are all the same .....

I figure this is a SQL 2000 problem, not 2005 ... so I left the one in SQL Admin 2005 rather than T-SQL 2005 ... thus I am certain that my algo is no more flawed than the posters choice of forum!

Plus I preferred khtan's answer ...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-26 : 06:34:29

I guess I have to set my DEADLOCK_PRIORITY at higher limits, so you can't make me deadlock victim.