Unwanted query execution failed in my app!

Question

Hi
i have a query which returns data (from sql server db) based on date ranges entered by end-users.
My problem is that when user get report from for example 3 month, it works correctly (after 1:20 minutes), But when getting report from smaller range, for example the last 20 days, it hanging for several minutes & don't get any result!!
What's the problem & how to work-around this?

Note : i'm using proc to generate dynamic query based on user input & both query works in sql server itself!

Thanks in advamce

Answer 1

Hi again
Sorry for late!
I cleaned it as much as possible :

CREATE PROC [dbo].[sp_GetCash] @whereClause NVARCHAR(MAX), @orderBy NVARCHAR(MAX)
AS
DECLARE @query NVARCHAR(MAX)
SET @query=N'
select c.CashID, c.AcceptanceID, c.ZoneID, c.Hesab, c.SubmitDate, c.Description, c.CreatedBy, c.TimeCreated, c.DateCreated, 
    c.LastModifiedBy, c.LastModifiedTime, c.LastModifiedDate, c.ReceptionRowID, c.ReserveRowID, c.PaiedFromPackagePayment, 
    c.CashRowID, c.IsEbtal, c.DoctorID, r.ReceptionDate, r.TotalDiscount, c.OwnerTableName, c.OwnerID, c.PaymentTypeID,
    c.HesabPrimary, c.HesabSecondary, c.POSMerchantID, c.POSTerminalID, c.POSRRN, c.POSStan, c.POSCardNumber, c.POSMessageID, 
    c.POSTransactionDate, c.POSTransactionTime, pt.PaymentTypeName, c.CashAcountID, c.DiffHesabAmount,
    case when c.IsEbtal=1 then r.TotalPrice*-1 else r.TotalPrice end as ''TotalPrice'', 
    case 
        when c.PatientCaseID is null and r.PatientCaseID<>N''0'' then p.PatientFname +N'' ''+p.PatientLname 
        when c.PatientCaseID is null and r.PatientCaseID=N''0'' then r.PatientFirstName+N'' ''+r.PatientLastName 
        when c.PatientCaseID is not null then (select p2.PatientFname +N'' ''+p2.PatientLname from Patients p2 where p2.PatientCaseID=c.PatientCaseID)
        when c.PatientCaseID is null and c.OwnerTableName=N''Reserve'' then (select r2.PatientFname +N'' ''+r2.PatientLname from Reserve r2 where r2.RowID=c.OwnerID)
    end as ''PatientFullName'', 
    case 
        when c.PatientCaseID is null and c.OwnerTableName=N''Reserve'' then (select r2.PatientCaseID from Reserve r2 where r2.RowID=c.OwnerID)
        when c.PatientCaseID is null and c.OwnerTableName<>N''Reserve'' then p.PatientCaseID else c.PatientCaseID 
    end as ''PatientCaseID'', 
    i1.InsuranceName as ''BasicInsuranceName'', i2.InsuranceName as ''SupplementaryInsuranceName'' 
from Cash c        
    JOIN Reception r ON c.OwnerID = r.ReceptionRowID and c.OwnerTableName=N''Reception'' 
    LEFT JOIN Doctors d ON c.DoctorID = d.DoctorID 
    LEFT JOIN Patients p ON r.PatientCaseID = p.PatientCaseID 
    LEFT JOIN Insurances i1 on r.BasicInsuranceID=i1.InsuranceID 
    LEFT JOIN Insurances i2 on r.SupplementaryInsuranceID=i2.InsuranceID
    JOIN PaymentsTypes pt on c.PaymentTypeID=pt.PaymentTypeID
'        
SET @query=N'with cte as ('+@query+N')
SELECT c.* FROM cte c'

IF(len(@whereClause)>0)
    set @query=@query+N' where '+@whereClause
if(len(@orderBy)>0)
    set @query=@query+N' order by '+@orderBy

exec sp_executesql @query

This segment of main query is important than other segments.