I'm using BTS 2002 (SP1) and AICSQL to parse XML documents
and insert/update records in an SQL Server db table (using
stored procedures).  Current testing is being done on a
fast Dell 2650 Server.  SQL Server is installed on this
same server for testing purposes and is a stand alone
config.  There is a mapping between the document
definitions, and existence functoids are used for fields
that may not be present in the data instance.  One
definition/map is used for Insertions/Updates/Deletions
etc; a scripting functiod determines the relevant stored
procedure param name (insert, update etc).

All works well except when processing documents for
update!  For example, testing 617 data instances (all for
insertion as new records), collected via a file receive
function, are processed within say 30 seconds.  Using same
data instances (simple single record XML) but amended for
updating the previously inserted records can take anything
up to 20 mins!  The interchanges spend a long time in the
work queue and sometimes up to a quarter are placed in the
retry queue and warnings are then generated.  The warning
is always a parsing failure and transport error (while
processing message port using transport component of
AICSQL pipeline 1).  Any retry and suspension queue docs
will eventually parse and update.

So, why does it take so long to do updates compared to
inserts (using same type of data, mapping, doc defs etc)?
Is there something I'm not doing right?  Any suggestions
much appreciated.

Phil.

[ Post a follow-up to this message ]

Hi

I am not sure this will work, but are there any indices on the tables
you are updating in SQL Server? Perhaps try with more indices or less
indices?

Jan Eliasen

On Wed, 23 Jun 2004 06:05:38 -0700, "Phil"
<anonymous@discussions.microsoft.com> wrote:

>I'm using BTS 2002 (SP1) and AICSQL to parse XML documents
>and insert/update records in an SQL Server db table (using
>stored procedures).  Current testing is being done on a
>fast Dell 2650 Server.  SQL Server is installed on this
>same server for testing purposes and is a stand alone
>config.  There is a mapping between the document
>definitions, and existence functoids are used for fields
>that may not be present in the data instance.  One
>definition/map is used for Insertions/Updates/Deletions
>etc; a scripting functiod determines the relevant stored
>procedure param name (insert, update etc).
>
>All works well except when processing documents for
>update!  For example, testing 617 data instances (all for
>insertion as new records), collected via a file receive
>function, are processed within say 30 seconds.  Using same
>data instances (simple single record XML) but amended for
>updating the previously inserted records can take anything
>up to 20 mins!  The interchanges spend a long time in the
>work queue and sometimes up to a quarter are placed in the
>retry queue and warnings are then generated.  The warning
>is always a parsing failure and transport error (while
>processing message port using transport component of
>AICSQL pipeline 1).  Any retry and suspension queue docs
>will eventually parse and update.
>
>So, why does it take so long to do updates compared to
>inserts (using same type of data, mapping, doc defs etc)?
>Is there something I'm not doing right?  Any suggestions
>much appreciated.
>
>Phil.

--
Jan Eliasen, representing himself and not the company he works for.

[ Post a follow-up to this message ]