January 27, 2009 at 3:01 pm
Consider the following script, wherein I create a table, populate it with some sample data, create a full-text index on that table, create an SP which queries said table and index (taking a search string as the input parameter), and then execute the SP.
Whenever I run the script as an entire unit (including the exec of the SP), OR if I run JUST the exec of the SP, for about 15-20 seconds, I get a very 'logical' Rank value for each row.
However anytime AFTER about 15-20 seconds, if I exec the SP, I get logical RANK values ONLY for the rows wherein there are EXACT matches with the parameter search string, the rest of the rows all get a RANK of 0.
There is sample output from the SP at the bottom of the script. I'm wondering if anyone can replicate this behaviour, and more importantly, if anyone knows how to fix it.
Note: it doesn't matter what change_tracking option I choose...
This is SQL Server Version 9.00.3068.00
-- re-runnable script
---- ********************************
---- DROP/CREATE TEST TABLE ***
---- ********************************
IF OBJECT_ID('dbo.cmtQuestion_Test') IS NOT NULL DROP TABLE dbo.cmtQuestion_Test;
CREATE TABLE dbo.cmtQuestion_Test
(
question_id INT IDENTITY(1,1) NOT NULL,
question VARCHAR(1000) NOT NULL
);
ALTER TABLE dbo.cmtQuestion_Test ADD CONSTRAINT pk_cmtQuestion_Test
PRIMARY KEY ( question_id );
INSERT cmtQuestion_Test (question) Values('test question');
INSERT cmtQuestion_Test (question) Values('Can I ask you a test question for a test?');
INSERT cmtQuestion_Test (question) Values('Brett couldnt ask a test question if somebody slapped him upside the head!');
INSERT cmtQuestion_Test (question) Values('Jim Testing. Lorem ipsum dolor sit amet, consectetuer adipiscing elit');
INSERT cmtQuestion_Test (question) Values('Testing this question asking form. Did the question go through?');
INSERT cmtQuestion_Test (question) Values('How much test would a question test test if a question test could test questions?');
INSERT cmtQuestion_Test (question) Values('Testing questions');
INSERT cmtQuestion_Test (question) Values('Hmmmm...how question test are you?');
INSERT cmtQuestion_Test (question) Values('asking a question?');
-- ********************************
-- FULL TEXT INDEXING SETUP ***
-- ********************************
----enable the full-text index/search capability on the server
--EXEC sp_fulltext_database 'enable';
IF EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[cmtQuestion_Test]'))
ALTER FULLTEXT INDEX ON [dbo].[cmtQuestion_Test] DISABLE
GO
/****** Object: FullTextIndex Script Date: 01/26/2009 19:01:25 ******/
IF EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[cmtQuestion_Test]'))
DROP FULLTEXT INDEX ON [dbo].[cmtQuestion_Test]
GO
IF EXISTS (SELECT * FROM sysfulltextcatalogs ftc WHERE ftc.name = N'ftc_cmtQuestion_Tests')
DROP FULLTEXT CATALOG [ftc_cmtQuestion_Tests]
GO
-- create the full-text catalog to store the full-text index(es)
CREATE FULLTEXT CATALOG ftc_cmtQuestion_Tests;
GO
-- create a full-text index on the searchable field
CREATE FULLTEXT INDEX ON c21_ngweb.dbo.cmtQuestion_Test
(
Question
)
KEY INDEX pk_cmtQuestion_Test ON ftc_cmtQuestion_Tests
WITH CHANGE_TRACKING OFF
GO
IF OBJECT_ID('dbo.spcmt_QuestionSearch_test') IS NOT NULL DROP PROC dbo.spcmt_QuestionSearch_test
GO
CREATE PROCdbo.spcmt_QuestionSearch_test
(
@strSearch nvarchar(500)
,@limitint
,@offset int
)
AS
SELECT a.* FROM
(
SELECT
k.[rank]
,q.*
,rank() over
(
ORDER BY
k.[rank] DESC
)RankOf
FROM
cmtQuestion_Test q
INNER JOIN
FREETEXTTABLE(cmtQuestion_Test, question, @strSearch, LANGUAGE 'ENGLISH', 10) k
ON
q.question_id = k.
) a
Where
RankOf Between @Offset+1 and @Limit+@Offset
GO
exec dbo.spcmt_QuestionSearch_test N'test question', 10, 0
/*
IMMEDIATE RESULT of running entire script, then executing the SP anytime in the first 15-20 seconds:
[rank] [question_id] [question] [rankof]
7602Can I ask you a test question for a test?1
7311test question2
7223Brett couldnt ask a test question if somebody slapped him upside the head!3
5976How much test would a question test test if a question test could test questions?4
5205Testing this question asking form. Did the question go through?5
4748Hmmmm...how question test are you?6
4697Testing questions7
2404Jim Testing. Lorem ipsum dolor sit amet, consectetuer adipiscing elit8
2279asking a question?9
RESULT of running the SP anytime AFTER the first 15-20 seconds after running the setup script
[rank] [question_id] [question] [rankof]
4041test question1
2783Brett couldnt ask a test question if somebody slapped him upside the head!2
2782Can I ask you a test question for a test?2
06How much test would a question test test if a question test could test questions?4
05Testing this question asking form. Did the question go through?4
07Testing questions4
09asking a question?4
08Hmmmm...how question test are you?4
04Jim Testing. Lorem ipsum dolor sit amet, consectetuer adipiscing elit4
*/
January 28, 2009 at 12:02 pm
Please excuse my shameless bump here, but I'd be *REALLY* appreciative if someone could take a look at this ... this problem is holding up a major project for one of my employers best customers, and it's making me look like an idiot ...
I included a fully-intact, runnable script which illustrates the problem I'm having, and I'm just asking if someone could help me out by running it on their SS2K5 box and seeing whether the same thing happens for them?
At least if that's the case, I can legitimately ask to open up a trouble ticket with MS and get the heat off for a couple of days 😉
The problem, to reiterate, is that for about 15-20 seconds after I create a full-text index on a table and query it using the fulltexttable object and simple search string, I get 'useful' rankings on all the items in the table that remotely match my search terms.
Then, for no apparent reason, about 15-20 seconds after I create the FTI, my query (via a parameterized SP) ceases to provide anything but a 0 ranking for any row other than the few that contain the EXACT search term (and even those change from what they were in the first 20 seconds) I've specified.
I'd be really really stoked if someone could please just give this script a try, observe the immediate results, and then try running the query/SP about 20-30 seconds later, and seeing whether the previously useful rankings returned have suddenly changed into rankings that are basically useless.
And of course, if anyone has any insights as to WHY this might happen, I'd be very excited to hear them.
TIA,
Brett J. Valjalo
DBA, Cyberitas Technologies
January 29, 2009 at 11:56 am
Wow.
1,000,000 users on the site, and not one person can take a few minutes to help me out with this.
I'll be sure to keep the *amazing* resource that is SQLServerCentral.com in mind in the future ...
February 2, 2009 at 2:10 am
rank question_id question RankOf
4041 test question 1
2783Brett couldnt ask a test question if somebody slapped him upside the head! 2
2782Can I ask you a test question for a test? 2
06How much test would a question test test if a question test could test questions? 4
05Testing this question asking form. Did the question go through? 4
07Testing questions 4
09asking a question? 4
08Hmmmm...how question test are you? 4
04Jim Testing. Lorem ipsum dolor sit amet, consectetuer adipiscing elit 4
I get the same above results regardless of whenever I execute:
exec dbo.spcmt_QuestionSearch_test N'test question', 10, 0
MJ
February 2, 2009 at 4:47 am
I got the same results as MANU.
-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."
February 2, 2009 at 11:27 am
Thanks for the help, guys.
So I guess the next logical question is this ... *should* every 'question' that gets pulled by the query in the SP be given a RANK of 0 UNLESS it contains the EXACT search text? IMHO, if this is the case, then it would certainly seem to make using freetexttable pretty much ... well, useless.
I'm definitely puzzled as to why I see totally different ranks for the first 15-20 seconds ... ranks that seem to make total sense, and be absolutely 'useful' ... that then disappear ... but nobody else that's tried it sees this happening when they run the script...
Any ideas, anyone?
Thanks again ...
February 2, 2009 at 11:41 am
bvaljalo (1/29/2009)
Wow.1,000,000 users on the site, and not one person can take a few minutes to help me out with this.
I'll be sure to keep the *amazing* resource that is SQLServerCentral.com in mind in the future ...
Well, not everyone may have full-text indexing installed or setup. Those of us who do answer questions for help do this on an as time is available basis. We don't get paid.
As for me, I don't use full-text indexing at the moment, so I wouldn't have been able to help you much myself.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply