question

HamedVaziri-4770 avatar image
0 Votes"
HamedVaziri-4770 asked ErlandSommarskog commented

Unwanted query execution failed in my app!

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

sql-server-generaldotnet-csharpdotnet-adonet
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What's the problem & how to work-around this?

Right now the problem is that you are not sharing enough information. As a start, you could share the code in question. It is not unlikely that we will ask for more information later.

0 Votes 0 ·
APoblacion avatar image
0 Votes"
APoblacion answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ObaidFarooqi-8403 avatar image
0 Votes"
ObaidFarooqi-8403 answered

Hi @HamedVaziri-4770:
The tags openspecs-* are dedicated to support open specifications. You can find open specifications here: https://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

Hello @HamedVaziri-4770

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;

54997-sql1.png




sql1.png (27.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HamedVaziri-4770 avatar image
0 Votes"
HamedVaziri-4770 answered

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'



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
2 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HamedVaziri-4770 avatar image
0 Votes"
HamedVaziri-4770 answered ErlandSommarskog commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It's still dynamic SQL, when it shouldn't be.

0 Votes 0 ·

Can u tell me what's your solution is?

0 Votes 0 ·

Can u tell me what's your solution is?

Please refer to my original reply further up in the thread.

0 Votes 0 ·