At 09:35 AM 11/07/2003 +0530, you wrote:

>Hello,
>
>Using IBO. I had some performance problems. From lurking on this list
>realised it probably because of OAT. Solved the problem. OAT and next
>transaction are sort of advancing hand in hand
>
>Now the transactions are moving ahead in my database pretty quickly and i
>realised because I am READING some information from the database regularly.
>
>What is the easiest way to execute something like select count(*) from
>table without "consuming a transaction".

Not sure what you think is the problem about "consuming a
transaction". Every conversation between the client and the server
"consumes" a transaction - even a read-only select. But there is no box of
transactions sitting over there that - one day - will all be used up. Your
concern with OAT is to make sure that all started transactions will finish
(commit or rollback) with the minimum reasonable duration for the task they
are doing. Use a TIB_DSQL for "hit-and-run" operations like these ad hoc
rowcounts.

If you need to hit the wire to fetch a record count, just write a little
self-contained function to do it in its own dedicated transaction. And
*don't* use row counts for anything important, like populating a value in a
database table.

1. Drop a transaction component on your datamodule, name it e.g. trCount
and set its ib_connection property.
2. Drop a TIB_DSQL (dsqCount) on your datamodule and set its
ib_transaction property to trCount.
a) if you are only going to be counting rows from one table, set the SQL
property to "SELECT COUNT(*) FROM ATABLE [WHERE ACOLUMN = :ACOLUMN]"
b) if you want to use this for a variety of tables, keep the SQL property
blank.

Now, for your function (assuming you want an unrestricted rowcount for any
old table):

function MyDM.GetLatestRowCount (ATableName: string): integer;
begin
Result := -1; // whatever you want to flag a problem with the transaction
trCount.StartTransaction;
try
with dsqCount do
begin
SQL.Clear;
SQL.Add('Select count(*) from ');
SQL.Add(ATableName);
Prepare;
Execute;
Result := Fields(0);
end;
trCount.Commit; // first choice because it's cheaper than rollback
except
trCount.Rollback;
end;
end;

If you want to have the function apply to only one table, then set the SQL
in the IDE, don't let the function call Clear on it, and replace the line

Prepare;

with
if not Prepared then Prepare;

Include any WHERE clause and parameter handling to suit.

Just understand that select count is ALWAYS a costly operation - nothing to
do with the transaction - the only way the server can give you a rowcount
is to walk the entire table and COUNT the rows (like beans).

Helen