| Author |
Topic |
|
pleibrecht
Starting Member
5 Posts |
 Posted - 2003-07-16 : 12:18:10
|
| Hi all,I've got an issue where adding a UDF inside a sproc results in a timeout from a web page. The sproc can be successfully run in QA with no problems and is very fast. If I remove the UDF from the sproc and call it from a web page it also runs fine and is very fast. I know it's not a security issue because I changed my connection string to connect as sa just to make sure that wasn't it. One last thing I tried as well was watching the call via SQL Profiler and what happens is also mysterious, I put a breakpoint on the acutal DB call and it hits immediately but doesn't pop up in Profiler for something like 30 seconds or longer. It's as if the call gets lost in limbo or something. I'm at a loss here. Has anyone experienced something like this before or know what might cause a problem like this?Thanks for any help.Paul |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-16 : 12:23:45
|
| How many rows of data is the UDF transferring? Could be just a lot of data is being processed. Perhaps if you post your UDF, we might be able to help out.Tara |
 |
|
|
pleibrecht
Starting Member
5 Posts |
Posted - 2003-07-16 : 12:31:39
|
| The UDF returns 1 record from an indexed view. The code is something like this:create function fn_GetAlbumReleaseDate( @artist varchar(25), @title varchar(25))returns varchar(4)asbegin returns (select min([year]) from vw_FreeDB_Artist_Album where artist = @artist and album = @album and [year] is not null and ltrim([year]) != '')end |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2003-07-16 : 12:39:56
|
| Two things I see:1) minor, but checking for year being non-null is redundant when you also check for it being != ''. You only need the second check as all comparison operators against NULL return false.2) you don't specify the noexpand hint after the view so it's not clear that you're accessing the materialized pages vs. executing the joins in the view definition. I don't advocate using join or indexing hints ever but this might be the one exception.Jonathan{0} |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-16 : 12:40:27
|
| I have not seen a UDF written this way. Why return VARCHAR(4) also? Why not CHAR(4) since years are 4 digits? And why is the year column character anyway? Wouldn't INT be better?I have rewritten your UDF. It is essentially equivalent, but maybe this way is more efficient. Give it a try at least.CREATE FUNCTION fn_GetAlbumReleaseDate ( @artist VARCHAR(25), @title VARCHAR(25)) RETURNS CHAR(4) ASBEGINDECLARE @minyear CHAR(4)SELECT @minyear = MIN([year]) FROM vw_FreeDB_Artist_Album WHERE artist = @artist AND album = @album AND [year] IS NOT NULLAND LTRIM([year]) != ''RETURN @minyear ENDTaraEdited by - tduggan on 07/16/2003 12:41:57 |
|
|
|
pleibrecht
Starting Member
5 Posts |
Posted - 2003-07-16 : 12:51:21
|
| I agree that the data type of the column should be int but I didn't build the table so don't have a choice. I'm checking for null and for empty fields because there actually are rows where there's is something other than null which when originally run would return a blank value as a result since ' ' is less than an actual number I guess. All this aside, as I originally stated there isn't a problem running this in QA and it's extremely fast. The problem is occurring when trying to execute it from an asp.net page. |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2003-07-16 : 13:05:52
|
| Do you connect in QA as the same user your web page is using? Shot in the dark here, but try a GRANT EXECUTE ON [fn_GetAlbumReleaseDate] TO [Public]statement.~ monkey |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-16 : 13:11:57
|
| Paul mentioned that he has already tried the sa account and still experiences the same problem. Paul, you might want to post a portion of your ASP .NET code. I won't be able to help for this part, but plenty of other people here will be able to let you know if they see any problems.Tara |
|
|
|
pleibrecht
Starting Member
5 Posts |
Posted - 2003-07-16 : 13:48:56
|
| I actually don't make the call from the asp.net page but rather a data access component. I generally abstract all data access into a seperate layer, but the whole DB call works fine from the client side if I edit the sproc to not include the UDF. Someone made a point earlier about using hints. I didn't consider this before because when I built the view and created the index for it I tested queries against the view and checked the Query plan to make sure the index was indeed being used, but now I'm wondering if for some reason in this particular scenario it's not being used. I will try adding NOEXPAND to the UDF. It's worth a shot. |
|
|
|
pleibrecht
Starting Member
5 Posts |
Posted - 2003-07-17 : 09:51:59
|
| Just wanted to let you all know that the problem has been resolved. I added the NOEXPAND hint to the udf that calls the indexed view and it in turn uncovered the problem. When creating an indexed view there have to be a lot of SET vars turned on which most of them ususally are by default if using QA but when I was making the call to the sproc from the asp.net app, ARITHABORT was not set in the sproc so the udf that calls the indexed view defaulted to NOEXPAND. Long story short, by adding ARITHABORT to the sproc it all works great! Thanks for everyone's help and Jonathan good call on the index hint! |
|
|
|
|
UDF in sproc causing weird behavior