Board index » delphi » TQuery.Next is slow for Oracle

TQuery.Next is slow for Oracle

At the client site, each TQuery.Next call is taking about .3 seconds when
there are 7 tables in the query, which, depending on the # of rows, is
generally much slower than the running of the query.  I noticed that the
time taken is proportional to the number of tables in the query, but has
nothing to do with the number or size of the fields in the SELECT clause.
What does the number of tables have to do with running Next?  Isn't the
result set just stored in memory (or in the mysterious INMEMxxx.REM files)?

When I run this on my own Oracle server, the results are far worse: with 7
tables, each Next call takes 12 seconds!  When I use Sybase or MS Access,
however, the Next call takes under a milisecond.

Any ideas how I can speed this up in Oracle?  (I tried setting
TQuery.Unidirectional -- makes no difference.)

I'm using BDE 5.1.1 with Oracle 8.0.5 (native driver).

 

Re:TQuery.Next is slow for Oracle


Are these tables joined by indexes ?

Re:TQuery.Next is slow for Oracle


In my database, no, but in the client database, yes.
Quote
Lukas Skala <lsk...@envinet.cz> wrote in message news:3abb6c09$2_1@dnews...
> Are these tables joined by indexes ?

Re:TQuery.Next is slow for Oracle


What do you mean by client database and server database ? You haven't
created primary keys and foreign keys on server, but you have implemented
this on client side (inside application) ? If so, SQL server cannot use
indexes for searching and joining tables. Try create primary and foreign key
(and/or indexes) for joined fields. It can help you ?

Re:TQuery.Next is slow for Oracle


Forgive me, Lukas.  By client database, I meant the server database my
company's client has.  In both the database server that I have on my
computer and the one at the production site, there are primary keys.  I
don't know much about indexes, but it looks like Oracle does not
automatically create an index for the primary keys (and I didn't create any
indexes on my Oracle server.)  At the production site, however, the tables
are properly indexed.

The question I was asking is that 'TQuery.Open' runs the query.  Does
TQuery.Next also require a trip to the db server? Why and under what
conditions?  (Though the production db server is properly indexed, it's
halfway around the world, which might explain the .3 seconds.)

Quote
Lukas Skala <lsk...@envinet.cz> wrote in message news:3abf2b9a_1@dnews...
> What do you mean by client database and server database ? You haven't
> created primary keys and foreign keys on server, but you have implemented
> this on client side (inside application) ? If so, SQL server cannot use
> indexes for searching and joining tables. Try create primary and foreign
key
> (and/or indexes) for joined fields. It can help you ?

Re:TQuery.Next is slow for Oracle


Quote
"Tom Strong" <t...@xwareinc.com> wrote in message news:3ac464c0$1_1@dnews...
> Forgive me, Lukas.  By client database, I meant the server database my
> company's client has.  In both the database server that I have on my
> computer and the one at the production site, there are primary keys.  I
> don't know much about indexes, but it looks like Oracle does not
> automatically create an index for the primary keys (and I didn't create
any
> indexes on my Oracle server.)  At the production site, however, the tables
> are properly indexed.

Forgive me if I am in the middle of your conversation with Lukas, but....
Oracle *does* create indexes for primary keys. So if you don't have them in
client database, it may mean that your primary keys are gone, too.

Quote
> The question I was asking is that 'TQuery.Open' runs the query.  Does
> TQuery.Next also require a trip to the db server? Why and under what
> conditions?  (Though the production db server is properly indexed, it's
> halfway around the world, which might explain the .3 seconds.)

I may be wrong, but 'how many rows are fetch from DB' depends on your
settings.
For example
if the query is set to be cached or not, or
in TwwQuery you acctually have property how many rows should be fetched
if you use BDE - it has a it's own settings about this issue
...
The easiest way to check when, how offten for what reason your app is
comunicating with db is to see your network flow (bytes received and sent -
assuming that app is on local computer and db is on the network). You can
also run SQL Monitor (comes with Delpih Enterprise) and/or a third party
tool monitoring your app's behaviour - showing you  statistics 'how long' ,
'where', etc.

hth
wysza

Quote

> Lukas Skala <lsk...@envinet.cz> wrote in message news:3abf2b9a_1@dnews...
> > What do you mean by client database and server database ? You haven't
> > created primary keys and foreign keys on server, but you have
implemented
> > this on client side (inside application) ? If so, SQL server cannot use
> > indexes for searching and joining tables. Try create primary and foreign
> key
> > (and/or indexes) for joined fields. It can help you ?

Re:TQuery.Next is slow for Oracle


OK, so TQuery.Open only fetches a certain number of rows, and Next will
fetch another batch of rows if needed.  In my case, it looks like since the
result set is 'wide' (many LONG and varchar2(2000) fields -- no BLOBS), it's
only fetching one row at a time.  Since I'm only doing a (unidirectional)
read, it would probably be faster overall to retrieve more rows at a time.
It sounds like you're referring to TBDEDataSet.BlockReadSize -- I'll try it.
Thanks.

Quote
wysza <awy...@invalid.yahoo.com> wrote in message news:3aca7f11_2@dnews...

> "Tom Strong" <t...@xwareinc.com> wrote in message

news:3ac464c0$1_1@dnews...
Quote
> > Forgive me, Lukas.  By client database, I meant the server database my
> > company's client has.  In both the database server that I have on my
> > computer and the one at the production site, there are primary keys.  I
> > don't know much about indexes, but it looks like Oracle does not
> > automatically create an index for the primary keys (and I didn't create
> any
> > indexes on my Oracle server.)  At the production site, however, the
tables
> > are properly indexed.
> Forgive me if I am in the middle of your conversation with Lukas, but....
> Oracle *does* create indexes for primary keys. So if you don't have them
in
> client database, it may mean that your primary keys are gone, too.

> > The question I was asking is that 'TQuery.Open' runs the query.  Does
> > TQuery.Next also require a trip to the db server? Why and under what
> > conditions?  (Though the production db server is properly indexed, it's
> > halfway around the world, which might explain the .3 seconds.)
> I may be wrong, but 'how many rows are fetch from DB' depends on your
> settings.
> For example
> if the query is set to be cached or not, or
> in TwwQuery you acctually have property how many rows should be fetched
> if you use BDE - it has a it's own settings about this issue
> ...
> The easiest way to check when, how offten for what reason your app is
> comunicating with db is to see your network flow (bytes received and
sent -
> assuming that app is on local computer and db is on the network). You can
> also run SQL Monitor (comes with Delpih Enterprise) and/or a third party
> tool monitoring your app's behaviour - showing you  statistics 'how long'
,
> 'where', etc.

> hth
> wysza

> > Lukas Skala <lsk...@envinet.cz> wrote in message

news:3abf2b9a_1@dnews...

- Show quoted text -

Quote
> > > What do you mean by client database and server database ? You haven't
> > > created primary keys and foreign keys on server, but you have
> implemented
> > > this on client side (inside application) ? If so, SQL server cannot
use
> > > indexes for searching and joining tables. Try create primary and
foreign
> > key
> > > (and/or indexes) for joined fields. It can help you ?

Other Threads