Hands up if you like watching progress bars slowly make their way from start to finish in any application you use?
Previously, if you had been working with large tables in MapInfo Pro and the columns in those tables had been indexed, then it would be highly likely that you’ve seen a good amount of these progress bars. They might be good for giving you time to think about what to have for lunch but not so good for your productivity (or stress levels if working to a deadline).
With the version 15 release of MapInfo Pro, Pitney Bowes introduced Smart Indexing which has dramatically improved the performance of many spatial and non-spatial operations in MapInfo Pro. And by dramatic, we mean the improvements in processing time can be in excess of 99% of the previous processing time and that’s significant by anyone’s measuring stick.
While those who know MapInfo well will say that this functionality has existed for a while, it has only been accessible while using MapBasic either in standalone tools or within the MapBasic window. And for Joe User, they firstly had to remember to had to open up the MapBasic window and secondly they had to remember the statement required to drop and then add the indices to the table in addition to the command they are typing in.
With Smart Indexing, users get access to these dramatic performance improvements if they are completing operations through the user interface or through the MapBasic window without having to remember any extra commands.
What are Indexes and why are they needed?
Some of you may be wondering what Indexes are and what they do.
At a basic level, you will want to create indexes for columns in tables where the tables have a large amount of records and you will query or search for records in the columns within these tables.
Indexing provides for faster searching and querying. However, previously if indexes were applied to any column in a table and you ran an operation, like combining map objects for example, MapInfo would continuously rebuild the indexes, slowing down the operation undertaken.
Smart Indexing uses an algorithm to determine if it is needed based on the number of records being updated and then, if needed, drops and then recreates the indexes at the start and the end of the operations resulting in some quite phenomenal performance improvements.
So how fast are we talking?
Example 1: Combining objects
In this example we combined 123,187 polygon objects (land parcels) into a single object. With Smart Indexing on, the operation took 26 seconds to complete versus 34 minutes to complete without Smart Indexing. A reduction in processing time of 98.7%
Example 2: Updating a column (123,187 records)
In this example we updated a column with the values that already existed in two other columns (Update parcels set Col3 = Col1 + “ “ +Col2). With Smart Indexing on, the operation took 9 seconds to complete versus 14 minutes to complete without Smart Indexing. A reduction in processing time of 99%!
Example 3: Updating a column (232,224 records)
In this example we decided to put the screws on and run the same test as example 2 above but this time with 232,224 records. With Smart Indexing on, the operation took 16 seconds to complete. After 30 minutes without Smart Indexing the operation was cancelled as only about 10% of the operation had been completed. Some rough extrapolation would have this operation complete without Smart Indexing in about 5 hours.
Note: There is such a significant difference in time that the speed to complete the task with Smart indexing is not showing in this chart.
We need to note that these tests were done on just one (average) PC and due to variances in PC specifications, the type of data being updated, the operation being undertaken and a whole lot of other variables you can expect that your performance would be better or worse than the examples noted above. What they do show is the potential for some significant improvement in the time to complete some operations.
The examples focused on two operations but you can expect performance improvements in all of the following operations – Update Column, Append, Delete, Combine Objects, Split objects, Erase Objects, Erase Outside, Line Split and Disaggregate where there are indexed columns.
Enabled by default in v15 onwards
Smart Indexing is now a default addition to all MapInfo Pro releases from version 15 onwards so you won't have to do anything additional to access these performance enhancements. This even applies to those who like using the MapBasic window to enter commands like updating a column; just entering the existing syntax for that command will access the Smart Indexing capability.