Middle Search Support

Some of the databases that I inherited contain search that are based on finding a string anywhere within a last name such as:

WHERE lastName like '%smith%'

It is desired that each of these names be returned:

Smith
Smithson
Nesmith

What is presently done is that updates to the last name fields trigger that substrings of the last name be sent off into a substring table wtih retention of no 2-char substrings. For these three last names the following would be kept:

(1) Smith, (2) mith, (3) ith and (4) th
(1) Smithson, (2) mithson, (3) ithson, ..., (n) on
(1) Nesmith, (2) esmith, (3) smith, ... (n) th

The where now becomes

WHERE lastNameSub like 'smith%'

This seems to make the search routine by last name faster. I would like to improve on this if I can. Suggestions?

Dave

Hello Dave,

I'm afraid you can't within the core engine alone. Obviously a good indexing strategy will help you, but if we're talking about improving the substring/like search on a character column, apart from indexing, you don't have many options.

As you've discovered, using 'smith%' is much faster then '%smith%' as the optimiser has the opportunity to perform an index scan in the first instance - it does not in the second.

If you are performing a lot of these types of searches, I would look at full-text search:

http://www.databasejournal.com/features/mssql/article.php/3441981

http://www.sql-server-performance.com/tb_search_optimization.asp

Cheers,

Rob

Microsoft.Reporting.WebForms.ReportViewer does not contain a definition for Reset

Hi everyone I developed a web form with a report viewer. I change the report datasource and report path based on user input. Before i do anything on the report i first reset it with this line:

rvWaitTime.Reset();

And then set the parameters, datasources, etc. It works fine on my dev machine: Windows Vista with .NET 2.0 installed (.NET 3.0 is installed as well). When I move the code to the production box: Windows Server 2003 with .NET 2.0 and 3.0 installed i get this error:

'Microsoft.Reporting.WebForms.ReportViewer' does not contain a definition for 'Reset'

and of course it fails on the line of code mentioned above.

Any ideas?

Who would have thought that you needed a seperate installation for a control that comes built in with VS 2005? I finally solved the problem. I needed to install theMicrosoft Report Viewer Redistributable 2005 SP1 (Full Installation) on my server and now everything works like a charm!