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 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 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 tables
> 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:
> 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 tables
> 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 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
"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
> > 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 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
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 columns
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 performance
impact of that.
John
> thanks for your time
> John Bell wrote:
> > 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 tables
> > 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 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,
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=98==E3=82=85=E3=81=86', 0
--EXECUTE [DicKanjiSearch] N'=E3=82=B3=E3=82=B3=E3=83=AD', 3, N'=E3=81=93==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:
> > 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
> >
> > > 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 or
> > > more similar and not produce different result sets for each combinati=on,
> > > which may clean up the client code.
> >
> > 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 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
> impact of that.
>
> John
> > thanks for your time
> >
> > John Bell wrote:
> > > 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 =tables
> > > 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 combinati=on,
> > > which may clean up the client code.
> > >
> > > You may also want to look at splitting the different sections into th=eir 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) =3D '', @.detailed bit AS
> > > > -- @.type : 1 =3D kanji
> > > > -- 2 =3D english
> > > > -- 3 =3D hiragana or katakana : pronunciation (on'yomi / ku=n'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 =3D 1
> > > > BEGIN
> > > > IF @.type =3D 1
> > > > BEGIN
> > > > -- select kanji ids corresponding to the search string
> > > > SELECT kanji_id as id
> > > > INTO #KanjiIdsByKanji
> > > > FROM dbo.Kanjis
> > > > WHERE kanji_kanji =3D @.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 =3D id
> > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D 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 =3D 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 =3D id
> > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D 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 =3D 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 =3D id
> > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D 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 =3D 1
> > > > BEGIN
> > > > -- select kanji ids corresponding to the search string
> > > > SELECT kanji_id as id
> > > > INTO #BasicKanjiIdsByKanji
> > > > FROM dbo.Kanjis
> > > > WHERE kanji_kanji =3D @.search
> > > >
> > > > SELECT kanji_kanji, kanji_meaning
> > > > FROM Kanjis INNER JOIN #BasicKanjiIdsByKanji ON kanji_id =3D 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 =3D 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 =3D 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 =3D 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 =3D 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? Conti=nue
> > > > that way and add an IF for each new type (of course, in both detail=ed
> > > > 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 p=er
> > > > query.
> > > >
> > > > Thanks for all your comments and suggestions
> > > > > > > >
> > > >|||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:
> 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), @.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
> 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:
> > 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
> >
> > >
> > > > 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 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
> > 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 columns
> > 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 performance
> > impact of that.
> >
> >
> > John
> > > thanks for your time
> > >
> > > John Bell wrote:
> > > > 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 tables
> > > > 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 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|||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.
> 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:
> > 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==98=E3=82=85=E3=81=86', 0
> > --EXECUTE [DicKanjiSearch] N'=E3=82=B3=E3=82=B3=E3=83=AD', 3, N'=E3=81==93=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'yo=mi
> > / 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:
> > >
> > > > 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
> > >
> > > >
> > > > > It may be possible to rationalise the use of the different tempor=ary tables
> > > > > 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 combi=nation,
> > > > > which may clean up the client code.
> > > >
> > > > How would you do that? I have trouble to get it :S
> > > Looking at the code you seem to have very similar temporary tables wi=th
> > > 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 sim=ilar
> > > code to the stored procedure as you seem to be producing unique outpu=t for
> > > each one. This could probably be cleaned up if you had the same colum=n names
> > > for everything. If you took this further and always returned the same= columns
> > > then you may be able to clean up the client coding further, the downs=ide may
> > > be that you produce a wider result set, you may need to judge the per=formance
> > > impact of that.
> > >
> > >
> > > John
> > > > thanks for your time
> > > >
> > > > John Bell wrote:
> > > > > 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 tempor=ary tables
> > > > > 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 combi=nation,
> > > > > which may clean up the client code.
> > > > >
> > > > > You may also want to look at splitting the different sections int=o their own
> > > > > stored procedures.
> > > > >
> > > > > John
> > > > >
> > > > > "ibiza" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have a stored proc intented to search a table on different fi=elds,
> > > > > > 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 # an=d 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), @.t=ype
> > > > > > 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 @.searc=h=2E
> > > > > > -- kun'yomi (japanese) pronunciation is in HIRAGANA ans is @.sea=rch2.
> > > > > > -- nanori (name) pronunciation is also in HIRAGANA ans therefor=e is
> > > > > > @.search2.
> > > > > > BEGIN
> > > > > > SET NOCOUNT ON
> > > > > >
> > > > > > -- return fields for detailed listing
> > > > > > IF @.detailed =3D 1
> > > > > > BEGIN
> > > > > > IF @.type =3D 1
> > > > > > BEGIN
> > > > > > -- select kanji ids corresponding to the search string
> > > > > > SELECT kanji_id as id
> > > > > > INTO #KanjiIdsByKanji
> > > > > > FROM dbo.Kanjis
> > > > > > WHERE kanji_kanji =3D @.search
> > > > > >
> > > > > > -- select kanji fields
> > > > > > SELECT id, kanji_kanji, kanji_strokecount, kanji_on, kanji_k=un,
> > > > > > kanji_nanori, kanji_meaning,
> > > > > > lk_filename, lk_idlesson,
> > > > > > lesson_idlevel, lesson_idlesson,
> > > > > > kanji_unicode, kanji_grade, kanji_strokemiscounts, kanji_fr=eq
> > > > > > FROM #KanjiIdsByKanji INNER JOIN Kanjis ON kanji_id =3D id
> > > > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> > > > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D 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 s=ake of
> > > > > > the simplicity post ]
> > > > > > END
> > > > > > ELSE IF @.type =3D 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_k=un,
> > > > > > kanji_nanori, kanji_meaning,
> > > > > > lk_filename, lk_idlesson,
> > > > > > lesson_idlevel, lesson_idlesson,
> > > > > > kanji_unicode, kanji_grade, kanji_strokemiscounts, kanji_fr=eq
> > > > > > FROM #KanjiIdsByEn INNER JOIN Kanjis ON kanji_id =3D id
> > > > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> > > > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D 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 s=ake of
> > > > > > the simplicity post ]
> > > > > > END
> > > > > > ELSE IF @.type =3D 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_k=un,
> > > > > > kanji_nanori, kanji_meaning,
> > > > > > lk_filename, lk_idlesson,
> > > > > > lesson_idlevel, lesson_idlesson,
> > > > > > kanji_unicode, kanji_grade, kanji_strokemiscounts, kanji_fr=eq
> > > > > > FROM #KanjiIdsByKana INNER JOIN Kanjis ON kanji_id =3D id
> > > > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> > > > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D 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 s=ake of
> > > > > > the simplicity post ]
> > > > > > END
> > > > > > END
> > > > > > ELSE
> > > > > > -- return fields for basic listing
> > > > > > BEGIN
> > > > > > IF @.type =3D 1
> > > > > > BEGIN
> > > > > > -- select kanji ids corresponding to the search string
> > > > > > SELECT kanji_id as id
> > > > > > INTO #BasicKanjiIdsByKanji
> > > > > > FROM dbo.Kanjis
> > > > > > WHERE kanji_kanji =3D @.search
> > > > > >
> > > > > > SELECT kanji_kanji, kanji_meaning
> > > > > > FROM Kanjis INNER JOIN #BasicKanjiIdsByKanji ON kanji_id =3D= 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 =3D 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 =3D 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 =3D 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|||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:
> 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 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:
> >
> > > 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), @.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
> > >
> > > 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:
> > > > 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
> > > >
> > > > >
> > > > > > 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 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
> > > > 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 columns
> > > > 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 performance
> > > > impact of that.
> > > >
> > > >
> > > > John
> > > > > thanks for your time
> > > > >
> > > > > John Bell wrote:
> > > > > > 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 tables
> > > > > > 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 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|||: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
> add the id you only insert distinct ones.
> John
> "ibiza" wrote:
> > Hello,
> >
> > > To implement multiple criteria you could separate your procedures int=o two
> > > bring the second select into DicKanjiSearch, you can then call the va=rious
> > > 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 i=t=2E In
> > > general I would only do that if you think your current solution is to=o slow.
> > >
> > > To implement multiple criteria you could separate your procedures int=o two
> > > bring the second select into DicKanjiSearch, you can then call the va=rious
> > > procedures to add new ids to the temporary table multiple times.
> > >
> > > John
> > >
> > > "ibiza" wrote:
> > >
> > > > Hi,
> > > >
> > > > yes, in fact each of my temporary tables strictly contains an id. E=ach
> > > > 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-procedure=s,
> > > > 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=98=E3=82=85=E3=81=86', 0
> > > > --EXECUTE [DicKanjiSearch] N'=E3=82=B3=E3=82=B3=E3=83=AD', 3, N'=E3==81=93=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 / ku=n'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, @.sear=ch2
> > > > ELSE IF @.type =3D 4
> > > > This will be a new type : by stroke count
> > > > EXEC DictKanjiGetBasicByStrokesCount CAST(@.se=arch
> > > > as tinyint)
> > > > END
> > > > END
> > > >
> > > > Now, after reading your two excellent articles (many thanks for tha=t),
> > > > it seems that, I quote, "dynamic SQL is often the best solution, bo=th
> > > > for performance and maintainability"...Now I have a concern with the
> > > > method I am using, because it's static SQL :| what do you think abo=ut
> > > > 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, @.strokesco=unt =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:
> > > > >
> > > > > > 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
> > > > >
> > > > > >
> > > > > > > It may be possible to rationalise the use of the different te=mporary tables
> > > > > > > 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 c=ombination,
> > > > > > > which may clean up the client code.
> > > > > >
> > > > > > How would you do that? I have trouble to get it :S
> > > > > Looking at the code you seem to have very similar temporary table=s 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 mo=re
> > > > > rationalisation. I would have expected the current client to have= similar
> > > > > code to the stored procedure as you seem to be producing unique o=utput for
> > > > > each one. This could probably be cleaned up if you had the same c=olumn names
> > > > > for everything. If you took this further and always returned the =same columns
> > > > > then you may be able to clean up the client coding further, the d=ownside may
> > > > > be that you produce a wider result set, you may need to judge the= performance
> > > > > impact of that.
> > > > >
> > > > >
> > > > > John
> > > > > > thanks for your time
> > > > > >
> > > > > > John Bell wrote:
> > > > > > > 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 te=mporary tables
> > > > > > > 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 c=ombination,
> > > > > > > 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 differen=t fields,
> > > > > > > > depending on the search type.
> > > > > > > >
> > > > > > > > Up until now, I have implemented 3 options, which is by kan=ji, 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 bu=t is only
> > > > > > > > because of the IF ELSE structure) :
> > > > > > > >
> > > > > > > > ... 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'y=omi / kun'yomi
> > > > > > > > / nanori)
> > > > > > > > -- @.search2 is there in case we have to search by pronuncia=tion, we
> > > > > > > > must
> > > > > > > > -- be able to search in hiragana and katakana. In that case,
> > > > > > > > -- on'yomi (chinese) pronunciation is in KATAKANA ans is @.s=earch.
> > > > > > > > -- kun'yomi (japanese) pronunciation is in HIRAGANA ans is =@.search2.
> > > > > > > > -- nanori (name) pronunciation is also in HIRAGANA ans ther=efore is
> > > > > > > > @.search2.
> > > > > > > > BEGIN
> > > > > > > > SET NOCOUNT ON
> > > > > > > >
> > > > > > > > -- return fields for detailed listing
> > > > > > > > IF @.detailed =3D 1
> > > > > > > > BEGIN
> > > > > > > > IF @.type =3D 1
> > > > > > > > BEGIN
> > > > > > > > -- select kanji ids corresponding to the search string
> > > > > > > > SELECT kanji_id as id
> > > > > > > > INTO #KanjiIdsByKanji
> > > > > > > > FROM dbo.Kanjis
> > > > > > > > WHERE kanji_kanji =3D @.search
> > > > > > > >
> > > > > > > > -- select kanji fields
> > > > > > > > SELECT id, kanji_kanji, kanji_strokecount, kanji_on, kan=ji_kun,
> > > > > > > > kanji_nanori, kanji_meaning,
> > > > > > > > lk_filename, lk_idlesson,
> > > > > > > > lesson_idlevel, lesson_idlesson,
> > > > > > > > kanji_unicode, kanji_grade, kanji_strokemiscounts, kanj=i_freq
> > > > > > > > FROM #KanjiIdsByKanji INNER JOIN Kanjis ON kanji_id =3D =id
> > > > > > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> > > > > > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D lesson_id
> > > > > > > > ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999)= ASC,
> > > > > > > > COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meani=ng), 201)
> > > > > > > > ASC
> > > > > > > >
> > > > > > > > ... [ * comment: I removed some other table select for t=he sake of
> > > > > > > > the simplicity post ]
> > > > > > > > END
> > > > > > > > ELSE IF @.type =3D 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, kan=ji_kun,
> > > > > > > > kanji_nanori, kanji_meaning,
> > > > > > > > lk_filename, lk_idlesson,
> > > > > > > > lesson_idlevel, lesson_idlesson,
> > > > > > > > kanji_unicode, kanji_grade, kanji_strokemiscounts, kanj=i_freq
> > > > > > > > FROM #KanjiIdsByEn INNER JOIN Kanjis ON kanji_id =3D id
> > > > > > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> > > > > > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D lesson_id
> > > > > > > > ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999)= ASC,
> > > > > > > > COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meani=ng), 201)
> > > > > > > > ASC
> > > > > > > >
> > > > > > > > ... [ * comment: I removed some other table select for t=he sake of
> > > > > > > > the simplicity post ]
> > > > > > > > END
> > > > > > > > ELSE IF @.type =3D 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, kan=ji_kun,
> > > > > > > > kanji_nanori, kanji_meaning,
> > > > > > > > lk_filename, lk_idlesson,
> > > > > > > > lesson_idlevel, lesson_idlesson,
> > > > > > > > kanji_unicode, kanji_grade, kanji_strokemiscounts, kanj=i_freq
> > > > > > > > FROM #KanjiIdsByKana INNER JOIN Kanjis ON kanji_id =3D id
> > > > > > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> > > > > > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D lesson_id
> > > > > > > > ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999)= ASC,
> > > > > > > > COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meani=ng), 201)
> > > > > > > > ASC
> > > > > > > >
> > > > > > > > ... [ * comment: I removed some other table select for t=he sake of
> > > > > > > > the simplicity post ]
> > > > > > > > END
> > > > > > > > END
> > > > > > > > ELSE
> > > > > > > > -- return fields for basic listing
> > > > > > > > BEGIN
> > > > > > > > IF @.type =3D 1
> > > > > > > > BEGIN
> > > > > > > > -- select kanji ids corresponding to the search string
> > > > > > > > SELECT kanji_id as id
> > > > > > > > INTO #BasicKanjiIdsByKanji
> > > > > > > > FROM dbo.Kanjis
> > > > > > > > WHERE kanji_kanji =3D @.search
> > > > > > > >
> > > > > > > > SELECT kanji_kanji, kanji_meaning
> > > > > > > > FROM Kanjis INNER JOIN #BasicKanjiIdsByKanji ON kanji_id= =3D id
> > > > > > > > ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999)= ASC,
> > > > > > > > COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meani=ng), 201)
> > > > > > > > ASC|||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:
> :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 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:
> >
> > > 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 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:
> > > >
> > > > > 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), @.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
> > > > >
> > > > > 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:
> > > > > > 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
> > > > > >
> > > > > > >
> > > > > > > > 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 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
> > > > > > 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 columns
> > > > > > 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 performance
> > > > > > impact of that.
> > > > > >
> > > > > >
> > > > > > John
> > > > > > > thanks for your time
> > > > > > >
> > > > > > > John Bell wrote:
> > > > > > > > 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 tables
> > > > > > > > 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 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,|||Hello again!
Thanks for the detailed explanations, now it is all clear, and I
appreciate it :)
> The second select statement seems to be very similar to each other of whi=ch
> there is the basic information and full information:
> SELECT kanji_kanji, kanji_meaning
> FROM Kanjis INNER JOIN #BasicKanjiIdsByKanji ON kanji_id =3D 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 =3D id
> LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> LEFT OUTER JOIN Lessons ON lk_idlesson =3D lesson_id
> ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
> COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201) ASC
Yes, you were right, I wonder why I didn't realize and understand what
you meant sooner, I'm sorry! Now, my new query takes that in account by
putting these SELECT at the dispatcher level (and the detailed one now
has its own proc).
I still separate the two resultsets (basic and detailed) however,
because as you noted it, it is not a good idea to return more fields
than needed. Is it still good practice?
Here is my new query that allows multiple search conditions (and
works!). Can you give me some comments about it? I also chose dynamic
SQL over static for this one, as I wrote the same procedure, but with
static code, which was much slower (maybe it's the way I wrote it...I
can post code if you'd like)
ALTER PROCEDURE [dbo].[DicKanjiSearch3] (@.detailed bit, @.type tinyint,
@.search nvarchar(200) =3D '',
@.search2 nvarchar(200) =3D '', @.sc tinyint =3D 0, @.rad tinyint =3D 0, @.gra=de
tinyint =3D 0)
WITH RECOMPILE
AS
-- EXAMPLE SEARCH
-- Detailed resultset about kanji that has
-- a pronunciation of '=E3=82=A2' (or '=E3=81=82')
-- 11 strokes
-- a radical # of 31
--EXECUTE [DicKanjiSearch3] 1, 3, N'=E3=82=A2', N'=E3=81=82',11,31
-- EXAMPLE SEARCH
-- Basic resultset about kanji that has
-- 15 strokes
-- a radical # of 115
-- is learned in grade 8
--EXECUTE [DicKanjiSearch3] 0, 0, N'', N'',15,115,8
-- @.type determines the kind of text @.search contains:
-- 0 =3D no search by text
-- 1 =3D kanji
-- 2 =3D english
-- 3 =3D pronunciation (on'yomi / kun'yomi / nanori)
-- @.search : main text search query
-- @.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.
-- @.sc : if not 0, search by strokes count =3D @.sc
-- @.rad : if not 0, search by radical number =3D @.rad
-- @.grade : if not 0, search by grade level =3D @.grade
BEGIN
SET NOCOUNT ON
DECLARE @.debug bit
SET @.debug =3D 1
DECLARE @.sql nvarchar(4000)
DECLARE @.tables nvarchar(4000)
SET @.tables =3D N''
DECLARE @.where nvarchar(4000)
SET @.where =3D N'1 =3D 1'
DECLARE @.params nvarchar(4000)
SET @.params =3D N'@.search nvarchar(200), @.search2 nvarchar(200), @.sc
tinyint, @.rad tinyint, @.grade tinyint'
-- temp table to hold ids of the multiple searches
CREATE TABLE #Ids (id int PRIMARY KEY)
-- query by kanji
IF @.type =3D 1
BEGIN
SET @.where =3D @.where + N' AND kanji_kanji =3D @.search'
END
-- query by english
ELSE IF @.type =3D 2
SET @.where =3D @.where + N' AND FREETEXT(kanji_meaning, @.search)'
-- query by pronunciation
ELSE IF @.type =3D 3
SET @.where =3D @.where + N' AND (FREETEXT(kanji_on, @.search) OR
FREETEXT(kanji_kun, @.search2) OR
FREETEXT(kanji_nanori, @.search2))'
-- query by strokes count
IF @.sc <> 0
SET @.where =3D @.where + N' AND kanji_strokecount =3D @.sc'
-- query by radical
IF @.rad <> 0
BEGIN
SET @.where =3D @.where + N' AND krad_idradical =3D @.rad'
SET @.tables =3D ' INNER JOIN KanjiRads ON krad_idkanji =3D kanji_id'
END
-- query by grade
IF @.grade <> 0
SET @.where =3D @.where + N' AND kanji_grade =3D @.grade'
-- select kanji ids that satisfy all queries
SET @.sql =3D N'
INSERT INTO #Ids (id)
SELECT kanji_id
FROM Kanjis' + @.tables + '
WHERE ' + @.where
EXEC sp_executesql @.sql, @.params, @.search, @.search2, @.sc, @.rad, @.grade
IF @.debug =3D 1 PRINT @.sql
-- return fields for detailed listing
IF @.detailed =3D 1
EXEC DictKanjiGetDetailed
ELSE
-- select basic info
SELECT kanji_kanji, kanji_meaning
FROM Kanjis INNER JOIN #Ids ON kanji_id =3D id
ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9999) ASC,
COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_meaning), 201) ASC
END
thanks again!
Bruno
John Bell wrote:
> Hi
> The second select statement seems to be very similar to each other of whi=ch
> there is the basic information and full information:
> SELECT kanji_kanji, kanji_meaning
> FROM Kanjis INNER JOIN #BasicKanjiIdsByKanji ON kanji_id =3D 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 =3D id
> LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji_id
> LEFT OUTER JOIN Lessons ON lk_idlesson =3D 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 ju=st
> contain single select statement and you can use INSERT.. EXEC e.g.
> INSERT #Tmptable (id)
> EXECUTE [DicKanjiSearch] N'=E5=8F=8B', 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 n=ot
> 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'=E5=8F=8B', 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:
> > :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 Kanj=i and
> > > LessonKanji! If these are always exclusive then you may want to separ=ate
> > > these into two different procedures.
> > >
> > > To get an AND condition you can select the ids where there is more th=an one
> > > occurence using a group by id and a having clause, providing each ti=me you
> > > add the id you only insert distinct ones.
> > >
> > > John
> > >
> > > "ibiza" wrote:
> > >
> > > > Hello,
> > > >
> > > > > To implement multiple criteria you could separate your procedures= into two
> > > > > bring the second select into DicKanjiSearch, you can then call th=e 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 tim=es"
> > > > (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 impleme=nt it. In
> > > > > general I would only do that if you think your current solution i=s too slow.
> > > > >
> > > > > To implement multiple criteria you could separate your procedures= into two
> > > > > bring the second select into DicKanjiSearch, you can then call th=e various
> > > > > procedures to add new ids to the temporary table multiple times.
> > > > >
> > > > > John
> > > > >
> > > > > "ibiza" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > yes, in fact each of my temporary tables strictly contains an i=d=2E Each
> > > > > > IF branch (now splitted into sub-procedures) fills that temp ta=ble with
> > > > > > the ids related to the search, then do many select queries base=d on
> > > > > > those ids.
> > > > > >
> > > > > > I've change the SELECT INTO and splitted each IF into sub-proce=dures,
> > > > > > here it is now (with some commented example search at the begin=ning) :
> > > > > >
> > > > > > --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=98=E3=82=85=E3=81=86', 0
> > > > > > --EXECUTE [DicKanjiSearch] N'=E3=82=B3=E3=82=B3=E3=83=AD', 3, N='=E3=81=93=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 @.searc=h=2E
> > > > > > -- kun'yomi (japanese) pronunciation is in HIRAGANA ans is @.sea=rch2.
> > > > > > -- nanori (name) pronunciation is also in HIRAGANA ans therefor=e 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 @.sea=rch
> > > > > > 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 wit=h 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 me=thod I
> > > > > > am currently using? That's because I added the search type 4, w=hich
> > > > > > 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.: @.meanin=g =3D
> > > > > > NULL, @.kanji =3D NULL, @.pronH =3D NULL, @.pronK =3D NULL, @.strok=escount =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 mo=re
> > > > > > shorter "sc&en") and parse then to execute corresponding querie=s=2E
> > > > > > Which one would perfrom faster?
> > > > > >
> > > > > > Then I still need a way to merge SELECT tables together...Any i=deas?
> > > > > >
> > > > > > Thank you very much!
> > > > > >
> > > > > > John Bell 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
> > > > > > >
> > > > > > > >
> > > > > > > > > It may be possible to rationalise the use of the differen=t temporary tables
> > > > > > > > > into one, in which case your second select might possibly= be made the same or
> > > > > > > > > more similar and not produce different result sets for ea=ch combination,
> > > > > > > > > which may clean up the client code.
> > > > > > > >
> > > > > > > > How would you do that? I have trouble to get it :S
> > > > > > > Looking at the code you seem to have very similar temporary t=ables 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 se=e more
> > > > > > > rationalisation. I would have expected the current client to =have similar
> > > > > > > code to the stored procedure as you seem to be producing uniq=ue output for
> > > > > > > each one. This could probably be cleaned up if you had the sa=me column names
> > > > > > > for everything. If you took this further and always returned =the same columns
> > > > > > > then you may be able to clean up the client coding further, t=he downside may
> > > > > > > be that you produce a wider result set, you may need to judge= the performance
> > > > > > > impact of that.
> > > > > > >
> > > > > > >
> > > > > > > John
> > > > > > > > thanks for your time
> > > > > > > >
> > > > > > > > John Bell wrote:
> > > > > > > > > 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 differen=t temporary tables
> > > > > > > > > into one, in which case your second select might possibly= be made the same or
> > > > > > > > > more similar and not produce different result sets for ea=ch combination,
> > > > > > > > > which may clean up the client code.
> > > > > > > > >
> > > > > > > > > You may also want to look at splitting the different sect=ions into their own
> > > > > > > > > stored procedures.
> > > > > > > > >
> > > > > > > > > John
> > > > > > > > >
> > > > > > > > > "ibiza" wrote:
> > > > > > > > >
> > > > > > > > > > Hi,
> > > > > > > > > >
> > > > > > > > > > I have a stored proc intented to search a table on diff=erent 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, radi=cal # and so on.
> > > > > > > > > >
> > > > > > > > > > Here it is, with the 3 options (it may seem overwhelmin=g but is only
> > > > > > > > > > because of the IF ELSE structure) :
> > > > > > > > > >
> > > > > > > > > > ... 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 pronu=nciation, we
> > > > > > > > > > must
> > > > > > > > > > -- be able to search in hiragana and katakana. In that =case,
> > > > > > > > > > -- on'yomi (chinese) pronunciation is in KATAKANA ans i=s @.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 =3D 1
> > > > > > > > > > BEGIN
> > > > > > > > > > IF @.type =3D 1
> > > > > > > > > > BEGIN
> > > > > > > > > > -- select kanji ids corresponding to the search stri=ng
> > > > > > > > > > SELECT kanji_id as id
> > > > > > > > > > INTO #KanjiIdsByKanji
> > > > > > > > > > FROM dbo.Kanjis
> > > > > > > > > > WHERE kanji_kanji =3D @.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 ==3D id
> > > > > > > > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji=_id
> > > > > > > > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D lesson_id
> > > > > > > > > > ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9=999) ASC,
> > > > > > > > > > COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_m=eaning), 201)
> > > > > > > > > > ASC
> > > > > > > > > >
> > > > > > > > > > ... [ * comment: I removed some other table select f=or the sake of
> > > > > > > > > > the simplicity post ]
> > > > > > > > > > END
> > > > > > > > > > ELSE IF @.type =3D 2
> > > > > > > > > > BEGIN
> > > > > > > > > > -- select kanji ids corresponding to the search stri=ng
> > > > > > > > > > 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 =3D= id
> > > > > > > > > > LEFT OUTER JOIN LessonKanji ON lk_idkanji =3D kanji=_id
> > > > > > > > > > LEFT OUTER JOIN Lessons ON lk_idlesson =3D lesson_id
> > > > > > > > > > ORDER BY kanji_isjouyou DESC, COALESCE(kanji_freq, 9=999) ASC,
> > > > > > > > > > COALESCE(kanji_grade, 99) ASC, COALESCE(LEN(kanji_m=eaning), 201)
> > > > > > > > > > ASC
> > > > > > > > > >
> > > > > > > > > > ... [ * comment: I removed some other table select f=or the sake of
> > > > > > > > > > the simplicity post ]
> > > > > > > > > > END
> > > > > > > > > > ELSE IF @.type =3D 3
> > > > > > > > > > BEGIN
> > > > > > > > > > -- select kanji ids corresponding to the search stri=ng
> > > > > > > > > > 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,|||Hi
You should add owner/schema prefixes to the tables and stored procedures e.g
SET @.tables = ' INNER JOIN dbo.KanjiRads ON krad_idkanji = kanji_id'
It seems that you are differentiating columns in different tables be calling
them differently. If you have a kanj_id in different table give it the same
name, to differential them in a query use a table alias e.g.
SET @.tables = ' INNER JOIN dbo.KanjiRads r ON r.kanji_id = k.kanji_id'
r is the table alias for KanjiRads and k would be the table alias for Kanjis.
Add some error checking see http://www.sommarskog.se/error-handling-II.html
and http://www.sommarskog.se/error-handling-I.html
I would put the "-- select basic info" query into a different procedure.
Query Analyser has it's own debugging facilities (right click the stored
procedure in the object browser and choose debug), using this would mean that
you can leave you do not have to change live code to debug it. Currently you
will need to change the @.debug value to stop the statement being displayed.
John
"ibiza" wrote:
> Hello again!
> Thanks for the detailed explanations, now it is all clear, and I
> appreciate it :)
> > 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
> Yes, you were right, I wonder why I didn't realize and understand what
> you meant sooner, I'm sorry! Now, my new query takes that in account by
> putting these SELECT at the dispatcher level (and the detailed one now
> has its own proc).
> I still separate the two resultsets (basic and detailed) however,
> because as you noted it, it is not a good idea to return more fields
> than needed. Is it still good practice?
> Here is my new query that allows multiple search conditions (and
> works!). Can you give me some comments about it? I also chose dynamic
> SQL over static for this one, as I wrote the same procedure, but with
> static code, which was much slower (maybe it's the way I wrote it...I
> can post code if you'd like)
> ALTER PROCEDURE [dbo].[DicKanjiSearch3] (@.detailed bit, @.type tinyint,
> @.search nvarchar(200) = '',
> @.search2 nvarchar(200) = '', @.sc tinyint = 0, @.rad tinyint = 0, @.grade
> tinyint = 0)
> WITH RECOMPILE
> AS
> -- EXAMPLE SEARCH
> -- Detailed resultset about kanji that has
> -- a pronunciation of 'ã?¢' (or 'ã'')
> -- 11 strokes
> -- a radical # of 31
> --EXECUTE [DicKanjiSearch3] 1, 3, N'ã?¢', N'ã'',11,31
> -- EXAMPLE SEARCH
> -- Basic resultset about kanji that has
> -- 15 strokes
> -- a radical # of 115
> -- is learned in grade 8
> --EXECUTE [DicKanjiSearch3] 0, 0, N'', N'',15,115,8
> -- @.type determines the kind of text @.search contains:
> -- 0 = no search by text
> -- 1 = kanji
> -- 2 = english
> -- 3 = pronunciation (on'yomi / kun'yomi / nanori)
> -- @.search : main text search query
> -- @.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.
> -- @.sc : if not 0, search by strokes count = @.sc
> -- @.rad : if not 0, search by radical number = @.rad
> -- @.grade : if not 0, search by grade level = @.grade
> BEGIN
> SET NOCOUNT ON
> DECLARE @.debug bit
> SET @.debug = 1
> DECLARE @.sql nvarchar(4000)
> DECLARE @.tables nvarchar(4000)
> SET @.tables = N''
> DECLARE @.where nvarchar(4000)
> SET @.where = N'1 = 1'
> DECLARE @.params nvarchar(4000)
> SET @.params = N'@.search nvarchar(200), @.search2 nvarchar(200), @.sc
> tinyint, @.rad tinyint, @.grade tinyint'
> -- temp table to hold ids of the multiple searches
> CREATE TABLE #Ids (id int PRIMARY KEY)
> -- query by kanji
> IF @.type = 1
> BEGIN
> SET @.where = @.where + N' AND kanji_kanji = @.search'
> END
> -- query by english
> ELSE IF @.type = 2
> SET @.where = @.where + N' AND FREETEXT(kanji_meaning, @.search)'
> -- query by pronunciation
> ELSE IF @.type = 3
> SET @.where = @.where + N' AND (FREETEXT(kanji_on, @.search) OR
> FREETEXT(kanji_kun, @.search2) OR
> FREETEXT(kanji_nanori, @.search2))'
> -- query by strokes count
> IF @.sc <> 0
> SET @.where = @.where + N' AND kanji_strokecount = @.sc'
> -- query by radical
> IF @.rad <> 0
> BEGIN
> SET @.where = @.where + N' AND krad_idradical = @.rad'
> SET @.tables = ' INNER JOIN KanjiRads ON krad_idkanji = kanji_id'
> END
> -- query by grade
> IF @.grade <> 0
> SET @.where = @.where + N' AND kanji_grade = @.grade'
> -- select kanji ids that satisfy all queries
> SET @.sql = N'
> INSERT INTO #Ids (id)
> SELECT kanji_id
> FROM Kanjis' + @.tables + '
> WHERE ' + @.where
> EXEC sp_executesql @.sql, @.params, @.search, @.search2, @.sc, @.rad, @.grade
> IF @.debug = 1 PRINT @.sql
> -- return fields for detailed listing
> IF @.detailed = 1
> EXEC DictKanjiGetDetailed
> ELSE
> -- select basic info
> SELECT kanji_kanji, kanji_meaning
> FROM Kanjis INNER JOIN #Ids 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
> thanks again!
> Bruno
> John Bell wrote:
> > 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:
> >
> > > :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 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:
> > > >
> > > > > 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 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:
> > > > > >
> > > > > > > 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), @.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