Hi all,
I have an application where a simple search box is used to do a
powerful search on whatever the user types in. The program recognizes
if it's english, a certain search type (e.g. "sc:7" is for "stroke
count equals 7") or japanese. Then I have the following procedure (I
included some commented valid calls to it to help) :

--EXECUTE [DicKanjiSearch] N'友', 1, N'', 0
--EXECUTE [DicKanjiSearch] N'勝', 1, N'', 1
--EXECUTE [DicKanjiSearch] N'test', 2, N'', 0
--EXECUTE [DicKanjiSearch] N'sun', 2, N'', 1
--EXECUTE [DicKanjiSearch] N'ジュウ', 3, N'じゅう', 0
--EXECUTE [DicKanjiSearch] N'ココロ', 3, N'こころ', 1
--EXECUTE [DicKanjiSearch] N'3', 4, N'', 0
--EXECUTE [DicKanjiSearch] N'5', 4, N'', 1
ALTER PROCEDURE [dbo].[DicKanjiSearch] @search nvarchar(200), @type
tinyint,

@search2 nvarchar(200) = '', @detailed bit AS
-- @type : 1 = kanji
--         2 = english
--         3 = hiragana or katakana : pronunciation (on'yomi / kun'yomi
/ nanori)
-- @search2 is there in case we have to search by pronunciation, we
must
-- be able to search in hiragana and katakana. In that case,
-- on'yomi (chinese) pronunciation is in KATAKANA ans is @search.
-- kun'yomi (japanese) pronunciation is in HIRAGANA ans is @search2.
-- nanori (name) pronunciation is also in HIRAGANA ans therefore is
@search2.
BEGIN
        SET NOCOUNT ON

        -- temp table to hold ids
        CREATE TABLE #TempTable (id int PRIMARY KEY)

        -- return fields for detailed listing
        IF @detailed = 1
        BEGIN
                IF @type = 1
                        EXEC DictKanjiGetDetailedByKanji @search
                ELSE IF @type = 2
                        EXEC DictKanjiGetDetailedByMeaning @search
                ELSE IF @type = 3
                        EXEC DictKanjiGetDetailedByPron @search,
@search2
                ELSE IF @type = 4
                      This will be a new type : by stroke count
                      EXEC DictKanjiGetDetailedByStrokesCount
CAST(@search as tinyint)
        END
        ELSE
        -- return fields for basic listing
        BEGIN
                IF @type = 1
                        EXEC DictKanjiGetBasicByKanji @search
                ELSE IF @type = 2
                        EXEC DictKanjiGetBasicByMeaning @search
                ELSE IF @type = 3
                        EXEC DictKanjiGetBasicByPron @search, @search2
                ELSE IF @type = 4
                      This will be a new type : by stroke count
                      EXEC DictKanjiGetBasicByStrokesCount CAST(@search
as tinyint)
        END
END

Performance is crucial here, as it is a key procedure of the app. It
works well for now, but I have some concerns. First, I'd like some
advice on using dynamic SQL or not. I always thought dynamic sql was
less performant than static SQL, but after reading
http://www.sommarskog.se/dyn-search.html, it seems that "dynamic SQL is
often the best solution, both for performance and maintainability".
What should I use?

Of course, I'll have to do tests to determine that, and that brings a
second question : how to do thoses tests? I mean, how to measure the
performance of each method? I know it will probably be with "Execution
Plan" and "SQL Server Profiler", but how to use these tools properly to
do a good analysis? I've never actually tried them thoroughly.

Finally, I'd like to implement the search method in such a way that
multiple conditions are possible. For example, if I type "name sc:8" in
the textbox, that'd mean "search by english term "name" AND by strokes
count of 8". For now, it's only possible to search by one term at a
time. What would be the best method (with static and/or dynamic SQL,
but mainly static because that's I use for now, and any static SQL can
be converted to dynamic later) to 'merge' multiple search conditions
like that?

Any help would be kind :)

Thanks a lot

ibiza (lambe***@gmail.com) writes:
From what I've seen so far, I don't really see any reason for dynamic
SQL. Your searches are so different from each other. But if you start
to combine them, then it's another matter.

The reason dynamic SQL is better for dynamic searches, is that it's
very difficult to cover all combinations in one query, and if you
split it up on many queries, maintenance becomes a problem. The key
here is that almost each combination of search parameters has its unique
best query plan. Dynamic SQL makes it a lot easier to arrive there.

The way to do tests is to run the queries on data that is akin to
what you can expect in production. If the production data is not
available, this is by no means an easy task. To generate a lot of
data is not that difficult, but what is difficult is to achieve a
data distribution which resembles the real-world data.

thanks for the feedback.

I have the real-word data, my tables are the final and static data
about kanjis.

So I did tried at first to do an Estimated Execution Plan on
   EXECUTE [DicKanjiSearch] N'test', 2, N'', 1

which is supposed to return every kanjis that has 'test' in its
kanji_meaning field
Here's the Execution plan :
http://img142.imageshack.us/img142/2834/whatvx7.gif

Am I supposing correctly that the execution plan should only execute
the procedure DictKanjiGetDetailedByMeaning @search, because of the way
the IF @detailed and IF @type branches with the given values?

And for the problem of merging multiples conditions, would it be
efficient to execute each necessary only sub-procedures (e.g.: typing
"sc:4 name" in the search box would somehow say to the dispatcher to
trigger DictKanjiGetDetailedByStrokesCount and
DictKanjiGetDetailedByMeaning), then in the dispatcher, select all rows
that appear in both tables? But again, I need to know how many tables
(2 or more, one per search type triggered) are involved...

Erland Sommarskog wrote:
Show quoteHide quote

ibiza (lambe***@gmail.com) writes:
The estimated execution plan is a little tricky. I don't know exactly
what is going on, but apparently it's look it all possible branches in
your procedure, and the result is not that easy to interpret.

And those percents relataive to the batch are never that reliable.

In the end, the best measure is wallclock time. Execution plans are good
so far that you can see that queries use the indexes you expect etc. But
as long as reponse times are good there is not much reason to go there.

That could indeed be a way to do it. But I like to repeat that my meagre
knowledge about strokes, radicals and full-text search, I am not well
equipped to judge what is the best for you.

Stored Procedure Help
Conversion failed when converting datetime from character string.
Variable table name in a cursor declaration
Comparing two tables
Querying from and xls file all format of data in one field (date,