Board index » delphi » Deleting Records from a record set.

Deleting Records from a record set.

Here is the situation:

I'm using paradox 7 Tables.

I'm creating a recordset using a table join in a query which goes as
follows:

SELECT DISTINCT SSN.Client_ID, SSN.Actual_SSN
FROM SSN JOIN Acaxref
ON (Acaxref.SSN = SSN.Client_ID)
WHERE Acaxref.SSN = SSN.Client_ID

What I'm trying to do is populate a field in the Acaxref table with a
value in the virtual recorset like this:

 while not table1.EOF do //Table 1 being the cross reference table
 begin
            if
JoinQueryXref.Locate('Client_ID',Table1.fieldbyname('SSN').asstring,[loCaseInsensitive])then

            begin
                table1.Edit;

table1.FieldByName('Actual_SSN').asstring:=JoinQueryXref.fields[1].asstring;

                table1.post;
            end;
  end;

The problem here is that I'm running into some major performance issues
because the recordset created
by the join is about 180,000 records.  I figured I could get rid of the
bottleneck by deleting each record out
of the recordset after I get the value. Something like this:

 while not table1.EOF do //Table 1 being the cross reference table
 begin
            if
JoinQueryXref.Locate('Client_ID',Table1.fieldbyname('SSN').asstring,[loCaseInsensitive])then

            begin
                table1.Edit;

table1.FieldByName('Actual_SSN').asstring:=JoinQueryXref.fields[1].asstring;

                JoinQueryXref.Delete;
                table1.post;
            end;
  end;

As each value is posted to the cross reference the JoinQueryXref
recordset grows smaller thereby (in theory) improving performance.

I've messed around with The TupdateSQL component but the documentation
is not to clear on how
I can delete the value form the virtual record set.  I'm not interested
in deleting any values from the joined
tables.

Your help will be appreciated

Evert.

--
Evert Claesson
Ameritas Life Insurance Corp.
http://www.ameritas.com

 

Re:Deleting Records from a record set.


Quote

JoinQueryXref.Locate('Client_ID',Table1.fieldbyname('SSN').asstring,[loCaseI
nsensitive])then

loCaseInsensitive  will slow the search down.   If your searching numeric
data, I'd drop the loCaseInsensitive.

Also, if you have the CS version of Delphi, you can pull the data into a
TClientDataSet and then create an index on the CDS,
and then use FindKey.

If no CS, you might think about saving the JoinQueryXref result set to a
table, indexing the table, and then using FindKey.

From bde32.hlp:

Create a table on disk by using a given SQL statement.

The filename is also passed as the parameter TblName. The function returns
the number of rows in the result table. This example uses the following
input:

  fDbiQExec(Database1.Handle, 'QUERY.DB', 'SELECT * FROM TEST;');
  fDbiQExec(Table1.DBHandle, 'QUERY2.DB', 'SELECT * FROM CUSTOMER');

The function is:

function fDbiQExec(hTmpDb: hDBIDB; TblName, SQL: String): Longint;

var
  hStmt: hDBIStmt;
  hQryCur, hNewCur: hDBICur;
  iRecCount: LongInt;
begin
  hQryCur := nil;
  hNewCur := nil;
  hStmt := nil;
  try
    Check(DbiQAlloc(hTmpDb, qrylangSQL, hStmt));
    Check(DbiQPrepare(hStmt, PChar(SQL)));
    Check(DbiQExec(hStmt, @hQryCur));
    Check(DbiQInstantiateAnswer(hStmt, hQryCur, PChar(TblName), szPARADOX,
      True, @hNewCur));
    Check(DbiGetRecordCount(hNewCur, iRecCount));
    Result := iRecCount;

  finally
    if (hStmt <> nil) then
      Check(DbiQFree(hStmt));
    if (hNewCur <> nil) then
      Check(DbiCloseCursor(hNewCur));
  end;
end;

=Bill

Other Threads