Hi,

I have a stored proc intented to search a table on different fields,
depending on the search type.

Up until now, I have implemented 3 options, which is by kanji, kana and
english. I'll add many others, like strokes count, radical # and so on.

Here it is, with the 3 options (it may seem overwhelming but is only
because of the IF ELSE structure) :

.... 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

        -- return fields for detailed listing
        IF @detailed = 1
        BEGIN
                IF @type = 1
                BEGIN
                        -- select kanji ids corresponding to the search
string
                        SELECT kanji_id as id
                        INTO #KanjiIdsByKanji
                        FROM dbo.Kanjis
                        WHERE kanji_kanji = @search

                        -- select kanji fields
                        SELECT id, kanji_kanji, kanji_strokecount,
kanji_on, kanji_kun,
kanji_nanori, kanji_meaning,
                                lk_filename, lk_idlesson,
                                lesson_idlevel, lesson_idlesson,
                                kanji_unicode, kanji_grade,
kanji_strokemiscounts, kanji_freq
                        FROM #KanjiIdsByKanji INNER JOIN Kanjis ON
kanji_id = id
                                LEFT OUTER JOIN LessonKanji ON
lk_idkanji = kanji_id
                                LEFT OUTER JOIN Lessons ON lk_idlesson
= lesson_id
                        ORDER BY kanji_isjouyou DESC,
COALESCE(kanji_freq, 9999) ASC,
                                COALESCE(kanji_grade, 99) ASC,
COALESCE(LEN(kanji_meaning), 201)
ASC

                        ... [ * comment: I removed some other table
select for the sake of
the simplicity post ]
                END
                ELSE IF @type = 2
                BEGIN
                        -- select kanji ids corresponding to the search
string
                        SELECT kanji_id as id
                        INTO #KanjiIdsByEn
                        FROM dbo.Kanjis
                        WHERE FREETEXT (kanji_meaning, @search)

                        -- select kanji fields
                        SELECT id, kanji_kanji, kanji_strokecount,
kanji_on, kanji_kun,
kanji_nanori, kanji_meaning,
                                lk_filename, lk_idlesson,
                                lesson_idlevel, lesson_idlesson,
                                kanji_unicode, kanji_grade,
kanji_strokemiscounts, kanji_freq
                        FROM #KanjiIdsByEn INNER JOIN Kanjis ON
kanji_id = id
                                LEFT OUTER JOIN LessonKanji ON
lk_idkanji = kanji_id
                                LEFT OUTER JOIN Lessons ON lk_idlesson
= lesson_id
                        ORDER BY kanji_isjouyou DESC,
COALESCE(kanji_freq, 9999) ASC,
                                COALESCE(kanji_grade, 99) ASC,
COALESCE(LEN(kanji_meaning), 201)
ASC

                        ... [ * comment: I removed some other table
select for the sake of
the simplicity post ]
                END
                ELSE IF @type = 3
                BEGIN
                        -- select kanji ids corresponding to the search
string
                        SELECT kanji_id as id
                        INTO #KanjiIdsByKana
                        FROM dbo.Kanjis
                        WHERE FREETEXT (kanji_on, @search)
                        UNION ALL
                        SELECT kanji_id as id
                        FROM dbo.Kanjis
                        WHERE FREETEXT (kanji_kun, @search2)
                        UNION ALL
                        SELECT kanji_id as id
                        FROM dbo.Kanjis
                        WHERE FREETEXT (kanji_nanori, @search2)

                        -- select kanji fields
                        SELECT id, kanji_kanji, kanji_strokecount,
kanji_on, kanji_kun,
kanji_nanori, kanji_meaning,
                                lk_filename, lk_idlesson,
                                lesson_idlevel, lesson_idlesson,
                                kanji_unicode, kanji_grade,
kanji_strokemiscounts, kanji_freq
                        FROM #KanjiIdsByKana INNER JOIN Kanjis ON
kanji_id = id
                                LEFT OUTER JOIN LessonKanji ON
lk_idkanji = kanji_id
                                LEFT OUTER JOIN Lessons ON lk_idlesson
= lesson_id
                        ORDER BY kanji_isjouyou DESC,
COALESCE(kanji_freq, 9999) ASC,
                                COALESCE(kanji_grade, 99) ASC,
COALESCE(LEN(kanji_meaning), 201)
ASC

                        ... [ * comment: I removed some other table
select for the sake of
the simplicity post ]
                END
        END
        ELSE
        -- return fields for basic listing
        BEGIN
                IF @type = 1
                BEGIN
                        -- select kanji ids corresponding to the search
string
                        SELECT kanji_id as id
                        INTO #BasicKanjiIdsByKanji
                        FROM dbo.Kanjis
                        WHERE kanji_kanji = @search

                        SELECT kanji_kanji, kanji_meaning
                        FROM Kanjis INNER JOIN #BasicKanjiIdsByKanji ON
kanji_id = id
                        ORDER BY kanji_isjouyou DESC,
COALESCE(kanji_freq, 9999) ASC,
                                COALESCE(kanji_grade, 99) ASC,
COALESCE(LEN(kanji_meaning), 201)
ASC
                END
                ELSE IF @type = 2
                BEGIN
                        -- select kanji ids corresponding to the search
string
                        SELECT kanji_id as id
                        INTO #BasicKanjiIdsByEn
                        FROM dbo.Kanjis
                        WHERE FREETEXT (kanji_meaning, @search)

                        SELECT kanji_kanji, kanji_meaning
                        FROM Kanjis INNER JOIN #BasicKanjiIdsByEn ON
kanji_id = id
                        ORDER BY kanji_isjouyou DESC,
COALESCE(kanji_freq, 9999) ASC,
                                COALESCE(kanji_grade, 99) ASC,
COALESCE(LEN(kanji_meaning), 201)
ASC
                END
                ELSE IF @type = 3
                BEGIN
                        -- select kanji ids corresponding to the search
string
                        SELECT kanji_id as id
                        INTO #BasicKanjiIdsByKana
                        FROM dbo.Kanjis
                        WHERE FREETEXT (kanji_on, @search)
                        UNION ALL
                        SELECT kanji_id as id
                        FROM dbo.Kanjis
                        WHERE FREETEXT (kanji_kun, @search2)
                        UNION ALL
                        SELECT kanji_id as id
                        FROM dbo.Kanjis
                        WHERE FREETEXT (kanji_nanori, @search2)

                        SELECT kanji_kanji, kanji_meaning,
kanji_isjouyou
                        FROM Kanjis INNER JOIN #BasicKanjiIdsByKana ON
kanji_id = id
                        ORDER BY kanji_isjouyou DESC,
COALESCE(kanji_freq, 9999) ASC,
                                COALESCE(kanji_grade, 99) ASC,
COALESCE(LEN(kanji_meaning), 201)
ASC
                END
        END
END

Okay, up until now, it's not too bad, I have a first IF to check if I
must return a lot of columns (detailed) or only a few (basic). Then, in
each, I have another IF for each search type, to search the correct
field.

I'd like to know on a performance point of view what is best? Continue
that way and add an IF for each new type (of course, in both detailed
and basic), or use dynamic SQL?

Also, if the answer is the IF ELSE structure, what would be a good way
to implement multiple search types (for example, by english meaning AND
strokes number). Because for now, I am limited to one search type per
query.

Thanks for all your comments and suggestions

ibiza (lambe***@gmail.com) writes:
Looking at your code, my first reaction is that you should stop using
SELECT INTO. All temp table appears to consist of a single column with
an ID. So create that table in the beginning, so you can use it in all
IF branches.

It's a little difficult to say  whether you should use dynamic SQL. Dynamic
SQL is often good for dynamic searches, but it appears here that your
searches are so different from each other that there is not really any
point. The more common scenario is that you have a number of search
parameters that can be NULL.

One alternative can be to break down the procedure in sub-procedures,
either one by search type, or one per type of result set returned. The
problem with a long procedure is that compilation takes longer time,
and due to parameter sniffing some branch can get a poor plan.

I will need to add the disclaimer that I have not really grasped the
business requirements. I know neither Japanese nor free-text search, so
I don't really understand exactly what is going on.

Well, thanks for your comment.

to clarify things, I'd like to add that the procedure is one that is
linked to a textbox free search in ASP.NET. So, the user types in
anything he'd like to search in the Kanjis table, either a specific
kanji, an english translation of that kanji, or one of the many
pronunciations of it (which are contained in the kanji_on, kanji_kun
and kanji_nanori fields of the Kanjis table). So the liberty of typing
anything the user wants is what's getting me some trouble to implement,
when I try to think of a method to include more than one search type.

Do you mean having a
   Create Table #TempTable (id int PRIMARY KEY)

at the beginning of the proc, then in each IF branch, have a
   Insert Into #TempTable (id)
   Select ...

that fills that table? If so, what is the performance difference
between the two methods?

That sounds like a good idea, I'd split it by search type, but again,
how to implement that method so that more than one search type can be
used? I'd have to merge tables together or something like that? I
really don't have a clue :S

Thanks for your time

Erland Sommarskog wrote:
Show quoteHide quote

ibiza (lambe***@gmail.com) writes:
Yes.

It's not likely to be significant. SELECT INTO is minimally logged,
but has more overhead for creating the table. Ease of development and
maintenance matters more here.

The main procedure would be a dispatcher that calls the various
subprocedures. Judging from the code you posted, I would not bother
about having some common code in the top procedure. But if there a lot
of complex logic that has to be carried out for many search types,
then it may be worth the bit of complexity that it
would incur.

Hi and thank you very much so far!

I've change the SELECT INTO as you said, here it is now (with some
commented example search at the beginning) :

--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
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

Besides being cleaner, it runs faster!

I have a concern though, after reading
http://www.sommarskog.se/dyn-search.html . Basically, it says that
dynamic SQL is usually the way to go with dynamic search like
that...But I've always thought that dynamic SQL was less efficient than
static SQL. What do you think about that one?

Finally, is there a way to 'merge' queries together with the method I
am currently using? That's because when I add the search type 4, which
is by kanji strokes count, I'd like to be able to search for by more
than one search type. (e.g., strokes count AND english term).
I see two ways of passing multiple parameters here. Either
1) have a @parameter_name for each type possible (e.g.: @meaning =
NULL, @kanji = NULL, @pronH = NULL, @pronK = NULL, @strokescount =
NULL, ...), then check which ones are assigned, then execute
corresponding queries.
2) use my @search paramater as a container, like "7&name", and have my
@type parameter something like "strokecount&meaning" (or the more
shorter "sc&en") and parse then to execute corresponding queries.
Which one would perfrom faster?

Then I still need a way to merge SELECT tables together...Any ideas?

thanks a lot again!

Erland Sommarskog wrote:
Show quoteHide quote

random select a row based a weight column.
Timeout expire
Optimize this query
store results over network
How can I insert a new field between existing field?