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

So this procedure has a lot bigger problem than the query with a date interval is slow. This is a total anti-pattern. When you run a dynamic search, you should either do all the work in the client or do all work in a stored procedure. Passing WHERE clauses and ORDER BY clauses is a complete no-no. It opens for SQL injection and creates a very tight coupling between client and database.

In this case, you have a query which is quite complex even without the dynamic SQL. And burying it in dynamic SQL makes it even more difficult to read, understand and maintain.

It is actually possible to implement dynamic searches without dynamic SQL, although depending on the complexity of the problem it is not always the best solution. I have an article on my web site where I discuss how you design dynamic searches, Dynamic Search Conditions, both with static SQL and dynamic SQL. That article will not tell you anything on how to fix this particular performance problem. You are simply not at the point where you can start to tackle it.

If you want to fix the problem at this point, you need to look at the execution plans, and you also need to investigate available indexes. But you may quite much on your own. I can speak for everyone else, but I refuse to look at the code you have posted until you have cleaned it up.

Answer 2

There is not information to determine what's the problem. You will need to use the debugger and step through the program until you find out the exact statement that is failing.
If this happens to be precisely the call that executes the SQL statement, then there are two possibilities: either the parameters that you are passing to the query are badly formed (which you would be able to determine by examining them from the debugger) or the SQL query itself is either badly formed or very inefficient for execution on the server side. At this point, you would need to refine and optimize the query on the SQL side; it would no longer be a C# problem, which is what the present forum is about. But before reaching this conclusion, you should really do the step-by-step with the debugger to determine whether the problem is indeed in the SQL query, since it may be something else.

Answer 3

Hi @Hamed Vaziri :
The tags openspecs-* are dedicated to support open specifications. You can find open specifications here: https://learn.microsoft.com/en-us/openspecs/
Your question is not related to open specifications. For a better chance of getting an answer, I have removed the openspecs-sqlserver tag from your inquiry and have added the sql-server-general tag.

Regards,
Obaid Farooqi -MSFT

Answer 4

Hello @Hamed Vaziri

Since there are no details code-wise, my question is are you doing something like this and using parameters? Below may not be the solution but what I'm presenting may help.

If you try this if in the right direction and fails I will delete this reply so not to diminish this thread.

using System;  
using System.Data;  
using System.Data.SqlClient;  
  
namespace SqlServerDemo  
{  
    public class SqlServerOperations  
    {  
        private static string _connectionString =  
            "Data Source=.\\sqlexpress;Initial Catalog=ForumExample;Integrated Security=True";  
        public static void BetweenDates(DateTime startDateTime, DateTime endDateTime)  
        {  
  
            if (startDateTime > endDateTime)  
            {  
                // This check should be done before calling this method  
            }  
            var selectStatement =  
                "SELECT OrderID, CustomerIdentifier, EmployeeID, ShipCountry " +  
                "FROM dbo.Orders " +  
                "WHERE dbo.Orders.OrderDate BETWEEN @StartDate AND @EndDate";  
  
            using (var cn = new SqlConnection { ConnectionString = _connectionString })  
            {  
                using (var cmd = new SqlCommand { Connection = cn })  
                {  
                    cmd.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime))  
                        .Value = startDateTime;  
  
                    cmd.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime))  
                        .Value = endDateTime;  
  
                    cmd.CommandText = selectStatement;  
  
                    cn.Open();  
  
                    var reader = cmd.ExecuteReader();  
  
                    if (reader.HasRows)  
                    {  
                        while (reader.Read())  
                        {  
                            // do something like populate a list say from EF Core  
                            // or don't and instead load a DataTable  
                        }  
                    }  
                }  
            }  
        }  
    }  
}  

Also running the SQL in SSMS may be helpful

DECLARE @StartDate DATETIME= '07/08/2014';  
DECLARE @EndDate DATETIME= '07/15/2014';  
  
SELECT OrderID,   
       CustomerIdentifier,   
       EmployeeID,   
       FORMAT(OrderDate, 'MM/dd/yyyy') AS OrderDate,   
       ShipCountry  
FROM NorthWindAzure1.dbo.Orders  
WHERE dbo.Orders.OrderDate BETWEEN @StartDate AND @EndDate;  

Answer 5

Thanks for reply & sorry for late!
I'm using a proc with 2 parameters (whereClause & orderByClause) which pass to it from my app.
This proc generate & execute dynaimc query.
here is my proc t-sql code :

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

    union all

    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, ''-'', 0, 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, 0 as ''TotalPrice'', p.PatientFname +N'' ''+p.PatientLname as ''PatientFullName'', c.PatientCaseID, 
    N''-'' as ''BasicInsuranceName'', N''-'' as ''SupplementaryInsuranceName'' 
from Cash c     
    LEFT JOIN Doctors d ON c.DoctorID = d.DoctorID 
    JOIN Patients p ON c.PatientCaseID = p.PatientCaseID 
    JOIN PaymentsTypes pt on c.PaymentTypeID=pt.PaymentTypeID
where c.PatientCaseID is not null and c.OwnerTableName is null and c.OwnerID is null and c.PaiedFromPackagePayment=1

union all

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, ''-'', 0, 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, 0 as ''TotalPrice'', 
    res.PatientFname +N'' ''+res.PatientLname as ''PatientFullName'', res.PatientCaseID, N''-'' as ''BasicInsuranceName'', N''-'' as ''SupplementaryInsuranceName'' 
from Cash c     
    JOIN Reserve res ON c.OwnerID = res.RowID and c.OwnerTableName=N''Reserve'' 
    LEFT JOIN Doctors d ON c.DoctorID = d.DoctorID 
    JOIN PaymentsTypes pt on c.PaymentTypeID=pt.PaymentTypeID
'
if(dbo.fn_IsLISActive()=1)
BEGIN
    SET @query+=N'
union all
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'',     
    p.PatientFname +N'' ''+p.PatientLname as ''PatientFullName'', p.PatientCaseID, 
    i1.InsuranceName as ''BasicInsuranceName'', i2.InsuranceName as ''SupplementaryInsuranceName'' 
from Cash c     
    JOIN  Lab_Receptions r ON c.OwnerID = r.ReceptionRowID and c.OwnerTableName=N''Lab_Receptions'' 
    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
    '
END
if(dbo.fn_IsDentalActive()=1)
BEGIN
    SET @query+=N'
union all
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, dr.ReceptionDate, dr.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 dr.TotalPrice*-1 else dr.TotalPrice end as ''TotalPrice'',   
    p.PatientFname +N'' ''+p.PatientLname as ''PatientFullName'', p.PatientCaseID, 
    i1.InsuranceName as ''BasicInsuranceName'', i2.InsuranceName as ''SupplementaryInsuranceName''
from Cash c     
    JOIN  Dental_Receptions dr ON c.OwnerID = dr.ReceptionRowID and c.OwnerTableName=N''Dental_Receptions''
    LEFT JOIN Doctors d ON c.DoctorID = d.DoctorID 
    LEFT JOIN Patients p ON dr.PatientCaseID = p.PatientCaseID 
    LEFT JOIN Insurances i1 on dr.BasicInsuranceID=i1.InsuranceID 
    LEFT JOIN Insurances i2 on dr.SupplementaryInsuranceID=i2.InsuranceID
    JOIN PaymentsTypes pt on c.PaymentTypeID=pt.PaymentTypeID
    '
END
if(dbo.fn_IsRISActive()=1)
BEGIN
    SET @query+=N'
union all
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.TotalDiscounts, 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'',     
    p.PatientFname +N'' ''+p.PatientLname as ''PatientFullName'', p.PatientCaseID, 
    i1.InsuranceName as ''BasicInsuranceName'', i2.InsuranceName as ''SupplementaryInsuranceName'' 
from Cash c     
    JOIN  Imaging_Receptions r ON c.OwnerID = r.ReceptionRowID and c.OwnerTableName=N''Imaging_Receptions'' 
    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.SupInsuranceID=i2.InsuranceID
    JOIN PaymentsTypes pt on c.PaymentTypeID=pt.PaymentTypeID
    '
END

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

The code which works is :

EXEC sp_GetCash @whereClause=N'',@orderBy=N'c.TimeCreated desc'

And the code which take a long time to complete is :

EXEC sp_GetCash N'c.ReceptionDate >= ''1399/10/01'' and c.ReceptionDate <= ''1399/10/20'' ', N'c.TimeCreated desc'