MapInfo Pro Developers User Group

Expand all | Collapse all

I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

Mahesh Tripathy10-01-2018 04:01

Mahesh Tripathy10-03-2018 19:46

Mahesh Tripathy10-03-2018 19:47

  • 1.  I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-01-2018 04:01


  • 2.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-01-2018 02:10

    Hi Mahesh

    Would it be possible for you to share the part of the code where you do your looping and updating?

    We have seen that especially performing Select statements into Selection instead of a named query and using the NoSelect keyword takes a lot longer in v17.0 compared to earlier versions.

    So instead of doing:

    Select * From SOME_TABLE Into Selection

    You should do:

    Select * From SOME_TABLE Into __QUERY_XX NoSelect

    Also note that MapBasic 17.0 gives you a couple of new tricks to optimize the performance of your tools:

    1. Set Table ... ReDraw Off
    2. Select .... Into __QUERY NoSelect Hide

     

    The first last you set a table into a no redraw mode which means that any changes to the table will not get rendered until you turn the redraw mode back on.

    The second makes it possible to create a query result and don't show this for example in the TableList window.



  • 3.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-01-2018 02:22

     

    Do While Not EOT(DCL_Y)

    a= DCL_Y.start_equipment_id

    b= DCL_Y.end_equipment_id

    c= DCL_Y.id

    select * from COPPER_INTERCONNECT_WORKBOOK where DPU_EQUIPMENT_ID = a and CJL_id =b AND DCL_ID="" into Q1

    select * from Q1 where rowid=1 INTO Q2

    If SelectionInfo(SEL_INFO_NROWS) > 0 Then 

        update selection set DCL_id = c

    end if

      CLOSE TABLE Q1 

      CLOSE TABLE Q2

    fetch next from DCL_Y

    Loop

     



  • 4.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-01-2018 02:27

    I have used separate query. May be I should try disabling redraw or the 2nd option you gave.Do you have any other options? Thanks a lot for this.



  • 5.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-01-2018 02:52

    Trying changing this part of your code:

    select * from COPPER_INTERCONNECT_WORKBOOK where DPU_EQUIPMENT_ID = a and CJL_id =b AND DCL_ID="" into Q1

    select * from Q1 where rowid=1 INTO Q2

    If SelectionInfo(SEL_INFO_NROWS) > 0 Then 

        update selection set DCL_id = c

    end if

    to this:

    Select * from COPPER_INTERCONNECT_WORKBOOK where DPU_EQUIPMENT_ID = a and CJL_id =b AND DCL_ID="" into Q1 NoSelect

    If TableInfo(Q1, TAB_INFO_NROWS) > 0 Then 

        update Q1 set DCL_id = c

    end if

    And remove the statement that closes table Q2 as that no longer exists.



  • 6.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-02-2018 20:08

    ?I can use TableInfo to see if there is any row but I created Q2 so that just the 1st row gets populated as I have multiple match case. Example: Fruits can be looked up with Apple, banana, orange etc. I want them to be filled in separate lines. Sorry my articulation is not that good but  Is there Any way that I can return 1st row of the query with just Q1? OR populate them in separate lines? Thanks in advance.



  • 7.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-02-2018 20:52

    Hi Mahesh,

    I think a series of whole-table updates might work better here. That is, join COPPER_INTERCONNECT_WORKBOOK to DCL_Y on the CJL_id and DCL_ID fields and update the resulting table with DCL_id = id. But you will need to come up with a method for hanlding the duplicates within COPPER_INTERCONNECT_WORKBOOK first. Using your fruit example, I use the technique of assigning an ID as follows:

    RowID, Type, Item_ID

    1, Apple, 1

    2, Apple, 2

    3, Banana, 1

    4, Banana, 2

    5, Banana, 3

    6, Orange, 1

    etc...

    Then when it comes to the table join you can use the Item_ID field to just update the first record of each group. Perform the join a number of times until all items of each group have been updated.

    This (or something similar) will be much faster than looping through and updating each record one at a time.

    If an "Item_ID" is not already available, it can be done via joins and group bys.

    I hope this makes sense. What do you think, @Peter Horsbøll Møller? ?



  • 8.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-03-2018 02:39

    That might work, James, but I struggle a bit understanding the why he only want to update the first record that doesn't have a DCL_id and not the remaining records?

    If you prefer to use your current structure, you can modify my suggestion slightly to only update the first:

    Select * from COPPER_INTERCONNECT_WORKBOOK where DPU_EQUIPMENT_ID = a and CJL_id =b AND DCL_ID="" into Q1 NoSelect

    If TableInfo(Q1, TAB_INFO_NROWS) > 0 Then 

        update Q1 set DCL_id = c Where ROWID = 1

    end if

    I do however agree with @James Nolet? that a single whole-table update is a lot more efficient than looping thru a table and updating one record at a time.



  • 9.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-03-2018 19:45

    Please refer to the two pictures in the attachment. 

    I needed to fill table Q1 with the values in the table DCL_Y and I can't use static item number for these cases which is why I fetched DCL_Y one by one and populated Q1 one by one. 



  • 10.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-03-2018 19:46
      |   view attached



  • 11.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-03-2018 19:47
      |   view attached


  • 12.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-03-2018 21:07

    Hi Mahesh,

    What is the Port_Number field?

    What do you do with those records that have a blank CJL_ID? Using your code they would not be assigned an ID from the DCL_Y table. Is that right?

    Are you able to provide the tables?

    Not that it answers your original question about why your process is much slower now, but it could be made much faster by using a different technique.



  • 13.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 10-03-2018 23:31

    Peter's logic to merge into 1query went just right. Now the runtime has comedown by Far. Thanks a lot mate, you're a Gem.

    update Q1 set DCL_id = c Where ROWID = 1

    James, I do not need any records for the bank CJLs they're just fine. Port number is needed to reflect the device with total 4 x ports their attributes need to be uniquely populated. Thanks for your insight, I agree populating through Query is far quicker.



  • 14.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Pitney Bowes
    Posted 29 days ago
    Has anyone fi​led an incident for this? It is great that we found a workaround but if the performance got worse from 16 to 17 we should investigate it.

    ------------------------------
    Bob Fortin
    Software Architect and Distinguished Engineer
    ------------------------------



  • 15.  RE: I had Mapinfo prov16.0 but now that I upgraded to V17.0 pro. I have a loop in my program which keeps updating the table until EOT. I tried all the performance modes but still is too slow. Previously it was 1 hr, now it takes half a day for same program.

    Posted 28 days ago
    I have raised a case with tech support, Bob, and they created a bug: MIPRO-103178

    ------------------------------
    Peter Horsbøll Møller
    Pitney Bowes
    ------------------------------