Find Matching Column in SQL Server Full Text Search

Ask a question

Thursday, October 20, 2011 2:47 PM

0
Hello All,

I've the following query to find the matching column in SQL Server Full text search from a table
```
SELECT 
	CASE	WHEN CONTAINS(S.Name, 'test') THEN 'Name'
		WHEN CONTAINS(S.Street, 'test') THEN 'Street'
		WHEN CONTAINS(S.City, 'test') THEN 'City'
	END AS MatchingColumn
FROM Student S
WHERE CONTAINS((S.Name, S.Street, S.City), 'test')
```
I wanted to know is there a better way of doing this as the query has performance issue for large number of records.

Thanks & Regards

Ramakrishnan S
- Reply
- Quote

All Replies

Friday, October 21, 2011 9:45 AM

0

You may find that this works better:

CREATE TABLE Student(PK INT IDENTITY CONSTRAINT StudentPK PRIMARY KEY,
NAME VARCHAR(20), street VARCHAR(20), City VARCHAR(20))
GO
CREATE FULLTEXT CATALOG victoria AS DEFAULT
GO
CREATE FULLTEXT INDEX ON Student(Name, street, city) KEY INDEX studentPK
GO
SELECT CASE    WHEN NAME.[KEY] is NOT NULL THEN 'Name'
        WHEN Street.[key] is NOT NULL THEN 'Street'
        WHEN City.[key] is NOT NULL   THEN 'City'
    END AS MatchingColumn
FROM Student S
left JOIN CONTAINSTABLE(student, NAME, 'test') AS NAME ON NAME.[key]=S.PK
left JOIN CONTAINSTABLE(student, Street, 'test') AS Street ON Street.[key]=S.PK
left JOIN CONTAINSTABLE(student, City, 'test') AS City ON City.[key]=S.PK

However - how are you going to handle the case when the hit occurs in all columns?
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Reply
- Quote
Friday, October 21, 2011 10:45 AM

0

Hilary Cotter,

I've modified the code as you suggested, still the performance is the same.

For me the performance is the key.

Thanks & Regards

Ramakrishnan S
- Reply
- Quote
Friday, October 21, 2011 10:51 AM

0

what about this?

SELECT CASE    WHEN NAME.[KEY] is NOT NULL THEN 'Name'
        WHEN Street.[key] is NOT NULL THEN 'Street'
        WHEN City.[key] is NOT NULL   THEN 'City'
    END AS MatchingColumn
FROM Student S
JOIN (
SELECT [key], 1 name, NULL street, NULL city   FROM CONTAINSTABLE(student, NAME, 'test')
UNION all
SELECT [key], NULL name, 1 street, NULL city   FROM CONTAINSTABLE(student, street, 'test')
UNION all
SELECT [key], NULL name, NULL street, 1 city   FROM CONTAINSTABLE(student, city, 'test')
) AS NAME ON NAME.[key]=S.PK

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Reply
- Quote

Friday, October 21, 2011 12:05 PM

Hilary Cotter,

The below is my actual problem,

CREATE PROCEDURE [dbo].[dnt_Tenders_Get](
	@SearchText nvarchar(200), 
	@SortColumn nvarchar(100) = '[Source Name]',
	@AscOrDesc varchar(4) = 'ASC',
	@LanguageCode varchar(5)
)
AS
BEGIN
	DECLARE @SqlSearchQuery nvarchar(max)
	SET @SqlSearchQuery = 
	'SELECT	
		T.Tender_Name AS [Tender Name],
		F.Reference_Number AS [Reference Number],
		FD.File_Name AS [Source Name] ,
		CASE WHEN CONTAINS((F.Reference_Number), ''' + @SearchText + ''', LANGUAGE ' + @LanguageCode + ') THEN (SELECT SnippetText FROM dbo.ufn_GS_Snippet_Get(F.Reference_Number, ''' + @SearchText + ''')) + '' ...'' 
			 WHEN CONTAINS((FD.[File_Name]), ''' + @SearchText + ''', LANGUAGE ' + @LanguageCode + ') THEN  (SELECT SnippetText FROM dbo.ufn_GS_Snippet_Get(FD.[File_Name], ''' + @SearchText + ''')) + '' ...'' 
		END AS [Snippet]
	FROM	
		Tenders T
		INNER JOIN Tender_Library_item TLI on T.Tender_ID  = TLI.Tender_ID
		INNER JOIN [File] F ON TLI.[File_ID] = F.[File_ID] 
		INNER JOIN File_Details FD ON F.[File_ID] = FD.[File_ID]
	WHERE	CONTAINS(F.Reference_Number, ''' + @SearchText + ''', LANGUAGE ' + @LanguageCode + ') OR 
			CONTAINS((FD.[File_Name]), ''' + @SearchText + ''', LANGUAGE ' + @LanguageCode + ')
	ORDER BY ' + 
		@SortColumn + ' '+ @AscOrDesc
	--insert into tender result table
	EXEC sp_executesql @SqlSearchQuery
END

From this query what i wanted to achive is

1. I need to select the distinct files from File (Reference_Number) or File_Details (File_Name) tables which matches the search criteria

2. Whenever the text matches, i need to extract part of text(Snippet) from the matching column by calling an UDF.

I've around 2000 records in each table and if i run the above query it took around 1.3 seconds to run.

How can I improve the performance of the above query?

Note:

1. I've tried by removing the CASE WHEN statment and the query took only 40 ms to execute. However, as part of the search result i should retrun the "Snippet" value.

2. i've tried with the following option and it took around 400 ms. However, because of the nvarchar(max) limitation (4000 chars for @SqlSearchQuery) I could not use this(in some other scenarios i need to join more tables). I need to use only "EXEC sp_executesql @SqlSearchQuery" and not "EXEC @SqlSearchQuery"

CREATE PROCEDURE [dbo].[dnt_Tenders_Get](
	@SearchText nvarchar(200), 
	@SortColumn nvarchar(100) = '[Source Name]',
	@AscOrDesc varchar(4) = 'ASC',
	@LanguageCode varchar(5)
)
AS
BEGIN
	DECLARE @SqlSearchQuery nvarchar(max)
	SET @SqlSearchQuery = 
	'SELECT	
		T.Tender_Name AS [Tender Name],
		F.Reference_Number AS [Reference Number],
		FD.File_Name AS [Source Name] ,
		(SELECT SnippetText FROM dbo.ufn_GS_Snippet_Get(F.Reference_Number, ''' + @SearchText + ''')) + '' ...'' AS [Snippet]
	FROM	
		Tenders T
		INNER JOIN Tender_Library_item TLI on T.Tender_ID  = TLI.Tender_ID
		INNER JOIN [File] F ON TLI.[File_ID] = F.[File_ID] 
		INNER JOIN File_Details FD ON F.[File_ID] = FD.[File_ID]
	WHERE	CONTAINS(F.Reference_Number, ''' + @SearchText + ''', LANGUAGE ' + @LanguageCode + ') 

	UNION ALL

	SELECT	
		T.Tender_Name AS [Tender Name],
		F.Reference_Number AS [Reference Number],
		FD.File_Name AS [Source Name] ,
		(SELECT SnippetText FROM dbo.ufn_GS_Snippet_Get(FD.[File_Name], ''' + @SearchText + ''')) + '' ...'' AS [Snippet]
	FROM	
		Tenders T
		INNER JOIN Tender_Library_item TLI on T.Tender_ID  = TLI.Tender_ID
		INNER JOIN [File] F ON TLI.[File_ID] = F.[File_ID] 
		INNER JOIN File_Details FD ON F.[File_ID] = FD.[File_ID]
	WHERE	CONTAINS((FD.[File_Name]), ''' + @SearchText + ''', LANGUAGE ' + @LanguageCode + ') AND
		NOT CONTAINS(F.Reference_Number, ''' + @SearchText + ''', LANGUAGE ' + @LanguageCode + ')
	ORDER BY ' + 
		@SortColumn + ' '+ @AscOrDesc
	--insert into tender result table
	EXEC sp_executesql @SqlSearchQuery
END

Thanks & Regards

Ramakrishnan S

Saturday, October 22, 2011 12:26 PM

0

Now I am confused as this looks quite different from what you showed me before.

First off there is no reason to do dynamic SQL.

What you should do is something like this:

DECLARE @OrderBY VARCHAR(20)='[Tender Name]'
DECLARE @order CHAR(3) ='DESC'

DECLARE @table TABLE(pk INT IDENTITY PRIMARY KEY, [Tender Name] VARCHAR(200),
[Reference Number] VARCHAR(200), [Source Name] VARCHAR(200), Snippet VARCHAR(200))

INSERT INTO @table([Tender Name], [Reference Number], [Source Name], [Snippet])

SELECT
        T.Tender_Name AS [Tender Name],
        F.Reference_Number AS [Reference Number],
        FD.File_Name AS [Source Name] ,
        F.Reference_Number Snippet
    FROM
        Tenders T
        INNER JOIN Tender_Library_item TLI on T.Tender_ID = TLI.Tender_ID
        INNER JOIN [File] F ON TLI.[File_ID] = F.[File_ID]
        INNER JOIN File_Details FD ON F.[File_ID] = FD.[File_ID]
    WHERE    CONTAINS(F.Reference_Number, 'test', LANGUAGE 1033)

    UNION ALL

    SELECT
        T.Tender_Name AS [Tender Name],
        F.Reference_Number AS [Reference Number],
        FD.File_Name AS [Source Name] ,
        FD.[File_Name]
    FROM
        Tenders T
        INNER JOIN Tender_Library_item TLI on T.Tender_ID = TLI.Tender_ID
        INNER JOIN [File] F ON TLI.[File_ID] = F.[File_ID]
        INNER JOIN File_Details FD ON F.[File_ID] = FD.[File_ID]
    WHERE    CONTAINS((FD.[File_Name]), 'test', LANGUAGE 1033) AND
        NOT CONTAINS(F.Reference_Number, 'test', LANGUAGE 1033)

SELECT [Tender Name], [Reference Number], [Source Name], dbo.ufn_GS_Snippet_Get(F.Reference_Number, 'test') FROM @table
ORDER BY CASE
WHEN @orderby='Tender Name' AND @order ='ASC' THEN 1
WHEN @orderby='Reference Number' AND @order ='ASC' THEN 2
WHEN @orderby='Source Name' AND @order ='ASC' THEN 3
ELSE 1
end

I am not exactly sure how to handle the desc order requirement.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Reply
- Quote

Wednesday, October 26, 2011 5:18 AM

Hi Hilary Cotter,

Thanks for the response, the solution was helpful.

However, as you said using the above query we cannot order the data in DESC.

I tweaked the query as shown below to acheive this.

CREATE PROCEDURE [dbo].[Tenders_Get](
	@SearchText nvarchar(200), 
	@SortColumn nvarchar(100),
	@SortOrder varchar(4),
	@LanguageCode int
)
AS
BEGIN
	DECLARE @SearchQuery nvarchar(max)

	DECLARE @SearchResult table(
		[Tender Name]			nvarchar(255),
		[Reference Number]		nvarchar(100),
		[Source Name]			nvarchar(148),
		[Snippet]				nvarchar(max)
	)

	create table #LibrarySearchResult (
		[Tender Name]			nvarchar(255),
		[Reference Number]		nvarchar(100),
		[Source Name]			nvarchar(148),
		[Snippet]				nvarchar(max)
	)

	INSERT INTO #LibrarySearchResult(
		[Tender Name],
		[Reference Number],
		[Source Name],
		[Snippet]
	)
	SELECT	
		T.Tender_Name AS [Tender Name],
		F.Reference_Number AS [Reference Number],
		FD.File_Name AS [Source Name] ,
		(SELECT SnippetText FROM dbo.Get_Snippet(F.Reference_Number, @SearchText)) AS [Snippet]
	FROM	
		Tenders T
		INNER JOIN Tender_Library_item TLI on T.Tender_ID  = TLI.Tender_ID
		INNER JOIN [File] F ON TLI.[File_ID] = F.[File_ID] 
		INNER JOIN File_Details FD ON F.[File_ID] = FD.[File_ID]
	WHERE	CONTAINS(F.Reference_Number, @SearchText, LANGUAGE @LanguageCode) 

	UNION ALL

	SELECT	
		T.Tender_Name AS [Tender Name],
		F.Reference_Number AS [Reference Number],
		FD.File_Name AS [Source Name] ,
		(SELECT SnippetText FROM dbo.Get_Snippet(FD.[File_Name], @SearchText)) AS [Snippet]
	FROM	
		Tenders T
		INNER JOIN Tender_Library_item TLI on T.Tender_ID  = TLI.Tender_ID
		INNER JOIN [File] F ON TLI.[File_ID] = F.[File_ID] 
		INNER JOIN File_Details FD ON F.[File_ID] = FD.[File_ID]
	WHERE	CONTAINS((FD.[File_Name]), @SearchText, LANGUAGE @LanguageCode) AND
		NOT CONTAINS(F.Reference_Number, @SearchText, LANGUAGE @LanguageCode)

	SET @SearchQuery =  
		'SELECT	
			[Tender Name],
			[Reference Number],
			[Source Name],
			[Snippet]
		FROM	
			#LibrarySearchResult
		ORDER BY ' + 
			@SortColumn + ' ' + @SortOrder

	INSERT INTO @SearchResult(
		[Tender Name],
		[Reference Number],
		[Source Name],
		[Snippet]
	) 
	EXEC sp_executesql @SearchQuery

	SELECT * FROM	@SearchResult
END

Please let me know, if we can still improve the performance.

Edited by Ramakrishnan S Wednesday, October 26, 2011 5:19 AM

Wednesday, October 26, 2011 10:05 AM

0
The temp table will force recompilation each time it is run which is not ideal.

I really think the root of your performance problem is the inline function calls here
```
(SELECT SnippetText FROM dbo.Get_Snippet(FD.[File_Name], @SearchText)) AS [Snippet]

You need to move that into the temp table as I originally suggested it.

Can you comment out that function call and see what performance is like?
```
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Reply
- Quote