At 10:31 PM 06-09-01 +0000, you wrote:

>Simple question from a simple mind.
>What is the absolute fastest way of determining whether a big table
>has a particular record ?
>
>Suppose we have table with columns A,B,C, and D of which A is primary
>key.
>Can do, "select Count(A) from mytable where B=5 and C=6 and D=7" say
>and if count is 0 we have no such record.

Avoid counts for this task (too expensive).

>Or can do "select A from mytable where B=5 and C=6 and D=7" and if A
>is null we have no such record.

If you only need to know whether the row exists but you don't need a dataset, this can be an approach. But an empty dataset will not return NULL for A, it simply returns nothing. Use a TIB_Cursor, call its First method (not Open) and test for EOF. If True, the set is empty.

>Or Locate ... ??
>Having to do many of these within a loop it is desirable to fnd the
>fastest.

The very fastest would be to test it by using a TIB_DSQL over a stored procedure that uses EXISTS():

create procedure R_U_THERE( ArgB integer, ArgC integer, ArgD integer)
returns (Response smallint)
as
begin
if ( EXISTS ( select A from mytable where B= :ArgB and C= :ArgC and D= :ArrD)) then
Response = 1
else
Response = 0;
end

On your client, you might want to create a function to call it:

function Datamodule1.R_U_There(B, C, D: integer): Boolean;
begin
with ib_dsql1 do begin
if not prepared then
Prepare;
Params[0].Value := B;
Params[1].Value := C;
Params[2].Value := D;
Execute;
if Fields[0].Value = 1 then
Result := True;
end;
end;

rgds,
Helen
All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________