Board index » delphi » Limiting SQL Result Sets by number of Records

Pacific Financial & Mortgage

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Limiting SQL Result Sets by number of Records

I know this is probably not adhering to proper form, but I need help.  I am
trying to limit the number of records in the result set by a number of
records count.  Is there a way to do this?

My problem is we host the main database file (dbase) on the server.  We
don't have SQL Server.  We have just added a bunch of code using the Query
component.  It worked find with a database of 30,000 records.  When we
tested it on 300,000 it took about 15 minutes to return a result set.
Yikes!  I simple solution was to limit the number of records returned.  Any
other suggestions.

Thanks - any and all comments welcomed.

Brian Laughlin

 
Glatzfam

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Limiting SQL Result Sets by number of Records


Quote
>I know this is probably not adhering to proper form, but I need help.  I am
>trying to limit the number of records in the result set by a number of
>records count.  Is there a way to do this?

>My problem is we host the main database file (dbase) on the server.  We
>don't have SQL Server.  We have just added a bunch of code using the Query
>component.  It worked find with a database of 30,000 records.  When we
>tested it on 300,000 it took about 15 minutes to return a result set.
>Yikes!  I simple solution was to limit the number of records returned.  Any
>other suggestions.

In the book Delphi 4 Developer's Guide, there came with it a query component
that contained a MaxRowCount property.  I do not know if the component is
available on their website:
http://www.xapware.com/ddg/, but you might want to check.  Also check out the
Delphi Super Page: http://sunsite.icm.edu.pl/delphi/, there might be a freeware
component there that might do the trick.

Have you  tried using a where clause to restrict your number of returned
records?

********************************
Michael Glatz
glatzfa...@aol.com
mgl...@briefcase.com

Ed Hochma

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Limiting SQL Result Sets by number of Records


Hi,

I don't know if you can, or would want to, limit the results to a specific
number of records.  What if the information wanted was in the n+1 th. record?

Are your queries based on indexes on the table(s).  If not, this is the first
line of attack.  Queries that can use indexes are MUCH faster.

Ed

Pacific Financial & Mortgage wrote:

Quote
> I know this is probably not adhering to proper form, but I need help.  I am
> trying to limit the number of records in the result set by a number of
> records count.  Is there a way to do this?

--
Ed Hochman - MBH Systems - e...@mbhsys.com
Ken

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Limiting SQL Result Sets by number of Records


Well, I don't know how many records you want returned, but if 20 will do,
I'll tell you what I'd do.  I'd just edit about 20 of 'em to include some
weird attribute, then I would include that attribute in the sql select.
E.g., select * from mydata where address like 'xxx%'.  [with xxx being a
literal perhaps in front of a street address, or something.  That will do
it.

Pacific Financial & Mortgage wrote in message
<7b5iso$dl...@nuhou.aloha.net>...

Quote
>I know this is probably not adhering to proper form, but I need help.  I am
>trying to limit the number of records in the result set by a number of
>records count.  Is there a way to do this?

>My problem is we host the main database file (dbase) on the server.  We
>don't have SQL Server.  We have just added a bunch of code using the Query
>component.  It worked find with a database of 30,000 records.  When we
>tested it on 300,000 it took about 15 minutes to return a result set.
>Yikes!  I simple solution was to limit the number of records returned.  Any
>other suggestions.

>Thanks - any and all comments welcomed.

>Brian Laughlin

Phelipe de Sterli

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:Limiting SQL Result Sets by number of Records


On Thu, 25 Feb 1999 21:33:28 -1000, "Pacific Financial & Mortgage"

Quote
<br...@referentia.com> wrote:
>I know this is probably not adhering to proper form, but I need help.  I am
>trying to limit the number of records in the result set by a number of
>records count.  Is there a way to do this?

>My problem is we host the main database file (dbase) on the server.  We
>don't have SQL Server.  We have just added a bunch of code using the Query
>component.  It worked find with a database of 30,000 records.  When we
>tested it on 300,000 it took about 15 minutes to return a result set.
>Yikes!  I simple solution was to limit the number of records returned.  Any
>other suggestions.

I don't know if it's possible but even if you can make it in any way
that will not solve the problem: without a SQL server the selection is
always made by the client pc so the whole table will be loaded by the
client and THEN the client extract the needed record... so it doesn't
matter if you want 10 or 10000 records back from your query: your
program always upload the whole table from the server to local memory
/ disk cache and then the records are extracted.
If you want to limit the network load (and increase the speed of your
query) you need a SQL server that process your query on the server and
gives back to your program only the selected record.

*=================================*
| by Phelipe de Sterlich          |
*---------------------------------*
| ELCO Sistemi Gestione del Tempo |
|   - e...@mbox.{*word*104}forli.it -   |
*=================================*

Other Threads

1. Limiting Result Sets - # of Records - TQuery

2. Number of record in result set

3. How to limit number of records returned by MS SQL Server

4. SQL...Limiting the number of records returned

5. Modifying records in a read-only SQL result set

6. Limiting number of results

7. Limiting result set size with ADO on MSSQL7.0

8. Limiting a Result Set

9. Limiting size of result set

10. row number in result set