Roy Lambert wrote:

>I guessed the ending halfway through the story <vbg>

I myself thought the butler did it with the candlestick holder in the pantry

>But without the clues you gave it would be difficult to spot in the wild.

Just a thought, what I've often done with things like this is use a mixture of Delphi and SQL. Generate a list (might be an array or a stringlist)  first and then walk the list doing the transfer. Overall its often more efficient just takes a bit more code.<

I see what you mean. If I want to use a filter that's likely the only solution; put the filter on another dataset and traverse that dataset and skip any rows in the destination table that are not in the dataset. I don't think an array or TStringList is needed. Just use a dataset. I can't use an SQL List() because the index values returned have to be in the same order as the index I'm traversing in the destination table and the List() doesn't permit an Order By clause.

I'll definitely keep this solution in mind for next time. This time around the filter on the destination table was superfluous; I could get away with not using it.

Barry

Are you syncing data between tables? Then this might help you.
-----------------------------------------------------------------------------------

I did discover something that sped up my sync process dramatically, almost 100%, and I am posting this in case it may help others who are syncing tables. I'm going to explain the problem so this will take a while to get to the point, so please bear with me. If you are in a hurry, jump to the end.

During testing of this problem, I discovered my TableSync routine, which sync's data between two TEDBTables of similar structure, would take TWICE as long when the destination table is empty compared to my TableCopy routine that just inserted the same rows from the source TEDBTable directly to the destination TEDBTable in a loop.  But why?

The two routines, TableSync and TableCopy, were very similar. Each had two loops, an outer loop to traverse through the rows and an inner loop to traverse through the fields of the table. Nothing new here. Granted there was a little overhead for the sync rtn, but not much when it found the destination table to be empty, it basically just copies the remaining rows from the source table to the destination table. So why did TableSync take twice as long as my TableCopy routine? It was copying the same data to the same table structure. Why so slow?

Well, after a bit of prodding and poking, I discovered the single Delphi statement that was the culprit, namely:
"taDest.Next;"

When syncing data it is customary to keep the rows of the two tables in sync wrt to the index that is in common between the two tables. Example. The index fields could be "Company_Id;Invoice_Num" that both the destination table and source table has in common. So the TableSync traverses both tables in the order of Company;Invoice_Num.

So after updating the taDest table or inserting a new row to the taDest table I would execute a:
   taSource.Next;
   taDest.Next;

so BOTH tables point to the next record to be compared. This is normal when syncing tables.

However, if the taDest table has already discovered to be at EOF, it is no longer necessary to execute the taDest.Next after inserting the new record. Doing so only generates more I/O and sets the EOF true again.

So my solution was to define a property for my TableSync class that once it detected EOF for taDest, the fEOF property would stay true and the taDest.Next would NOT be executed.

This virtually doubled the speed of the sync routine (when syncing to an empty destination table or adding rows to end of destination table) and made it almost the same speed as the CopyTable routine! When inserting hundreds of thousands of rows, this makes a big difference.

I hope this helps someone who needs more speed when syncing tables.

Barry