Memory in Windows was grown in strange way ! | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Memory in Windows was grown in strange way !

I used SQL Server 2000 in Windows XP Professional. I write a stored procedure in Query Analyzer which process in about 4 hour to finish. That job was successful, I ran it in Query Analyzer, but, about 2 hour later, I cheked my Windows by Task Manager and detected that Windows has been used a large memory (about over 900 Mb), Windows worked so slowly and it was able to affect to other program when I wanted to open. I previewed my source code in my stored procedure and not able to find some "problems" because I allocated a new cursor and deallocated it in the right way ! Please help me !. You can see my source code her !<br /><br />ALTER PROCEDURE AnalyzeString<br /><br />(<br /> @Str1 nvarchar(2000),<br />@Str2 nvarchar(2000)<br />)<br /><br />AS<br /><br />Declare @i Int<br />Declare @j Int<br /><br />Select @Str1 = Convert(nvarchar(2000), @Str1)<br />Select @Str2 = Convert(nvarchar(2000), @Str2)<br /><br />– Xu ly @Str1<br />Delete From SeperateString1<br />Select @i = 1<br />Select @j = 1<br /><br />While @i &lt;= LEN(@Str1)<br />Begin<br />If Len(@Str1) = 1<br />Begin<br />Begin Transaction<br />INSERT INTO SeperateString1 (String1) VALUES (@Str1)<br />Commit Transaction<br />– GoTo ProcessSTring2<br />End<br /><br />If (SUBSTRING(@Str1, @i, 1) = ‘ ‘) And (@j &gt; 1) And (Len(@Str1) &gt; 1)<br />Begin<br />If (SUBSTRING(@Str1, @i + 1, 1) &lt;&gt; ‘ ‘)<br />Begin<br />Begin Transaction<br />INSERT INTO SeperateString1 (String1) VALUES (RTRIM(SUBSTRING(@Str1, @j + 1, @i – @j)))<br />Commit Transaction<br />Select @j = @i<br />End<br /> End<br /><br />If (SUBSTRING(@Str1, @i, 1) = ‘ ‘) And (@j = 1) And (Len(@Str1) &gt; 1)<br />Begin<br />If (SUBSTRING(@Str1, @i + 1, 1) &lt;&gt; ‘ ‘)<br />Begin<br />Begin Transaction<br />INSERT INTO SeperateString1 (String1) VALUES (RTRIM(SUBSTRING(@Str1, @j, @i – @j)))<br />Commit Transaction<br />Select @j = @i<br />End<br /> End<br /><br />If (@i = Len(@Str1)) And (@j &gt; 1) And (Len(@Str1) &gt; 1)<br />Begin<br />Begin Transaction<br />INSERT INTO SeperateString1 (String1) VALUES (LTRIM(Right(@Str1, Len(@Str1) – @j)))<br />Commit Transaction<br /> End<br /><br />/*<br />If (@i = Len(@Str1)) And (@j = 1) And (Len(@Str1) &gt; 1)<br />Begin<br />INSERT INTO SeperateString1 (String1) VALUES (LTRIM(Right(@Str1, Len(@Str1) – @j) + 1))<br /> End<br />*/<br /><br />Select @i = @i + 1<br />End<br /><br />– Xu ly @Str2<br />– ProcessSTring2 :<br /><br />Delete From SeperateString2<br />Select @i = 1<br />Select @j = 1<br /><br />While @i &lt;= LEN(@Str2)<br />Begin<br />If Len(@Str2) = 1<br />Begin<br />Begin Transaction<br />INSERT INTO SeperateString2 (String2) VALUES (@Str2)<br />Commit Transaction<br />End<br /><br />If (SUBSTRING(@Str2, @i, 1) = ‘ ‘) And (@j &gt; 1) And (Len(@Str2) &gt; 1)<br />Begin<br />If (SUBSTRING(@Str2, @i + 1, 1) &lt;&gt; ‘ ‘)<br />Begin<br />Begin Transaction<br />INSERT INTO SeperateString2 (String2) VALUES (RTRIM(SUBSTRING(@Str2, @j + 1, @i – @j)))<br />Commit Transaction<br />Select @j = @i<br />End<br /> End<br /><br />If (SUBSTRING(@Str2, @i, 1) = ‘ ‘) And (@j = 1) And (Len(@Str2) &gt; 1)<br />Begin<br />If (SUBSTRING(@Str2, @i + 1, 1) &lt;&gt; ‘ ‘)<br />Begin<br />Begin Transaction<br />INSERT INTO SeperateString2 (String2) VALUES (RTRIM(SUBSTRING(@Str2, @j, @i – @j)))<br />Commit Transaction<br />Select @j = @i<br />End<br /> End<br /><br />If (@i = Len(@Str2)) And (@j &gt; 1) And (Len(@Str2) &gt; 1)<br />Begin<br />Begin Transaction<br />INSERT INTO SeperateString2 (String2) VALUES (LTRIM(Right(@Str2, Len(@Str2) – @j)))<br />Commit Transaction<br /> End<br /><br />/*<br />If (@i = Len(@Str2)) And (@j = 1) And (Len(@Str2) &gt; 1)<br />Begin<br />INSERT INTO SeperateString2 (String2) VALUES (LTRIM(Right(@Str2, Len(@Str2) – @j) + 1))<br /> End<br />*/<br /><br />Select @i = @i + 1<br />End<br /><br />______________________________<br /><br />ALTER PROCEDURE CompareString<br /><br />(<br /> @Str1 nvarchar(2000),<br />@Str2 nvarchar(2000),<br />@Result int Output<br />)<br /><br />AS<br /><br />Declare @i Int<br />Declare @Count Int<br /><br />Select @Result = 0<br />Select @Count = 0<br />Select @i = 1<br /><br />If (Len(@Str1) &lt;&gt; Len(@Str2)) Or (Len(@Str1) = 0 Or Len(@Str2) = 0)<br />Begin<br />Select @Result = 0<br />– Return 0<br />End<br /><br />If (Len(@Str1) = Len(@Str2)) And (Len(@Str1) &gt; 3)<br />Begin<br />While @i &lt;= len(@Str1)<br />Begin<br />If SUBSTRING(@Str1, @i, 1) &lt;&gt; SUBSTRING(@Str2, @i, 1)<br />Begin<br />Select @Count = @Count + 1<br />End<br />Select @i = @i + 1<br />End<br /><br />If @Count &lt;= 1<br />Select @Result = 1<br />– Return 1<br />Else<br />Select @Result = 0<br />– Return 0<br />Print ‘let here !'<br />End<br />Else<br />Begin<br />Select @Result = 0<br />End<br /><br />___________________________________<br /><br />ALTER PROCEDURE ExecCompareTable<br />AS<br /><br />DECLARE @ID nvarchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, @Name nvarchar(2000), @Kind nvarchar(2), @City nvarchar(100),@Address nvarchar(2000)<br />DECLARE @IDCI nvarchar(10), @NameCI1 nvarchar(2000), @NameCI2 nvarchar(2000), @CityCI nvarchar(100),@AddressCI nvarchar(225), @POBoxCI nvarchar(10), @FileNameCI nvarchar(225)<br />Declare @String1 nvarchar(100), @String2 nvarchar(100)<br />Declare @KetQua int<br />Declare @ResCol1 nvarchar(3900), @ResCol2 nvarchar(500), @ResCol3 nvarchar(500), @ResCol4 nvarchar(500), @ResCol5 nvarchar(100)<br />Declare @ResultReturn int<br />DECLARE Sec_Cursor CURSOR FOR <br />SELECT ID, Name,Kind, City, Address FROM FindCompany.dbo.SecurityDetail<br />OPEN Sec_Cursor<br /><br />FETCH NEXT FROM Sec_Cursor INTO @ID, @Name, @Kind, @City, @Address <br />WHILE @@FETCH_STATUS = 0 <br />BEGIN <br />– Bat dau vong lap CI_Cursor<br />DECLARE CI_Cursor CURSOR FOR <br />SELECT ID, Name1, Name2, City, Address, POBox, FileName FROM FindCompany.dbo.CompanyInfo<br />OPEN CI_Cursor <br /><br />FETCH NEXT FROM CI_Cursor INTO @IDCI, @NameCI1, @NameCI2, @CityCI, @AddressCI, @POBoxCI, @FileNameCI<br />WHILE @@FETCH_STATUS = 0 <br />BEGIN <br /><br />– So sanh Name1 và Name<br />– Phan tich 2 chuoi Name va Name1<br /><br />If (@Name &lt;&gt; ” And @NameCI1 &lt;&gt; ”)<br />Begin<br />Exec AnalyzeString @Name, @NameCI1<br />End<br /><br />DECLARE ST1_Cursor CURSOR FOR <br />SELECT String1 FROM FindCompany.dbo.SeperateString1<br />OPEN ST1_Cursor<br /><br />FETCH NEXT FROM ST1_Cursor INTO @String1<br />WHILE @@FETCH_STATUS = 0<br />Begin<br /><br />DECLARE ST2_Cursor CURSOR FOR <br />SELECT String2 FROM FindCompany.dbo.SeperateString2<br />OPEN ST2_Cursor<br /><br />FETCH NEXT FROM ST2_Cursor INTO @String2<br />WHILE @@FETCH_STAT US = 0<br />Begin<br /><br />Exec CompareString @String1, @String2, @Result = @KetQua Output<br /><br />If @KetQua = 1<br />Begin<br />Exec SetCol1 @ID, @Rescol1 = @Rescol1 Output<br />Exec SetCol2 @IDCI, @Rescol2 = @Rescol2 Output<br />Select @ResCol3 = ‘Name: ‘+ Char(13) + Char(10) + @String1 + ‘ &lt;==&gt; ‘ + @String2<br />Select @ResCol4 = 0<br /><br />Select @ResCol5 = GetDate()<br /><br />If (Not Exists(Select * From Result Where IAdata like @ResCol1 And DataFound like @ResCol2 And Reason like @Rescol3)) And (Not Exists(Select * From Dictionary Where Value = @String1)) And (Not Exists(Select * From Dictionary Where Value = @String2))<br />Begin<br />INSERT INTO Result (IAData, DataFound, Reason, Decide, DateNow) VALUES (@ResCol1, @ResCol2, @ResCol3, @ResCol4, @ResCol5)<br />End<br /><br />End<br />FETCH NEXT FROM ST2_Cursor INTO @String2<br />End<br /><br />CLOSE ST2_Cursor<br />DEALLOCATE ST2_Cursor<br /><br />FETCH NEXT FROM ST1_Cursor INTO @String1<br />End <br /><br /><br />CLOSE ST1_Cursor<br />DEALLOCATE ST1_Cursor<br /><br />– Ket thuc so sanh Name1 và Name<br /><br />FETCH NEXT FROM CI_Cursor INTO @IDCI, @NameCI1, @NameCI2, @CityCI, @AddressCI, @POBoxCI, @FileNameCI<br />END<br />– Ket thuc vong lap CI_Cursor<br />CLOSE CI_Cursor<br />DEALLOCATE CI_Cursor<br /><br />FETCH NEXT FROM Sec_Cursor INTO @ID, @Name, @Kind, @City, @Address<br />END<br /><br />CLOSE Sec_Cursor<br />DEALLOCATE Sec_Cursor<br /><br />__________________________________ <br /><br />…… and other !
Are you using Developer edition or Desktop edition? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
I use Microsoft SQL Server 2000, Personal Edition, and so that SQL Query Analyzer version 8.0, I intend that I will create this stored procedure and run it in Visual Basic 6.0 throughtout ADO tech. Thanks for you reply and I hope that I will receive more from you ! I am Tran Quang Phuong
I use Microsoft SQL Server Personal Edition, so that my SQL Query Analyzer in 8.0 version. I intend that after I create my those stored procedures, I will run it in Visual Basic 6.0 throughout ADO tech. Thanks for your reply, I hope that I will receive more from you ! I am Tran Quang Phuong
I use Microsoft SQL Server 2000 and so that, my SQL Query Analyzer in 8.0 version. It run in Microsoft Windows XP. I intend that I will run those stored procs in Visual Basic 6.0 version throughout ADO tech. Thanks for you reply, I hope that I will receive more from you ! I am Tran Quang Phuong
]]>