Antw: RE: Crystal Reports and the IdiotManager(TM)
- From: "Markus Reger" <Reger@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 08 May 2004 09:57:42 +0200
very good idea - not just because i did it the same way for one of our =
databases.=20
>>> Waleed.Khedr@xxxxxxx 08.05.2004 05:23:16 >>>
The problem with this tool and similar tools, is that they decide =3D
sometimes to read the data and join it by itself (the tool does the =3D
join) to some other data that is hosted by the tool or to some other =3D
data that was read from Oracle in a previous step. What I do, is simply =
=3D
creating an Oracle table to host the intermediate results, so that they =
=3D
can get it done using the Oracle server.
You simply need to be more involved in what they are trying to do, to =3D
find the most efficient method.
Something else these tools do, is pivoting the data (converting rows to =
=3D
columns, and doing some kind of aggregation) which might be acceptable =3D
for small amount of data. For good amount of data, you need to create a =
=3D
view that does that using decode/case (and may be group by).
Regards,
Waleed
-----Original Message-----
From: Don Granaman [mailto:granaman@xxxxxxx]=20
Sent: Saturday, May 08, 2004 11:11 AM
To: oracle-l@xxxxxxxxxxxxx=20
Subject: Crystal Reports and the IdiotManager(TM)
Again, the IdiotManager(TM) theme...
I found out late yesterday afternoon that there is a report in Crystal =3D
that
someone has been repeatedly trying to run - and then killing before it
finishes. It will run for 20+ hours and still be only perhaps 50% done.
They tried it about 3 or 4 times this week of course, expecting it to
somehow "get better". When I found out, I asked for the SQL. It was a =
=3D
bit
ridiculous as it attempts to sucks up 4.5 million rows out of a 20 =3D
million
row table to create a two fairly simple one page bar graphs. There is =3D
an
index on all the columns in the where clause, but in this case the =3D
optimizer
made the intelligent choice (verified) of a full table scan. Running it =
=3D
on
the DB server took only 10 minutes 51 seconds. Running it from another
server via SQL*Net took only slightly longer. I asked the developer to =
=3D
run
it again and traced it with the 10046 event level 12. It ran for almost =
=3D
two
hours before it was cancelled. Even though it had done little yet,
99.9999999999% of the wait time (almost=3D3Delapsed time) was on SQL*Net =
=3D
message
from client! It turns out that the client was trying to read the 1.2 =3D
gig of
data into 256M of memory. The client PC was completely unusable for the
duration of course - CPU usage was only about 5% but memory was totally
saturated and it was swapping like mad. IdiotManager(TM) had spent most =
=3D
of
the last two days in meetings trying to convince executive management =3D
that
it was a "database problem" and that the company should switch to =3D
Postgres
or, preferably, flat files - "for performance". (Seriously! For a 300+ =
=3D
GB
OLTP/hybrid database with hundreds of tables, a billion records, and >10
million transactions and 20-50 reports per day.) I overheard her coming =
=3D
out
of one of these meeting saying that "The problem with Oracle is that you =
=3D
can
put the data in, but can't ever get it back". During all this time she
never bothered to mention this "database problem" to me or anyone else =3D
who
might have any sort of clue as to how to actually diagnose anything. =3D
She
was in an extremely foul mood this morning after I sent out the trace
results of the attempt from Crystal, of four test runs using SQL and a =3D
very
detailed technical analysis of why it was not really a database problem =
=3D
-
and cc'ed the executives to whom she has been constantly spewing this =3D
sort
of nonsense.
I don't know beans about Crystal (and would actually prefer to keep it =3D
that
way). Does anyone know if it is somehow "tunable" for result sets >>
memory? Would 2GB of client memory help significantly (at least
temporarily)?. Is this simply a Crystal "scalability ceiling" thing? =3D
Does
anyone know of Crystal running well against large (10-500 million =3D
records)
data sets and large (N*million record) result sets? I'm really looking =
=3D
for
pointers, experiences, and perhaps even general information to pass to =3D
the
duhveloper and mangler. The only way I can think of to make this pig =3D
(and
its cousins) any better is to create materialized views or rollup tables =
=3D
in
the database (or better - in another database) and have them change the
report so that Crystal hits them and doesn't attempt to read millions of
records to find the "Top 20" of this and that grouped by the other =3D
thing. I
would like to avoid any having a hundred "report-specific" persistent
storage objects - all requiring modification every time they change a =3D
report
(at least daily).
-Don Granaman
OraSaurus - Kinda hungry for a duhvelopment mangler BBQ
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=20
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/=20
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=20
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com=20
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx=20
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/=20
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html=20
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » Antw: RE: Crystal Reports and the IdiotManager(TM)
