performance issue - 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 2000 Forums

Transact-SQL (2000)

performance issue

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2005-12-22 : 06:16:10

i have one query in peocedure which return 4 lacks records.
Because of 4 lacks it takes more time..how i can resolve this problem

select distinct test.PUID,test.PayerID,test.ContractID,test.InsuranceCo,
test.ClaimID,test.EventID
,ClaimDenials.DenialDate,
ClaimDenials.PriorityNormalizedRsnCd,
ClaimDenials.DenialProcessDate,ClaimAppeals.AppealDate,
ClaimAppeals.AppealProcessDate
from
ClaimDenials,Reimburse,ReimburseOut,
ClaimAppeals,
(select distinct vda.EventID,vda.InstanceNo,vr.ContractID,
vr.PayerID,vr.ClaimID,vr.PUID,vr.InsuranceCo
from vcmDenialAppeal vda,vcmPayReimburHistorySummarytest vr
where vda.EventID=vr.EventID ) test
where test.EventID = ClaimDenials.EventID and
test.InstanceNo = ClaimDenials.InstanceNo and
test.EventID = ClaimAppeals.EventID and
test.InstanceNo = ClaimAppeals.InstanceNo and
and
isnull(PayerID,'') like ltrim(rtrim('%')) and

isnull(Reimburse.ContractID, '') like ltrim(rtrim('%'))
and isnull(ReimburseOut.ClaimID, '') like ltrim(rtrim('%'))
and isnull(Reimburse.InsuranceCo,'') like ltrim(rtrim('%'))

please help me.....

shubhada

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-22 : 06:27:28

Hi Shubhada,
i did not understand what exactly are you trying to acheive using ltrim(rtrim('%'))

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-22 : 06:29:30

quote:
Originally posted by shubhada

i have one query in peocedure which return 4 lacks records.
Because of 4 lacks it takes more time..how i can resolve this problem

select distinct test.PUID,test.PayerID,test.ContractID,test.InsuranceCo,
test.ClaimID,test.EventID
,ClaimDenials.DenialDate,
ClaimDenials.PriorityNormalizedRsnCd,
ClaimDenials.DenialProcessDate,ClaimAppeals.AppealDate,
ClaimAppeals.AppealProcessDate
from
ClaimDenials,Reimburse,ReimburseOut,
ClaimAppeals,
(select distinct vda.EventID,vda.InstanceNo,vr.ContractID,
vr.PayerID,vr.ClaimID,vr.PUID,vr.InsuranceCo
from vcmDenialAppeal vda,vcmPayReimburHistorySummarytest vr
where vda.EventID=vr.EventID ) test
where test.EventID = ClaimDenials.EventID and
test.InstanceNo = ClaimDenials.InstanceNo and
test.EventID = ClaimAppeals.EventID and
test.InstanceNo = ClaimAppeals.InstanceNo and
and
isnull(PayerID,'') like ltrim(rtrim('%')) and

isnull(Reimburse.ContractID, '') like ltrim(rtrim('%'))
and isnull(ReimburseOut.ClaimID, '') like ltrim(rtrim('%'))
and isnull(Reimburse.InsuranceCo,'') like ltrim(rtrim('%'))

please help me.....

shubhada

What is the realtion/use of Reimburse and ReimburseOut tables. Either use Joins for those table or use subquery.

Surendra

shubhada
Posting Yak Master

117 Posts

Posted - 2005-12-22 : 06:31:11

isnull(PayerID,'') like ltrim(rtrim('%')) and

isnull(Reimburse.ContractID, '') like ltrim(rtrim('%'))
and isnull(ReimburseOut.ClaimID, '') like ltrim(rtrim('%'))
and isnull(Reimburse.InsuranceCo,'') like ltrim(rtrim('%'))

here i have passed proces parameter...like as below.

isnull(PayerID,'') like ltrim(rtrim('Payer')) and

isnull(Reimburse.ContractID, '') like ltrim(rtrim('contract'))
and isnull(ReimburseOut.ClaimID, '') like ltrim(rtrim('claimid'))
and isnull(Reimburse.InsuranceCo,'') like ltrim(rtrim('company'))

shubhada

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-22 : 06:40:01

As rightly pointed out by Surendra. you are neither retrieving any column from Reimburse and ReimburseOut tables nor these tables are included in any join with rest of the tables . just you are referring them in where clause.

shubhada
Posting Yak Master

117 Posts

Posted - 2005-12-22 : 06:47:27

I have passed 4 parameter to procedure.those are in where clause. And I want to compare those parameter with Reimburse and ReimburseOut table…
So I used those table in from clause
isnull(PayerID,'') like ltrim(rtrim('@Payer')) and
isnull(Reimburse.ContractID, '') like ltrim(rtrim('@contract'))
and isnull(ReimburseOut.ClaimID, '') like ltrim(rtrim('@claimid'))
and isnull(Reimburse.InsuranceCo,'') like ltrim(rtrim('@company'))

shubhada

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-22 : 06:54:31

There wont be any use of putting a where clause on these two tables as the records resulted from the where clause are not linked to any of the tables referred in the query.
please explain the relationship b/w the tables referred in the query.
and what all records you want in the resultset.

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-22 : 07:05:27

quote:
Originally posted by shubhada

(select distinct vda.EventID,vda.InstanceNo,vr.ContractID,
vr.PayerID,vr.ClaimID,vr.PUID,vr.InsuranceCo
from vcmDenialAppeal vda,vcmPayReimburHistorySummarytest vr
where vda.EventID=vr.EventID
and check all the conditions here) test

quote:
Originally posted by shubhada

isnull(PayerID,'') like ltrim(rtrim('@Payer')) and
isnull(Reimburse.ContractID, '') like ltrim(rtrim('@contract'))
and isnull(ReimburseOut.ClaimID, '') like ltrim(rtrim('@claimid'))
and isnull(Reimburse.InsuranceCo,'') like ltrim(rtrim('@company'))

Remove tables Reimburse and ReimburseOut from FROM clause and all like conditions from query and check all like conditions in TEST query.
Hope that I understand your process very well.

Surendra

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-12-22 : 07:19:13

quote:
Originally posted by surendrakalekar

[quote]Originally posted by shubhada

(select distinct vda.EventID,vda.InstanceNo,vr.ContractID,
vr.PayerID,vr.ClaimID,vr.PUID,vr.InsuranceCo
from vcmDenialAppeal vda,vcmPayReimburHistorySummarytest vr
where vda.EventID=vr.EventID
and check all the conditions here... and join Reimburse and ReimburseOut tables here if required. ) test

Surendra

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-22 : 08:25:43

POST some sample input data AND matching EXPECTED results....otherwise we're guessing at what you're doing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources