Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select Large Data From Multiple tables Expand / Collapse
Author
Message
Posted Saturday, May 10, 2014 12:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:52 AM
Points: 64, Visits: 206
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?
Post #1569495
Posted Saturday, May 10, 2014 3:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 2,533, Visits: 7,101
Have you looked at Phil Factor's article on string search?
Post #1569500
Posted Saturday, May 10, 2014 5:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 1:52 AM
Points: 64, Visits: 206
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+')'
)

--PRINT
-- (
-- '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
Post #1569515
Posted Tuesday, May 13, 2014 4:45 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 2,330, Visits: 3,509
Full-text indexing would likely help you the most here.

SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1570582
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse