MS SQL XML - OpenXML + Remote Scan performance help required.

Author

OpenXML + Remote Scan performance help required.

jamie.downs@risk.sungard.com

2005-09-28, 9:23 am

Hi,

I'm a bit of a newbie where XML is concerned but I have come accross a
stored procedure which we heavily use and I would like to know if is
possible to improve its definition(performan
ce).

When examining the execution plan, 80% is a Remote Scan. Please can
someone explain what this means.

I can supply the stored proc if this helps. If a solution does not
exist for SQL 2000 but one does in SQL 2005 I would also be interested
in this.

Thanks in advance for any help offered.

Jamie.

Michael Rys [MSFT]

2005-10-01, 8:23 pm

OpenXML is implemented as basically a Rowset provider. Can you please post
the code of the stored proc? We probably can provide some tips and how to
potentially improve it in SQL Server 2005.

Best regards
Michael

<jamie.downs@risk.sungard.com> wrote in message
news:1127915702.793233.198740@g44g2000cwa.googlegroups.com...
> Hi,
>
> I'm a bit of a newbie where XML is concerned but I have come accross a
> stored procedure which we heavily use and I would like to know if is
> possible to improve its definition(performan
ce).
>
> When examining the execution plan, 80% is a Remote Scan. Please can
> someone explain what this means.
>
> I can supply the stored proc if this helps. If a solution does not
> exist for SQL 2000 but one does in SQL 2005 I would also be interested
> in this.
>
> Thanks in advance for any help offered.
>
> Jamie.
>

jamie.downs@risk.sungard.com

2005-10-03, 3:23 am

Hi Michael,

Thanks for the reply. I appreciate any advice on this as this proc is
executed many thousands of times a day. Please find below the S-Proc:

CREATE PROCEDURE dbo. spResolvePfolIds_XML
(@PfolIdentifier TEXT)
AS
BEGIN

SET NOCOUNT ON

-- sProc to select the portfolio ids for a given set of portfolio
identifiers - submitted as the text input parameter.
-- An XML 'virtual table' technique is then used for the
lookup/resolve. Used in the TPortfolioList.ResolveIDs routine
-- Define an XML document
DECLARE @iDoc INT

-- Create the XML document, inputting the Portfolio Identifiers
(input parameter)
EXEC sp_xml_preparedocume
nt @iDoc OUTPUT, @PfolIdentifier

-- Select the: XML Sequence Number (used as a pointer)
-- XML Portfolio Identifier itself
-- Portfolio ID from the Portfolio table (a join made
between the XML 'virtual table'
-- and Portfolio
via Identifier)
SELECT PfolIdentifiers.s, PfolIdentifiers.i, P.PortfolioID,
P.HasChecks
FROM Portfolio AS P WITH (NOLOCK),
OPENXML (@idoc, '/doc/a',1) WITH (s INT, i VARCHAR(100)) AS
PfolIdentifiers
WHERE P.Identifier = i

-- Destroy the XML Document
EXEC sp_xml_removedocumen
t @iDoc
END

Cheers
Jamie.

Sponsored Links