Hi,

We recently upgraded our live database to ASA 9 from ASA 6.
It seems although everything is working fine a database
extract on our biggest table now takes over 3 hours where on
ASA 6 it only took 2 minutes.

Nothing has changed on indexes and stuff and it also seems
Sybase support is not able to help us without us sending our
database to them, which we are not allowed to.

Any idea how we can do the following:

1) See what query is executed for extracting the data? Maybe
we can optimize indexes based on the plan.
2) Other things we can check/do to optimize this query.

Kind regards,
Roel.

It's been a lonnnggg time, but I remember looking at the queries used
by dbxtract once, and finding some really nasty stuff

Does dbxtract -v show the actual queries?

If not, you can try using Request Level Logging to capture the
queries:

CALL sa_server_option
( 'Request_level_log_file', 'C:\\temp\\rlog.txt' );

CALL sa_server_option
( 'Request_level_logging', 'SQL+hostvars' );

-- Run dbxtract at this point

CALL sa_server_option
( 'Request_level_logging', 'NONE' );

Then look inside rlog.txt to find the long-running queries; you can
copy and paste them into ISQL and use the Graphical Plan facility to
look for issues.

Breck



On 17 Dec 2003 03:47:25 -0800, Roel Sergeant wrote:

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com

Turn on request logging on the server (-zr sql -zo requests.out). Then run
the extract. You probably don't need to wait for it to complete. Just wait
long enough to be sure the long query is executing. You should be able to
locate the long query in the request log file (requests.out). You can then
run that query through DBISQL to find out the plan for the statement and
begin to analyze why it takes so long.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

<Roel Sergeant> wrote

In addition to using -v on dbxtract, I'd also suggest running sa_locks while
the extract is running to ensure that you're not just running into blocking
issues during the extract.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

<Roel Sergeant> wrote

Thanks all,

We have been able to localise the problem. Thanks to the
queries we were able to:

1) Put an extra index on one of our subscription tables,
which speeds up queries a bit.

2) See from the plan that the queries were not really the
problem after all. We found out that the problem really is
as follows:

It seems the query for one of our tables is very slow (
takes +3 hours ) where it took only 2 minutes on ASA 6. The
problem is solved when we do a revoke connect <user> and
grant remote to <user> before the extract. Also it keeps
being fast until we shut down the database an restart it.

We contacted Sybase support again to ask if they can give an
explanation for this.

But if anybody has seen this behaviour before.....

Anyway, thanks a lot.

BTW: Sorry for cross-posting.... My bad, won't happen again.

Roel.