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...
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
With
" <anonymous@.discussions.microsoft.com> wrote in message
about how the backup system works in SQL Server. I'm using