|
Home > Archive > BizTalk Server General > June 2004 > VERY SLOW AICSQL UPDATES!
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
VERY SLOW AICSQL UPDATES!
|
|
|
| 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.
| |
| Lee Graber [MSFT] 2004-06-28, 8:47 pm |
| Is this a Microsoft SQL Adapter (AIC), or is this a custom AIC that you
wrote? I don't understand the parsing failures. Parsing takes place before
data reaches the workQ, so you must be doing some parsing in your AIC. What
I am guessing is happening is that you have lock contention in your
database which is serializing the processing of all of your data. We have a
limited worker thread pool and once all of our threads are tied up because
they are serialized trying to update your data in your AIC, we will leave
the rest of the data in SQL in case another BTS instance appears to process
the data. Have you done any analysis of your db to see if you are having
contention issues?
HTH
Lee
This posting is provided "AS IS" with no warranties, and confers no rights.
EBusiness Server Team
--------------------[vbcol=seagreen]
| |
|
| Hi Lee
I am using Microsofts AICSQL! I will do some more
analysis and post if relevant. I find it difficult to
understand why it works very well when calling the Insert
stored procedure but not so well when calling the Update
stored procedure (same map/ doc defs etc).
Thanks Phil.
>-----Original Message-----
>Is this a Microsoft SQL Adapter (AIC), or is this a
custom AIC that you
>wrote? I don't understand the parsing failures. Parsing
takes place before
>data reaches the workQ, so you must be doing some parsing
in your AIC. What
>I am guessing is happening is that you have lock
contention in your
>database which is serializing the processing of all of
your data. We have a
>limited worker thread pool and once all of our threads
are tied up because
>they are serialized trying to update your data in your
AIC, we will leave
>the rest of the data in SQL in case another BTS instance
appears to process
>the data. Have you done any analysis of your db to see if
you are having
>contention issues?
>
>HTH
>Lee
>
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>EBusiness Server Team
>--------------------
microsoft.public.biztalk.general:16535[vbcol=seagreen]
documents[vbcol=seagreen]
(using[vbcol=seagreen]
fields[vbcol=seagreen]
stored[vbcol=seagreen]
for[vbcol=seagreen]
same[vbcol=seagreen]
for[vbcol=seagreen]
anything[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
warning[vbcol=seagreen]
docs[vbcol=seagreen]
etc)?[vbcol=seagreen]
suggestions[vbcol=seagreen]
>
>.
>
| |
|
| Have Indexed field on table used in WHERE clause of stored
procedure; used SET NOCOUNT ON; and adjusted RETRYs to
every min. Result is much improved performance, although
not perfect, but aceptable. Test data quantity is
exceptional, about a weeks work in one min or so; but then
there is always the possibilty of this when some systems
are down.
>-----Original Message-----
>Hi Lee
>
>I am using Microsofts AICSQL! I will do some more
>analysis and post if relevant. I find it difficult to
>understand why it works very well when calling the Insert
>stored procedure but not so well when calling the Update
>stored procedure (same map/ doc defs etc).
>
>Thanks Phil.
>
>custom AIC that you
>takes place before
parsing[vbcol=seagreen]
>in your AIC. What
>contention in your
>your data. We have a
>are tied up because
>AIC, we will leave
>appears to process
if[vbcol=seagreen]
>you are having
>confers no rights.
V5.50.4910.0300[vbcol=seagreen]
>microsoft.public.biztalk.general:16535
>documents
>(using
a[vbcol=seagreen]
this[vbcol=seagreen]
>fields
Insertions/Updates/Deletions[vbcol=seagreen]
>stored
>for
receive[vbcol=seagreen]
>same
>for
>anything
>the
>the
>warning
>docs
>etc)?
>suggestions
>.
>
|
|
|
|
|