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
