New full-text search queries

InstantASP Community Forums

Home

Members

Calendar

Who's On

Welcome Guest ( Login | Register )

New full-text search queries

Rate Topic

Display Mode

Topic Options

Author

Message

Drew Black

Posted 27/02/2006 21:50:16

Forum Newbie

Group: Customers
Last Login: 15/06/2006 15:30:05
Posts: 19, Visits: 95

I built a fast full-text search feature for my forum a couple of years ago. I started porting it to IF earlier today. It's mostly done but I'm not proficient with .NET and don't know the IF source very well yet. Here's my numbers and my source. (At the moment I think this only works from the advanced search page.) Also, my code doesn't support the Rank feature. I don't know how important/relevant rank is. I might be able to tweak it too support ranking if that's important.

Searching against a database of 691,000 posts in all forums, all dates.

I tested each search term twice and not in order. The same words were used for both freetext and contains tests. Also the same set was used to test my code vs. IF code. The IF code returns more results on some test terms so that would need to be investigated as well. The database server used is not dedicated to this application so the numbers vary a bit due to load. The numbers quoted are from the top of the search results page.

Freetext results:
my code:
8 character single word term - 135 topics returned in 0.86 seconds first attempt /.53 seconds second attempt
7 character single word term - 315 topics in 6.256/2.856
4 character single word - 260 topics in 2.733/2.403

IF code:
8 character single word term - 135 topics returned in 31.003 seconds first attempt /27.9 seconds
7 character single word term - 375 topics in 45.210/35.640
4 character single word - 314 topics in 36.730/65.98

Contains results:
my code:
8 character single word term - 136 topics returned in 0.770 seconds first attempt /2.9 seconds
7 character single word term - 315 topics in 7.583/3.640
4 character single word - 260 topics in 1.746/1.676

IF code:
8 character single word term - 136 topics returned in 93.860 seconds first attempt /31.75 seconds
7 character single word term - 393 topics in 41.626/29.170
4 character single word - 336 topics in 24.993/33.876

So, it's a bit faster. How do I do it? SQL server is much more efficient when working with sets (even huge sets) of data in derived tables than it is at doing complex joins between many tables.

To use my admittedly sloppy code you have to do three things. 1) Create a user-defined function in your instantforum database. 2) Create a two-column index on InstantForum_topics using ForumID then PostID. (This prevents costly table scans during the full-text query.) 3) edit search.vb in \instantasp.instantforum\instantasp.instantforum\business

Step 1
CREATE function if_fnInstantFullTextSubQuery(@searchtype tinyint=2,
@strSearch nvarchar(1000),@searchwithin tinyint=1 )
returns @returnset table (postid integer not null primary key)
as

/*Enum EnumSearchWithin
EntirePost = 1
TitlesOnly = 2
MessageOnly = 3
Enum EnumSearchType
FreeTextTable = 2
ContainsTable = 3
*/

begin

if @searchtype = 3
begin
if @searchwithin=1 /*entire post*/
begin
/*get title matches */
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid in (select z.postid from Instantforum_topics z where z.postid in
(select x.postID from InstantForum_topics x where contains (x.title,@strSearch)
)))
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid not in (select postid from @returnset ) and
(a.postid in (select z.postid from Instantforum_topics z where (z.postid in
(select x.postID from InstantForum_Topics x where contains (x.description,@strSearch)
)))))

/*get post matches */
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid not in (select postid from @returnset ) and
(a.postid in (select z.postid from Instantforum_topics z where (z.postid in
(select x.postID from InstantForum_messages x where contains (x.message,@strSearch)
)))))
end
if @searchwithin=2 /* titles only */
begin
/*get title matches only*/
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid in (select z.postid from Instantforum_topics z where (z.postid in
(select x.postID from InstantForum_topics x where contains (x.title,@strSearch)
))))
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid not in (select postid from @returnset ) and
(a.postid in (select z.postid from Instantforum_topics z where (z.postid in
(select x.postID from InstantForum_topics x where contains (x.title,@strSearch)
)))))

end
if @searchwithin=3 /* message only */
begin
/*get post matches only */
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid in (select z.postid from Instantforum_topics z where (z.postid in
(select x.postID from InstantForum_messages x where contains (x.message,@strSearch)
))))
end
end
if @searchtype=2
/* freetext section */
begin
if @searchwithin=1 /*entire post*/
begin
/*get title matches */
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid in (select z.postid from Instantforum_topics z where z.postid in
(select x.postID from InstantForum_topics x where freetext (x.title,@strSearch)
)))
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid not in (select postid from @returnset ) and
(a.postid in (select z.postid from Instantforum_topics z where (z.postid in
(select x.postID from InstantForum_Topics x where freetext (x.description,@strSearch)
)))))

/*get post matches */
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid not in (select postid from @returnset ) and
(a.postid in (select z.postid from Instantforum_topics z where (z.postid in
(select x.postID from InstantForum_messages x where freetext (x.message,@strSearch)
)))))
end
if @searchwithin=2 /* titles only */
begin
/*get title matches only*/
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid in (select z.postid from Instantforum_topics z where (z.postid in
(select x.postID from InstantForum_topics x where freetext (x.title,@strSearch)
))))
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid not in (select postid from @returnset ) and
(a.postid in (select z.postid from Instantforum_topics z where (z.postid in
(select x.postID from InstantForum_topics x where freetext (x.title,@strSearch)
)))))

end
if @searchwithin=3 /* message only */
begin
/*get post matches only */
insert into @returnset SELECT DISTINCT a.postID FROM InstantForum_Topics a
where (a.postid in (select z.postid from Instantforum_topics z where (z.postid in
(select x.postID from InstantForum_messages x where freetext (x.message,@strSearch)
))))
end
end

return
end

Step 2
CREATE INDEX [ix_forumID_postID] ON [dbo].[InstantForum_Topics]([ForumID], [PostID]) ON [PRIMARY]

(I actually have all of my non-clustred indexes stored on a separate volume so my create index statement is slightly different.)

Step 3

Edit Shared Function ConstructManualSelectStatement in search.vb. Since it's not my code to be posting I won't post the whole function here. (I'd be happy to make it available to Ryan though.) The general idea is:

Case Enumerations.EnumSearchWithin.TitlesOnly
' search topic title index only
' Tables.Append(", CONTAINSTABLE(InstantForum_Topics, *, '" & Keywords & "', " & ntFullTextResultLimit.ToString() & ") AS Topic")
' add the full text key to the clustered post key
'WhereClause.Append("(Topic.[Key] = InstantForum_Messages.PostID) AND ")
WhereClause.Append(" instantforum_messages.postid in (select * from if_fnInstantFullTextSubQuery(3,'" & Keywords & "',2)) and ")

There are more changes to the function call but this gives you an idea of how the user-defined SQL function call is used to perform the full-text subquery.

My code isn't elegant, it's more of a proof of concept that I hoped could be integrated into a future version of IF. With this type of change in IF then we might be able to roll it out of testing mode and onto a live production server.

Post #10211

Ryan Healey

Posted 01/03/2006 08:00:16

IF.NET 4.2 Coming Soon

Group: Administrators
Last Login: 13/11/2008 13:12:14
Posts: 1,955, Visits: 3,014

Hi Drew,

Hope your well. Many thanks for sharing your code and taking time to look at this.

We will certainly investigate this for future versions. I'm particularly interested in the performance gains you've managed with your method.

Would it be possible for you to send your code via private message or over MSN to sales@instantasp.co.uk I'd certainly like to investigate and test further.

If everything works well and we don't have issues with user defined functions within shared hosting environments we'll certainly look at using this method in future versions. I doubt this will be in place for v4.1.4 as it will require some testing. Of course if we were to use your method we will of reward you some how for your suggestion Wink