Well, it's not Google, but it works...
I've been struggling with an architecture problem since a friend made a suggestion that led to my diving into amping up my site's internal search capabilities. The server that my site is on doesn't have SQL Server 2000 Full-Text Indexing installed, so I'd either have to (a) migrate to a new database server, or (b) build something from scratch.
I opted with the latter, choosing to make all 10,000+ of my company's news articles searchable. This is something that's actually been bugging me for a long time, and let me tell you...for large sites, there's no harder thing to do than create great search capability. I essentially wrapped a WebForm around a T-SQL LIKE statement (I'd much rather have used the FTI-dependent FREETEXTTABLE function, had I been privvy to it), and returned results.
It's not as slow as I was thinking, and while it does take about 4-5 seconds at most for large recordsets to come down the wire and the results aren't quite as accurate as I would hope with the aforementioned FREETEXTTABLE, it performs quite nicely. I tried the same thing a couple of years ago with Access, which nearly crashed my entire server. :(
A couple of points on why it runs smoothly for you architecture-heads out there on what I'm using to make it do what it does:
- uses a stored procedure for speed & security as opposed to straight T-SQL
- uses a SqlDataAdapter binding to a single DataTable
- stores the derived recordset as a Session variable prior to databinding, reducing the need for recurring database roundtrips
- pages the resultset in a Repeater control rather than the normative DataGrid by specifying a PagedDataSource object as the Repeater's DataSource property