☞Forperformance tips for Adaptive Server Enterprise users using a
subscription-list
column,see “Tuning extraction performance” on page 155
and“Tuning extraction performance for shared rows” on page 162.
Thereare several potential causes of inefficiency in a large-scale extraction
process:
♦Theextraction utility extracts one database at a time, including the
schemaand data for each user. Commonly,many users share a common
schema,and only the data differs. The brute force method of running the
extractionutility for each user repeats large amounts of work
unnecessarily.Extracting schema and data separately can help with this
problem.
♦Runningfrom Sybase Central, the extraction utility creates a new
databasefor each user. Ifsubscribers share a common schema, you could
createa single database, with schema but no data, and copy the file.
♦Bydefault, the extraction utility runs at isolation level zero. If you are
extractinga database from an active server, you should run it at isolation
level3 (see “Extraction utility options” on page 306) to ensure that data
inthe extracted database is consistent with data on the server.
Runningat isolation level 3 may hamper others’ turnaround time on the
serverbecause of the large number of locks required. It is recommended
thatyou run the extraction utility when the server is not busy, or run it
againsta copy of the database.
Anefficient approach to
extractingmany
databases
Oneapproach that avoids these problems is as follows:
1. Make a copy of the consolidated database, and at the same time start the
subscriptionsfrom the live database. Messages will nowstart being sent
tosubscribers, even though they have no database and will not receive
themyet.
Tostart several subscriptions within a single transaction, use the
REMOTERESET statement (Adaptive Server Anywhere ) or sp_remote
procedure(Adaptive Server Enterprise).
2. Extract the remote databases from the copy of the database. As the
databaseis a copy, there are no locking and concurrency problems. For a
largenumber of remote databases, this process may take several days.
3. As each remote database is created, it is out of date, but its user can
receiveand apply messages that have been being sent from the live
consolidateddatabase, to bring themselves up to date.
Thissolution interferes with the production database only during the first
step. The copy must be made at isolation level three if the database is in use,
194