Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GreatInca
Posting Yak Master
102 Posts |
 Posted - 2001-11-28 : 15:50:31
|
| I got a query that runs in 1-2 seconds in QA. Its very optimized. It, however accesses a user defined function and also accesses an indexed computed column that is generated by a user defined function. This query will not run through ADO, either as formed in VB or when run as a Stored procedure. Anybody know how to keep ADO from tripping over this user defined function (it calculates metaphone codes - replacing soundex) |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2001-11-28 : 17:16:59
|
| Does it run as a Stored Procedure outside of using ADO? Is this another case of needing to put SET NOCOUNT ON in the sproc? If not, then what do you mean by ADO is "tripping over this user defined function"?-------------------It's a SQL thing... |
 |
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2001-11-28 : 18:18:26
|
| All versions have SET NOCOUNT ON.Tripping over the user defined function as in that it runs in 1-2 seconds in query analyzer in either query form or dynamic query in a sp form (has to be dynamic becuase of bulk insert). But it times out (60 seconds I think) (Microsoft OLE DB Provider for ODBC Drivers error '80040e31' [Microsoft][ODBC SQL Server Driver]Timeout expired /live-dev/spellcheck/spellcheck3.asp, line 114 ) when I run it through ASP. WHen I change over to the SoundEx version it works fine.Metaphone verson (Times out in ASP, runs fine in QA:SET NOCOUNT ON CREATE TABLE #Words (WordID int IDENTITY(1,1), Word VarChar(50) NOT NULL DEFAULT '') BULK INSERT #Words FROM 'd:\iisroot\live-dev\SpellCheck\CheckFiles\SpellCheck90.txt' WITH (TABLOCK, FORMATFILE='d:\iisroot\live-dev\SpelLCheck\Words.fmt') SELECT BadWords.WordID, ISNULL(WordListM.Length,0) AS Length, BadWords.Word, '"' + ISNULL(CASE WHEN ASCII(LEFT(BadWords.Word,1))<91 AND ASCII(RIGHT(BadWords.Word,1))<91 THEN UPPER(WordListM.Word) WHEN ASCII(LEFT(BadWords.Word,1))<91 THEN UPPER(LEFT(WordListM.Word,1)) + RIGHT(WordListM.Word,WordListM.Length-1) ELSE WordListM.Word END,0) + '",' AS Suggestion FROM ( SELECT WordID, Word, CONVERT(VarChar(50), dbo.metaphone(word,50)) AS Metaphone, CONVERT(int, LEN(Word)*.65) AS LengthL, CONVERT(int,LEN(Word)*1.35) AS LengthH FROM #Words WHERE LEN(Word)>1 AND NOT EXISTS (SELECT * FROM WordList_SpellCheck WHERE WordList_SpellCheck.Word=LOWER(#Words.Word)) ) AS BadWords LEFT OUTER JOIN WordList_Suggestion AS WordListM ON BadWords.Metaphone=WordListM.[Metaphone] AND WordListM.Length BETWEEN BadWords.LengthL AND BadWords.LengthH ORDER BY BadWords.WordID, BadWords.Word, ABS(LEN(BadWords.Word)-WordListM.Length), LEN(BadWords.Word)-WordListM.Length, Suggestion DROP TABLE #Words SoundEx Version (runs fine in ASP And QA)SET NOCOUNT ON CREATE TABLE #Words (WordID int IDENTITY(1,1), Word VarChar(50) NOT NULL DEFAULT '') BULK INSERT #Words FROM 'd:\iisroot\live-dev\SpellCheck\CheckFiles\SpellCheck90.txt' WITH (TABLOCK, FORMATFILE='d:\iisroot\live-dev\SpelLCheck\Words.fmt') SELECT BadWords.WordID, ISNULL(WordListM.Length,0) AS Length, BadWords.Word, '"' + ISNULL(CASE WHEN ASCII(LEFT(BadWords.Word,1))<91 AND ASCII(RIGHT(BadWords.Word,1))<91 THEN UPPER(WordListM.Word) WHEN ASCII(LEFT(BadWords.Word,1))<91 THEN UPPER(LEFT(WordListM.Word,1)) + RIGHT(WordListM.Word,WordListM.Length-1) ELSE WordListM.Word END,0) + '",' AS Suggestion FROM ( SELECT WordID, Word, CONVERT(Char(4), SoundEx(Word)) AS SoundEx, CONVERT(int, LEN(Word)*.65) AS LengthL, CONVERT(int,LEN(Word)*1.35) AS LengthH FROM #Words WHERE LEN(Word)>1 AND NOT EXISTS (SELECT * FROM WordList_SpellCheck WHERE WordList_SpellCheck.Word=LOWER(#Words.Word)) ) AS BadWords LEFT OUTER JOIN WordList_Suggestion AS WordListM ON BadWords.SoundEx=WordListM.[SoundEx] AND WordListM.Length BETWEEN BadWords.LengthL AND BadWords.LengthH ORDER BY BadWords.WordID, BadWords.Word, ABS(LEN(BadWords.Word)-WordListM.Length), LEN(BadWords.Word)-WordListM.Length, Suggestion DROP TABLE #Words The difference in the 2 versions is in 2 lines - the Select in the subquery and the left outer join to the suggestions. It seems to be the Join predicate on the computed column that is derived from the metaphone user sql function that is causing the problem. Replacing only the left outer Join to the suggestion's line and not the subqueries' select line makes it work fine but it times out when the opposite occurs (select is soundex, left outer join is metaphone). The WordList_Suggestion table has 3 computed columns - soundex, metaphone, and length. There are indexes on soundex and length & metaphone and length. |
|
|
|
|
|
|
|
|
ADO having problems with user SQL functions