April 7, 2015 at 10:18 am
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 "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" error. 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(R) Xeon(R) X3430 @ 2.40Ghz.
My stored procedure can use some tuning.
I already have indexes on
Institutions.OpeidNumber
FeedEDData.State
FeedEDData.OpeidNumber
Any help will be appreciated.
ALTER PROCEDURE [dbo].[SearchSchoolData]
@Statenvarchar(max) = '', --ex "AL" or "AL,GA,CA"
@LearningMethodnvarchar(100) = '', --ex "Classroom" or "Distance,Correspondence"
@AccreditationTypenvarchar(100) = '',
@Programs nvarchar(100) = '',
@InstitutionTypenvarchar(100) = '',
@DegreeLevelnvarchar(200) = ''--ex "Certificate" or "Certificate,Associate"
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ClassroomLearningbit;
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 @ClassroomLearningIS 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
April 7, 2015 at 11:08 am
@State nvarchar(max) = '' --> can it be NVARCHAR(10)
ISNULL((Select TOP 1 TotalTaEnrollments From dbo.InstitutionMilitaryTuition pa Where pa.OpeidNumber = dbo.FeedEDData.OpeidNumber),'Not Reported') --> can this not work better with a outer apply?
That where clause with all the OR conditions--> maybe create proc with recompile will make it better.
The alternative is to do dynamic SQL to get only the parameters you want making a sargable where clause.
,IsProgramLengthAssociates int
,IsProgramLengthBachelors int
,IsProgramLengthMastersDoctorate int
,IsProgramLengthProfCertification int
,IsProgramLengthNonDegree int
,IsProgramLengthNonDegree1Year int
,IsProgramLengthNonDegree2Year int
,IsProgramLengthNonDegree3Year int
,IsProgramLengthShortTerm int
Shouldn't the above be bits?
Is CommaStringListIntoTable a table variable?
April 7, 2015 at 11:35 am
@State nvarchar(max) = '' --> can it be NVARCHAR(10)
User has the option to do a multi-select on states so nvarchar(10) will not work but i guess i can do nvarchar(200) to accomodate the commas right?
ISNULL((Select TOP 1 TotalTaEnrollments From dbo.InstitutionMilitaryTuition pa Where pa.OpeidNumber = dbo.FeedEDData.OpeidNumber),'Not Reported') --> can this not work better with a outer apply?
Will do
,IsProgramLengthAssociates int...
Good Catch..they are bits
Is CommaStringListIntoTable a table variable?
Its a function that returns a table with id of int. What it does is breaks apart the comma separated string from the parameter
That where clause with all the OR conditions...
Ok..i will think about this one
April 7, 2015 at 12:11 pm
Can you post your splitter function? That may very well be the culprit of the performance issues.
Also, I noticed you are using the isolation level read uncommitted. Are you aware of all the caveats there? Are you ok with missing and/or duplicated rows. It can and will happen with this level of isolation.
The where predicates are also a clear indication this is a type of catch all query. You can and will get horrible execution plans the way you have this coded. Take a look at this article as you will need to incorporate some of the teachings there. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 7, 2015 at 12:14 pm
If the splitter function returns a table, i.e. a table variable, you may lose parallelism, which should be OK if your query has high cardinality and supporting indexes.
And yes, as Sean has mentioned SQL int the wild, go to Gail's site before taking any advice from me.
April 7, 2015 at 12:16 pm
Careful with that read uncommitted, it could allow incorrect data to get inserted into #MainLocationsTempFL. That's the same as a NOLOCK table hint which, if you read books online, can "generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all)."
If you don't need those ORDER BY's in your final select statement then you should certainly get rid of those. If the data must be sorted get rid of the ORDER BY's and let the application do the sorting to see if that is faster. Also, if the ORDER BY is required, you could add a primary key or clustered index to #MainLocationsTempFL which will eliminate that costly sort in the query plan.
Something else to test is to do a SELECT INTO #MainLocationsTempFL (vs. creating and populating it). On many occasions I have seen SELECT INTO perform better than CREATE TABLE + INSERT INTO...
Lastly, MadAdmin was asking about that CommaStringListIntoTable function. These types of functions are commonly referred to on SQLServerCentral as "Splitter" functions. If your Splitter function is a mutli-line table valued function then it's bad and should be replaced with the splitter function referenced in my signature line. Or, if it's an in line table valued function but uses some XML logic or Recursive CTE to do the split then it should probably be replaced with the splitter function in my signature line.
-- Itzik Ben-Gan 2001
April 7, 2015 at 12:18 pm
Maybe changing the splitter function with the DelimitedSplit8K which is available in here:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
I'll also go with the recommendation of using RECOMPILE or using dynamic sql as this seems like a catch-all query. Read more about this in here: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
By the way, consider that you should stop using 3-part column names in your queries (just column names). Try to use alias for your tables to prevent this problem. You should also qualify your column names in your subqueries.
April 7, 2015 at 12:39 pm
I am using 2 splitter function.
ALTER FUNCTION [dbo].[CommaListIntoTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(id int not null)
AS
BEGIN
;-- Ensure input ends with comma
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @sp-2 INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0
BEGIN
SELECT @sp-2 = PATINDEX('%,%',@INSTR)
SELECT @VALUE = LEFT(@INSTR , @sp-2 - 1)
SELECT @INSTR = STUFF(@INSTR, 1, @sp-2, '')
INSERT INTO @TempTab(id) VALUES (@VALUE)
END
RETURN
END
ALTER FUNCTION [dbo].[CommaStringListIntoTable] (@InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(id nvarchar(100) not null)
AS
BEGIN
;-- Ensure input ends with comma
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @sp-2 INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0
BEGIN
SELECT @sp-2 = PATINDEX('%,%',@INSTR)
SELECT @VALUE = LEFT(@INSTR , @sp-2 - 1)
SELECT @INSTR = STUFF(@INSTR, 1, @sp-2, '')
INSERT INTO @TempTab(id) VALUES (@VALUE)
END
RETURN
END
April 7, 2015 at 12:42 pm
I posted my splitter function Above.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
My understanding was that instead of doing a NOLOCK on each table this would apply to the entire query. So i do not need to get the last record inserted 10 seconds ago and i figured this command will not lock my tables by other users.
So its not a good practice?
April 7, 2015 at 12:48 pm
djacobos (4/7/2015)
I posted my splitter function Above.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDMy understanding was that instead of doing a NOLOCK on each table this would apply to the entire query. So i do not need to get the last record inserted 10 seconds ago and i figured this command will not lock my tables by other users.
So its not a good practice?
That is correct. When you set the isolation level is it the same thing as putting NOLOCK on every single table in your entire procedure. The ramifications of that can be a lot bigger deal than it sounds. Here are few articles on the topic.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 7, 2015 at 12:52 pm
djacobos (4/7/2015)
I am using 2 splitter function.
Your splitters are almost certainly the issue here. They need to come to a quick and painless death immediately if not sooner. Looping in sql is a recipe for poor performance. Add to that what you have is multi statement table valued functions (another poor performer) and you have a performance timebomb ticking so loudly the bomb dogs are running away. Take a look at the splitter linked in my signature. It will not handle varchar(max) but it can handle up to 8,000 characters which is a pretty long string of csv values. If you truly need longer capability I can point you in the direction of an xml splitter that is pretty fast too and can handle varchar(max).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 7, 2015 at 1:10 pm
Read committed also has it's issues.
It is possible to read the same row twice with read committed, once before a change, then after the change.
All isolation levels have issues which need to be understood in the business context, even isolation level serializable.
April 7, 2015 at 1:14 pm
MadAdmin (4/7/2015)
Read committed also has it's issues.It is possible to read the same row twice with read committed, once before a change, then after the change.
All isolation levels have issues which need to be understood in the business context, even isolation level serializable.
I think you may be confusing this with read uncommitted. If you have an article or something that explains how you can read the same row twice using read committed I would love to look at it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 7, 2015 at 1:25 pm
Sean Lange (4/7/2015)
MadAdmin (4/7/2015)
Read committed also has it's issues.It is possible to read the same row twice with read committed, once before a change, then after the change.
All isolation levels have issues which need to be understood in the business context, even isolation level serializable.
I think you may be confusing this with read uncommitted. If you have an article or something that explains how you can read the same row twice using read committed I would love to look at it.
Look at the first problem in experiment 4.
This is the problem which repeatable reads solves apparently, but I may be wrong.
April 7, 2015 at 1:41 pm
MadAdmin (4/7/2015)
Sean Lange (4/7/2015)
MadAdmin (4/7/2015)
Read committed also has it's issues.It is possible to read the same row twice with read committed, once before a change, then after the change.
All isolation levels have issues which need to be understood in the business context, even isolation level serializable.
I think you may be confusing this with read uncommitted. If you have an article or something that explains how you can read the same row twice using read committed I would love to look at it.
Look at the first problem in experiment 4.
This is the problem which repeatable reads solves apparently, but I may be wrong.
OK that is what I thought. This is not the same thing as getting the same row twice. This is a good demonstration of how values can change during an execution of a single procedure though. Reading the same twice means you will get two copies of the same row. This is possible when using read uncommitted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply