Interoperating with SQL Server

After we have queried our index (see Building an Index) and converted the results into XML we can simply pass this XML to SQL Server.

If you recently read the building an index page at about this point I’m sure you are thinking something along the lines of I’m using SQL Server 2005, why not use the XmlDocument rather than pass this value in as text? The answer to that question is simple – For some very strange reason the new XML capability in SQL 2005/2008 is VERY SLOW! It is unusably slow for sets of data larger than just 500 nodes!

Here is a snippet of my T-SQL code:
ALTER PROCEDURE [dbo].[QuickSearch]
(
    @TracksKeysXml text
    ,@LanguageID int
    ,@PageSize int
    ,@PageNumber int
    ,@RecordLabelID int = NULL
    ,@UserID int
    ,@SortID int = NULL 
    ,@RowCount int OUTPUT
) AS
BEGIN

    DECLARE @Tracks TABLE 
    ( 
        TrackID int primary key, 
        [Rank] real 
    )

    IF ( @TracksKeysXml IS NOT NULL )
    BEGIN
        DECLARE @xmlDocTracks int
        EXEC sp_xml_preparedocument 
            @xmlDocTracks OUTPUT, 
            @TracksKeysXml

        /* xml input: 
        <root>
            <result>
                <key>123456</key>
                <rank>0.75245</rank>
            </result>
        </root>*/
                
        INSERT INTO @Tracks
            SELECT * FROM 
            OPENXML(@xmlDocTracks, '/root/result', 2) 
            WITH ( [key] int 'key[1]', [rank] real 'rank[1]')
            
        EXEC sp_xml_removedocument @xmlDocTracks
    END

...


Using this method I was able to pass tens of thousands of results into SQL Server without much of a performance impact. If you are doing some other specialized sorting in the database then you'll have to pass all of the nodex so it's not recommended that you do that. Sorting on anything but relevance can be expensive. If you don't require specialized sorting then I recommend haldlign the paging logic before passing the results into SQL Server.

Last edited Oct 16, 2009 at 6:25 PM by NathanZaugg, version 1

Comments

No comments yet.