MapInfo Pro

Happy #MapInfoMonday!

In today's session, we will dive a bit more into the details around joining two tables and see if there are ways to improve the performance.

There are a few things to consider, especially when joining a table with few records to a table with many records.​ In the example, I will show you, we are joining a table with 227 records to a table with 2.5 million records.

The typical way you would position the tables is controlled by the spatial object you want to be returned. That's because, before MapInfo Pro v2021, this was the only way you could control this. The spatial object would be taken from the first table listed in the SQL Select statement.

Here's an example of the typical order you would use when you wanted the small boundaries returned, that is the smaller boundaries, the properties, that intersect a potential larger boundary, the city center.

In order to time the statement, I have added a few Print statement before and after that will give me some time details:

Print Time(24) + " Start: Many to Few"
Select p.MATRNR, p.ELAVSNAVN, c.FEAT_TYPE
  From Properties As "p", City_Center As "c"
  Where p.Obj Intersects c.Obj
  Into _RESULT NoSelect
Print Time(24) + " Done!"
Print Time(24) + " Records: " + TableInfo(_RESULT, TAB_INFO_NROWS)

Here's the same Select statement but with a reversed order of the two tables, and the Where condition too:

Print Time(24) + " Start: Few to Many"
Select p.MATRNR, p.ELAVSNAVN, c.FEAT_TYPE, p.Obj Object
From City_Center as "c", Properties As "p"
Where c.Obj Intersects p.Obj
Into _RESULT NoSelect
Print Time(24) + " Done!"
Print Time(24) + " Records: " + TableInfo(_RESULT, TAB_INFO_NROWS)

Also, note that I had to change the projection list too. I had to add the expression p.obj Object to force MapInfo Pro to use the spatial object from the Properties table instead of the City Center table. The Object keyword tells MapInfo Pro to use the spatial expression in front of the keyword as the spatial object for the resulting query table. In this example, it's the spatial object from the other table but it can in fact be any spatial expression.

When I run these two statements through the SQL Window, the timing information is printed to the Message window.

Looking closely at the details in the Message window reveals that the second Select statement is much faster than the first.

09:38:57 Start: Many to Few
09:40:05 Done!
09:40:05 Records: 53580
09:40:05 Start: Few to Many
09:40:17 Done!
09:40:17 Records: 53580

The first query took 68 seconds to complete. The second took only 12 seconds.

I hope this gave you some ideas on how you can improve the performance of your joins. If you have any questions, do not hesitate to post them in the comments below.

------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------