6
\$\begingroup\$

I am running a test load using Telerik Test Studio using 100 users at once entering search filters on a page that calls a stored procedure.

My application that calls the stored procedure is an ASP.NET MVC 5 application and it returns this error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

I only get the error on load of 100 users.

(SQL Server 2008 is running on Windows Server 2008 R2 Standard, 64-bit, 4 GB RAM, Intel Xeon X3430 @ 2.40Ghz.)

My stored procedure can use some tuning. I already have indexes on:

  • Institutions.Status
  • Institutions.OpeidNumber
  • FeedEDData.State
  • FeedEDData.OpeidNumber
ALTER PROCEDURE [dbo].[SearchSchoolData]
@State                          nvarchar(max) = '', --ex "AL" or "AL,GA,CA"
@LearningMethod                 nvarchar(100) = '', --ex "Classroom" or "Distance,Correspondence"
@AccreditationType              nvarchar(100) = '',
@Programs                       nvarchar(100) = '',
@InstitutionType                nvarchar(100) = '',
@DegreeLevel                    nvarchar(200) = ''--ex "Certificate" or "Certificate,Associate"
AS

BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @ClassroomLearning      bit;
DECLARE @DistanceOnlineLearning bit;
DECLARE @CorrespondenceLearning bit;

IF (@LearningMethod != '')
BEGIN
    SET @ClassroomLearning      = CASE WHEN CHARINDEX('Classroom', @LearningMethod,0) = 0 THEN NULL ELSE CAST(1 AS BIT) END
    SET @DistanceOnlineLearning = CASE WHEN CHARINDEX('Distance', @LearningMethod,0) = 0 THEN NULL ELSE CAST(1 AS BIT) END
    SET @CorrespondenceLearning = CASE WHEN CHARINDEX('Correspondence', @LearningMethod,0) = 0 THEN NULL ELSE CAST(1 AS BIT) END
END

DECLARE @Certificate bit;
DECLARE @Associate bit;
DECLARE @Bachelor bit;
DECLARE @Master bit;

IF (@DegreeLevel != '')
BEGIN
    SET @Certificate = CASE WHEN CHARINDEX('Certificate', @DegreeLevel,0) = 0 THEN NULL ELSE CAST(1 AS BIT) END
    SET @Associate = CASE WHEN CHARINDEX('Associate', @DegreeLevel,0) = 0 THEN NULL ELSE CAST(1 AS BIT) END
    SET @Bachelor = CASE WHEN CHARINDEX('Bachelor', @DegreeLevel,0) = 0 THEN NULL ELSE CAST(1 AS BIT) END
    SET @Master = CASE WHEN CHARINDEX('Master', @DegreeLevel,0) = 0 THEN NULL ELSE CAST(1 AS BIT) END
END


CREATE TABLE #MainLocationsTempFL
(
    InstitutionName nvarchar(100)
    ,TradeName nvarchar(100)
    ,City nvarchar(50)
    ,OpeidNumber nvarchar(8)
    ,InstitutionID int
    ,State nvarchar(10)
    ,HasAdditionalLocations bit
    ,IsProgramLengthAssociates int
    ,IsProgramLengthBachelors int
    ,IsProgramLengthMastersDoctorate int
    ,IsProgramLengthProfCertification int
    ,IsProgramLengthNonDegree int
    ,IsProgramLengthNonDegree1Year int
    ,IsProgramLengthNonDegree2Year int
    ,IsProgramLengthNonDegree3Year int
    ,IsProgramLengthShortTerm int
    ,TaUsers  nvarchar(100)
)


INSERT INTO #MainLocationsTempFL SELECT 
      InstitutionName
    , TradeName
    , FeedEDData.City
    , FeedEDData.OpeidNumber
    , InstitutionID
    , FeedEDData.State
    , HasAdditionalLocations = (CASE WHEN EXISTS (Select 1 FROM dbo.ApprovedAdditionalLocations() tblAdditionalLocations WHERE tblAdditionalLocations.ParentOpeidNumber = dbo.FeedEDData.OpeidNumber) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END)
    ,IsProgramLengthAssociates
    ,IsProgramLengthBachelors
    ,IsProgramLengthMastersDoctorate
    ,IsProgramLengthProfCertification
    ,IsProgramLengthNonDegree
    ,IsProgramLengthNonDegree1Year
    ,IsProgramLengthNonDegree2Year
    ,IsProgramLengthNonDegree3Year
    ,IsProgramLengthShortTerm
    , ISNULL((Select TOP 1 TotalTaEnrollments From dbo.InstitutionMilitaryTuition pa Where pa.OpeidNumber = dbo.FeedEDData.OpeidNumber),'Not Reported')
FROM dbo.FeedEDData
    INNER JOIN dbo.Institutions ON dbo.Institutions.OpeidNumber = dbo.FeedEDData.OpeidNumber
WHERE dbo.Institutions.Status IN (SELECT id FROM dbo.[CommaListIntoTable]( (SELECT ListOfStatus FROM dbo.ParticipatingInstitutionStatuses )) ) 
AND (FeedEDData.State in (SELECT id FROM CommaStringListIntoTable(@State)) OR @State = '')
AND (FeedEDData.AccreditationType in (SELECT id FROM CommaStringListIntoTable(@AccreditationType)) OR @AccreditationType = '')
AND (Institutions.IsClassroomLearning       = @ClassroomLearning      OR @ClassroomLearning IS NULL)
AND (Institutions.IsDistanceLearning        = @DistanceOnlineLearning OR @DistanceOnlineLearning IS NULL)
AND (Institutions.IsCorrespondenceLearning  = @CorrespondenceLearning OR @CorrespondenceLearning IS NULL)
AND (FeedEDData.SchType in (SELECT * FROM CommaStringListIntoTable(@InstitutionType)) OR @InstitutionType = '')
AND (dbo.FeedEDData.OpeidNumber IN (SELECT OpeidNumber FROM dbo.InstitutionIpedsCipCodes WHERE CipCode in (SELECT id FROM CommaStringListIntoTable(@Programs))) OR @Programs = ''  )



IF (@DegreeLevel = 'Certificate')
    BEGIN
        SELECT * 
        FROM #MainLocationsTempFL 
        WHERE 
        IsProgramLengthProfCertification= @Certificate OR
        IsProgramLengthNonDegree= @Certificate OR
        IsProgramLengthNonDegree1Year= @Certificate OR
        IsProgramLengthNonDegree2Year= @Certificate OR
        IsProgramLengthNonDegree3Year= @Certificate OR
        IsProgramLengthShortTerm= @Certificate
        ORDER BY InstitutionName
    END
ELSE
    BEGIN
        SELECT * 
        FROM #MainLocationsTempFL 
        WHERE   (IsProgramLengthAssociates  = @Associate OR @Associate IS NULL)
            AND (IsProgramLengthBachelors   = @Bachelor OR @Bachelor IS NULL)
            AND (IsProgramLengthMastersDoctorate    = @Master OR @Master IS NULL)
        ORDER BY InstitutionName
    END


DROP TABLE #MainLocationsTempFL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET NOCOUNT OFF

END
\$\endgroup\$
3
\$\begingroup\$

It's odd that the TaUsers result column is NVARCHAR(100) rather than a nullable number.

The setting of the local variables (@ClassroomLearning, etc.) could be a bit more succinct.

I don't see any reason to use a temporary table; it just complicates things. The whole procedure is just a complex query, and should be written as such. If you help managing the complexity, use Common Table Expressions instead.

I'm not a fan of SELECTs within an attribute selection list. I'd rather use joins.

ALTER PROCEDURE [dbo].[SearchSchoolData]
    @State                          NVARCHAR(max) = '', --ex "AL" or "AL,GA,CA"
    @LearningMethod                 NVARCHAR(100) = '', --ex "Classroom" or "Distance,Correspondence"
    @AccreditationType              NVARCHAR(100) = '',
    @Programs                       NVARCHAR(100) = '',
    @InstitutionType                NVARCHAR(100) = '',
    @DegreeLevel                    NVARCHAR(200) = ''--ex "Certificate" or "Certificate,Associate"
AS
BEGIN

    DECLARE @ClassroomLearning      BIT = CAST(CHARINDEX('Classroom',      @LearningMethod) AS BIT);
    DECLARE @DistanceOnlineLearning BIT = CAST(CHARINDEX('Distance',       @LearningMethod) AS BIT);
    DECLARE @CorrespondenceLearning BIT = CAST(CHARINDEX('Correspondence', @LearningMethod) AS BIT);

    DECLARE @Certificate BIT = CAST(CHARINDEX('Certificate', @DegreeLevel) AS BIT);
    DECLARE @Associate   BIT = CAST(CHARINDEX('Associate',   @DegreeLevel) AS BIT);
    DECLARE @Bachelor    BIT = CAST(CHARINDEX('Bachelor',    @DegreeLevel) AS BIT);
    DECLARE @Master      BIT = CAST(CHARINDEX('Master',      @DegreeLevel) AS BIT);

    WITH SuitableInstitutions AS (
        SELECT *
            FROM Institutions
            WHERE
                (@LearningMethod = '' OR (
                    IsClassroomLearning      = @ClassroomLearning AND
                    IsDistanceLearning       = @DistanceOnlineLearning AND
                    IsCorrespondenceLearning = @CorrespondenceLearning
                )) AND
                Status IN (SELECT id FROM CommaListIntoTable(SELECT ListOfStatus FROM dbo.ParticipatingInstitutionStatuses))
    ), AdditionalLocations AS (
        SELECT ParentOpeidNumber
            FROM dbo.ApprovedAdditionalLocations()
            GROUP BY ParentOpeidNumber
    ), TaEnrollments AS (
        SELECT OpeidNumber, MAX(TotalTaEnrollments) OVER (PARTITION BY OpeidNumber) AS TotalTaEnrollments
            FROM dbo.InstitutionMilitaryTuition
    )
    SELECT 
          InstitutionName
        , TradeName
        , FeedEDData.City
        , FeedEDData.OpeidNumber
        , InstitutionID
        , FeedEDData.State
        , CAST(CASE WHEN AdditionalLocations.ParentOpeidNumber IS NULL THEN 0 ELSE 1 AS BIT) AS HasAdditionalLocations
        , IsProgramLengthAssociates
        , IsProgramLengthBachelors
        , IsProgramLengthMastersDoctorate
        , IsProgramLengthProfCertification
        , IsProgramLengthNonDegree
        , IsProgramLengthNonDegree1Year
        , IsProgramLengthNonDegree2Year
        , IsProgramLengthNonDegree3Year
        , IsProgramLengthShortTerm
        , ISNULL(CAST(TotalTaEnrollments AS NVARCHAR(100)), 'Not Reported') AS TaUsers
        FROM dbo.FeedEDData
            INNER JOIN SuitableInstitutions
                ON SuitableInstitutions.OpeidNumber = dbo.FeedEDData.OpeidNumber
            LEFT OUTER JOIN AdditionalLocations
                ON AdditionalLocations.ParentOpeidNumber = db.FeedEDData.OpeidNumber
            LEFT OUTER JOIN TaEnrollments AS pa
                ON pa.OpeidNumber = db.FeedEDData.OpeidNumber
        WHERE
            (@State = '' OR FeedEDData.State in (SELECT id FROM CommaStringListIntoTable(@State))) AND
            (@AccreditationType = '' OR FeedEDData.AccreditationType in (SELECT id FROM CommaStringListIntoTable(@AccreditationType)) AND
            (@Programs = '' OR dbo.FeedEDData.OpeidNumber IN (
                SELECT OpeidNumber
                    FROM dbo.InstitutionIpedsCipCodes
                    WHERE CipCode in (SELECT id FROM CommaStringListIntoTable(@Programs))
            ) AND
            (@InstitutionType = '' OR FeedEDData.SchType in (SELECT * FROM CommaStringListIntoTable(@InstitutionType))) AND
            (@DegreeLevel = ''
             OR
             (@DegreeLevel = 'Certificate' AND (IsProgramLengthProfCertification OR
                                                IsProgramLengthNonDegree OR
                                                IsProgramLengthNonDegree1Year OR
                                                IsProgramLengthNonDegree2Year OR
                                                IsProgramLengthNonDegree3Year OR
                                                IsProgramLengthShortTerm))
             OR
             ((IsProgramLengthAssociates       = @Associate) AND
              (IsProgramLengthBachelors        = @Bachelor) AND
              (IsProgramLengthMastersDoctorate = @Master))
            );
END
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.