dynamic SQL versus a huge IF structure

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 suggestionsHi
Check out http://www.sommarskog.se/dyn-search.html and
http://www.sommarskog.se/dyn-search.html.
It may be possible to rationalise the use of the different temporary tables
into one, in which case your second select might possibly be made the same o
r
more similar and not produce different result sets for each combination,
which may clean up the client code.
You may also want to look at splitting the different sections into their own
stored procedures.
John
"ibiza" wrote:

> 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
>|||Hi, thanks for your reply.
By the way, you gave me the two same links, is that a typo?

> It may be possible to rationalise the use of the different temporary table
s
> into one, in which case your second select might possibly be made the same
or
> more similar and not produce different result sets for each combination,
> which may clean up the client code.
How would you do that? I have trouble to get it :S
thanks for your time
John Bell wrote:[vbcol=seagreen]
> Hi
> Check out http://www.sommarskog.se/dyn-search.html and
> http://www.sommarskog.se/dyn-search.html.
> It may be possible to rationalise the use of the different temporary table
s
> into one, in which case your second select might possibly be made the same
or
> more similar and not produce different result sets for each combination,
> which may clean up the client code.
> You may also want to look at splitting the different sections into their o
wn
> stored procedures.
> John
> "ibiza" wrote:
>|||Hi
"ibiza" wrote:

> Hi, thanks for your reply.
> By the way, you gave me the two same links, is that a typo?
Yes it was a cut and paste issue! Try
http://www.sommarskog.se/dynamic_sql.html

>
> How would you do that? I have trouble to get it :S
Looking at the code you seem to have very similar temporary tables with
different name. Without looking in depth it struck me that if you just
created a single table at the begining you may be able to see more
rationalisation. I would have expected the current client to have similar
code to the stored procedure as you seem to be producing unique output for
each one. This could probably be cleaned up if you had the same column names
for everything. If you took this further and always returned the same column
s
then you may be able to clean up the client coding further, the downside may
be that you produce a wider result set, you may need to judge the performanc
e
impact of that.
John
> thanks for your time
> John Bell wrote:
>|||Hi,
yes, in fact each of my temporary tables strictly contains an id. Each
IF branch (now splitted into sub-procedures) fills that temp table with
the ids related to the search, then do many select queries based on
those ids.
I've change the SELECT INTO and splitted each IF into sub-procedures,
here it is now (with some commented example search at the beginning) :
--EXECUTE [DicKanjiSearch] N'=E5=8F=8B', 1, N'', 0
--EXECUTE [DicKanjiSearch] N'=E5=8B=9D', 1, N'', 1
--EXECUTE [DicKanjiSearch] N'test', 2, N'', 0
--EXECUTE [DicKanjiSearch] N'sun', 2, N'', 1
--EXECUTE [DicKanjiSearch] N'=E3=82=B8=E3=83=A5=E3=82=A6', 3, N'=E3=81=9
8=
=E3=82=85=E3=81=86', 0
--EXECUTE [DicKanjiSearch] N'=E3=82=B3=E3=82=B3=E3=83=AD', 3, N'=E3=81=9
3=
=E3=81=93=E3=82=8D', 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) =3D '', @.detailed bit AS
-- @.type : 1 =3D kanji
-- 2 =3D english
-- 3 =3D 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 =3D 1
BEGIN
IF @.type =3D 1
EXEC DictKanjiGetDetailedByKanji @.search
ELSE IF @.type =3D 2
EXEC DictKanjiGetDetailedByMeaning @.search
ELSE IF @.type =3D 3
EXEC DictKanjiGetDetailedByPron @.search,
@.search2
ELSE IF @.type =3D 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 =3D 1
EXEC DictKanjiGetBasicByKanji @.search
ELSE IF @.type =3D 2
EXEC DictKanjiGetBasicByMeaning @.search
ELSE IF @.type =3D 3
EXEC DictKanjiGetBasicByPron @.search, @.search2
ELSE IF @.type =3D 4
This will be a new type : by stroke count
EXEC DictKanjiGetBasicByStrokesCount CAST(@.search
as tinyint)
END
END
Now, after reading your two excellent articles (many thanks for that),
it seems that, I quote, "dynamic SQL is often the best solution, both
for performance and maintainability"...Now I have a concern with the
method I am using, because it's static SQL :| what do you think about
that?
Finally, is there a way to 'merge' queries together with the method I
am currently using? That's because I added 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 =3D
NULL, @.kanji =3D NULL, @.pronH =3D NULL, @.pronK =3D NULL, @.strokescount =3D
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?
Thank you very much!
John Bell wrote:
> Hi
> "ibiza" wrote:
>
> Yes it was a cut and paste issue! Try
> http://www.sommarskog.se/dynamic_sql.html
>
tables[vbcol=seagreen]
same or[vbcol=seagreen]
on,[vbcol=seagreen]
> Looking at the code you seem to have very similar temporary tables with
> different name. Without looking in depth it struck me that if you just
> created a single table at the begining you may be able to see more
> rationalisation. I would have expected the current client to have similar
> code to the stored procedure as you seem to be producing unique output for
> each one. This could probably be cleaned up if you had the same column na=
mes
> for everything. If you took this further and always returned the same col=
umns
> then you may be able to clean up the client coding further, the downside =
may
> be that you produce a wider result set, you may need to judge the perform=
ance[vbcol=seagreen]
> impact of that.
>
> John
tables[vbcol=seagreen]
same or[vbcol=seagreen]
on,[vbcol=seagreen]
eir own[vbcol=seagreen]
and[vbcol=seagreen]
on.[vbcol=seagreen]
n'yomi[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
of[vbcol=seagreen]
I[vbcol=seagreen]
, in[vbcol=seagreen]
nue[vbcol=seagreen]
ed[vbcol=seagreen]
way[vbcol=seagreen]
AND[vbcol=seagreen]
er[vbcol=seagreen]|||Hi
The only way to see if dynamic SQL is quicker would be to implement it. In
general I would only do that if you think your current solution is too slow.
To implement multiple criteria you could separate your procedures into two
bring the second select into DicKanjiSearch, you can then call the various
procedures to add new ids to the temporary table multiple times.
John
"ibiza" wrote:
[vbcol=seagreen]
> Hi,
> yes, in fact each of my temporary tables strictly contains an id. Each
> IF branch (now splitted into sub-procedures) fills that temp table with
> the ids related to the search, then do many select queries based on
> those ids.
> I've change the SELECT INTO and splitted each IF into sub-procedures,
> 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
> --EXECUTE [DicKanjiSearch] N'3', 4, N'', 0
> --EXECUTE [DicKanjiSearch] N'5', 4, N'', 1
> ALTER PROCEDURE [dbo].[DicKanjiSearch] @.search nvarchar(200), @.typ
e
> 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
> Now, after reading your two excellent articles (many thanks for that),
> it seems that, I quote, "dynamic SQL is often the best solution, both
> for performance and maintainability"...Now I have a concern with the
> method I am using, because it's static SQL :| what do you think about
> that?
> Finally, is there a way to 'merge' queries together with the method I
> am currently using? That's because I added 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?
> Thank you very much!
> John Bell wrote:|||Hello,

> To implement multiple criteria you could separate your procedures into two
> bring the second select into DicKanjiSearch, you can then call the various
> procedures to add new ids to the temporary table multiple times.
I don't really understand. Separating which procedures into two?
Also, multiple criteria would mean a sort of AND implemantation, so
that does not mean "add new ids to the temporary table multiple times"
(this would be an OR, am I correct?)
thanks for your time
John Bell wrote:
> Hi
> The only way to see if dynamic SQL is quicker would be to implement it. In
> general I would only do that if you think your current solution is too sl=
ow.[vbcol=seagreen]
> To implement multiple criteria you could separate your procedures into two
> bring the second select into DicKanjiSearch, you can then call the various
> procedures to add new ids to the temporary table multiple times.
> John
> "ibiza" wrote:
>
=98=E3=82=85=E3=81=86', 0[vbcol=seagreen]
=93=E3=81=93=E3=82=8D', 1[vbcol=seagreen]
mi[vbcol=seagreen]
=3D[vbcol=seagreen]
ary tables[vbcol=seagreen]
the same or[vbcol=seagreen]
nation,[vbcol=seagreen]
th[vbcol=seagreen]
ilar[vbcol=seagreen]
t for[vbcol=seagreen]
n names[vbcol=seagreen]
columns[vbcol=seagreen]
ide may[vbcol=seagreen]
formance[vbcol=seagreen]
ary tables[vbcol=seagreen]
the same or[vbcol=seagreen]
nation,[vbcol=seagreen]
o their own[vbcol=seagreen]
elds,[vbcol=seagreen]
kana and[vbcol=seagreen]
d so on.[vbcol=seagreen]
only[vbcol=seagreen]
ype[vbcol=seagreen]
/ kun'yomi[vbcol=seagreen]
, we[vbcol=seagreen]
h=2E[vbcol=seagreen]
rch2.[vbcol=seagreen]
e is[vbcol=seagreen]
un,[vbcol=seagreen]
eq[vbcol=seagreen]
201)[vbcol=seagreen]
ake of[vbcol=seagreen]
un,[vbcol=seagreen]
eq[vbcol=seagreen]
201)[vbcol=seagreen]
ake of[vbcol=seagreen]
un,[vbcol=seagreen]
eq[vbcol=seagreen]
201)[vbcol=seagreen]
ake of[vbcol=seagreen]
id[vbcol=seagreen]
201)[vbcol=seagreen]
201)[vbcol=seagreen]|||Hi
The second select statement seems to be in two flavours based on Kanji and
LessonKanji! If these are always exclusive then you may want to separate
these into two different procedures.
To get an AND condition you can select the ids where there is more than one
occurence using a group by id and a having clause, providing each time you
add the id you only insert distinct ones.
John
"ibiza" wrote:
[vbcol=seagreen]
> Hello,
>
> I don't really understand. Separating which procedures into two?
> Also, multiple criteria would mean a sort of AND implemantation, so
> that does not mean "add new ids to the temporary table multiple times"
> (this would be an OR, am I correct?)
> thanks for your time
> John Bell wrote:|||:S sorry I'd need examples to understand better what you mean.
I suppose 'the second select statement' you refer to are any select
after the
SELECT kanji_id as id
INTO #TempTable
FROM dbo.Kanjis
WHERE (condition)
into each sub-procedure? How come you say that it seems to be in two
flavours?
thanks,
Bruno
John Bell wrote:
> Hi
> The second select statement seems to be in two flavours based on Kanji and
> LessonKanji! If these are always exclusive then you may want to separate
> these into two different procedures.
> To get an AND condition you can select the ids where there is more than o=
ne
> occurence using a group by id and a having clause, providing each time y=
ou[vbcol=seagreen]
> add the id you only insert distinct ones.
> John
> "ibiza" wrote:
>
o two[vbcol=seagreen]
rious[vbcol=seagreen]
t=2E In[vbcol=seagreen]
o slow.[vbcol=seagreen]
o two[vbcol=seagreen]
rious[vbcol=seagreen]
ach[vbcol=seagreen]
with[vbcol=seagreen]
s,[vbcol=seagreen]
) :[vbcol=seagreen]
=81=98=E3=82=85=E3=81=86', 0[vbcol=seagreen]
=81=93=E3=81=93=E3=82=8D', 1[vbcol=seagreen]
n'yomi[vbcol=seagreen]
ch2[vbcol=seagreen]
arch[vbcol=seagreen]
t),[vbcol=seagreen]
th[vbcol=seagreen]
ut[vbcol=seagreen]
I[vbcol=seagreen]
=3D[vbcol=seagreen]
unt =3D[vbcol=seagreen]
my[vbcol=seagreen]
mporary tables[vbcol=seagreen]
made the same or[vbcol=seagreen]
ombination,[vbcol=seagreen]
s with[vbcol=seagreen]
just[vbcol=seagreen]
re[vbcol=seagreen]
similar[vbcol=seagreen]
utput for[vbcol=seagreen]
olumn names[vbcol=seagreen]
same columns[vbcol=seagreen]
ownside may[vbcol=seagreen]
performance[vbcol=seagreen]
mporary tables[vbcol=seagreen]
made the same or[vbcol=seagreen]
ombination,[vbcol=seagreen]
into their own[vbcol=seagreen]
t fields,[vbcol=seagreen]
ji, kana and[vbcol=seagreen]
# and so on.[vbcol=seagreen]
t is only[vbcol=seagreen]
, @.type[vbcol=seagreen]
omi / kun'yomi[vbcol=seagreen]
tion, we[vbcol=seagreen]
earch.[vbcol=seagreen]
@.search2.[vbcol=seagreen]
efore is[vbcol=seagreen]
ji_kun,[vbcol=seagreen]
i_freq[vbcol=seagreen]
id[vbcol=seagreen]
ASC,[vbcol=seagreen]
ng), 201)[vbcol=seagreen]
he sake of[vbcol=seagreen]
ji_kun,[vbcol=seagreen]
i_freq[vbcol=seagreen]
ASC,[vbcol=seagreen]
ng), 201)[vbcol=seagreen]
he sake of[vbcol=seagreen]
ji_kun,[vbcol=seagreen]
i_freq[vbcol=seagreen]
ASC,[vbcol=seagreen]
ng), 201)[vbcol=seagreen]
he sake of[vbcol=seagreen]
=3D id[vbcol=seagreen]
ASC,[vbcol=seagreen]
ng), 201)[vbcol=seagreen]|||Hi
The second select statement seems to be very similar to each other of which
there is the basic information and full information:
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
or
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
Rather than repeating this in multiple procedures they could be put at a
level where the code would only be written once (helping maintainance). If
you do this then the procedures that populate the temporary tables can just
contain single select statement and you can use INSERT.. EXEC e.g.
INSERT #Tmptable (id)
EXECUTE [DicKanjiSearch] N'友', 1, N'', 0
As the basic listing is a subset of the full listing, then you may want to
return the full listing result set each time and throw away the columns not
used to try and simplify the client code (although normally you would not
want to return excessively wide result sets!).
To get the 'AND' result you can do something like:
CREATE TABLE #Tmptable (id int)
INSERT #Tmptable (id)
EXECUTE [DicKanjiSearch] N'友', 1, N'', 0
INSERT #Tmptable (id)
EXECUTE [DicKanjiSearch] N'test', 2, N'', 0
-- And results will be have two entried one from each insert
SELECT id
FROM #Tmptable
GROUP BY id
HAVING COUNT(*) > 1
I hope that is clearer?
John
"ibiza" wrote:
[vbcol=seagreen]
> :S sorry I'd need examples to understand better what you mean.
> I suppose 'the second select statement' you refer to are any select
> after the
> SELECT kanji_id as id
> INTO #TempTable
> FROM dbo.Kanjis
> WHERE (condition)
> into each sub-procedure? How come you say that it seems to be in two
> flavours?
> thanks,
> Bruno
> John Bell wrote: