After rebuilding my database, when I run reports against the
new rebuilt database, the report which was taking 1 hour 40
mins before is now taking less than 2 mins to give results.

But, this lasts for only 6-7 times the report is fetched.
When I run the report for the 8th time, again it starts
taking more than an hour.

I just dont understand how can this happen... the same
report which pops up in less than a min now is again taking
more than an hour whereas no change has been done after the
rebuild.

In the documentation for ASA7, its mentioned that rebuilding
offers performance gains and defrags space and indexes and
this is what I also learnt on this forum as well.

But how can the effect last for only 5-6 times the report is
run.

How is the report created? Does it involve only SELECT statements, or
does it also include INSERT, UPDATE and/or DELETE operations?
Sometimes people create complex reports with complex logic that copies
data around from table to table, stuff like that.

It is not easy to solve a problem like yours using a forum like
this... interactions are slow, so please be patient.

Breck Thinks There Should Be Some Better Way Of Doing This


On 17 Nov 2009 01:46:01 -0800, Deepali wrote:

--
Breck Carter - Blog: http://sqlanywhere.blogspot.com/

SQLA questions and answers: http://sqla.stackexchange.com

RisingRoad helps SQL Anywhere developers make better databases
http://www.risingroad.com/
Breck.Carter at gmail

Thank you for replying Breck..

It is a crystal report. I opened the report in crystal
report software and I opened Visual Linking Expert dialog
box which only shows one table. It seems report does select
and update. I don't think it deletes any data. Even a simple
select query with order by clause is taking a lot of time
on that particular table.

But, how is it that initially report is being fetched very
fast and slows down after it has been fetched 6-7 times.
This would mean as more users connect to the database, again
performance will be back to the same levels as it was before
rebuilding.

Thanks, deepali

On Nov 17, 12:38*pm, "Breck Carter [TeamSybase]"
<NOSPAM__breck.car... (AT) gmail (DOT) com> wrote:
Sometimes it is happened because query optimizer make different way to
perform query. There are one bug in query optimizer on joining tables
when one table has more than one index. There are one example :

Table_types, table_headers, table_items are tables...

Table_headers has one foreign key to table_types (FK_1) column name :
"t_type" for example
Table_header has primary key (PK_1)
table_items has foreign key to table_headers (FK_2)

In real situation there was about 100K records in table_headers and
about 1M records in table_items. BUT over 90% records in table_headers
have column value for t_type = "RN"

if you write << select * from table_items JOIN table_headers where
table_headers.t_type = "RN" and table_items.product_id = "something"
sometime query plan show : table_items(seq), table_headers(PK_1)
because query optimizer think that the best way is scrolling through
table_items and use foreign key between table_headers and table_items
(CORRECT)

sometime query plan show : table_items(seq), table_headers(FK_1)
because query optimizer think it is the best way is scrolling through
table_items and use foreign key in table_headers (WRONG). Now server
for each record in table_items compare "foreign key definition" for
every records in table_headers with "RN" value in t_type column.

BUT if use ON clause in joining "select * from table_items JOIN
table_headers ON table_items.col1 = table_headers.col1 where
table_headers.t_type = "RN" and table_items.product_id = "something",
query optimizer is forced to use foreign key between table_headers and
table_items.
Query processor do not request ON clause if exists foreign key between
two tables, but i found it is better to use.

TRY to rewrite your report-sql and extend JOIN with exact link between
columns using ON .....

I detected this in ASA7 version and still happened in SA11 version. So
my instruction to our programmers is : DO NOT USE ONLY JOIN in select
command, USE JOIN .. ON ... !!!!!

Bofcilo wrote:
IMHO, each JOIN that is not a NATURAL JOIN, a KEY JOIN or a CROSS JOIN
*must* use an ON clause to specify the join condition. Otherwise the
JOIN keywords seems quite useless.

It seems that SA accepts the mere "JOIN" when there are FKs between the
tables (and so might use a KEY JOIN silently). Call it a bug or a
feature... When there are no FKs between the table, an error is raised.
(Tested on ASA 8.0.3.5574 and 11.0.1.2331).

I frankly use KEY JOINs quite often as the syntax is handy, but have
never had performance problems with those joins.

HTH
Volker

On Nov 18, 10:28*am, Volker Barth <No_VBarth (AT) Spam_GLOBAL-FINANZ (DOT) de>
wrote:
Ok, i have to add more about this. First, I know for KEY JOIN and
existing FKs between tables. I don't write about bug. I confirm that
we detect problem in query optimizer in very specific situation. For
many months one query on customer location work fine, fast. And then
start slow execution. We tested that query in iSql and there found
different query plan between fast and slow execution. Again, SAME
query without any changes did work fast. First solution was drop
statitistic (something like that i forgot). After that query work
fast, and after some days again slow. Then we try (it is not needed)
with rewriting query. ON clause was added in JOIN between master and
detail tables and for many, many years works fine. This is happened in
ASA7. Ok, it was first time, but believe me or not, there was in other
app and situations. Something has been repeat. It is happened only IF
master table has foreign key on column and one value is very often in
that column. In 99.999% other situation query optimizer use FK
between master and detail table.
One of my partner told me last month that they have some problem with
Java app on SA11.
And what did he say ? "One query in their app works fast,fast,fast for
a seconds and then slow.... slow... for a hours". And what was
suggestion ? "ADD ON clause in select command if you have JOIN".
And ? Yes, now work fast, fast, fast and still working fast.

The use of implied key joins (JOIN without an ON condition) and
fast-slow-fast query plans are unrelated. Using JOIN without an ON
condition is processed very early - the query is rewritten to include
the ON condition defined by the foreign key relationships before the
query goes through subsequent optimization.

If you have fast vs. slow query plans for the same query, post the
graphical plans (with statistics) of each instance and I'll take a look
at them.

Glenn

Bofcilo wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

On Nov 18, 1:03*pm, "Glenn Paulley [Sybase iAnywhere]"
<paul... (AT) ianywhere (DOT) com> wrote:
Yes i have fast vs slow query plans for the same query. Only if i add
ON clause then query optimizer is forced to use FK between master-
detail tables not FK on master table.
I did click on "replay to author" with question how to send to you
jpegs ? If it's wrong way to ask you please send email to
sprintrz (AT) gmail (DOT) com with instructions.

Thx.

Breck has posted a thorough description of how to save a graphical plan.

http://sqlanywhere.blogspot.com/2009...ical-plan.html

Attach the files in a post, rather than sending them to me directly via
email.

Glenn

Bofcilo wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport