Happy #MapInfoMonday and today also happy #MapBasicMonday.
I decided to write this #MapBasicMonday post as I got a question from @Richard Robinson from Yorkshire Water.
Richard reached out as I wanted to optimize his current process of updating around 19 columns on two tables with values based on the current value in the columns. There were more than 60 different current values.
His current method had been optimized a bit by looping over the columns. But he was still running 60+ Select and Update statements for each column.
I have simplified his problem a bit for this article.
I have two tables: Components and Pipes.
The table Components has 2 columns: StatusC, LocationC.
The table Pipes has 3 columns: StatusC, DimensionC, and LocationC.
The existing values could be one of these 5 values: 1, 2, 3, 4, or 5. All strings.
They were to be replaced with these values: C+, C1, C2, C3, and C-. Keep in mind these values are made up.
Let's have a look at the options we have.
Looping over the Tables and Columns
As we have many tables and columns to loop, we will create two arrays to hold these. This allows us to build a loop over these arrays when we do the updates.
Dim arrTables(5), arrColumns(5) As String
Dim i As Integer
i = i + 1
arrTables(i) = "Components"
arrColumns(i) = "StatusC"
i = i + 1
arrTables(i) = "Components"
arrColumns(i) = "LocationC"
i = i + 1
arrTables(i) = "Pipes"
arrColumns(i) = "StatusC"
i = i + 1
arrTables(i) = "Pipes"
arrColumns(i) = "DimensionC"
i = i + 1
arrTables(i) = "Pipes"
arrColumns(i) = "LocationC"
As you can see I created two fixed-sized array variables, arrTables and arrColumns, to hold the table and column names and I then assigned the names of the tables and columns to these.
Using these variables, I can now build a loop structure allowing me to loop over these table and column pairs
For i = 1 To Ubound(arrTables)
'Here's goes the logic
Next
Now we need to fill in the logic to do the updates.
Using Select and Update
This is the typical process that many of us have been using for decades. We start by selecting the records that meet the condition into a temporary query table. Now we can run an update statement on this query.
For one of the columns and value above, this could look like this:
For i = 1 To Ubound(arrTables)
Run Command "Select " + arrColumns(i) + " From " + arrTables(i) + " Where " + arrColumns(i) + " = ""1"" Into __TEMP NoSelect Hide"
Update __TEMP Set COL1 = "C+"
Run Command "Select " + arrColumns(i) + " From " + arrTables(i) + " Where " + arrColumns(i) + " = ""2"" Into __TEMP NoSelect Hide"
Update __TEMP Set COL1 = "C1"
Run Command "Select " + arrColumns(i) + " From " + arrTables(i) + " Where " + arrColumns(i) + " = ""3"" Into __TEMP NoSelect Hide"
Update __TEMP Set COL1 = "C2"
Run Command "Select " + arrColumns(i) + " From " + arrTables(i) + " Where " + arrColumns(i) + " = ""4"" Into __TEMP NoSelect Hide"
Update __TEMP Set COL1 = "C3"
Run Command "Select " + arrColumns(i) + " From " + arrTables(i) + " Where " + arrColumns(i) + " = ""5"" Into __TEMP NoSelect Hide"
Update __TEMP Set COL1 = "C-"
Next
As you can see this results in several statements inside the loop. Two for each value pair we need to find and change.
As the commands a dynamic, I have chosen to use a Run Command statement to run a string holding the query. In this way can I change the table and column names. The Update statement will always update the same table and column as I have controlled this using the Select statement
Using Update with a Where Clause
With MapInfo Pro v2019, we added a long-time wish from the user community to extend the Where condition on the Update statement. Until then, you could only use the Where part of the Update statement to specify a single record using the ROWID.
But now you can also build other conditions. We can use this to replace the two statements above per value pair with a single statement per value pair.
For i = 1 To Ubound(arrTables)
Run Command "Update " + arrTables(i) + " Set " + arrColumns(i) + " = ""C+"" Where " + arrColumns(i) + " = ""1"""
Run Command "Update " + arrTables(i) + " Set " + arrColumns(i) + " = ""C1"" Where " + arrColumns(i) + " = ""2"""
Run Command "Update " + arrTables(i) + " Set " + arrColumns(i) + " = ""C2"" Where " + arrColumns(i) + " = ""3"""
Run Command "Update " + arrTables(i) + " Set " + arrColumns(i) + " = ""C3"" Where " + arrColumns(i) + " = ""4"""
Run Command "Update " + arrTables(i) + " Set " + arrColumns(i) + " = ""C-"" Where " + arrColumns(i) + " = ""5"""
Next
As you can see, I have cut down the number of statements by half. The code will be easier to read and change but performance-wise, there may not be much change.
Using a Function to Get the New Value
A very performance solution is to use a function in a Update statement. This way you can move some of the logic into the function. In our specific case, we will move the logic of which value to use for an existing value into the function.
Here's the logic of the function
Function ReplaceInitialColumnValue(ByVal sInitialValue As String) As String
Dim sNewValue As String
ReplaceInitialColumnValue = sInitialValue
sNewValue = sInitialValue
Do Case sInitialValue
Case "1"
sNewValue = "C+"
Case "2"
sNewValue = "C1"
Case "3"
sNewValue = "C2"
Case "4"
sNewValue = "C3"
Case "5"
sNewValue = "C-"
End Case
ReplaceInitialColumnValue = sNewValue
End Function
The function can now be called with a value and will return the matching new value.
Now that we have moved the handling of the different values into the function, we can simplify the update statements into a single statement.
For i = 1 To Ubound(arrTables)
aCol = arrTables(i) + "." + arrColumns(i)
Update arrTables(i) Set arrColumns(i) = ReplaceInitialColumnValue(aCol)
Next
I have used a Alias variable (aCol) to pass over the existing value to the function.
Also note that as the function now handles the logic behind replacing one value with another, this has been removed from the part of the code. So instead of doing 5 Update statements, I can limit it to one per table/column pair. This will perform much better than before.
Running all Update in a single Statement per Table
Now that we have moved the complexity of the values into a function, it is also worth revisiting how we do the updates. Because I only have a handful of columns to update, I can hardcode these using two Update statements, one for each table.
Update Components
SET StatusC = ReplaceInitialColumnValue(StatusC)
, LocationC= ReplaceInitialColumnValue(LocationC)
Update Pipes
SET StatusC = ReplaceInitialColumnValue(StatusC)
, DimensionC= ReplaceInitialColumnValue(DimensionC)
, LocationC= ReplaceInitialColumnValue(LocationC)
You can update multiple columns in one Update statement as seen above. Make sure to separate the individual columns with a comma, as seen above.
This is much easier to read than the structure above where I loop over a list of tables and columns and update one column at a time.
And performance-wise, this approach is just as fast, maybe even slightly faster.
Hopefully, this gives you some ideas on how to structure your core when doing multiple updates on multiple tables.
In the specific case that Richard had, we cut down the processing time to a third using one of the last two methods.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------