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

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.

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Joey Ebright" <joeyebright (AT) decadesoftware (DOT) com> wrote

Allan Mitchell wrote:

Thanks for the advice Allan. I am looking right now into trying to
compare the ODBC and OLEDB providers. Unfortunately a possible bug in
the OLEDB provider is also causing this to be a headache. See my post
with the subject "Connection for Sybase (Adaptive Server Anywhere)" for
information on this possible bug. I will report results back here when
I have made some progress.

-Joey