Hi Warren, The first column in the table (Location) is indexed, and I assume that is what Col1 is referring to. When I turn the index off, the program slows down considerably, so I think that part is working. Per other recommendations, I confirmed that FastEdit is on and Undo is off in other parts of the program. I find it odd that the program selects and updates one record at a time. Is that the usual MapInfo way? In other languages, I would probably select all of the applicable records, update them, then commit the changes. Then again, maybe MapInfo was not made to process this much data. Thanks for you help. Any other suggestions would be appreciated.
Joel -----Original Message----- From: Warren Vick, Europa Technologies Ltd. [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 3:51 PM To: Joel Leach; [EMAIL PROTECTED] Subject: RE: MI-L Slow MapBasic Code Hello Joel, Based on a very quick glance, I'd say the critical line in the code is: SELECT * FROM Map_Table_Name WHERE Col1=Location_Key INTO Check NOSELECT If Col1 is not indexed, then 20,000+ searches on a large database will take hours/days/etc. Regards, Warren Vick Europa Technologies Ltd. http://www.europa-tech.com -----Original Message----- From: Joel Leach [mailto:[EMAIL PROTECTED] Sent: 19 February 2004 21:02 To: '[EMAIL PROTECTED]' Subject: MI-L Slow MapBasic Code I am Windows developer, but I have very limited experience with MapInfo and MapBasic. A former developer wrote a custom program to pull data from a client's CSV file and update their map with the information. The program works, but it does so very slowly. For example, updating 20,000 records in the map takes almost 6 hours. Larger maps have taken up to 4 days to update. I attribute some of this to the fact that the client is updating the map over a network, but even when I run the program locally, it is very slow. From my experience, the best way to speed up programs is to "take out the slow parts" <g>. Unfortunately, I don't have the expertise to know which parts are slow, but I have suspicions. I have included source code below. Basically, the client's data is pulled into a table called Mappingwk. That part is pretty fast. Then, for each record in Mappingwk, the procedure below is called. I have included the entire procedure for your review, but I think things start to slow down when the applicable record is found and updated in the map. I have made a note at that point below. I am sure there is a better way to handle this, so any tips would be appreciated. Thanks, Joel Leach MBS, Inc. www.mbs-intl.com ---------------------- '*********************************************** '* SUB Procedure: Convert_Progress * '* Client's data pulled into Mappingwk table * '* This procedure is called for each record in * '* Mappingwk * '*********************************************** SUB Convert_Progress Records_Processed=Records_Processed+1 Select_Error=FALSE RecordUpdated=FALSE '**** Set Location Key Delimiter1=INSTR(1,Mappingwk.Location,".") Delimiter2=INSTR(Delimiter1+1,Mappingwk.Location,".") Delimiter3=INSTR(Delimiter2+1,Mappingwk.Location,".") Delimiter4=INSTR(Delimiter3+1,Mappingwk.Location,".") Delimiter5=INSTR(Delimiter4+1,Mappingwk.Location,"/") Descriptor1=LEFT$(Mappingwk.Location,Delimiter1-1) Descriptor2=MID$(Mappingwk.Location,Delimiter1+1,Delimiter2-Delimiter1-1) Descriptor3=MID$(Mappingwk.Location,Delimiter2+1,Delimiter3-Delimiter2-1) Descriptor4=MID$(Mappingwk.Location,Delimiter3+1,Delimiter4-Delimiter3-1) IF Delimiter5=0 THEN Descriptor5=RTRIM$(MID$(Mappingwk.Location,Delimiter4+1,20)) DepthDescriptor="" ELSE Descriptor4=MID$(Mappingwk.Location,Delimiter4+1,Delimiter5-Delimiter4-1) DepthDescriptor=RTRIM$(MID$(Mappingwk.Location,Delimiter5+1,20)) END IF Location_Key=Descriptor1+","+Descriptor2+","+Descriptor3+","+Descriptor4+"," +Descriptor5 '**** Set Depth DO CASE DepthDescriptor CASE "1","L","B3","X","X3","Z","H3","M3","F3","Z3","S3","LC" Depth="A" CASE "2","U","B2","X2","H","H2","M2","F2","Z2","S2","UC" Depth="B" CASE "3","B1","X1","M","F","H1","M1","F1","Z1","S1" Depth="C" CASE ELSE Depth="A" END CASE Raw_Location=RTRIM$(Mappingwk.Location) '**** '** Capture Fields From The Mappingwk File '**** W_Location = Mappingwk.Location W_UserName1 = InsertComma(Mappingwk.UserName1) W_ConveyNo = Mappingwk.ConveyNo W_ConveyDate = Mappingwk.ConveyDate W_VaultDescription = Mappingwk.VaultDescription W_VaultInstallDate = Mappingwk.VaultInstallDate W_MarkerDescription = Mappingwk.MarkerDescription W_MarkerOrderNo = Mappingwk.MarkerOrderNo W_MarkerOrderDate = Mappingwk.MarkerOrderDate W_MarkerInstallDate = Mappingwk.MarkerInstallDate W_ContractNumber1 = Mappingwk.ContractNumber1 W_ContractNumber2 = Mappingwk.ContractNumber2 W_ContractNumber3 = Mappingwk.ContractNumber3 W_ContractNumber4 = Mappingwk.ContractNumber4 W_ContractNumber5 = Mappingwk.ContractNumber5 W_OwnerNo1 = Mappingwk.OwnerNo1 W_OwnerNo2 = Mappingwk.OwnerNo2 W_OwnerNo3 = Mappingwk.OwnerNo3 W_OwnerNo4 = Mappingwk.OwnerNo4 W_OwnerNo5 = Mappingwk.OwnerNo5 W_OwnerName1 = InsertComma(Mappingwk.OwnerName1) W_OwnerName2 = InsertComma(Mappingwk.OwnerName2) W_OwnerName3 = InsertComma(Mappingwk.OwnerName3) W_OwnerName4 = InsertComma(Mappingwk.OwnerName4) W_OwnerName5 = InsertComma(Mappingwk.OwnerName5) W_UserInfo1 = Mappingwk.UserInfo1 W_UserService1 = Mappingwk.UserService1 W_Status=Mappingwk.Status '**** Reformat User Info W_DOB=RTRIM$(LEFT$(W_UserInfo1,10)) W_DOD=RTRIM$(MID$(W_UserInfo1,12,10)) '** User Info no longer used W_UserInfo1="" IF W_DOB<>"" AND W_DOD<>"" THEN W_UserInfo1="DOB="+W_DOB+", DOD="+W_DOD END IF IF W_DOB<>"" AND W_DOD="" THEN W_UserInfo1="DOB="+W_DOB+", DOD= / / " END IF IF W_DOD<>"" AND W_DOB="" THEN W_UserInfo1="DOB= / / , DOD="+W_DOD END IF '** Set Service Fields W_DateOfService1=LEFT$(W_UserService1,10) W_PlaceOfService1=MID$(W_UserService1,12,15) =============================================================== ==> Joel Leach: I think this is where things start to get slow. =============================================================== '**** '** Grab Mapping Data Base Record For Purposes Of Update '** There is an index on Location_Key in the map '**** SELECT * FROM Map_Table_Name WHERE Col1=Location_Key INTO Check NOSELECT IF TABLEINFO("Check",TAB_INFO_NROWS)>0 THEN '**** '** Update Fields '**** IF Column_Number(10)<>"" THEN IF W_ConveyNo <> Column_Names(10) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(10)=W_ConveyNo RecordUpdated=TRUE END IF END IF IF Column_Number(11)<>"" THEN IF W_ConveyDate <> Column_Names(11) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(11)=W_ConveyDate RecordUpdated=TRUE END IF END IF IF Column_Number(12)<>"" THEN IF W_VaultDescription <> Column_Names(12) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(12)=W_VaultDescription RecordUpdated=TRUE END IF END IF IF Column_Number(13)<>"" THEN IF W_VaultInstallDate <> Column_Names(13) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(13)=W_VaultInstallDate RecordUpdated=TRUE END IF END IF IF Column_Number(14)<>"" THEN IF W_MarkerDescription <> Column_Names(14) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(14)=W_MarkerDescription RecordUpdated=TRUE END IF END IF IF Column_Number(15)<>"" THEN IF W_MarkerOrderNo <> Column_Names(15) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(15)=W_MarkerOrderNo RecordUpdated=TRUE END IF END IF IF Column_Number(16)<>"" THEN IF W_MarkerOrderDate <> Column_Names(16) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(16)=W_MarkerOrderDate RecordUpdated=TRUE END IF END IF IF Column_Number(17)<>"" THEN IF W_MarkerInstallDate <> Column_Names(17) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(17)=W_MarkerInstallDate RecordUpdated=TRUE END IF END IF IF Column_Number(20)<>"" THEN IF W_ContractNumber1 <> Column_Names(20) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(20)=W_ContractNumber1 RecordUpdated=TRUE END IF END IF IF Column_Number(21)<>"" THEN IF W_ContractNumber2 <> Column_Names(21) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(21)=W_ContractNumber2 RecordUpdated=TRUE END IF END IF IF Column_Number(22)<>"" THEN IF W_ContractNumber3 <> Column_Names(22) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(22)=W_ContractNumber3 RecordUpdated=TRUE END IF END IF IF Column_Number(23)<>"" THEN IF W_ContractNumber4 <> Column_Names(23) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(23)=W_ContractNumber4 RecordUpdated=TRUE END IF END IF IF Column_Number(24)<>"" THEN IF W_ContractNumber5 <> Column_Names(24) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(24)=W_ContractNumber5 RecordUpdated=TRUE END IF END IF IF Column_Number(28)<>"" THEN IF W_OwnerNo1 <> Column_Names(28) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(28)=W_OwnerNo1 RecordUpdated=TRUE END IF END IF IF Column_Number(29)<>"" THEN IF W_OwnerNo2 <> Column_Names(29) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(29)=W_OwnerNo2 RecordUpdated=TRUE END IF END IF IF Column_Number(30)<>"" THEN IF W_OwnerNo3 <> Column_Names(30) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(30)=W_OwnerNo3 RecordUpdated=TRUE END IF END IF IF Column_Number(31)<>"" THEN IF W_OwnerNo4 <> Column_Names(31) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(31)=W_OwnerNo4 RecordUpdated=TRUE END IF END IF IF Column_Number(32)<>"" THEN IF W_OwnerNo5 <> Column_Names(32) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(32)=W_OwnerNo5 RecordUpdated=TRUE END IF END IF IF Column_Number(36)<>"" THEN IF W_OwnerName1 <> Column_Names(36) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(36)=W_OwnerName1 RecordUpdated=TRUE END IF END IF IF Column_Number(37)<>"" THEN IF W_OwnerName2 <> Column_Names(37) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(37)=W_OwnerName2 RecordUpdated=TRUE END IF END IF IF Column_Number(38)<>"" THEN IF W_OwnerName3 <> Column_Names(38) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(38)=W_OwnerName3 RecordUpdated=TRUE END IF END IF IF Column_Number(39)<>"" THEN IF W_OwnerName4 <> Column_Names(39) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(39)=W_OwnerName4 RecordUpdated=TRUE END IF END IF IF Column_Number(40)<>"" THEN IF W_OwnerName5 <> Column_Names(40) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(40)=W_OwnerName5 RecordUpdated=TRUE END IF END IF '**** '** Individual Depth Fields '**** DO CASE UCASE$(Depth) CASE "A" i=0 CASE "B" i=1 CASE "C" i=2 CASE ELSE i=0 END CASE IF Column_Number(52+i)<>"" THEN IF W_UserName1 <> Column_Names(52+i) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(52+i)=W_UserName1 RecordUpdated=TRUE END IF END IF IF Column_Number(60+i)<>"" THEN IF W_DOB <> Column_Names(60+i) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(60+i)=W_DOB RecordUpdated=TRUE END IF END IF IF Column_Number(63+i)<>"" THEN IF W_DOD <> Column_Names(63+i) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(63+i)=W_DOD RecordUpdated=TRUE END IF END IF IF Column_Number(76+i)<>"" THEN IF W_DateOfService1 <> Column_Names(76+i) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(76+i)=W_DateOfService1 RecordUpdated=TRUE END IF END IF IF Column_Number(79+i)<>"" THEN IF W_PlaceOfService1 <> Column_Names(79+i) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(79+i)=W_PlaceOfService1 RecordUpdated=TRUE END IF END IF '**** '** Property Status Flag Logic '**** W_Prop_Status="A" ' Make decision based upon the population of the field or rather the array of statuses IF Column_Number(44+i)<>"" THEN IF W_Status <> Column_Names(44+i) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(44+i)=W_Status RecordUpdated=TRUE END IF END IF IF Column_Number(44+i)="S" THEN W_Prop_Status="S" END IF IF Column_Number(44+i)="I" THEN W_Prop_Status="I" END IF IF Column_Number(1)<>"" THEN IF W_Prop_Status <> Column_Names(1) THEN Columns_Updated=Columns_Updated+1 UPDATE Check SET Column_Number(1)=W_Prop_Status RecordUpdated=TRUE END IF END IF IF RecordUpdated THEN Records_Updated=Records_Updated+1 END IF ELSE IF MBS_Debug=TRUE THEN PRINT "Didn't find Location " + Location_Key + " : " + STR$(Timer() - Timer_Start) PRINT #8, "Didn't find Location " + Location_Key + " : " + STR$(Timer() - Timer_Start) END IF PRINT #9, "(Mapping.DBF ) "+" ("+Raw_Location+SPACE$(20-LEN(Raw_Location))+")" Records_In_Error=Records_In_Error+1 Select_Error=TRUE END IF COMMIT TABLE Check FETCH NEXT FROM Mappingwk PROGRESSBAR=Records_Processed IF EOT(Mappingwk) THEN PROGRESSBAR=-1 END IF END SUB --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 10536 --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 10544
