Post reply

Strange performance problem involving SQL Compatibility and use of a temp table

  • April 22, 2010 at 12:08 pm

    #138703

    The following statement performs very, very poorly when run against a database with Compatibility Level = 100. The same statement, when run on the same server against a database with Compatibility Level = 90, returns almost instantly. NOTE: The bad performance is not apparent unless the number of items in the xml string is greater than 3000, but for obvious reasons, I have not included a fully populated xml string in this post.

    Server Configuration:

    SQL Version: 10.0.2757

    Data/Log/TempDB are all on separate drives.

    Anybody have an ideas as to why the compatibility level would affect the performance of this?

    DECLARE @p3 xml

    SET @p3=convert(xml,N'<IdList><Id>13187666</Id><Id>13187667</Id><Id>13187668</Id><Id>13187669</Id></IdList>')

    DECLARE @IdTable TABLE (Id int primary key)

    INSERT INTO @IdTable

    SELECT xmln.nv.value('.','int') as Id

    FROM @p3.nodes( 'IdList/Id' ) as xmln(nv)

  • April 22, 2010 at 2:01 pm

    #1156416

    Duplicate post (moved to SS2K8 forum for a vaild reason... ;-))

    Continue here


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 2 (of 2 total)

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