Forgive me if I am being dense, You have a directory of .pdf that you wish to record in a table.
You are running checks A: .pdf in PDF_Table -> !in directory B: .pdf in directory -> !in PDF_Table Would it not be easier to A: Create scratch table of .pdf that actually exist in the directory B: Clear PDF_Table of .pdf (delete * from PDF_Table) C: Append Scratch table to PDF_Table If not possible, can you use a scratch table & a simiar SQL to this: Delete from PDF_Table where name not = scratch.name I have no experience of Advantage, but stored procedures are standard in most rdbms > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > Sent: 28 June 2007 10:07 > To: [email protected] > Subject: RE: Slowing down in loop > > "Nesler, Thomas J" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 27/06/2007 15:27 > Please respond to > "Borland's Delphi Discussion List" <[email protected]> > > > To > "Borland's Delphi Discussion List" <[email protected]> > cc > > Subject > RE: Slowing down in loop > > > > > Thanks for all the replays. > The database I am using is Advantage Database Server. Their > publicity says > that the locate will use an underlying index if one is available, the > field is indexed. The reason I tried locate was I do not control the > naming of the files or the entire of the file names into the > table. So I > used locate for a case insensitive search. > After reading the replays I now have a thread run on > loading that > turns all the pdf names in the table to lower case. This is > not visible to > the user so the second or so it takes is not important. > > I have included the timing from all three runs for completeness. > > Code fragment. . > The filenames are loaded into the stringlist as lowercase > > for i := 0 to pred(sl_PdfFiles.Count ) do begin > SearchForFileInTable(i, start, psFilePath, sl_PdfFiles, y, tb); > on E:EADSDatabaseError do begin > showmessage(e.Message); > raise; > end; > > procedure TDM1.SearchForFileInTable(i: Integer; start: Int64; > psFilePath: > string; sl_PdfFiles: TStringList; var y: Integer; tb: TAdsTable); > begin > y := i; > tb.indexname := 'PDFNAME'; > if not tb.FindKey([ sl_PdfFiles[i] ]) then begin > > // if not tb.Locate('PdfName', sl_PdfFiles[i], > [loCaseInsensitive]) > then begin > > // dosql(format('Select * from %s where lower(PdfName) = ''%s'' > ',[tb.TableName,sl_PdfFiles[i]]),Qypl, SQL_SELECT ); > // if qypl.RecordCount = 0 then begin > > > inc(x); > AddRecordToPdfTable(sl_PdfFiles[i], tb, psFilePath); > fmMain.LbStaticText1.Caption := ' PDFs added = ' + > IntToStr(x); > end; > > RefreashPRgBar(i); > if i mod 100 = 0 then begin > WriteftoFile('c:\log\Pdfupdate.log', > IntToStr(Gettickcount > - start), FL_APPEND); > start := GetTickCount; > end; > > end > > ============================================================== > ================== > FINDKEY ======= LOCATE ========== SQL > ===================================== > =========================================== > 16 32 > 31 > 63 141 > 609 > 94 344 1125 > 157 657 1688 > 266 969 2219 > 344 1344 2750 > 422 1891 3344 > 485 2407 3875 > ============================================================== > ================== > 0 15 15 > 47 140 > 1359 > 78 359 > 2703 > 125 609 > 4078 > 172 1031 5437 > 219 1437 6859 > 250 1906 8250 > 297 2546 9656 > 344 3296 > 11062 > 391 4031 > 12422 > 438 4921 > 13875 > 485 5812 > 15281 > 531 6781 > 16656 > 578 7859 > 18047 > 625 9031 > 19453 > 688 10265 20890 > 735 11593 22328 > 781 12968 23718 > 828 14421 25093 > 891 15968 26468 > 953 17609 27859 > 1000 19296 29250 > 1063 21031 30640 > 1125 22906 32000 > 1188 24828 33406 > 1235 26843 34828 > 1297 29015 36297 > 1360 31156 37843 > 1438 33437 39422 > 1500 35812 41000 > 1610 38218 42515; > > Hi Robert! > > Is there any way you could read in the file names into a stringlist, > sort the stringlist and then do the locate? I think it would help if > you could give us a sample of your code to critique. > > One of your problems is to determine what is slowing down the process. > Is it the reading of the file names or is it the database search? > > Tom Nesler > Live long!... Code Well!... and Prosper!... V > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of [EMAIL PROTECTED] > Sent: Tuesday, June 26, 2007 8:18 AM > To: [email protected] > Subject: Slowing down in loop > > > i have an app that has to update two table with pdf files from > directories. There are about 900 files in one Dir and about > 3000 in the > > other. I have to check if each file is in the table if not > add it. This > requires 3000 searches on the table but as the app progresses > it slows > down to a crawl. Anybody have any ideas?. > > I have to add to this the revers for deleted files, so I > really could do > > with speeding up the loop. > > The list below is the milliseconds it takes to do 100 hundred > records. > There are no updates in the trimmings just locates > bob Pointon > > ====================================== > IPACTA_TRIALS_PDFS > 0 > 109 > 203 > 266 > 328 > 500 > 531 > 610 > ====================================== > IPACTA_LABELS_PDFS > 0 > 110 > 297 > 250 > 328 > 422 > 578 > 579 > 718 > 750 > 813 > 937 > 1000 > 1063 > 1094 > 1250 > 1328 > 1406 > 1453 > 1516 > 1578 > 1718 > 2047 > 1907 > 1875 > 2031 > 2032 > 2187 > 2250 > 2344 > 2406 > _______________________________________________ > Delphi mailing list -> [email protected] > http://www.elists.org/mailman/listinfo/delphi > > _______________________________________________ Delphi mailing list -> [email protected] http://www.elists.org/mailman/listinfo/delphi
