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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy