In this article, I'll give you a number of tips for boosting the performance of your MapBasic application. This will be tips for making your queries faster, your updates faster and how to avoid screen redraws.
This article is an updated version of a similar article I posted on LinkedIn four years ago.
Faster Queries
There are a few very easy things you can do to improve the queries and then there are some that might need a bit more consideration.
The NoSelect keyword
This is probably one of the most overlooked features of the SQL Select statement when running these from MapBasic.
Select * From sTableName
Where aCol = "0"
Into __TO__UPDATE NoSelect
The NoSelect keyword tells MapInfo Pro not to highlight the selected records. This has an effect on the speed but it also makes sure that your application doesn't interfere with the user's current Selection.
It also has another side effect. To use the NoSelect keyword: you need to name your query. This minimizes the chance of cluttering the temp folder with thousands of temp files as you probably will use the same query name over and over again - certainly with a loop.
To me, there is only one reason why you don't want to use the NoSelect keyword: You need the Selection. You might need the selection as input to a MapBasic statement that works with the selection or you might want to present the Selection to the user.
The Hide keyword
MapInfo Pro v17.0 brought another useful keyword to the Select statement: Hide.
This keyword allows you to create query tables that aren't visible to the end-user. By hiding the table from the end-user, it also has another effect: the Table List isn't constantly updated during a loop that creates numerous query tables that the end-user doesn't need to worry about.
Select * From sTableName
Where aCol = "0"
Into __TO__UPDATE NoSelect Hide
As a MapBasic programmer, you do however need to be careful when using the Hide keyword. When you are testing your application, you cannot see if you have forgotten to close these temporary queries as they aren't visible in the user interface.
The Hide keyword is also supported when opening a table through the Open Table statement. This gives you the ability to open tables through your application that the end-user can't see.
Index
Another feature that can improve query performance is the index. If you often are looking for specific values in a column, it can be a good idea to create an index on the column.
The index makes it easier and faster for MapInfo Pro to find specific values. I normally compare the index of a table with the index of a book. Imagine that a book had no index and you had to look for a certain word. In that case, you need to go thru all the pages. With the index, you know exactly what pages to look at.
Now the index doesn't come for free. The price is that it has to be maintained. Every time you update a value in an indexed column, insert a new record or delete a record the index needs to reflect this change.
MapInfo Pro 15.0 comes with smart indexing which calculates the best way to deal with the index updating. It will figure out whether it's best to update the index or to drop it and recreate it when the change to the table has been done.
Prior to MapInfo Pro 15.0, you need to consider this yourself. In many cases, it might be better to drop the index before updating, deleting or inserting into the table and then create the index again afterward.
Thru MapBasic, you need to specify if you want to use SmartIndex or not. You control this thru the DropIndex option:
Update sTableName
Set AREA = CartesianArea(OBJ, "sq m")
Where AREA = 0
DropIndex Auto
From the MapBasic Help:
Specifying that the DropIndex clause is On suspends updating transaction indexes while executing the operation and recreates them when the operation is complete. In some cases, this significantly improves performance, but not always. For the best performance, we recommend setting this clause to Auto, so that MapInfo Pro decides whether or not to suspend and recreate indexes. MapInfo Pro will suspend and recreate indexes later if the operation is deleting or updating the rows and the table contains more than 1000 rows and more than 0.1% of the total rows are getting updated. When the DropIndex clause is Off the operation executes without suspending and recreating the transaction indexes.
This means that DropIndex On is similar to manually dropping the Index and recreating it after you have updated, deleted or inserted records.
Faster Updates
Faster Updates don't only refer to updating existing records. It also refers to deleting and adding new records.
Index
We did cover how dropping and recreating an index can improve performance under queries, so I'll not mention this again. I'll just highlight that this really can make a huge difference to the performance.
FastEdit
You can also set your table in a state where changes are written directly to the table and not to transaction files. This can also be combined with turning off the user's option to undo changes:
Set Table sTableName FastEdit On Undo Off
This is a very efficient way to speed up update, insert and delete statements. The drawback is that there is no way to rollback or undo the changes. So make sure your MapBasic application does the right thing to the data.
Normally when you start editing a table, MapInfo Pro creates temporary transaction files. These files have two purposes: One, they let other MapInfo Pros know that the table is currently being edited. Two, they make it possible to undo all changes to a table since you saved your changes the last time.
The transaction files are generally speaking a copy of the existing files, the DAT, the MAP, and the ID and maybe also the IND file. Now if your table is huge, it takes time to write these files to disk when you start editing the table. And it will afterward take time to save the changes to the table.
If you set the table in FastEdit mode before editing it, MapInfo Pro will not create these temporary files and it will therefore not spend time on writing them to disk. It will only create a single temporary transaction file telling other instances of MapInfo Pro that the table is currently being edited.
As soon as you commit the changes to your table, MapInfo Pro will take the table out of its FastEdit mode.
Custom functions
This actually has enough content for a full article in itself, but I just wanted briefly to talk a bit about using custom functions in Update statements.
You'll find that updating tables with custom MapBasic functions can be a lot faster than updating the same table thru a loop that actually does the same thing:
Update Selection Set OBJ = STLAlterPen(OBJ, newPen)
When using an Update statement, MapInfo Pro handles the looping and this really speeds up things compared to you building the loop via Fetch statements.
The idea here is to pass values from the table to the custom function, let the custom function modify or calculate a value and then return this to be written back to the current record in the table.
Avoiding screen redraws
MapBasic has a number of statements that can avoid screen redraws and progressbars from appearing. Sometimes these can be helpful as they will prevent the screen from being updated while updating a table.
Map redraws
You have a couple of statements for disabling the map redraw.
Set Map Redraw Off
The "light" (Set Map Redraw Off) one should disable map redraws, but I have seen that it doesn't always work as effectively as you might want.
Set Map Redraw Suspended
The heavier version (Set Map Redraw Suspended) that was added with MapInfo Pro 10.0 turns of the map display entirely and shows a text on the map telling the user that map redraw has been suspended. That's quite effective.
Set Map Redraw On
Remember to turn the map redraws back on when your process has finished.
Table ReDraw
With MapInfo Pro v16, we added a new option to prevent window redraws. This one works a lot better than Set Map Redraw because it is related to a specific table.
The idea is that you set the table in a certain state before updating it. This is quite similar to the way you would use the FastEdit mode.
Set Table sTable ReDraw Off
When you are done updating the table, you need to turn back on the redraw for the table
Set Table sTable ReDraw On
This is also easier than having to handle potential numerous maps as you here refer to the specific table that you are updating.
Event Processing
You can also during a certain process in your application turn of the event processing which will prevent MapInfo Pro from redrawing the map windows.
Set Event Processing Off
The Set Event Processing statement has a similar effect as the Set Map Redraw statement but it will have an effect on all map windows where Set Map Redraw only with have an effect on the specific map window.
Set Event Processing On
Do remember to turn the event processing back on afterward.
Progressbars
A number of MapBasic statements will show a Progressbar dialog on the screen giving the user an idea of how the process is moving along. While this in some situations can be quite helpful, in other situations it can result in a lot of progressbar dialogs being displayed for a brief moment and then disappearing again.
Also if your application has it's own progressbar, having these other progressbar dialogs appear might be confusing for the user.
Set Progressbars Off
You can turn on and off these progressbars using the Set Progressbars statement.
Set Progressbars On
As with the statements above, remember to turn on the progressbars again.
The Hide keyword
I already covered the Hide keyword under Faster Queries so I'll not go into details again here. This is just to remind you that you can use the Hide keyword via the Select and the Open Table statements to avoid showing queries and tables in the user interface.
----------------
I hope you find some of these tips useful and that they will improve the performance of your tool. If you have some other useful tips, feel free to share them via the comments below.
I also want to thank my colleague from "down under" @Ashley Crane for the idea for the original article and for sharing some of these tips with me.
If you have come across other ways to improve the performance of your MapBasic application, do share these with the community.
------------------------------
Peter Horsbøll Møller
Distinguished Engineer, Technical Ladder
Pitney Bowes Software & Data
------------------------------