At 06:07 AM 16/07/2004 +0000, you wrote:

>Hi All!!
>
>How can I get dbexpress in delphi to return only the selected amount
>of rows in the recordcount and not process the entire database and
>return the entire recordcount for that database?
>
>I use a simple TSQLQUERY component with :
>
>SELECT FIRST 5 IndexKey,AccountNo FROM TARIFF
>WHERE IndexKey >= '020110 '
>ORDER BY IndexKey;
>
>But with this I get a resultant recordcount of 8000 and not 5!! As a
>result our program is hellishly slow!!

A recordcount of 5 rows is 5. You don't need a recordcount to tell you that.

If you want to know how many records are in the table (a pretty silly idea,
in a client/server database) run a separate query inside the same
transaction, select count(*) from Tariff where IndexKey >= '020110 '.

By the look of your key (a CHAR type padded with right blanks), you're
likely to run into some grief with "false matches", too. Did you know
that, under SQL rules, '020110 ' and '020110' are treated as a match in
equality comparisons?

As to why FIRST n returns a count of all rows: FIRST is an output
operation. So the query runs first and caches 8000 rows. Then it picks
off the requested n rows and discards the rest. For the same reason,
therefore, if you do something like

delete from TableA t
where t.PKEY in (select FIRST 5 t2.ANOTHERKEY from TableA t2)
you will delete all of the rows in the table that match the criterion, not
just the first 5.

A tip for multi-user client/server development: trying to do client-side
operations based on rowcounts is NOT cool. Set sizes are never more than
approximate, since there is no way of knowing what operations are pending
on the rows. Keep those natty little "gauge" controls for desktop
databases and spreadsheets.

/heLen