Re: Optimizing fetch of big result set - HELP!

Well we seem to have improved greatly our results.

We started off by analysing our table and we were able to shave off about half a second on our time. Next, we decided to try Oracle drivers directly (not using ODBC or OLEDB). Using ProC, we were able to reduce our time to about a second and a half, so we shaved off almost 4 seconds by eliminating ODBC. Great! but now we'll to work out the protability issue. Not such a big problem considering it's a very localised "problem".

Our target time was 1 second with half a second margin. The thing is that we where moving from a filesystem "database" to an Oracle DB. We figured that we would be able to get approximatly the same results as a local filesystem using Oracle and now we are comparable. Of course we now have all the advantages of using an Oracle DB (network capable, much higher upper entry limit, etc.) It's still probably possible to improve our performances, but we'll have to look deep, and we are pretty satisfied with our results.

Thanks every one!

"Keith Boulton" <kboulton_at_ntlunspam-world.com> wrote in message news:kVg97.26393$vN4.207613_at_news11-gui.server.ntli.net...
> I was working on a long reply to this, but I made the mistake of using
> windows 98 and I lost it.
>
> However:
> You don't say what proportion of the table you are accessing - this is
> significant.
>
> I'm don't believe the fetch is the issue - you could test this by using:
> SELECT XD_ID,TERM,WEIGHT FROM THEMES WHERE ROWNUM < 200000
> I tried this in a VB app using the Oracle OLEDB provider - it took about 3
> seconds.
>
> You don't say if the table has been analysed or not (ANALYZE TABLE THEMES
> COMPUTE STATISTICS). Without this, the rule based optimiser is used which
> issues a query for each element in the in list and concatenates the result
> sets - I think this may be the problem given that you appear to have
 10,000
> elements. With the cost-based optimiser, a full table scan occurs. On my
> Windows98 machine this takes about 4 seconds (shown by select count(*)
 from)
> for an 800,000 row table and nothing cached (ie after a database start).
> This is reasonable, given that it occupies 40m of disk space. With the
 data
> all in the sga, this drops to < 1 second. I would expect these figures to
 be
> lower under NT or UNIX.
>
> One bottleneck will be the full table scan which I normally reckon 2
 seconds
> per million records scanned, although parallel query might speed this up.
>
> The next bottleneck is the actual fetch process via ODBC/OLEDB. These are
> famously inefficient. The documentation for use from VB/ADO is also
> dreadful, but it would seem you're looking at best at 1 second per 100,000
> rows. This means you probably are not going to get the fetch below 2 or 3
> seconds anyway.
>
> Maybe you should consider an alternative approach e.g. having multiple
> processes/threads working on different subsets of the data.
>
> What was your target time for this process?
>
> "Philippe Laflamme" <> wrote in message
> news:eNf87.27666$A4.2780228_at_news20.bellglobal.com...
> > Here is are the table creation and select statements we used:
> >
> > CREATE TABLE SYSTEM.THEMES (
> > THEME_ID NUMBER(5,0) NOT NULL,
> > TERM VARCHAR2(30) NOT NULL,
> > WEIGHT NUMBER(8,2) NOT NULL,
> > XD_ID NUMBER(12) NOT NULL,
> > CONSTRAINT PK_THEMES PRIMARY KEY (XD_ID,THEME_ID),
> > CONSTRAINT FK_THEME_XD FOREIGN KEY(XD_ID) REFERENCES
 SYSTEM.XD(XD_ID)
> > ON DELETE CASCADE);
> >
> > SELECT XD_ID,TERM,WEIGHT FROM THEMES WHERE XD_ID IN (0,1,2,3,4,5...999)
> > OR XD_ID IN (1000...1999) OR .... OR XD_ID IN (9000...9999);
> >
> > We are looking at the stats from v$sesstat and v$session_event to find
 the
> > bottleneck. We're having trouble finding the logical I/O taking place
 during
> > our query. All the I/O values are set to 0. Do we need to alter the
 system
> > table to get the I/O statistics, or having TIMED_STATISTICS and
> > TIMED_OS_STATISTICS set to true is enough?
> >
> > Thanks a bunch!
> >
>
>
>
Received on Tue Jul 31 2001 - 12:14:54 CDT