Board index » delphi » How limit number records retrieved in select statement(Delphi 3 and Oracle 7.3)

How limit number records retrieved in select statement(Delphi 3 and Oracle 7.3)

Hi,

I'm(SQL Newbie) trying to select a large number of records(30 million), but
I wan't to retrive only say 200 at a time(like TClientDataset) in a query
using Oracle 7.3 database. I don't know what SQL or TQuery command  I can
issue to do this, since the query take a long time to execute.I saw a
similar capability in SQL explorer where you enter SQL statement to select
data in a table and it was quite fast than
same SQL statement in a TQuery.

Thanks in advance...

Regards
Alfred
rakgo...@mtn.co.za

 

Re:How limit number records retrieved in select statement(Delphi 3 and Oracle 7.3)


Hi Alfred

Try Looking under SET - statement and especially rowcount in Your SQL-server
material or in the help-function

Hope this helps

Regards
--
Engineer, Allan Bentsen
SystemsConsultant at
 4D KONSULENTERNE A/S
Telefon: 33 14 71 44 Ext 342
mailto:abent...@4d.dk
http://www.4d.dk/

Quote
Alfred wrote in message <6vut8f$es...@forums.borland.com>...
>Hi,

>I'm(SQL Newbie) trying to select a large number of records(30 million), but
>I wan't to retrive only say 200 at a time(like TClientDataset) in a query
>using Oracle 7.3 database. I don't know what SQL or TQuery command  I can
>issue to do this, since the query take a long time to execute.I saw a
>similar capability in SQL explorer where you enter SQL statement to select
>data in a table and it was quite fast than
>same SQL statement in a TQuery.

>Thanks in advance...

>Regards
>Alfred
>rakgo...@mtn.co.za

Re:How limit number records retrieved in select statement(Delphi 3 and Oracle 7.3)


Quote
>I'm(SQL Newbie) trying to select a large number of records(30 million), but
>I wan't to retrive only say 200 at a time(like TClientDataset) in a query

It's almost never a good idea to select a large number of records, even if
you find a way to do it a few at a time.  Doing this attempts to impose a
file-server-based "browse mode" mentality onto a client/server architecture.
This is likely to leave you with a mediocre app.

I'd suggest re-thinking your strategy.  Instead of opening a huge table and
letting the user browse, why not ask the user for some type of filtering
criteria first?  You can then check (via a count query) to see how many hits
there are, and either ask for additional criteria (if too many records fit
the criteria) or return a reasonable sized result set.

Hope this helps,

Mark

Re:How limit number records retrieved in select statement(Delphi 3 and Oracle 7.3)


Unless you do something to force fetching to EOF, using a TQuery with Oracle
native driver will perform incremental fetching.  You can use SQL monitor to
check this for your application.  A live query is slightly slower to open
because it grabs schema info.

In any case, you should avoid selecting 30 million records to the client.
The network traffic alone would be formidable.  Consider pushing processing
up to the server in a stored procedure.

V/R
Russell L. Smith

Quote
Alfred wrote in message <6vut8f$es...@forums.borland.com>...
>I'm(SQL Newbie) trying to select a large number of records(30 million), but
>I wan't to retrive only say 200 at a time(like TClientDataset) in a query
>using Oracle 7.3 database. I don't know what SQL or TQuery command  I can

Other Threads