How to avoid SQL server time out issue while handling huge amount of data

I have to handle a huge amount of data in my ASP.Net MVC application. Sometimes there is timeout while retrieving data. Which are all known methods to speed up the execution and avoid time out? The application uses SQL SPs.

The following query in SP takes more time.

WITH CTESelect AS (
  SELECT  TOP 20 CompanyName
  FROM    Periodicals P
  WHERE   P.PID IN (
            SELECT  DISTINCT CASE WHEN P.ParentID IS NULL 
                             THEN P.PID
                             ELSE P.ParentID
                             END AS PID
            FROM    Periodicals P
                    LEFT JOIN Companies C ON P.CompanyID = C.CompanyID
                    LEFT JOIN UserContacts UC ON P.CreatedUserID = UC.UserID
                    LEFT JOIN Contacts CT ON UC.ContactID = CT.ContactID
                    LEFT JOIN UserContacts URC ON P.CustomerID = URC.UserID
                    LEFT JOIN Contacts RCT ON URC.ContactID = RCT.ContactID
    开发者_开发问答                LEFT JOIN UserRepresentCompanies UCP ON UCP.UserID = URC.UserID
                    LEFT JOIN Contacts CC ON CC.ContactID = UCP.CompanyContactID
            WHERE   ( @CompanyID IS NULL OR @CompanyID = C.CompanyID )
                    AND ( @CustomerID = 0 OR @CustomerID = URC.UserID )
                    AND ( P.SaveMode IS NULL )
          )
)
SELECT  DISTINCT  CompanyName
FROM    CTESelect  

As per comments, selecting at most 20 records should not take a huge amount of time.

Some things to verify

• does each foreign key have an index
• is there a covering index on SaveMode and companyname

Sidenote: It seems to me your SQL Statement could be simplified to the following. Only change in output should be that there are always 20 records selected whereas that might not have been the case in your original query.

SQL Statement

SELECT  TOP 20 DISTINCT COALESCE(Pp.CompanyName, p.CompanyName)
FROM    Periodicals P
        LEFT JOIN Periodicals Pp ON Pp.PID = P.ParentID
        LEFT JOIN Companies C ON P.CompanyID = C.CompanyID
        LEFT JOIN UserContacts UC ON P.CreatedUserID = UC.UserID
        LEFT JOIN Contacts CT ON UC.ContactID = CT.ContactID
        LEFT JOIN UserContacts URC ON P.CustomerID = URC.UserID
        LEFT JOIN Contacts RCT ON URC.ContactID = RCT.ContactID
        LEFT JOIN UserRepresentCompanies UCP ON UCP.UserID = URC.UserID
        LEFT JOIN Contacts CC ON CC.ContactID = UCP.CompanyContactID
WHERE   ( @CompanyID IS NULL OR @CompanyID = C.CompanyID )
        AND ( @CustomerID = 0 OR @CustomerID = URC.UserID )
        AND ( P.SaveMode IS NULL )

First do you need LEFT joins for each table? If some of those can be changed to inner joins that'll help. Make sure that you have index everywhere that you need them. You'll want to check the execution plan to insure that you have everything indexed correctly.

optimize your stored proc. there are a million pages on this on google.

if you can't, then increase the timeout value in the connection string.

The problem is most definitely with the query.

A few things of notice.

You are doing multiple joins to the same tables for different fields If you did these in one join as opposed to an entire table lookup/scan would be more efficient

e.g

 LEFT JOIN UserContacts UC ON UC.UserID = P.CreatedUserID
 AND UC.UserID = P.CustomerID
 LEFT JOIN Contacts CT ON CT.ContactID = UC.ContactID 
 AND CT.ContactID = UC.ContactID

Also noticed that P.CustomerId = UserID? Is that a valid relationship? as that would make 2 joins to the UserContacts table to the Periodicals Table on the same field. You may get away with only 1 join

LEFT JOIN UserContacts URC ON P.CustomerID = URC.UserID

You may also be returning extra tables of data in the join for no output reason e.g why is UserRepresentCompanies in the query? You aren't seleting data from it or using in where clause.