May 10, 2014 at 12:59 am
In a Library Management database we have these tables
1) Document ( DocNo , Doc_type , permalink,inDate)
2)Title(id, DocNo,Main_Title, Other_Title)
3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....)
4)Publisher(id,DocNo , Name,Publisedate,address)
5)Subject(id,DocNo,Subject)
6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc
In document table I have 500,000 records.
I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc.
how can I do this with best performance?
May 10, 2014 at 3:21 am
May 10, 2014 at 5:59 am
I Use this sp but execution time is very high--more than 10 min--
CREATE PROC [dbo].[SearchAllTables2]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
SET @SearchStr = dbo.fnChangeChar(@SearchStr);
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630),DocNo nvarchar(3630))
--CREATE TABLE Temporary( ColumnName nvarchar(370), ColumnValue nvarchar(3630),DocNo nvarchar(3630))
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(2000)
DECLARE @SearchStr_two2 nvarchar(2000),@SearchStr_three2 nvarchar(2000)
SET @TableName = ''
SET @SearchStr = '"' + @SearchStr + '*"';
SET @SearchStr2 = @SearchStr
SET @SearchStr2 = QUOTENAME(@SearchStr2,'''')
PRINT @SearchStr2
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
AND @TableName IN ('[dbo].[Header]','[dbo].[Publisher],
'[dbo].[Subjects]','[dbo].[Title]','[dbo].[Description1]')
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
,'+@TableName + '.DocNo' +' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE CONTAINS( ' + @ColumnName + ' , ' + @SearchStr2+')'
)
-- (
-- 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
-- ,'+@TableName + '.DocNo' +' FROM ' + @TableName + ' (NOLOCK) ' +
-- ' WHERE CONTAINS( ' + @ColumnName + ' , ' + @SearchStr2 +' ' + ' '+
-- +')'
--)
END
END
END
SELECT ROW_NUMBER() OVER(ORDER BY [Document].DocNo DESC) AS Row,
[Document].DocNo AS DocNo,
[Document].DocType,
Description1.Price,
Description1.ISBN,
Description1.description,
Description1.Vazhe,
Author.WriterName,
Author.WriterFamily,
Author.Born AS HBorn,
Author.Death AS HDeath,
Publisher.PublisherName,
Publisher.PublishedPlace,
Publisher.PublishedDate,
Subjects.SubjectF,
Title.Title,
Title.TitleF,
Title.TitleL,
FROM [Document] LEFT OUTER JOIN
Description1 ON [Document].DocNo = Description1.DocNo LEFT OUTER JOIN
AuthorON [Document].DocNo = Author.DocNo LEFT OUTER JOIN
Publisher ON [Document].DocNo = Publisher.DocNo LEFT OUTER JOIN
Subjects ON [Document].DocNo = Subjects.DocNo LEFT OUTER JOIN
Title ON [Document].DocNo = Title.DocNo
WHERE document.DocNo
IN(
SELECT document.DocNo FROM Document INNER JOIN #Results ON #Results.DocNo=document.DocNo COLLATE DATABASE_DEFAULT
)
END
May 13, 2014 at 4:45 pm
Full-text indexing would likely help you the most here.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply