Why is Interbase sooo sloooow when doing full scans

dfg

Delphi Developer

Fri, 22 Aug 2003 01:08:34 GMT

Why is Interbase sooo sloooow when doing full scans

I have a table with close to 4 million rows in it. 8 fields. No blobs,
etc.

The table exists in Interbase 6.01 and Oracle 8.1.6.
The server machine is Windows 2000 with 512MB RAM and 2xPIII800. RAID.

Both databases were accessed as remoter servers from an Athlon 1GHz
client machine with 256MB RAM, W2K.

The very first "count(*) from table_name" takes ~7 seconds on Oracle.
The successive "count(*) from table_name" takes 2-3 seconds on Oracle.

The very first "count(*) from table_name" takes ~27 seconds on
Interbase.
The successive "count(*) from table_name" takes 24-25 seconds on
Interbase.

Why such a HUGE difference?

By the way, doing a count on an indexed fields didn't yield any
significantly different results.

The explanation I came up with is that Interbase accesses disk like
crazy. According to documentation, each logical access (which you can
only have as large as 8K page), results in multiple disk accesses. The
example given was Unix system where 512bytes are read in each access.
Oracle on the other hand lets you tune the "buffer" size. That is, how
much data will be read in each disk access. Given these facts, even the
computer science 101 student will be able to tell the reason for such a
huge difference.

Doesn't look good!

Regards,
Nasir.

Ali Gokce

Delphi Developer

Fri, 22 Aug 2003 05:17:52 GMT

Re:Why is Interbase sooo sloooow when doing full scans

hi,
unfortunatelly, ib does record scan instead of index scan.
IB makes too much DISK-IO & memory allocation-deallocations.
There is no SMP advantages, even its a disavantage.
I hope developer group can solve this problems in next version.
we want to say "good bye" to ugly ORACLE :)

Ali

Quote

"dfg" <f...@adf.m> wrote in message news:3AA27692.D3C86300@adf.m...
> I have a table with close to 4 million rows in it. 8 fields. No blobs,
> etc.

> The table exists in Interbase 6.01 and Oracle 8.1.6.
> The server machine is Windows 2000 with 512MB RAM and 2xPIII800. RAID.

> Both databases were accessed as remoter servers from an Athlon 1GHz
> client machine with 256MB RAM, W2K.

> The very first "count(*) from table_name" takes ~7 seconds on Oracle.
> The successive "count(*) from table_name" takes 2-3 seconds on Oracle.

> The very first "count(*) from table_name" takes ~27 seconds on
> Interbase.
> The successive "count(*) from table_name" takes 24-25 seconds on
> Interbase.

> Why such a HUGE difference?

> By the way, doing a count on an indexed fields didn't yield any
> significantly different results.

> The explanation I came up with is that Interbase accesses disk like
> crazy. According to documentation, each logical access (which you can
> only have as large as 8K page), results in multiple disk accesses. The
> example given was Unix system where 512bytes are read in each access.
> Oracle on the other hand lets you tune the "buffer" size. That is, how
> much data will be read in each disk access. Given these facts, even the
> computer science 101 student will be able to tell the reason for such a
> huge difference.

> Doesn't look good!

> Regards,
> Nasir.

Aage Johanse

Delphi Developer

Sat, 23 Aug 2003 04:24:25 GMT

Re:Why is Interbase sooo sloooow when doing full scans

I don't think IB ever will just scan the index - the architecure (MGA)
requires IB to visit the records. Maybe it can be changed for ReadOnly
databases ...

Regards,
Aage J.

Quote

Ali Gokcen wrote:
> unfortunatelly, ib does record scan instead of index scan.
> IB makes too much DISK-IO & memory allocation-deallocations.
> There is no SMP advantages, even its a disavantage.
> I hope developer group can solve this problems in next version.
> we want to say "good bye" to ugly ORACLE :)

Ali Gokce

Delphi Developer

Sat, 23 Aug 2003 07:50:53 GMT

Re:Why is Interbase sooo sloooow when doing full scans

Hi Aage,
yes, i heard something about this from Ann H.
perhaps they can do it via some ODS change and modifying index structer
uh ???
todays dbs are multi GB and record scaning killing IB server activity.
Let's pray&hope.. ;)

Ali

Quote

"Aage Johansen" <aagjo...@online.no> wrote in message

news:3AA3F5F9.50166CBB@online.no...

Quote

> I don't think IB ever will just scan the index - the architecure (MGA)
> requires IB to visit the records. Maybe it can be changed for ReadOnly
> databases ...

> Regards,
> Aage J.

> Ali Gokcen wrote:
> > unfortunatelly, ib does record scan instead of index scan.
> > IB makes too much DISK-IO & memory allocation-deallocations.
> > There is no SMP advantages, even its a disavantage.
> > I hope developer group can solve this problems in next version.
> > we want to say "good bye" to ugly ORACLE :)

dfg

Delphi Developer

Sun, 24 Aug 2003 21:43:50 GMT

Re:Why is Interbase sooo sloooow when doing full scans

Hi All,

Some have pointed out that the difference in count(*) time was due to the
fact that Interbase doesn't do index scan. I would respectfully point out the
fact that indexed fields weren't used. I guess "*" as the count param threw
them off. BTW, when indexed field was used as the param to count, Oracle came
back in unbelievably short times!

Regards,
Nasir.

Quote

Ali Gokcen wrote:
> Hi Aage,
> yes, i heard something about this from Ann H.
> perhaps they can do it via some ODS change and modifying index structer
> uh ???
> todays dbs are multi GB and record scaning killing IB server activity.
> Let's pray&hope.. ;)

> Ali
> "Aage Johansen" <aagjo...@online.no> wrote in message
> news:3AA3F5F9.50166CBB@online.no...
> > I don't think IB ever will just scan the index - the architecure (MGA)
> > requires IB to visit the records. Maybe it can be changed for ReadOnly
> > databases ...

> > Regards,
> > Aage J.

> > Ali Gokcen wrote:
> > > unfortunatelly, ib does record scan instead of index scan.
> > > IB makes too much DISK-IO & memory allocation-deallocations.
> > > There is no SMP advantages, even its a disavantage.
> > > I hope developer group can solve this problems in next version.
> > > we want to say "good bye" to ugly ORACLE :)

Other Threads

1. Why is Delphi sooo slow with Queries? Or am I doing something wrong?

2. Why is Delphi sooo slow with Queries? Or am I doing something wrong?

3. TabbedNotebook + lots of BitBtn = sloooow load--Why?

4. HELP ME PLEASE! (Interbase sooo slow)

5. Interbase : Sloooow Reeeespooonnnse

6. What am I doing wrong

7. What am I doing wrong?

8. What am I doing wrong...

9. What am I doing wrong?

10. TDBGrid onvalidate - what am i doing wrong