Re: MI-L Slow MapBasic Code

[Robert Crossley]

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: 10546