Post reply

table variables in function slower in 2008

  • January 31, 2012 at 2:10 pm

    #250820

    i have a function that returns a table that executes in 40 secs. in 2000. I created the fucntion in 2008 r2 and it takes 4 min to run the same function. The function uses several table variables and updates them before summarizing the results into the returned table. I would show the sql but it is rather complicated and is netting inventory on the fly.

    My questions is why would the function execute so poorley on 2008? Is there anything that I need to be aware that is differnt in 2008 as it related to table variables.

    This function is used all over our application to determine current inventory levels. It would be a huge undetaking to replace the function with some other means (procedure,etc.)

  • January 31, 2012 at 2:30 pm

    #1440968

    The answer is "It Depends".

    Several factors will influence:

    1. Data Load

    - Are you sure the data load between 2000 and 2008 R2 is the same? a variance of even 5% can have drastic impacts on Table Variable performance in joins.

    2. How many rows in each table variable?

    - More than about 50 - 100 and a join, you need to be wary

    3. Is this the first time the function is being executed?

    - compile/optimization time can affect

    - Bad query plan can be cached from the first time a function is used (look up parameter sniffing)

    4. Indexing

    - Are the base tables that are filling the Table Variables indexed properly?

    Without much more information, and sample data and sample data loads and/or execution plans it will be nearly impossible to tell specifically.

  • January 31, 2012 at 2:38 pm

    #1440971

    I was about to type nearly the same thing venoym, but you beat me to it.

    One thing I would also check is whether or not it is possible to make the function an inline table function in SQL2008 as opposed to a multistatement table function. It can make a huge difference.

  • January 31, 2012 at 2:56 pm

    #1440979

    spot on advice from venoym and Recurs1on;

    if you can provide more details, we can help you witht he perforamnce issue;

    ideally, if you can save the ACTUAL execution plan and post it here, that will give any volunteers anything they need to look at the root causes.

    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!

  • January 31, 2012 at 3:00 pm

    #1440982

    Possibly a question so obvious that it verges on the daft, but have you run a statistics update when you migrated?

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

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

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