SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select Large Data From Multiple tables


Select Large Data From Multiple tables

Author
Message
Eskandari
Eskandari
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 210
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?
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15248 Visits: 18607
Have you looked at Phil Factor's article on string search?
Cool
Eskandari
Eskandari
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 210
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
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7943 Visits: 7159
Full-text indexing would likely help you the most here.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search