"Joey Ebright" <joeyebright@decadesoftware.com> wrote in message
news:3FF36731.4010907@decadesoftware.com...
> Hello all!
>
> I am experiencing some performance problems when using Sybase Adaptive
> Server Anywhere with DTS. I am finding it 2-3 times slower in some cases.
>
> Here is some background:
>
> I am designing an application that will use DTS to move data between 2
> databases. These two databases are nicknamed REMOTE and CONS. As you
> might guess, this involves small REMOTE databases that are used to
> capture data and then replicate the changes back to a centralized
> location - CONS. After the REMOTE changes are replicated, a fresh
> snapshot of data is passed down to each REMOTE database.
>
> We chose to use MSDE as the REMOTE database because this database is
> essentially free for us to distribute and fits well since the number of
> users on the REMOTE database will always be one. As for the CONS
> database, we already have a number of customers who use Sybase Adaptive
> Server Anywhere 8.02 (henceforth referred to as ASA8) and a number of
> clients who use Microsoft SQL Server 2000 (henceforth referred to as
> MSSQL) therefore my application must support both MSSQL and ASA8 as the
> CONS database.
>
> The overall concept is quite simple - use DTS DataPumpTasks to insert
> new data from the the REMOTE database into the CONS database. I realize
> here I cannot use FastLoad if the CONS database is ASA8 but I am alright
> with that because I WANT the triggers on the CONS database to fire
> anyway. Once all data has been moved from the REMOTE to the CONS, a
> fresh snapshot of data is moved down to the REMOTE. To get the snapshot,
> I truncate the tables on the REMOTE database and use DTS DataPumpTasks
> (with FastLoad turned ON) to supply the snapshot to the REMOTE.
>
> Since I am not using FastLoad when moving data from REMOTE to CONS
> regardless of whether the CONS database is ASA8 or MSSQL, I would expect
> that the performance would be approximately equal. This is definitely
> not true even when only one user is replicating but when I tested in a
> multi-user environment with 5 users replicating at the same time, the
> performance difference even worse:
>
> - Using ASA8 as the CONS database it took 290.618 seconds to move about
> 1000 records.
> - Using MSSQL as the CONS database it took 113.03 seconds to move about
> 1000 records.
>
> Now as for the snapshot part (moving data from CONS to REMOTE) I am
> using FastLoad and single user performance is pretty much equal between
> ASA8 and SQL Server. But when I tested in a multi user environment with
> 5 users at the same time, the performance difference was as follows:
>
> - Using ASA8 as the CONS database it took 306.671 seconds to move about
> 38000 records.
> - Using MSSQL as the CONS database it took 192.707 seconds to move about
> 38000 records.
>
> One other thing - when I tested with 15 users at the same time,
> performance was horrible - something like 30 minutes for ASA8 compared
> to 6.5 minutes for MSSQL.
>
> Here is what I tried so far:
> -----------------------------
> -Tuning DataPumpTask options. I set FetchBufferSize=5000 and
> InsertCommitSize=100000. This did help a little. I also turned on
> table locking but this actually slowed things down a little bit and I
> turned it back off.
>
> - Switching out my ASA8 DTS connection from ODBC to OLE DB. This failed
> as I could not get the ASAProv to work in DTS (see my post from 12/31/03
> entitled "Connection for Sybase (Adaptive Server Anywhere)"
>
> - Monitoring locking on the ASA8 database. I had assumed maybe a
> prolonged blocking condition was the problem. I found very little
> blocking, and nothing more than a few milliseconds in length. Although
> I must say ASA8 tools are nothing like MSSQL and trying to detect
> blocking conditions under ASA8 are error-prone and tedious (at least for
> me).
>
> - Switching ASA8 isolation level from 1 to 0. My thought was to reduce
> number of read locks - I know it was silly but I tried it anyway and it
> did not help.
>
> - Setting ASA8 WAIT_FOR_COMMIT=True so that referential
> integrity/constraints are not checked until the transaction is committed
> (my whole DTS package runs in a single transaction). This did not
> help and I was not surprised.
>
> - Temporarily turning off triggers. I was wondering if the trigger
> implementation between the two databases were different. For example
> maybe ASA8 was several times slower with triggers on the database. This
> attempt was also fruitless.
>
>
> In closing...
> I am having a hard time just chalking this up to ASA8 being slower than
> MSSQL. Sure this might sometimes be the case, but I can time other
> query operations where ASA8 comes out ahead of MSSQL. Although I cannot
> solve this problem yet, I feel it is a matter of circumstances. Said
> another way the technologies used together and the ways in which I am
> using/configured them is hampering performance. What to do about is
> where I could use some help ;)
>
> Thanks in advance,
> Joey
>
>
>
>
>
>
>

Allan Mitchell wrote:

> Great description thanks.
>
> Have you used the drivers in any other technology? If the driver is slow
> using ANY application then we are not solely looking to DTS.
>
> If I had the remote sites as Sybase (REM)and the repository (REP) as SQL
> Server I would probably look to do this
>
> Each REM would push it's data to a working copy version of the REP.
> TSQL would be used to update the real REP from each working REP.
> Each Working REP would be SIMPLE recovery model, No triggers, minimal
> indices as these may be needed when updating the real REP.
> Pushing back again I would specify NOLOCK on my SELECT statements.
>
>
> I have to admit I have no expereince of the Sybase driver but I would be
> looking at getting stats using other technologies i.e. MS Access to see if
> the problem lies with DTS and the driver. I have a datapump between
> Informix and SQL Server every week of > 20 million rows and Access likes it
> more than DTS (Slower though).
>
> Let us know how you get on.
>