(SQL Server) XML Path Performance Optimizations

Discusses some important things to know about using Chilkat paths in the Chilkat XML API.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @xml int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Let's load XML containing the following:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <xyz>
    --     <licenses>
    --         <license>
    --             <id>1234</id>
    --         </license>
    --         <license>
    --             <id>1234</id>
    --         </license>
    -- ...
    -- My sample XML contains 64,000 "license" nodes ..
    -- ...
    --         <license>
    --             <id>1234</id>
    --         </license>
    --         <license>
    --             <id>1234</id>
    --         </license>
    --     </licenses>
    -- </xyz>
    -- 
    DECLARE @success int
    EXEC sp_OAMethod @xml, 'LoadXmlFile', @success OUT, 'qa_output/large.xml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @xml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- Iterating over the individual "license" nodes with this code snippet is
    -- extremely slow:
    DECLARE @licCount int
    EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @licCount OUT, 'licenses|license'

    PRINT 'license count = ' + @licCount

    DECLARE @s nvarchar(4000)

    DECLARE @i int
    SELECT @i = 0
    -- If "10" is changed to licCount, then it becomes apparent that this loop gets slower with each iteration.
    WHILE @i < 10
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i
        EXEC sp_OAMethod @xml, 'GetChildContent', @s OUT, 'licenses|license[i]|id'


        PRINT @i + ': ' + @s
        SELECT @i = @i + 1
      END

    -- The reason it is extremely slow is that the "license[i]" part of the path passed to GetChildContent
    -- says: find the i'th child of "licenses" having the tag "license".  Chilkat cannot assume that all
    -- children of an XML node have the same tag.  Therefore it's not possible to directly access the i'th child.
    -- Internally, Chilkat must start at the 1st child and iterate until it reaches the i'th child having the
    -- tag "license".

    -- For example, imagine if the XML was like this:

    -- <?xml version="1.0" encoding="utf-8"?>
    -- <xyz>
    --     <licenses>
    --         <license>
    --             <id>1234</id>
    --         </license>
    --         <somethingElse>
    --             <a>abc</a>
    --         </somethingElse>
    --         <license>
    --             <id>1234</id>
    --         </license>
    -- ...

    -- In the above XML, the 1st "license" is the 1st child of "licenses", but the 2nd "license"
    -- is the 3rd child of "licenses".

    -- If you already know that all children have the same tag, there is a shortcut that allows
    -- for direct access to that child.  Just leave off the tag name, like this:

    SELECT @i = 0
    -- If "10" is changed to licCount, then we can see the time for each loop is the same, and it's fast.
    WHILE @i < 10
      BEGIN
        EXEC sp_OASetProperty @xml, 'I', @i
        EXEC sp_OAMethod @xml, 'GetChildContent', @s OUT, 'licenses|[i]|id'


        PRINT @i + ': ' + @s
        SELECT @i = @i + 1
      END

    -- When we pass just the index "[i]", we're saying: Get the i'th child regardless of tag.
    -- This is extremely fast because internally we can just access the i'th child directly.

    -- Another performance improvement is to call NumChildrenAt rather than NumChildrenHavingTag.
    -- For example:
    EXEC sp_OAMethod @xml, 'NumChildrenAt', @licCount OUT, 'licenses'

    PRINT 'licCount = ' + @licCount

    -- NumChildrenAt returns the total number of children at the tag path.  If we already know
    -- all children will have the same tag, we can just get the count

    EXEC @hr = sp_OADestroy @xml


END
GO