Hi all,

I created search query that combines fulltextsearch with some other
subsearches and other conditions. My problem is that SQL server also
evaluates conditions or some subqueries which are not needed. For example:

....
@search_string is null or contains(b.*, @search_string)
....

If @search_string is null, it is not necessary to execute contains(b.*,
@search_string).

I have the same database on two different machines and at the beginning the
same query takes aprox. the  same time on both machines. I wanted to find
bottleneck so I was commenting some parts of query and experimenting.
Sometimes it went faster, sometimes slower but from one moment it started
running significantly faster than before even I ran the original query. Okey
SQL Server optimizer learns how to execute this query. The same query runs
still slow on second computer.

My question :
1) How can I control the execution and avoid performing contains(b.*,
@search_string) from my example ?
2) How can I force the server to learn more optimized way of executing the
query ? (creating better execution plan ...)

Regards,
Jan

Never use an SQL product for textbases.  The design of RDBMS and
textbases do not match.  If you want performance, use the right tool.

Hi,

I didn't catch the point. What right tool do you mean ?
The search functionality is small but important part of this application. It
is database engine with search engine and I require certain level of
perforamance. Or should I use mysql ? Joking. Anyway one solution is to use
if on top level  :
if @search is null
begin
.... select without fulltextsearch
end
else
begin
.... select with fulltextsearch
end
But it is not answer for my question how can I affect execution plan or
optimizer to run select in more effecient way.

Regards,
Jan
Show quoteHide quote

Using CASE in WHERE clause
Tempdb is growing and I can't tell what process is to blame
Beating a dead horse - Drop table from proc
Can I do this?
Sql Server Lock