MapInfo Pro Developers User Group

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.

 

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

 

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.

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.

?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.

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? ?

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.

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. 

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.

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.

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

Original Message:
Sent: 10-03-2018 23:30
From: Mahesh Tripathy
Subject: 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.

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.

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

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

Original Message:
Sent: 12-19-2018 14:36
From: Bob Fortin
Subject: 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.

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

Original Message:
Sent: 10-03-2018 23:30
From: Mahesh Tripathy
Subject: 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.

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.