How can I optimize this SQL query to avoid stored procedures timeout on the production server

Question

Hi all, I have a stored procedure query that is going to timeout on the production server for some time. However, I am not sure how to fix this query issue.

I have 12 years old data in sql database those data fetching from this query, day by day my data is increasing on large amount may be for that reason query is getting timout. But when I am executing this query, it is taking 2 seconds to execute on the production database. I don't know how it will time out.

declare @orgid int=3,

@salesperson int=0

select

pd.patientName,pd.personId,cn.dateCreated,cn.notes,rr.col1,rr.col2,b.kk

from patientdetails pd

inner join (select max(doId) doid,personId from product where productId=99 and personId>0

and statusno NOT IN(33,44,55,66,77) and effectDate IS NOT NULL group by personId) d on pd.personId=d.personId

join product dd on d.doid=dd.doId

join (select max(requestId) requestid,doId from subpro group by doId ) p on dd.doId=p.doId

join subpro pp on p.requestid=pp.requestId

join (select MAX(approvedOrderId)approvedOrderId,requestId,personId from ApprovedOrders group by requestId,personId)c on pp.requestId=c.requestId

join ApprovedOrders O on c.approvedOrderId=O.approvedOrderId and O.categoryId not in (3,4)

left join Notes cn on O.personId=cn.personid and objectType='Contact'

join t1 rs on o.requestId=rs.requestId

join t2 rr on rs.requestedSubProductId=rr.requestedSubProductId

join t3 b on rs.billableItemId=b.billableItemId and b.billableItemId in (9,90,88)

join t4 cp on cp.doNumber=d.doId and cp.commissionType='t1'

join t5 prn on cp.personId=prn.personId

where pd.organizationId=@orgId

and (@salesperson=0 or cp.personId=@salesperson)

order by pd.patientName

Please suggest how to fix this calling this stored procedure using C# web api method

Answer 1

First, the procedure as such does not time out. The timeout occurs client-side. Most client APIs have a default timeout of 30 seconds, and if your sole problem is the timeout, you can change the timeout to be for instance 0, which means "wait forever". The timeout is a property on the command object.

Next, it is little unclear if you have a stored procedure, or if you are submitting the code from the client code. Whatever, I don't think the actual code starts off:

declare @orgid int=3,
@salesperson int=0

Rather I will assume that they are parameters.

Also, tuning a query only from the query text is not that easy, since I don't have any knowledge about tables, indexes and the data. I can only remark on things that stand out. As it happens, there is one such thing in your query:

where pd.organizationId=@orgId
and (@salesperson=0 or cp.personId=@salesperson)

The OR condition here is problematic. If @salesperson has a non-zero value, you want to use an index on PersonID to locate the sales person, but this does not work when @salesperson is 0. And when you have a static query, you have a static plan that returns correct values for both cases, but which does not give the best performance for any of the cases.

There is more than one way to deal with this problem, but the easiest as long as the query is not called several times per second is to add this line at the end:

OPTION (RECOMPILE)

This forces a recompile every time of the query, so that you get a plan fits the value of @salesperson.

Keep in mind what I said above. This advice may work for you. But understand that it is a bit of a shot in the dark. If it does not work out, we need more information, particularly query plans.

Answer 2

Hi @coder rock

Ensure that your server has enough resources (CPU, memory, disk I/O) to handle the workload. You can use Windows Performance Monitor to track various performance counters.

Refer to this doc: Monitor Resource Usage (Performance Monitor).

Try below code to adjust the timeout setting in SQL Server:

EXEC sp_configure 'remote query timeout', <desired_time_in_seconds>;
RECONFIGURE;

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Optimizing this SQL query can be approached in several ways to improve performance and reduce the likelihood of timeouts. Here are some specific suggestions based on the structure of your query:

1. Review and Create Indexes

• PatientDetails Table: Ensure there are indexes on personId and organizationId since they are used in joins and conditions.
• Product Table: Index productId, personId, and effectDate to speed up the subquery.
• SubPro Table: Create indexes on doId and requestId.
• ApprovedOrders Table: Index approvedOrderId, requestId, and personId.
• Notes Table: Index personId and objectType since they are used in the join condition.
• RequestedSubProduct and RequestedGCM Tables: Index the keys used in joins (requestedSubProductId and requestId).
• BillableItem Table: Index billableItemId.
• CommissionRatePaid Table: Index doNumber and personId.
• Person Table: Ensure there is an index on personId.

Proper indexing can drastically reduce query execution time, especially when the data size increases.

2. Use Temporary Tables or CTEs (Common Table Expressions)

• Instead of using multiple nested subqueries, try breaking them into temporary tables or CTEs. This can help in optimizing execution by reducing the complexity of the query.Optimizing this SQL query can be approached in several ways to improve performance and reduce the likelihood of timeouts. Here are some specific suggestions based on the structure of your query:
  1. Review and Create Indexes
  • PatientDetails Table: Ensure there are indexes on personId and organizationId since they are used in joins and conditions.
  • Product Table: Index productId, personId, and effectDate to speed up the subquery.
  • SubPro Table: Create indexes on doId and requestId.
  • ApprovedOrders Table: Index approvedOrderId, requestId, and personId.
  • Notes Table: Index personId and objectType since they are used in the join condition.
  • RequestedSubProduct and RequestedGCM Tables: Index the keys used in joins (requestedSubProductId and requestId).
  • BillableItem Table: Index billableItemId.
  • CommissionRatePaid Table: Index doNumber and personId.
  • Person Table: Ensure there is an index on personId.
  Proper indexing can drastically reduce query execution time, especially when the data size increases.
  2. Use Temporary Tables or CTEs (Common Table Expressions)
  • Instead of using multiple nested subqueries, try breaking them into temporary tables or CTEs. This can help in optimizing execution by reducing the complexity of the query. Example using CTEs: sql WITH ProductData AS (

    SELECT MAX(doId) AS doid, personId
    
    FROM product
    
    WHERE productId = 99 
    
      AND personId > 0
    
      AND statusno NOT IN (33, 44, 55, 66, 77)
    
      AND effectDate IS NOT NULL
    
    GROUP BY personId
    

    ), SubProData AS (

    SELECT MAX(requestId) AS requestid, doId
    
    FROM subpro
    
    GROUP BY doId
    

    ), ApprovedOrdersData AS (

    SELECT MAX(approvedOrderId) AS approvedOrderId, requestId, personId
    
    FROM ApprovedOrders
    
    GROUP BY requestId, personId
    

    ) -- Now continue your main query using these CTEs for the joins Using CTEs can help with readability and allows SQL to optimize better.
    3. Reduce Data in Subqueries
    • Check if all fields in your subqueries are necessary or if you can reduce the number of columns selected.
    • Avoid calculating MAX() if you can already determine the maximum values through indexing or limiting the result set.
    4. Optimize Joins
    • Make sure to use INNER JOIN only if necessary and LEFT JOIN when you expect null values. Avoid joins with unnecessary tables unless required for the result set.
    • Ensure that join columns are indexed since they are often a major cause of slow query execution.
    5. Avoid SELECT * and Specify Columns
    • Always specify the required columns instead of using SELECT *. This reduces the amount of data fetched and improves performance.
    6. Query Execution on Smaller Data Sets
    • If possible, partition older data into separate tables. This way, the primary table used for frequent queries remains smaller and more efficient to handle.
    7. Review Server Resources and Timeout Settings
    • Check server performance metrics to ensure that the database server has enough CPU, memory, and I/O resources to handle the workload.
    • Temporarily increasing the query timeout setting in your web API or database may help, but it's not a permanent solution.
    8. Optimize Query Execution from the Web API
    • If you're calling this query from a C# Web API, you can use asynchronous operations to handle the query execution.
    • Ensure you're handling timeouts gracefully and that the API has appropriate retry logic.
    9. Analyze the Execution Plan
    • Review the query execution plan in your SQL Server to identify which operations are consuming the most time.
    • Look for suggestions like missing indexes, expensive sort operations, or large table scans.
    Example Query Adjustment with Improved Subqueries Here's a modified version of your query using CTEs to structure the logic better:Using CTEs can help with readability and allows SQL to optimize better.
    3. Reduce Data in Subqueries
    • Check if all fields in your subqueries are necessary or if you can reduce the number of columns selected.
    • Avoid calculating MAX() if you can already determine the maximum values through indexing or limiting the result set.
    4. Optimize Joins
    • Make sure to use INNER JOIN only if necessary and LEFT JOIN when you expect null values. Avoid joins with unnecessary tables unless required for the result set.
    • Ensure that join columns are indexed since they are often a major cause of slow query execution.
    5. Avoid SELECT * and Specify Columns
    • Always specify the required columns instead of using SELECT *. This reduces the amount of data fetched and improves performance.
    6. Query Execution on Smaller Data Sets
    • If possible, partition older data into separate tables. This way, the primary table used for frequent queries remains smaller and more efficient to handle.
    7. Review Server Resources and Timeout Settings
    • Check server performance metrics to ensure that the database server has enough CPU, memory, and I/O resources to handle the workload.
    • Temporarily increasing the query timeout setting in your web API or database may help, but it's not a permanent solution.
    8. Optimize Query Execution from the Web API
    • If you're calling this query from a C# Web API, you can use asynchronous operations to handle the query execution.
    • Ensure you're handling timeouts gracefully and that the API has appropriate retry logic.
    9. Analyze the Execution Plan
    • Review the query execution plan in your SQL Server to identify which operations are consuming the most time.
    • Look for suggestions like missing indexes, expensive sort operations, or large table scans.
    Example Query Adjustment with Improved Subqueries Here's a modified version of your query using CTEs to structure the logic better: sql WITH ProductCTE AS (

    SELECT MAX(doId) AS doid, personId
    
    FROM product
    
    WHERE productId = 99 
    
      AND personId > 0
    
      AND statusno NOT IN (33, 44, 55, 66, 77)
    
      AND effectDate IS NOT NULL
    
    GROUP BY personId
    

    ), SubProCTE AS (

    SELECT MAX(requestId) AS requestid, doId
    
    FROM subpro
    
    GROUP BY doId
    

    ), ApprovedOrdersCTE AS (

    SELECT MAX(approvedOrderId) AS approvedOrderId, requestId, personId
    
    FROM ApprovedOrders
    
    GROUP BY requestId, personId
    

    ) SELECT pd.patientName, pd.personId, cn.dateCreated, cn.notes, rr.col1, rr.col2, b.kk FROM patientdetails pd JOIN ProductCTE d ON pd.personId = d.personId JOIN product dd ON d.doid = dd.doId JOIN SubProCTE p ON dd.doId = p.doId JOIN subpro pp ON p.requestid = pp.requestId JOIN ApprovedOrdersCTE c ON pp.requestId = c.requestId JOIN ApprovedOrders O ON c.approvedOrderId = O.approvedOrderId AND O.categoryId NOT IN (3, 4) LEFT JOIN Notes cn ON O.personId = cn.personid AND cn.objectType = 'Contact' JOIN requestedsubproduct rs ON O.requestId = rs.requestId JOIN requestedgcm rr ON rs.requestedSubProductId = rr.requestedSubProductId JOIN billableitem b ON rs.billableItemId = b.billableItemId AND b.billableItemId IN (9, 90, 88) JOIN commissionratepaid cp ON cp.doNumber = d.doid AND cp.commissionType = 't1' JOIN person prn ON cp.personId = prn.personId WHERE pd.organizationId = @orgid AND (@salesperson = 0 OR cp.personId = @salesperson) ORDER BY pd.patientName;

This approach should help the SQL optimizer to handle the data more efficiently, reducing query execution times.