Post reply

user defined table function problem

  • December 24, 2008 at 5:07 am

    #155052

    Hi all and merry xmas...

    I have a problem.

    I made a user table function (SQL Server 2005) that return the result of a query. If I run from management studio the same query it return me the result set in 15 seconds, if I select from the UDF it sleep for days until I stop it?

    Where is the problem? The UDF run the same qry I run from management studio. I tried to run the sp_recompile sp before run the UDF but no way, it doesn't run.

    Any suggestion?

    Thanks a lot and again merry Xmas

    Roberto

  • December 24, 2008 at 5:15 am

    #917312

    15 seconds is a long time, unless it is fiddling with billions of records.

    post the query itself so we can see, maybe a missing join or something is killing performance.

    saving it as a udf shouldn't make much of a difference, as long as statistics are up to date.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 24, 2008 at 5:20 am

    #917315

    It works with joins on 5 tables each with more than 100 milions of records, so 15 seconds is a good working time, the statistics on the indexes are up to date.

    I can't understand.

  • December 24, 2008 at 2:55 pm

    #917572

    Someone in the forum can be of help to you if they can see the code here.

    Regards,

    Sam.

  • December 24, 2008 at 7:48 pm

    #917595

    post the query.

    you might be suffering from parameter sniffing, where an execution plan is built on an assumption by the Query optimizer about one or more of the parameters;

    then when it is actually called, because the assumption is off, the execution plan is useless, and ends up being a never ending query.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply