![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 11 total |
![]() |
Fri, Dec 15 2006 3:16 AM | Permanent Link |
Mark Elder | Hello,
I have a loop where I am comparing items in a StringList to records in a filtered dataset(using SetRange). The loop has two purposes. Delete anything from the dataset that is not in the string list, and remove everything from the string list that is already in the dataset. I need the string list to have all of the items not in the dataset by the end of the loop. Here is the basic loop: while Table.Eof = false do begin if StringList.Find(Field.AsString, FoundIndex) = true then begin StringList.Delete(FoundIndex); Table.Next; end else begin Table.Delete; end; end; The problem I have found is that Eof is not set after deleting the last record in the range. This is really bad in my case since when the loop repeats one more time it will start deleting all of the records in the range until it empties the dataset. I think I need to check if the current record is the last record after calling Delete. But how do I check that? I saw a similar question that Clive posted back in May 2006. But in that case it looks like the solution was to just move last directly instead of checking. I don't see how that would help here. The only idea I have is to move Last before the loop and get the bookmark of the last record. Then I can check the bookmarks before the call to delete. That or get the number of records in the range and change my outer loop from a while loop to a for loop. Thanks, Mark Elder |
Fri, Dec 15 2006 4:23 AM | Permanent Link |
"Frans van Daalen" | "Mark Elder" <Mark.Elder@uaar.net> wrote in message news:CB301A1F-FF8B-4824-88EC-773647E98221@news.elevatesoft.com... > Hello, > > I have a loop where I am comparing items in a StringList to records in a > filtered dataset(using SetRange). > > The loop has two purposes. Delete anything from the dataset that is not > in the string list, and remove everything from the string list that is > already in the dataset. I need the string list to have all of the items > not in the dataset by the end of the loop. > > Here is the basic loop: > > while Table.Eof = false do > begin > > if StringList.Find(Field.AsString, FoundIndex) = true then > begin > StringList.Delete(FoundIndex); > Table.Next; > end > else > begin > Table.Delete; > end; > end; > Turn it arround. Loop thru the stringlist and on a table.locate = true delete the stringlist entry. At the end only the records that are not in the stringlist are left and you can delete all of them. I could ofcourse ask if the result in the stringlist itself came from a query ![]() |
Fri, Dec 15 2006 11:45 AM | Permanent Link |
Mark Elder | Frans van Daalen wrote:
> Turn it arround. Loop thru the stringlist and on a table.locate = true > delete the stringlist entry. At the end only the records that are not in the > stringlist are left and you can delete all of them. > > I could ofcourse ask if the result in the stringlist itself came from a > query ![]() Thanks for the suggestion. Unfortunately I am doing the lookups this way for performance reasons. I was originally doing the lookup in the dataset. However the StringList has fewer items compared to the dataset and the lookup is much faster. Also the normal case is that everything is already in the dataset and everything will be deleted from the string list. So as the loop progresses the StringList lookups get faster and faster. The string list is actually filled with a list of files from iterating a directory. The files are parsed and have some information loaded into a table. This code is checking timestamps on the files to see if anything needs to be updated. It also checks for new files that have not been parsed yet and checks to see if anything is in the table that does not exist on disk anymore. Thanks, Mark |
Fri, Dec 15 2006 2:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Mark,
<< The problem I have found is that Eof is not set after deleting the last record in the range. This is really bad in my case since when the loop repeats one more time it will start deleting all of the records in the range until it empties the dataset. >> Your title is a bit of a misnomer - when you say "last record" what you mean is the last positional record, not the actual last record in the range. That is, there are still records present in the range due to the Next. << However the StringList has fewer items compared to the dataset and the lookup is much faster >> It shouldn't be as long as there is an index available for a FindKey or Locate call. These are binary searches in such a case, whereas the string list Find call is a brute-force search. My suggestion would be to do as Frans recommends and flip things around. There's really no other accurate way of dealing with the issue that I can think of. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Dec 15 2006 4:49 PM | Permanent Link |
Mark Elder | Tim Young [Elevate Software] wrote:
> Mark, > > << The problem I have found is that Eof is not set after deleting the last > record in the range. This is really bad in my case since when the loop > repeats one more time it will start deleting all of the records in the range > until it empties the dataset. >> > > Your title is a bit of a misnomer - when you say "last record" what you mean > is the last positional record, not the actual last record in the range. > That is, there are still records present in the range due to the Next. I don't think that is the case. This happens when I try and delete the last record in the range. My understanding is that the equivalent of "Next" happens after Delete. Thus in a delete loop you do not need to call next explicitly, and if you do call next you will skip records. However, if you call delete when you are on the last record then the previous record will be the current record and EOF will not be set. Take this code for example. You may expect it to clear out everything except the first record. When actually it will delete all records from the table. MyTable.First; MyTable.Next; // Move to the second record // so that the first will not be deleted. while not MyTable.EOF do begin MyTable.Delete; end; // MyTable.RecordCount is now zero. At any rate this appears to be standard Dataset behavior, or at least posts on Borland's newsgroups seem to indicate this is the case. DBISAM has so many nice features that the standard TDataset does not. I was hoping that maybe there was a way to check for the last record before EOF was actually set. At any rate it's not that big of a deal. I have replaced the while loop with a straight forward For loop using the RecordCount of the range. It looks a little strange but it seems to work. If you can think of a reason not to take this approach please let me know. > << However the StringList has fewer items compared to the dataset and the > lookup is much faster >> > > It shouldn't be as long as there is an index available for a FindKey or > Locate call. These are binary searches in such a case, whereas the string > list Find call is a brute-force search. My suggestion would be to do as > Frans recommends and flip things around. There's really no other accurate > way of dealing with the issue that I can think of. > Well actually the StringList is sorted in the same order as my Dataset. The collation order does not match exactly but it is close enough that most of the time the record being searched for is at the top of the list. So the find operation ends up being very fast - basically constant time. Also this allows for just one pass through the list. If I flip it around I end up needing to run another pass on the string list to find the rows that really need to be deleted. It's been a year since I worked on cleaning this up. But as I remember the difference in speed was significant. Nothing wrong with DBISAM - it's very fast. It's just hard for any type of index search in the middle of a loop to compare with a single pass constant time loop. Thanks for your input. Also thanks to everyone who answers posts in these groups. I have not needed to post here before because I can always find answers to my questions in other posts. Since making the decision to use DBISAM I have been a very satisfied customer. Thanks, Mark |
Sat, Dec 16 2006 6:02 AM | Permanent Link |
"Frans van Daalen" | "Mark Elder" <Mark.Elder@uaar.net> wrote in message news:31E148E3-D6EB-4566-9AA8-55BA282C500A@news.elevatesoft.com... > Also this allows for just one pass through the list. If I flip it around > I end up needing to run another pass on the string list to find the rows > that really need to be deleted. > What you can do is construct a delete with a IN clause. That way you while running try the thru the stringlist you construct this IN clause, if however there are more then few 100 items then you need to split it. somethink like // warning bad code habits ahead ![]() s_del : Array[0..300] of String; // to construct the delete in clause For I:=0 to Stringlist.count-1 do Begin s_del[del_i div 250] := s_del[del_i div 250]+',"'+stringlist[i]+'"'; del_I := del_i + 1; end; // to delete from database For I:=0 to (del_i div 250) do Try s_del[i][1] :=char(32); // remove the first , cmdIm.CommandText := 'DELETE <table> where <field> IN ('+s_del[i]+')'; CmdIm.Execute; except end; //---------------------------------- Mind you that if you would save the results from the filescan in a temp datafile all of this could be done much easier. Both the removal from the temp file and the removal from the "real file" could then be done with 2 sql statements. > > Since making the decision to use DBISAM I have been a very satisfied > customer. > left it here for anyone to read ![]() |
Mon, Dec 18 2006 1:55 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Mark,
<< I don't think that is the case. This happens when I try and delete the last record in the range. >> It can't be - if there's no records left in the range after a delete then both BOF and EOF will be set, otherwise neither will be set. << My understanding is that the equivalent of Next" happens after Delete. >> Not exactly. DBISAM just positions itself on the nearest matching record, which may be the prior record in some cases. << However, if you call delete when you are on the last record then the previous record will be the current record and EOF will not be set. >> Correct, the only two actions that set EOF are trying to navigate past EOF using Next or calling Last. << At any rate it's not that big of a deal. I have replaced the while loop with a straight forward For loop using the RecordCount of the range. It looks a little strange but it seems to work. If you can think of a reason not to take this approach please let me know. >> The only downside would be the possibility that records are added while processing that change the RecordCount. But, if this isn't possible (single-user, etc.) then there isn't really any issue. << Well actually the StringList is sorted in the same order as my Dataset. The collation order does not match exactly but it is close enough that most of the time the record being searched for is at the top of the list. So the find operation ends up being very fast - basically constant time. >> Well, if you can be assured that the item being searched for is always at the top of the list, then yes, that would be fairly quick. << Since making the decision to use DBISAM I have been a very satisfied customer. >> I'm glad to hear that. ![]() -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Dec 19 2006 4:20 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Tim
>Not exactly. DBISAM just positions itself on the nearest matching record, >which may be the prior record in some cases. Is this just when there's a range set, or always? I, like many others, use constructs like while not xx.eof do begin if condition then xx.delete else xx.next; end; and you seem to be saying this is unsafe. Roy Lambert |
Tue, Dec 19 2006 9:58 AM | Permanent Link |
"Robert" | "Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message news:E7CD99F0-3298-46D9-B4EA-B81E201549D6@news.elevatesoft.com... > Tim > >>Not exactly. DBISAM just positions itself on the nearest matching record, >>which may be the prior record in some cases. > > Is this just when there's a range set, or always? > > I, like many others, use constructs like > > while not xx.eof do begin > if condition then xx.delete else xx.next; > end; > I think that will always work 1 2 3 << last record, delete when you are on 3, you delete it, which puts you back on 2, which does not meet the delete criteria so you do a next and get eof. Robert |
Tue, Dec 19 2006 10:32 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Robert
Of course ![]() Roy Lambert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |