September 2, 2008 at 1:18 am
Dear All,
Before getting into the problem, I would like to explain the structure of my Application DB.
I am having a table in which the type of the knowledge repository is stored and for each type a dynamic table will be created (as the properties of each repository's content will vary).
In the dynamic table, my application is going to store the documents related to it and the properties of the repository's document will be stored as a field in the dynamic table.
Apart from this the general information of the documents are stored in different tables.
Now when the user is performing the search, the search part of the application is calling a procedure.
The search procedure has a cursor to iterate the repositories and based on the repository's dynamic table it is framing a Dynamic SQL and executes and has the result in a temporary table.
Then returns the result from the temporary table to the front end.
Now arise the issue:
The query is executed very slow, if some other operations are being done in SQL at the same time, the response is approx from 5 min.
I believe, this is because of cursor and dynamic SQL. But I don't know the way to improve it.
The above case is when the record or document count is above 100,000
I also want to know how to load the index in memory on demand.
This also consumes lots of memory and I couldn't find a way to release the memory or allocate the specific memory for this process always.
Kindly help me in this regard.
Thanks a lot in advance.
[font="Arial"]Nothing is impossible with Hard Work[/font]:)
September 2, 2008 at 2:07 am
If you can post the code, I'm sure someone will be able to help you remove the cursor.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 2, 2008 at 3:10 am
Dear Friends,
Below is my code:
[font="Arial"]CREATE PROCEDURE [dbo].[vsp_SearchDocuments1]
/****************************************************************************************/
--Name: vsp_SearchDocuments1
--Desc: To search the knowledge base
/****************************************************************************************/
-- Add the parameters for the stored procedure here
@vcextn varchar(max),
@biguserID bigint
AS
BEGIN
--Declare variable for Dynamic Table Name
--Set NoCount On
DECLARE @vcTableName varchar(11)
DECLARE @errMsg nvarchar(255)
DECLARE @tempTablename varchar(20)
DECLARE @tempVersionTablename varchar(20)
DECLARE @nvcsql nvarchar(MAX)
DECLARE @nvcverisonsql nvarchar(MAX)
DECLARE @uiRepID nvarchar(50)
DECLARE @profilequery nvarchar(4000)
DECLARE @username varchar(50)
DECLARE databaseCursor CURSOR forward_only for
select RepID from DatabaseRights where UserId = @biguserID
-- Create new Temp Table based on UserLogin
SET @tempTablename = 'TempTable' + CAST (@biguserID as nvarchar(10))
SET @tempVersionTablename = 'TempVersionTable'+CAST(@biguserID as nvarchar(10))
IF EXISTS (SELECT * FROM SYS.Objects where name like @tempTablename )
BEGIN
SET @nvcsql = 'DROP Table ' + @tempTablename
EXEC ( @nvcsql )
END
IF EXISTS (SELECT * FROM SYS.Objects where name like @tempVersionTablename )
BEGIN
SET @nvcsql = 'DROP Table ' + @tempVersionTablename
EXEC ( @nvcsql )
END
BEGIN TRY
PRINT GetDate()
EXEC @username = [fn_GetUserNameById] @biguserID
-- To get the query for dynamic profile fields
PRINT GetDate()
EXEC @profilequery = GetFields 'D',@username
PRINT GetDate()
-- Replaces the standard filters.
SET @profilequery = @profilequery + ','
SET @profilequery = Replace(@profilequery,'D.[DocumentTitle],','')
SET @profilequery = Replace(@profilequery,'D.[FileSize],','')
SET @profilequery = Replace(@profilequery,'D.[FileDate],','')
SET @profilequery = Replace(@profilequery,'D.[FileExtID],','')
SET @profilequery = Replace(@profilequery,'D.[DocmentID]','')
SET @profilequery = Replace(@profilequery,'D.[DisplayFileName],','')
SET @profilequery = Replace(@profilequery,'D.[FileName],','')
PRINT GetDate()
IF LEN(@profilequery) > 0
BEGIN
SET @profilequery = substring(@profilequery ,1, Len(@profilequery)-1)
END
PRINT GetDate()
OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @uiRepID
PRINT GetDate()
-- Iterates the data in the cursor
WHILE @@fetch_status = 0
BEGIN
EXEC @vcTableName = GetDynamicTableName @uiRepID
IF (EXISTS (SELECT * FROM Sys.objects WHERE [Name] like @tempTablename))
BEGIN
SET @nvcsql='INSERT INTO '+ @tempTablename +' SELECT w.RevNo, w.DocGupType, w.Release, w.pathOfDoc, '
+ ' w.RepPath, w.RepFolder, w.Revised, w.DocType, v.vno, '
+ ' v.RepID, D.DocmentID as DocID, case v.Approved when 1 then ''Yes'' '
+ ' else ''No'' end as checked, ' + @profilequery + (case WHEN LEN(@profilequery)>0 THEN ',' ELSE ''END) + ' dt.[Name], v.Comments '
+ ', '''+ @vcTableName +''' as DynamicTableName , D.[DocumentTitle], '
+ ' D.[ProjectName], D.[FileSize] , D.[FileDate] , D.[FileExtID], '
+ ' D.[DocmentID] , D.[DisplayFileName] , D.[FileName] , D.[LastUsed] , '
+ ' v.RootID from ' + @vcTableName
+ ' D inner join w_DocumentData w on w.DocmentID = D.DocmentID'
+ ' inner join w_DocumentType dt on w.DocType = dt.ID '
+ ' inner join w_Documentversion v on w.DocmentID = v.DocmentID WHERE '
+ ' D.DocmentID is not null and d.DocmentID not in (select parentDocmentID from '
+ ' w_documentversion where parentDocmentID is not null ) and v.markedfordeletion = 0'
+ ' and D.FileExtID in(' + @vcextn + ')'
END
ELSE
BEGIN
SET @nvcsql = 'SELECT w.RevNo, w.DocGupType, w.Release, w.pathOfDoc, '
+ ' w.RepPath, w.RepFolder, w.Revised, w.DocType, v.vno, '
+ ' v.RepID, D.DocmentID as DocID, (case v.Approved when 1 then ''Yes'' else ''No'' end) as checked, '
+ @profilequery + (case WHEN LEN(@profilequery)>0 THEN ',' ELSE '' END) + ' dt.[Name], v.Comments '
+ ', '''+ @vcTableName +''' as DynamicTableName , D.[DocumentTitle], '
+ ' D.[ProjectName], D.[FileSize] , D.[FileDate] , D.[FileExtID], '
+ ' D.[DocmentID] , D.[DisplayFileName] , D.[FileName] , D.[LastUsed] , '
+ ' D.[MarkUser], v.RootID into ' + @tempTablename
+ ' from ' + @vcTableName
+ ' D inner join w_DocumentData w on w.DocmentID = D.DocmentID'
+ ' inner join w_DocumentType dt on w.DocType = dt.ID '
+ ' inner join w_Documentversion v on w.DocmentID = v.DocmentID WHERE '
+ ' D.DocmentID is not null and d.DocmentID not in (select parentDocmentID from '
+ ' w_documentversion where parentDocmentID is not null ) and v.markedfordeletion = 0'
+ ' and D.FileExtID in(' + @vcextn + ')'
END
PRINT @nvcsql
EXEC (@nvcsql)
IF ( NOT EXISTS (SELECT * FROM Sys.objects WHERE [Name] like @tempVersionTablename))
BEGIN
SET @nvcverisonsql = 'SELECT w.RevNo, w.DocGupType, w.Release, w.pathOfDoc, '
+ ' w.RepPath, w.RepFolder, w.Revised, w.DocType, v.vno, '
+ ' v.RepID, D.DocmentID as DocID, (case v.Approved when 1 then ''Yes'' else ''No'' end) as checked, '
+ @profilequery + (case WHEN LEN(@profilequery)>0 THEN ',' ELSE '' END) + ' dt.[Name], v.Comments '
+ ', '''+ @vcTableName +''' as DynamicTableName , D.[DocumentTitle], '
+ ' D.[ProjectName], D.[FileSize] , D.[FileDate] , D.[FileExtID], '
+ ' D.[DocmentID] , D.[DisplayFileName] , D.[FileName] , D.[LastUsed] , '
+ ' D.[MarkUser], v.RootID into ' + @tempVersionTablename
+ ' from ' + @vcTableName
+ ' D inner join w_DocumentData w on w.DocmentID = D.DocmentID'
+ ' inner join w_DocumentType dt on w.DocType = dt.ID '
+ ' inner join w_Documentversion v on w.DocmentID = v.DocmentID WHERE '
+ ' D.DocmentID is not null and d.DocmentID not in (select DocmentID from '
+ @tempTablename + ' ) and v.RootID in (SELECT RootID FROM '
+ @tempTablename + ' ) and v.markedfordeletion = 0'
+ ' and D.FileExtID in(' + @vcextn + ')'
END
ELSE
BEGIN
SET @nvcverisonsql = 'INSERT INTO ' + @tempVersionTablename +' SELECT w.RevNo, w.DocGupType, w.Release, w.pathOfDoc, '
+ ' w.RepPath, w.RepFolder, w.Revised, w.DocType, v.vno, '
+ ' v.RepID, D.DocmentID as DocID, (case v.Approved when 1 then ''Yes'' else ''No'' end) as checked, '
+ @profilequery + (case WHEN LEN(@profilequery)>0 THEN ',' ELSE '' END) + ' dt.[Name], v.Comments '
+ ', '''+ @vcTableName +''' as DynamicTableName , D.[DocumentTitle], '
+ ' D.[ProjectName], D.[FileSize] , D.[FileDate] , D.[FileExtID], '
+ ' D.[DocmentID] , D.[DisplayFileName] , D.[FileName] , D.[LastUsed] , '
+ ' D.[MarkUser], v.RootID from ' + @vcTableName
+ ' D inner join w_DocumentData w on w.DocmentID = D.DocmentID'
+ ' inner join w_DocumentType dt on w.DocType = dt.ID '
+ ' inner join w_Documentversion v on w.DocmentID = v.DocmentID WHERE '
+ ' D.DocmentID is not null and d.DocmentID not in (select DocmentID from '
+ @tempTablename + ' ) and v.RootID in (SELECT RootID FROM '
+ @tempTablename + ' ) and v.markedfordeletion = 0'
+ ' and D.FileExtID in(' + @vcextn + ')'
END
PRINT @nvcverisonsql
EXEC (@nvcverisonsql)
FETCH NEXT FROM databaseCursor INTO @uiRepID
END
DEALLOCATE databaseCursor
IF EXISTS (SELECT * FROM Sys.objects where name like @tempTablename)
BEGIN
SET @nvcsql = 'alter table '+ @tempTablename +' add RowID bigint identity (1,1)'
EXEC (@nvcsql)
SET @nvcsql = 'SELECT * FROM ' + @tempTablename
EXEC (@nvcsql)
SET @nvcsql = 'select count(*) as RecordCount from '+ @tempTablename +' '
EXEC (@nvcsql)
END
IF ( EXISTS (SELECT * FROM Sys.objects WHERE [Name] like @tempVersionTablename))
BEGIN
SET @nvcsql = 'Select * FROM ' + @tempVersionTablename
EXEC ( @nvcsql )
END
END TRY
BEGIN CATCH
SET @errMsg = 'Error message : ' + error_message()
deallocate databasecursor
RAISERROR (@errMsg, 16, 1 )
END CATCH
END[/font]
[font="Arial"]Nothing is impossible with Hard Work[/font]:)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply