Robert, I think you hit the nail on the head. The program accesses the CSV file directly using a TAB file. If I convert the file to a native format, the program runs much faster. Still not what I would consider "fast", but much closer to acceptable. Of course, I didn't include that part of the code, because I didn't think that was the issue. See, I told you I don't know anything <g>.
Many thanks to all, Joel -----Original Message----- From: Robert Crossley [mailto:[EMAIL PROTECTED] Sent: Thursday, February 19, 2004 5:06 PM To: Joel Leach Cc: MapInfo List Subject: Re: MI-L Slow MapBasic Code You mentioned a CSV file, I take it that you have saved it to a native format. Then, any technique where you loop through a table and update that record individually is always slow. Without getting into to much depth of your code, an answer may be to write a function that accepts parameters of the values of the neccessary columns to return the answer for that field., and Update the whole table using those functions. This way, the recordset processing is done by mapInfo once, and the code in Mapbasic just does logic. I think that there is a large loss in performance as mapInfo does a lot of selections and updates. The example given below is a guide only, without getting into too much of your logic. eg. Update Map_Table_Name Set Column_Names(10) = fReturnCol10Value(Column_Names(10), W_ConveryNo), Column_Names(9) = fReturnCol10Value(Column_Names(9), W_ConveryNo),..... FUnction fReturnCol10Value(ByVal ColumnNumberVal As String, ByVal W_ConveryNo As String) fReturnCol10Value = ColumnNumberVal IF ColumnNumberVal <>"" THEN IF W_ConveyNo <> ColumnNumberVal THEN fReturnCol10Value=W_ConveyNo END IF END IF I use this technique a lot, and even is even quite fast if you store much of your look up table logic in type arrays, and have a function that returns the correct value by screaming through the array On Thu, 19 Feb 2004 16:09:43 -0600, Joel Leach <[EMAIL PROTECTED]> wrote: > 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 > -- Robert Crossley Agtrix P/L 9 Short St New Brighton 2483 Far Southern Queensland AUSTRALIA 153.549004 E 28.517344 S P: 02 6680 1309 F: New Connection M: 0419 718 642 E: [EMAIL PROTECTED] W: www.agtrix.com W: www.wotzhere.com --------------------------------------------------------------------- List hosting provided by Directions Magazine | www.directionsmag.com | To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] Message number: 10547
