August 3, 2004 at 5:39 am
Can anypne provide me the tuning tips for this code. Thanks in anticipation
CREATE PROCEDURE dbo.spFixHtml @uiDocumentId uniqueidentifier
AS
BEGIN
-- cursor which fetches the rows flagged as 7
DECLARE curFixHtml CURSOR FOR SELECT nvtext,sentenceid FROM #Sentence WHERE JFlag = 7 FOR UPDATE
DECLARE @vExtractString AS NVARCHAR(1000),
@vFetchedString AS NVARCHAR(1000),
@vExtractHtml AS NVARCHAR(20),
@vExtractValidWord AS NVARCHAR(20),
@vTempString AS NVARCHAR(1000),
@vExtractAttribute AS NVARCHAR(20),
@iEqualPos AS INT,
@iAttrCount AS INT,
@iStartBracketPos AS INT,
@iCount AS INT,
@iRowCount AS INT,
@iEndBracketPos AS INT,
@bIsHtmlTag AS BIT,
@uSentenceID AS UNIQUEIDENTIFIER
OPEN curFixHtml
WHILE(1=1)
BEGIN
FETCH NEXT FROM curFixHtml INTO @vFetchedString,@uSentenceID
IF (@@FETCH_STATUS <> 0) BREAK
SET @iEndBracketPos=0
SET @bIsHtmlTag = 0
SET @vExtractString = ''
WHILE(1=1)
BEGIN
-- Getting position of angular brackets
SET @iStartBracketPos = CHARINDEX('<',@vFetchedString,1)
SET @iEndBracketPos = CHARINDEX('>',@vFetchedString,1)
-- Break if angular brakets not found
IF @iStartBracketPos = 0 and @iEndBracketPos = 0
BEGIN
SET @vExtractString = @vExtractString + @vFetchedString
BREAK
END
IF @iStartBracketPos = 0 or (@iStartBracketPos > @iEndBracketPos and @iEndBracketPos !=0)
SET @iCount = @iEndBracketPos - 1
ELSE
SET @iCount = @iStartBracketPos + 1
-- Takes care not to extract spaces in extracting html tags
WHILE UNICODE(SUBSTRING(@vFetchedString,@iCount,1)) = 32 and @iCount > 0 and @iCount <= LEN(@vFetchedString)
BEGIN
IF @iStartBracketPos = 0 or (@iStartBracketPos > @iEndBracketPos and @iEndBracketPos !=0)
SET @iCount = @iCount - 1
ELSE
SET @iCount = @iCount + 1
END
-- if angular bracket is at start
IF @iCount = 0
BEGIN
SET @vExtractString = @vExtractString + SUBSTRING(@vFetchedString,1,1)
SET @vFetchedString = SUBSTRING(@vFetchedString,2,LEN(@vFetchedString))
CONTINUE
END
-- if angular bracket is at end
IF @iCount > LEN(@vFetchedString)
BEGIN
SET @vExtractString = @vExtractString + SUBSTRING(@vFetchedString,1,LEN(@vFetchedString))
BREAK
END
SET @vExtractHtml = ''
--Extratcing HTML keyword between the angular brackets
WHILE (UNICODE(SUBSTRING(@vFetchedString,@iCount,1)) between 65 and 90 or UNICODE(SUBSTRING(@vFetchedString,@iCount,1)) between 97 and 122 or
SUBSTRING(@vFetchedString,@iCount,1) = '/') and @iCount > 0 and @iCount < = LEN(@vFetchedString)
BEGIN
SET @vExtractHtml = @vExtractHtml + SUBSTRING(@vFetchedString,@iCount,1)
IF @iStartBracketPos = 0 or (@iStartBracketPos > @iEndBracketPos and @iEndBracketPos !=0)
SET @iCount = @iCount - 1
ELSE
SET @iCount = @iCount + 1
END
IF @iStartBracketPos = 0 or (@iStartBracketPos > @iEndBracketPos and @iEndBracketPos !=0)
SET @vExtractHtml = REVERSE(@vExtractHtml)
--If no keyword is found find prepare string to find next angular brackets
IF LEN(@vExtractHtml) = 0
BEGIN
IF @iStartBracketPos = 0 or (@iStartBracketPos > @iEndBracketPos and @iEndBracketPos !=0)
BEGIN
SET @vExtractString = @vExtractString + SUBSTRING(@vFetchedString,1,@iEndBracketPos)
IF @iEndBracketPos < LEN(@vFetchedString)
SET @vFetchedString = SUBSTRING(@vFetchedString,@iEndBracketPos+1,LEN(@vFetchedString))
ELSE
SET @vFetchedString = ''
END
ELSE IF @iEndBracketPos = 0 or (@iStartBracketPos < @iEndBracketPos and @iStartBracketPos !=0)
BEGIN
SET @vExtractString = @vExtractString + SUBSTRING(@vFetchedString,1,@iStartBracketPos)
IF @iStartBracketPos < LEN(@vFetchedString)
SET @vFetchedString = SUBSTRING(@vFetchedString,@iStartBracketPos+1,LEN(@vFetchedString))
ELSE
SET @vFetchedString = ''
END
CONTINUE
END
IF SUBSTRING(@vExtractHtml,1,1) = '/'
BEGIN
SET @vExtractHtml = SUBSTRING(@vExtractHtml,2,(LEN(@vExtractHtml)-1))
SET @vExtractHtml = ltrim(rtrim(@vExtractHtml))
END
SET @vExtractAttribute = ''
IF @iStartBracketPos = 0 or (@iStartBracketPos > @iEndBracketPos and @iEndBracketPos !=0)
BEGIN
SET @iEqualPos = charindex('=',@vFetchedString,1)
SET @iEqualPos = @iEqualPos - 1
WHILE (UNICODE(SUBSTRING(@vFetchedString,@iEqualPos,1)) between 65 and 90 or UNICODE(SUBSTRING(@vFetchedString,@iEqualPos,1)) between 97 and 122 )
and @iEqualPos > 0
BEGIN
SET @vExtractAttribute = @vExtractAttribute + SUBSTRING(@vFetchedString,@iEqualPos,1)
SET @iEqualPos = @iEqualPos - 1
END
END
SET @vExtractAttribute = REVERSE(@vExtractAttribute)
SELECT @iAttrCount=count(keyword) FROM Junk_Lookup WHERE RTRIM(keyword) = @vExtractAttribute and JFlag = 7
--SET @iAttrCount = @@ROWCOUNT
SELECT @iRowCount = count(keyword) FROM Junk_Lookup WHERE RTRIM(keyword) = @vExtractHtml and JFlag = 7
--SET @iRowCount = @@ROWCOUNT
IF @iRowCount = 0 and @iAttrCount <> 0
BEGIN
BREAK
END
-- If HTML keyword found and it is a proper tag with opening and closing angular brackets
IF @iRowCount != 0 and @iEndBracketPos != 0 and @iStartBracketPos < @iEndBracketPos and @iStartBracketPos != 0
BEGIN
--if html keyword found
SET @vExtractValidword = ''
SET @iCount = @iEndBracketPos + 1
IF @iCount > LEN(@vFetchedString)
BEGIN
SET @vExtractString = @vExtractString + SUBSTRING(@vFetchedString,1,@iStartBracketPos - 1)
SET @bIsHtmlTag = 1
BREAK
END
WHILE UNICODE(SUBSTRING(@vFetchedString,@iCount,1)) = 32 and @iCount <= LEN(@vFetchedString)
BEGIN
SET @iCount = @iCount + 1
END
-- checks for valid word that can come next to html keyword
WHILE (UNICODE(SUBSTRING(@vFetchedString,@iCount,1)) between 65 and 90 or UNICODE(SUBSTRING(@vFetchedString,@iCount,1)) between 97 and 122) and
@iCount <= LEN(@vFetchedString)
BEGIN
set @vExtractValidword = @vExtractValidword + SUBSTRING(@vFetchedString,@iCount,1)
set @iCount = @iCount + 1
END
IF not exists (SELECT Jflag FROM Junk_Lookup WHERE validword = @vExtractValidword and JFlag = 7)
BEGIN
SET @vExtractString = @vExtractString + SUBSTRING(@vFetchedString,1,@iStartBracketPos - 1)
SET @vFetchedString = SUBSTRING(@vFetchedString,@iEndBracketPos+1,LEN(@vFetchedString))
SET @bIsHtmlTag = 1
END
ELSE
BEGIN
SET @bIsHtmlTag = 0
BREAK
END
END -- HTML keyword found and no ending brackets
ELSE IF @iRowCount != 0 and @iEndBracketPos = 0
BEGIN
SET @vExtractString = @vExtractString + SUBSTRING(@vFetchedString,1,@iStartBracketPos - 1)
SET @vFetchedString = ''
SET @bIsHtmlTag = 1
CONTINUE
END
-- HTML keyword found and no Opening brackets before the ending brackets
ELSE IF @iRowCount != 0 and ( @iStartBracketPos = 0 or @iStartBracketPos > @iEndBracketPos)
BEGIN
IF @iEndBracketPos < LEN(@vFetchedString)
SET @vFetchedString = SUBSTRING(@vFetchedString,@iEndBracketPos+1,LEN(@vFetchedString))
ELSE
SET @vFetchedString = ''
SET @vExtractString = ''
SET @bIsHtmlTag = 1
END
ELSE --- No HTML keyword is found
BEGIN
IF @iEndBracketPos = 0 or (@iStartBracketPos < @iEndBracketPos and @iStartBracketPos !=0)
BEGIN
SET @vExtractString = @vExtractString + SUBSTRING(@vFetchedString,1,@iStartBracketPos)
IF @iStartBracketPos < LEN(@vFetchedString)
SET @vFetchedString = SUBSTRING(@vFetchedString,@iStartBracketPos+1,LEN(@vFetchedString))
ELSE
SET @vFetchedString = ''
END
ELSE
BEGIN
SET @vExtractString = @vExtractString + SUBSTRING(@vFetchedString,1,@iEndBracketPos)
IF @iEndBracketPos < LEN(@vFetchedString)
SET @vFetchedString = SUBSTRING(@vFetchedString,@iEndBracketPos+1,LEN(@vFetchedString))
ELSE
SET @vFetchedString = ''
END
END
END
IF @iRowCount = 0 and @iAttrCount <> 0
BEGIN
/*INSERT INTO JUNK_SENTENCE(SentenceID,DocumentID,nvText,LineNumber,Paragraph,bCorpus,SigSum,SigAngle,SigID1,SigID2,SigID3,SigID4,Bytes,JFlag) SELECT SentenceID,
DocumentID,nvText,LineNumber,Paragraph,bCorpus,SigSum,SigAngle,SigID1,SigID2,SigID3,SigID4,Bytes,JFlag FROM sentence AS sent WHERE sentenceid = @u_SentenceID and not exists
(SELECT sentenceid FROM junk_sentence AS JunkSent WHERE sent.sentenceID = junkSent.sentenceID )*/
IF @@ERROR <> 0
GOTO OnError
UPDATE #Sentence SET JFlag=7 WHERE CURRENT OF curFixHtml
IF @@ERROR <> 0
GOTO OnError
END
ELSE
BEGIN
IF @bIsHtmlTag = 1
BEGIN
/*INSERT INTO JUNK_SENTENCE(SentenceID,DocumentID,nvText,LineNumber,Paragraph,bCorpus,SigSum,SigAngle,SigID1,SigID2,SigID3,SigID4,Bytes,JFlag) SELECT SentenceID,
DocumentID,nvText,LineNumber,Paragraph,bCorpus,SigSum,SigAngle,SigID1,SigID2,SigID3,SigID4,Bytes,JFlag FROM sentence AS sent WHERE sentenceid = @u_SentenceID and not exists
(SELECT sentenceid FROM junk_sentence AS JunkSent WHERE sent.sentenceID = junkSent.sentenceID )*/
IF @@ERROR <> 0
GOTO OnError
--UPDATE sentence SET nvtext = @v_ExtractString,JFlag=100 WHERE CURRENT OF cur_FixHtml
UPDATE #Sentence SET JFlag=7 WHERE CURRENT OF curFixHtml
IF @@ERROR <> 0
GOTO OnError
END
ELSE
BEGIN
UPDATE #Sentence SET JFlag=100 WHERE CURRENT OF curFixHtml
IF @@ERROR <> 0
GOTO OnError
END
END
END
CLOSE curFixHtml
DEALLOCATE curFixHtml
UPDATE #Sentence SET JFlag = 7 WHERE nvtext like N'ww%'
/*
INSERT INTO JUNK_SENTENCE(SentenceID,DocumentID,nvText,LineNumber,Paragraph,bCorpus,SigSum,SigAngle,SigID1,SigID2,SigID3,SigID4,Bytes,JFlag) SELECT SentenceID,
DocumentID,nvText,LineNumber,Paragraph,bCorpus,SigSum,SigAngle,SigID1,SigID2,SigID3,SigID4,Bytes,JFlag FROM sentence AS sent WHERE nvtext like N'ww%' and not exists
(SELECT sentenceid FROM junk_sentence AS JunkSent WHERE sent.sentenceID = junkSent.sentenceID )
DELETE FROM sentence WHERE nvtext like N'ww%'
*/
IF @@ERROR <> 0
GOTO OnError
GOTO OnExit
OnError:
INSERT INTO LogTable VALUES(@uiDocumentId, 'Error','Transaction failed during fixing of HTML tags on ' + CAST(GETDATE() AS VARCHAR(30)),CAST(GETDATE() AS DATETIME))
RETURN
OnExit:
RETURN
END
GO
August 3, 2004 at 9:17 pm
Take a look at the fine articles in this forum on why it is 'very bad' to use cursors. I haven't looked at your SQL at all other than to see it uses cursors. I would be looking to remove the cursor then see what the state of play is.
August 3, 2004 at 11:34 pm
There are several oppourtunities for improvement but rather than tuning pieces of the procedure, we can probably rewrite it without cursors if you explain what it's doing overall. (I could figure it out but that's tedious.)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply