Are you talking about Cognos Finance here? It has a very nasty habit when
loading data through the File Processing module using the SQL interface that
it first issues a SELECT COUNT(*) to get a rowcount. So in effect you are
running your sql statement twice.
--
Terry Dykstra
Canadian Forest Oil Ltd.
"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
news:3D820D4D.D6069FBE_at_exesolutions.com...
> Billy Verreynne wrote:
>
> > Christian Svensson wrote:
> >
> > > Since these SELECT get a lot of data, we are talking about approx 100
> > > million rows. It takes approx 7-8 hours for Cognos to select this data
> > > into a Cognos tempfile where it later use this to build the cubes.
> >
> > Pulling a 100 million rows across is, pardon me for being very blunt
Chris,
> > UTTER FRIGGEN STUPIDITY!
> >
> > Why have a warehouse in the first place? Why have a database engine as
> > capable as Oracle in the first place? Why not simply dump that data on a
PC
> > in some file format and use it directly there?
> >
> > The purpose of having the data in Oracle is to use Oracle's abilities at
> > processing the data. Retrieving a 100 million rows from Oracle is a lot
> > more expensive than reading a 100 million records from a flat file. And
> > guess what - reading it from a local flat file (using a file stream to
> > block read) will even be _faster_ than having to pull it from Oracle
across
> > the network.
> >
> > The reason I'm foaming at the mouth (not a pretty sight I know ;-), use
the
> > tools (in this case Oracle and Cognos) correctly!
> >
> > This means _not_ pulling across a 100 million rows, but only that which
is
> > applicable. Perform as much processing as possible on the database
server
> > (this is after all why we are using db engines in the first place). Have
a
> > look at what Oracle offers ito data warehousing.
> >
> > Next, disable Cognos from using its own functions. Cognos has a nice
suite
> > of functions. But consider:
> > SELECT region, city, NICE_FUNCTION( sales ) FROM the_world
> >
> > Oracle does not know what NICE_FUNCTION does - it is not a ANSI SQL
> > function. So Cognos changes the SQL to the following:
> > SELECT region, city, sales FROM the_world
> >
> > Cognos pulls _all_ the data from the table across. And then it applies
the
> > function NICE_FUNCTION(sales) and it performs the aggregation and
grouping
> > and sorting. On a client PC.
> >
> > It works when dealing with mickey mouse data sets. It does not work when
> > dealing with real data warehouses.
> >
> > > What I wonder is what Oracle setting/tuning can you make when you want
> > > to get this amount of data from the database ?
> >
> > Nothing. The only thing that needs tuning is the attitude and
preconceptions
> > of people using large volumes of data. Nothing personal Chris, simply
that
> > I have been this exact same problem on more than one occassion with
users
> > that refuse to understand the impact of attempting to process large
volumes
> > of data on the client side and not the server side.
> >
> > --
> > Billy
>
> A point I wish I had made. Cognos is a reporting tool. No report EVER
required
> 100M rows of data be transferred anywhere. 100M rows is a dump file. Do
your
> processing on the server and only transfer the result set.
>
> Daniel Morgan
>
Received on Fri Sep 13 2002 - 12:56:43 CDT
