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.
| Author |
Topic |
|
Rita Bhatnagar
Posting Yak Master
172 Posts |
 Posted - 2001-10-26 : 12:54:33
|
| This query is taking long time to give record count in VB Program.If i put set nocount on it runs faster but it gives me record count=-1.What do i need to do to get record count faster.CREATE PROCEDURE qryBilling_FetchMDS2Info@prmeCompanyID INT,@prmCRID INT ASSELECT mds2.MdsId, mds2.careRecipientId, CMP.EntityId as CompanyEntityId, mds2_aa.aaq8a, mds2_aa.aaq8b, mds2_aa.aaq8c, mds2_a.aq3a, mds2_t.tq3MDCR, mds2_t.tq3state, mds2_r.rq2bDate, mds2_v.vqb2DateFROM mds2 INNER JOIN mds2_a ON mds2_a.mdsid = mds2.mdsid INNER JOIN mds2_aa ON mds2_aa.mdsid = mds2.mdsid INNER JOIN mds2_r ON mds2_r.mdsid = mds2.mdsid INNER JOIN mds2_t ON mds2_t.mdsid = mds2.mdsid INNER JOIN mds2_v ON mds2_v.mdsid = mds2.mdsid INNER JOIN ADT ON ADT.eCareRecipientId = MDS2.CareRecipientId INNER JOIN Company CMP ON CMP.EntityId = ADT.eCompanyIDWHERE mds2.CareRecipientId <> 0 AND (Ltrim(Rtrim(mds2.MDSStatus)) = 'Complete' OR LEFT(Ltrim(Rtrim(mds2.MDSStatus)),9) = 'EDS-Ready' OR LEFT(Ltrim(Rtrim(mds2.MDSStatus)),8) = 'EDS-Sent' OR LEFT(Ltrim(Rtrim(mds2.MDSStatus)),10) = 'EDS-Accept' OR LEFT(Ltrim(Rtrim(mds2.MDSStatus)),9) = 'EDS-Reject') AND mds2_aa.aaq8a IS NOT NULL AND LTrim(Rtrim(mds2_aa.aaq8a)) <> '' AND (LTrim(Rtrim(mds2_aa.aaq8a)) ='2') OR ( mds2_aa.aaq8b IS NOT NULL) AND LTrim(Rtrim(ISNULL(mds2_aa.aaq8a,'0')))<6 AND mds2_a.aq3a IS NOT NULL AND ADT.AdtCodeId = 1 AND ADT.eCompanyId = @prmeCompanyID AND (@prmCRID = 0 OR mds2.CareRecipientID = @prmCRID) ORDER BY Mds2.MdsId, mds2.CareRecipientId, LTrim(Rtrim(ISNULL(mds2_aa.aaq8a,'0'))) + LTrim(Rtrim(ISNULL(mds2_aa.aaq8b,'0'))) |
|
|
|
|
|
|
|
query is very slow