By the way, I forgot to mention, I'm running this on MapInfo/MapBasic 6.0. Not sure if that makes a difference regarding performance. The code is older than that.
-----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: 10545
