Alternative for RecordCount ?

[gto]

Hello!!

Searching the forum and trying myself, I think the RecordCount is an expensive property and may be used with caution to avoid the component to pass through all the matched data only to count the rows...

It's become more evident when we use big databases, like one I made to test, with 350000 rows..

So, in my program there's a function that checks if certain data is already in the table. I use as parameters the name of the table, the column and data do compare. Internal, there's a ZReadOnlyQuery executing a select with the function data, and finally it compares the RecordCount of the query with zero, becouse if it's one or more, the data is duplicated.

For that propouses, could I use the RecNo property? If the SQL results no data, the RecNo returns zero, no rows, no RecNo. But if the SQL returns data, the RecNo becomes one, and symbols the duplicate record.

Could the RecNo used for that? It's less expensive than RecordCount?

Sorry for the bad writing
Thanks in advance!

[zippo]

Can you show an example?

[MarkusD]

Hello gto,

Just checking if BOF and EOF aren't both true, is imo the best way to check if a query didn't return a row.

Code: Select all

Query1.SQL.Text := 'select 1 from tablename where columnname = comparevalue';
Query1.Open;
if not ( Query1.BOF and Query1.EOF) then
  ShowMessage( 'Record found');
Query1.Close;

You could also use a parameter for the comparevalue and ensure that you only get one record ( could be faster):

Code: Select all

Query1.SQL.Text := 'select 1 where exists(select 1 from tablename where columnname = :comparevalue)';
Query1.ParamByName( 'comparevalue').Value := comparevalue;
Query1.Open;
if not ( Query1.BOF and Query1.EOF) then
  ShowMessage( 'Record found');
Query1.Close;

Best Regards,
Markus Dütting

[btrewern]

There is an TZQuery.IsEmpty property which may be what you need.

Regards,

Ben

[gto]

Hello gto,

Just checking if BOF and EOF aren't both true, is imo the best way to check if a query didn't return a row.

I totally forgot about EOF and BOF !
thank you MarkusD !!

There is an TZQuery.IsEmpty property which may be what you need.
Regards,
Ben

tomorrow, in work, I'll check that also!
I like to go "in depth" with that functions