RE: VERY SLOW AICSQL UPDATES!

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Phil (anonymous_at_discussions.microsoft.com)
Date: 06/29/04 

Date: Tue, 29 Jun 2004 07:55:59 -0700

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.
>
>>-----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
>>--------------------
>>>>Content-Class: urn:content-classes:message
>>>>From: "Phil" <anonymous@discussions.microsoft.com>
>>>>Sender: "Phil" <anonymous@discussions.microsoft.com>
>>>>Subject: VERY SLOW AICSQL UPDATES!
>>>>Date: Wed, 23 Jun 2004 06:04:59 -0700
>>>>Lines: 33
>>>>Message-ID: <2053301c45922$b04fdac0$a101280a@phx.gbl>
>>>>MIME-Version: 1.0
>>>>Content-Type: text/plain;
>>>> charset="iso-8859-1"
>>>>Content-Transfer-Encoding: 7bit
>>>>X-Newsreader: Microsoft CDO for Windows 2000
>>>>X-MimeOLE: Produced By Microsoft MimeOLE
V5.50.4910.0300
>>>>Thread-Index: AcRZIrBPkY7CYORJTaewUmTO2oMKkg==
>>>>Newsgroups: microsoft.public.biztalk.general
>>>>Path: cpmsftngxa10.phx.gbl
>>>>Xref: cpmsftngxa10.phx.gbl
>microsoft.public.biztalk.general:16535
>>>>NNTP-Posting-Host: tk2msftngxa09.phx.gbl 10.40.1.161
>>>>X-Tomcat-NG: microsoft.public.biztalk.general
>>>>
>>>>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.
>>>>
>>>>
>>
>>.
>>
>.
>