April 7, 2015 at 1:43 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.
Read uncommitted is the only ISOLATION lever that allows dirty reads (e.g. reading data that has not been committed to disk). Because this is so the ACID principles (detailed in the link you included) do not fully apply. That's what makes NOLOCK (e.g. READ UNCOMMITTED) completely different from the other 3 isolation levels.
-- Itzik Ben-Gan 2001
April 7, 2015 at 1:51 pm
djacobos (4/7/2015)
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
As everyone said, delimitedSplit8K is what you need.
On a side note: these are multi-statement table valued functions (mTVF for short). mTVFs are ALWAYS bad (if you care about performance).
-- Itzik Ben-Gan 2001
April 7, 2015 at 1:59 pm
Sean Lange (4/7/2015)
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.
Yes, I should have been more clear regarding getting 2 versions of the same row in a single result set.
But what this shows is that isolation levels need to be clearly thought out in the context as each has a shortcoming.
I am careful not to punt a specific isolation level purely because I do not know the business context or environment that Mr Jacobs is in, and following each isolation level has its own trade-off.
April 8, 2015 at 11:05 am
I went with several recommendations which were.
1. With RECOMPILE
2. Replaced splitter with DelimitedSplit8K
3. Went with INTO #TempFL vs Creating Table #TempFL
And last but not least the SQL in the wild article seem to help the most as i was able to :
4. Implent Dynamic SQL
I ran my load test again for 100 users running search queries for 4 minutes straight and my Timeout errors are gone.
I believe Im good for now, i will be doing a load test for 500 users and see how that goes.
April 8, 2015 at 12:09 pm
April 8, 2015 at 12:22 pm
I still received timeout errors after i made changes based on the suggestions i was given.
Even after i replaced my splitter with DelimitedSplit8K I still did receive timeout errors.
But when i stopped receiving timeout errors was when i used dynamic sql on my stored procedure.
My stored procedure looks like this now.
ALTER PROCEDURE [dbo].[SearchSchoolData]
@Statenvarchar(300) = '',
@LearningMethodnvarchar(100) = '',
@AccreditationTypenvarchar(100) = '',
@Programsnvarchar(100) = '',
@InstitutionType nvarchar(100) = '',
@DegreeLevelnvarchar(200) = ''
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON
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
SELECT InstitutionID, ISNULL(tblmilitaryTuition.TotalTaEnrollments,'Not Reported') as TaUsers
INTO #MainLocationsTempFL
FROM dbo.FeedEDData
INNER JOIN dbo.Institutions ON dbo.Institutions.OpeidNumber = dbo.FeedEDData.OpeidNumber
OUTER APPLY
(
Select TOP 1 TotalTaEnrollments From dbo.InstitutionMilitaryTuition pa Where pa.OpeidNumber = dbo.FeedEDData.OpeidNumber
) tblmilitaryTuition
WHERE dbo.Institutions.Status IN (SELECT Item FROM dbo.[DelimitedSplit8K]( (SELECT ListOfStatus FROM dbo.ParticipatingInstitutionStatuses ),',') )
DECLARE @sSQL NVARCHAR(2000), @Where NVARCHAR(1000) = '';
SET @sSQL =
'SELECT
InstitutionName
, TradeName
, FeedEDData.City
, FeedEDData.OpeidNumber
, FeedEDData.State
,IsProgramLengthAssociates
,IsProgramLengthBachelors
,IsProgramLengthMastersDoctorate
,IsProgramLengthProfCertification
,IsProgramLengthNonDegree
,IsProgramLengthNonDegree1Year
,IsProgramLengthNonDegree2Year
,IsProgramLengthNonDegree3Year
,IsProgramLengthShortTerm
,#MainLocationsTempFL.TaUsers
,#MainLocationsTempFL.InstitutionID
FROM #MainLocationsTempFL
INNER JOIN dbo.Institutions ON dbo.Institutions.InstitutionID = #MainLocationsTempFL.InstitutionID
INNER JOIN dbo.FeedEDData ON dbo.FeedEDData.OpeidNumber = Institutions.OpeidNumber '
IF ISNULL(@State,'') <> ''
SET @Where = @Where + 'AND FeedEDData.State in (SELECT Item FROM [DelimitedSplit8K](@p1,'','')) '
IF ISNULL(@AccreditationType,'') <> ''
SET @Where = @Where + 'AND AccreditationType in (SELECT Item FROM [DelimitedSplit8K](@p2,'','')) '
IF ISNULL(@ClassroomLearning,'') <> ''
SET @Where = @Where + 'AND Institutions.IsClassroomLearning= @p3 '
IF ISNULL(@DistanceOnlineLearning,'') <> ''
SET @Where = @Where + 'AND Institutions.IsDistanceLearning= @p4 '
IF ISNULL(@CorrespondenceLearning,'') <> ''
SET @Where = @Where + 'AND Institutions.IsCorrespondenceLearning = @p5 '
IF ISNULL(@InstitutionType,'') <> ''
SET @Where = @Where + 'AND FeedEDData.SchType in (SELECT Item FROM [DelimitedSplit8K](@p6,'','')) '
IF ISNULL(@Programs,'') <> ''
SET @Where = @Where + 'AND FeedEDData.OpeidNumber IN (SELECT OpeidNumber FROM dbo.InstitutionIpedsCipCodes WHERE CipCode in (SELECT Item FROM [DelimitedSplit8K](@p7,'',''))) '
IF ISNULL(@DegreeLevel,'') <> ''
BEGIN
IF (@Certificate = 1)
SET @Where = @Where + 'AND (IsProgramLengthProfCertification= 1 OR IsProgramLengthNonDegree= 1 OR IsProgramLengthNonDegree1Year= 1 OR IsProgramLengthNonDegree2Year= 1 OR IsProgramLengthNonDegree3Year= 1 OR IsProgramLengthShortTerm=1) '
IF (@Associate = 1)
SET @Where = @Where + 'AND IsProgramLengthAssociates =1 '
IF (@Bachelor = 1)
SET @Where = @Where + 'AND IsProgramLengthBachelors =1 '
IF (@Master = 1)
SET @Where = @Where + 'AND IsProgramLengthMastersDoctorate =1 '
END
IF LEN(@Where) > 0
SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-2)
EXEC sp_executesql @sSQL, N'@p1 nvarchar(300), @p2 nvarchar(100), @p3 nvarchar(100), @p4 nvarchar(100), @p5 nvarchar(100), @p6 nvarchar(100), @p7 nvarchar(100)',@p1 = @State, @p2 = @AccreditationType,@p3 = @ClassroomLearning,@p4 = @DistanceOnlineLearning,@p5 = @CorrespondenceLearning,@p6 = @InstitutionType,@p7 = @Programs
DROP TABLE #MainLocationsTempFL
SET NOCOUNT OFF
END
GO
April 9, 2015 at 12:54 am
Cool. Just a note. Because you have used the dynamic-sql method, there is no need for with recompile. I heard Gail mention that she would use the with recompile only if she did not have time to do d-sql. But since you have dsql, you will have plans created for each parameter permutation that has been executed, making with recompile redundant and slightly more costly.
April 9, 2015 at 7:29 am
Just a suggestion. I would avoid using ambiguous parameter names. @p1, @p2 are meaningless. Give them some context so that maintenance on this down the road is a lot simpler. It takes only a few seconds today and will save several hours of smashing your head (or the person after you) against the wall later.
_______________________________________________________________
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 9, 2015 at 9:49 am
Suggestions were applied. I removed RECOMPILE and renamed the @p parameters to meaningful names.
Also, i ran a load test yesterday with 500 users putting a load on a web page that calls this stored procedure.
The load test ran from 5 computers all running 100 users.
The initial test begins with 1 user which increments and by the 5th minute of the 10 minute test all 500 users were loaded.
So for 5 minutes straight i am putting a load on a search page.
No sql timeout errors.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply