Searching school data

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)

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))
);
E

StackExchange Code Review Q#86165, answer score: 3