Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Question Unanswered: Is my table scan unavoidable?

    Hi guys-n-gals...

    I have a table that contains the following:
    PortfolioID(int), EndDate(smalldatetime), Begdate(smalldatetime), WklyCloseIndex (float)

    It has a primary key which is PortfolioID/BegDate/EndDate

    I need to create a table that summarizes, by date range, the weeklycloseindex of several portfolios, like this:

    BegDate EndDate Portfolio2 Portfolio67 Portfolio11 Portfolio90
    05/28/2004 06/04/2004 xx.xx xx.xx xx.xx xx.xx
    05/21/2004 05/28/2004 xx.xx xx.xx xx.xx xx.xx

    So I wrote a function...

    This function results in a table scan *GASP!!!!* (at least that's what the 'splain plan tells me when I run it in SQL Analyzer). Before I made it into the function, when I was testing the code in SQL Analyzer, it resulted NOT in a table scan, but rather a series of nested loops (the joins) and clustered index seeks...resulting in about 1/3 the total cost of the function.

    I suspected originally that it was the TOP/ORDER BY that the function insisted upon, but even if I remove those, still get a table scan.

    Wassup? Why does the function turn my cool lil' self-join into a table scan? Whut am I missing? Any thoughts? Disgusted Derisions? Hurled Insults? Bring it on!!! (please! )

    My predecessor did this in a similar project using a separate cursor for each portfolio by date, then looped through the dates, pulling in the per-portfolio index value and building the output table. I would rather avoid the cursors if I can.
    Last edited by TallCowboy0614; 06-10-04 at 18:29.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Nevermind...found it...

    it wasn't the logic inside the function that was the cause of the table scan...but rather the code 'Select * from <function> ' that caused it to be reported as a table scan *blush*

    So...how does one get an execution plan for what goes on inside a function anyway? Just cut out the code and run it in the analyzer? Seems like ya ought to be able to 'splain the internal cost of a function by calling it too...Hmmm...

    Anyway, thanks for reading!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Highlight it in the object browser on Query Analyzer and hit "Display Estimated Execution Plan". ??
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    nope...not this time.

    Quote Originally Posted by derrickleggett
    Highlight it in the object browser on Query Analyzer and hit "Display Estimated Execution Plan". ??
    Nope, doesn't give me the results I would like...since in order to execute the function, I have to do something like
    Code:
    select * from dbo.fn_Get_Performance_Table ('2004-01-01', '2004-06-01')
    (my function returns a table...). So the execution plan displayed just gives me a table scan due to the "select * from" part of the overall query.

    I just used cut-n-paste to copy the internal function code to the QA window, then used "show execution plan" to actually get what I need...just thought (or hoped) there was a way to "see inside" the function in an explain (sorry...Tandem on the brain...OUCH!) ...errr...EXECUTION plan...

    Thanks for taking a shot!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This has been a bit of a problem with the Show Estimated Execution feature in Query Analyzer. It is blind to the costs of UDFs. I have come across a number of postings, and anecdotes about people using functions, and being really happy about how clean their code looks. After they test it, though, they go back to the ugly code without functions, and vow never to drink-er, that is, never to use functions again.

    The performance problem in a function is that if you have a query in the function, that goes out and racks up say 20 reads and 3 ticks of CPU, then you put that function into the field list of a query that returns 1000 rows, you have a query that runs up 20,000 reads, and 3 full seconds of CPU time.

    As with anything, of course, use caution and test.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •