Hi,

Is it generally the case that TDBISAMTable will outperfom SQL script for
Update/Insert operations?

Basically I am trying to insert records from one table to another where they
don't already exist and update them if they do exist.  I had assumed that
Insert/Update operations involving thousands of records would be faster uing
SQL yet this doesn't appear to be the case with the example I have created.

The following TDBISAMTable example only takes 2 secs to run, yet the SQL
version takes approx 20 secs to update and a further 20 secs to insert

while not FinexTbl.Eof do
 begin
 if ManagersTbl.FindKey([FinexTbl.FieldByName('Code').AsString]) then
   begin
   ManagersTbl.Edit;
   ManagersTbl.FieldByName('AssetManager').AsString :=
FinexTbl.fieldbyname('Name').AsString;
   end
 else
   begin
   ManagersTbl.Insert;
   ManagersTbl.FieldByName('FinexCode').AsString :=
FinexTbl.fieldbyname('Code').AsString;
   ManagersTbl.FieldByName('AssetManager').AsString :=
FinexTbl.fieldbyname('Name').AsString;
   end;
 ManagersTbl.Post;
 FinexTbl.Next;
 end;


SQL Script: -

UPDATE AssetManagers M SET M.AssetManager=F.name
FROM AssetManagers M
LEFT OUTER JOIN FinexManagers F ON (F.Code = M.FinexCode)
WHERE F.Code IS NOT NULL;

INSERT INTO AssetManagers (AssetManager, FinexCode)
SELECT F.Name, F.Code FROM FinexManagers F
LEFT OUTER JOIN AssetManagers A ON (A.FinexCode = F.Code)
WHERE A.FinexCode IS NULL;


Jack

Jack,

<< Is it generally the case that TDBISAMTable will outperfom SQL script for
Update/Insert operations? >>

No, it depends upon the UPDATE/INSERT query.

<< Basically I am trying to insert records from one table to another where
they don't already exist and update them if they do exist.  I had assumed
that Insert/Update operations involving thousands of records would be faster
uing SQL yet this doesn't appear to be the case with the example I have
created. >>

See this thread:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=8&msg=12206&page=1#msg12206

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks for your help Tim - it sent me in the right direction.

Both of my tables have almost the same number of rows and virtually every
record has a match to be updated; the insert part typically adds only a
couple of new rows when exectuted.  So, unlike the example in the thread
where it would be faster to scan the smaller table looking up the larger
table for a match, I wouldn't have expected it to apply in my example.

However, I have now discovered that the performance was due to my having the
Case-Ins property of my index set to true.  When I removed this property the
SQL update executed in a flash and, as expected, wins over the TDBISAMTable
version.

Cheers,
Jack

"Jack Marsh" <jack@marshdata.co.uk> wrote in message
news:DA0D76A4-EBCE-4023-8E74-6FD4006A3235@news.elevatesoft.com...
> Thanks for your help Tim - it sent me in the right direction.
>
> Both of my tables have almost the same number of rows and virtually every
> record has a match to be updated; the insert part typically adds only a
> couple of new rows when exectuted.  So, unlike the example in the thread
> where it would be faster to scan the smaller table looking up the larger
> table for a match, I wouldn't have expected it to apply in my example.
>
> However, I have now discovered that the performance was due to my having
> the Case-Ins property of my index set to true.  When I removed this
> property the SQL update executed in a flash and, as expected, wins over
> the TDBISAMTable version.
>
> Cheers,
> Jack
with case-ins use the upper statement on the where clause on both sides,
like:  where Upper(field)=Upper(value)

Thanks for that Frans.

"Frans van Daalen" <Account@is.invalid> wrote in message
news:F4C9DCD2-60B3-4F4E-B1A6-8E0C17DBD23A@news.elevatesoft.com...
>
> "Jack Marsh" <jack@marshdata.co.uk> wrote in message
> news:DA0D76A4-EBCE-4023-8E74-6FD4006A3235@news.elevatesoft.com...
>> Thanks for your help Tim - it sent me in the right direction.
>>
>> Both of my tables have almost the same number of rows and virtually every
>> record has a match to be updated; the insert part typically adds only a
>> couple of new rows when exectuted.  So, unlike the example in the thread
>> where it would be faster to scan the smaller table looking up the larger
>> table for a match, I wouldn't have expected it to apply in my example.
>>
>> However, I have now discovered that the performance was due to my having
>> the Case-Ins property of my index set to true.  When I removed this
>> property the SQL update executed in a flash and, as expected, wins over
>> the TDBISAMTable version.
>>
>> Cheers,
>> Jack
> with case-ins use the upper statement on the where clause on both sides,
> like:  where Upper(field)=Upper(value)
>

Jack,

<< Both of my tables have almost the same number of rows and virtually every
record has a match to be updated; the insert part typically adds only a
couple of new rows when exectuted.  So, unlike the example in the thread
where it would be faster to scan the smaller table looking up the larger
table for a match, I wouldn't have expected it to apply in my example. >>

You are correct.  I also forgot that you were using a LOJ, which wouldn't
apply to the join re-ordering issue anyways because LOJs are never
re-ordered by the optimizer.  So, I was 0-2.

<< However, I have now discovered that the performance was due to my having
the Case-Ins property of my index set to true.  When I removed this property
the SQL update executed in a flash and, as expected, wins over the
TDBISAMTable version. >>

Ahh, yes.  That will certainly cause a slowdown.  Joins are especially
sensitive to missing indexes due to their nested-loop processing which
amplifies the fact that brute-force table scans are occurring for every
parent record in the join.

--
Tim Young
Elevate Software
www.elevatesoft.com