"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
*********************************************************************** NOTICE: This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential or privileged information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify the sender and delete the e-mail and attached documents. Thank you. ***********************************************************************
_______________________________________________ Delphi mailing list -> [email protected] http://www.elists.org/mailman/listinfo/delphi
