Full Text Searching....THOUSANDS of records!

Hope I am in the correct section.
I am installing a FTS system on an existing system (that used LIKE % queries!! hahaha)
Anyway, it is working pretty well (AND FAST!) but when I type in acommon word like "damage" I get like 32,000 records. Now, theserver handles those records in about one second but the ASP page thatreturns the results takes about one MINUTE to download. When Isave the source, it is almost 12 MEGS!!
So, basically, I am streaming 12 megs across the pipe and I want to reduce that.
I would like the system to detect over maybe 500 records and cancel the search.
I have put a "TOP 500" into the search and that actually works pretty well but is there a better/smarter method?
Thanks!
cbmeeks

Your Top 500 query is good, but you could also do a SELECT COUNT SQL query first getting exactly how may records would be returned. Just replace the fields to be returned by "COUNT(*)".
// Instantiate a Command object...
SqlCommand dbCommand = new SqlCommand();
dbCommand.Connection = yourConnectionObject;
dbCommand.CommandText = "SELECT COUNT(*) " +
"FROM table-name WHERE column-name = 'some-value'";
dbCommand.CommandType = CommandType.Text;

// Execute the Command object...
int returnValue = (int)dbCommand.ExecuteScalar();
if ( returnValue > 500 )
string errorMessage = "Your query brings back " + returnValue.ToString() + " records!";
else
// Execute your regular query...
Or you could also just execute your normal query and test the number of rows in the DataTable:
if ( dataSet.Tables[0].Rows.Count > 500 )
string errorMessage = "Your query brings back " + dataSet.Tables[0].Rows.Count.ToString() + " records!";
else
// Display it...
The last could be your best bet as it only incurs one trip to the database.
NC...

|||Thanks!
What I actually did (after I posted the question) is leave the TOP 200(was 500 but I shortened it) and as I was displaying the results, Iupdated a variable. At the end of the page, I say somethinglike: "Over 200 records found, try narrowing your search".
The disadvantage is that you never really know how many records therewas (201 would be the same as 10,000) and it's at the bottom of thepage. But, I can live with it.
How much overhead would the extra SELECT COUNT method cost? Icould profile it I guess. But everyone is complaining about thespeed now.
I wrote the program 4 years ago when I was a rookie.
At least now even very common words just take a second or two to get the page. :-)
cbmeeks
|||

Run a search for CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE Microsoft proprietry implementation of ANSI SQL in SQL Server BOL (books online). Full Text is an add on to SQL Server so you must populate the Microsoft search catalog to get expected results. Hope this helps.

sql

Full Text Search in text field for 'JN00001'

Hi
(I posted this on the full text newsgroup but it seems pretty quiet at the
mo and I was hoping someone on this list might know the answer).
I have a full text index on a table on a few columns including a text data
type field, and I'm searching for JN00001 which I know is in the text column
that is indexed by the full text index, however the result is not being
returned. Is this an issue related to noise words, or why would the result
not be being returned?
Thanks for any help
Cheers
MattHi Matt,
Can you post the query please.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
news:uW7jVOPeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi
> (I posted this on the full text newsgroup but it seems pretty quiet at the
> mo and I was hoping someone on this list might know the answer).
> I have a full text index on a table on a few columns including a text data
> type field, and I'm searching for JN00001 which I know is in the text
> column
> that is indexed by the full text index, however the result is not being
> returned. Is this an issue related to noise words, or why would the result
> not be being returned?
> Thanks for any help
> Cheers
> Matt
>
>|||Hi, thanks, the query is
SELECT rank, doc_id, pub_date, title, synopsis, notes
FROM doc JOIN CONTAINSTABLE(doc, *, 'JN00001') AS Result
ON doc.doc_id=Result.[Key]
But when I replace 'JN00001' with 'EHPs' then record 538 gets returned,
which has the text column in question with the 'JN00001' data in it and
other data, as follows:
RESEARCH SUMMARY
Title:
Emergency Help Points (EHP) at bus stations
Objective:
Passengers' awareness of EHP's, perceived uses and attitudes towards them
as a personal security aid.
Date:
February 2001
Methodology:
387 passengers were interviewed waiting for buses at Crystal Palace and
Addington Village bus stations between 9AM and 9PM. Interviews achieved are
broadly representative of London Buses users in terms of gender and age.
Key findings
<trimmed text>Three fifths of passengers at Addington station were aware
that there was an EHP at their station compared with only two fifths of
passengers at Crystal Palace station. Some passengers at Addington had
learned of the EHPs in the media
</trimmed text>.
Job number: JN00001
Any ideas?
Cheers
Matt
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:ODYnsZPeGHA.3952@.TK2MSFTNGP04.phx.gbl...
> Hi Matt,
> Can you post the query please.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
> news:uW7jVOPeGHA.4900@.TK2MSFTNGP02.phx.gbl...
>|||Does it return when you do just this?
SELECT *
FROM CONTAINSTABLE(doc, *, 'JN00001') AS Result
I know this works because I have a process that takes the USENET groups and
puts them into a SQL Server 2000 database that has a full-text catalogue on
it and the following query brings back our posts!
select *
from mb_message_detail
where contains( body, 'JN00001' )
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
news:%239QaFuPeGHA.380@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks, the query is
> SELECT rank, doc_id, pub_date, title, synopsis, notes
> FROM doc JOIN CONTAINSTABLE(doc, *, 'JN00001') AS Result
> ON doc.doc_id=Result.[Key]
> But when I replace 'JN00001' with 'EHPs' then record 538 gets returned,
> which has the text column in question with the 'JN00001' data in it and
> other data, as follows:
> RESEARCH SUMMARY
> Title:
> Emergency Help Points (EHP) at bus stations
> Objective:
> Passengers' awareness of EHP's, perceived uses and attitudes towards them
> as a personal security aid.
> Date:
> February 2001
> Methodology:
> 387 passengers were interviewed waiting for buses at Crystal Palace and
> Addington Village bus stations between 9AM and 9PM. Interviews achieved
> are broadly representative of London Buses users in terms of gender and
> age.
>
> Key findings
> <trimmed text>Three fifths of passengers at Addington station were aware
> that there was an EHP at their station compared with only two fifths of
> passengers at Crystal Palace station. Some passengers at Addington had
> learned of the EHPs in the media
> </trimmed text>.
> Job number: JN00001
>
> Any ideas?
> Cheers
> Matt
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:ODYnsZPeGHA.3952@.TK2MSFTNGP04.phx.gbl...
>|||No...
Thanks
Matt
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uG$1CXQeGHA.3932@.TK2MSFTNGP05.phx.gbl...
> Does it return when you do just this?
> SELECT *
> FROM CONTAINSTABLE(doc, *, 'JN00001') AS Result
> I know this works because I have a process that takes the USENET groups
> and puts them into a SQL Server 2000 database that has a full-text
> catalogue on it and the following query brings back our posts!
> select *
> from mb_message_detail
> where contains( body, 'JN00001' )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
> news:%239QaFuPeGHA.380@.TK2MSFTNGP04.phx.gbl...
>|||Hi Matt,
Do a full rebuild on the catalogue, the row can't be indexed for some
reason, the noise file won't effect that being indexed.
Can you script out the CREATE TABLE and full-text catalogue creation for me.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
news:eQyl6pQeGHA.2188@.TK2MSFTNGP04.phx.gbl...
> No...
> Thanks
> Matt
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uG$1CXQeGHA.3932@.TK2MSFTNGP05.phx.gbl...
>