MapInfo Pro

I manage several large vector layers but also use QGIS, so made the decision to change them all to gpkg files as Q seems to damage my tab files sometimes, and the theory is gpkg files work properly with both. The layers are edited both manually and using a bespoke MapBasic tool for various processes. The tool code runs to around 11000 lines so I'm not rewriting it in Python (I'm not really a programmer so the prospect makes me shudder, and I don't have the time anyway). The files vary in size from less than 1 MB to about 7 or 8 MB, and contain between 20 and 5000 polygons. Each attribute row has 104 columns of data, mostly character strings, a few dates, and a few float integers.

After saving new versions of the files in the gpkg format, I had to run a process to extract a value based on other values within the same attribute using a Do Loop. The smaller datasets were fine and the process runs through in seconds (each attribute taking fractions of a second), but the larger ones now take around 20 or 30 seconds per attribute. I have also noticed other parts of the MI tool have a similar or even longer delay. These are actions that took less than a second in the tab format.

I also can't modify the structure.

Any insights would be very welcome.

Steve

------------------------------
Steve Oram
Biodiversity Officer
PTES
London
------------------------------

Hi Steve

>> I had to run a process to extract a value based on other values within the same attribute using a Do Loop

Would you be able to use SQL to do this?

Can you share your code for the process - just the Do Loop?
I don't have anything specific in mind but was wondering if seeing your loop could spark some ideas of why it is getting slow.

Maybe if you are fetching a specific record number instead of just fetching the next record can make the process slower. Not sure.

Are you updating the dataset through this loop too?

Thanks

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

Original Message:
Sent: 11-05-2022 07:30
From: Steve Oram
Subject: Geopackage problems

I manage several large vector layers but also use QGIS, so made the decision to change them all to gpkg files as Q seems to damage my tab files sometimes, and the theory is gpkg files work properly with both. The layers are edited both manually and using a bespoke MapBasic tool for various processes. The tool code runs to around 11000 lines so I'm not rewriting it in Python (I'm not really a programmer so the prospect makes me shudder, and I don't have the time anyway). The files vary in size from less than 1 MB to about 7 or 8 MB, and contain between 20 and 5000 polygons. Each attribute row has 104 columns of data, mostly character strings, a few dates, and a few float integers.

After saving new versions of the files in the gpkg format, I had to run a process to extract a value based on other values within the same attribute using a Do Loop. The smaller datasets were fine and the process runs through in seconds (each attribute taking fractions of a second), but the larger ones now take around 20 or 30 seconds per attribute. I have also noticed other parts of the MI tool have a similar or even longer delay. These are actions that took less than a second in the tab format.

I also can't modify the structure.

Any insights would be very welcome.

Steve

------------------------------
Steve Oram
Biodiversity Officer
PTES
London
------------------------------

It's quite long, and please bear in mind that I don't claim to be a programmer so expect ugly. I couldn't upload the .mb so pasting below:

Sub ReHabCon

Dim aSite, aPridet, aInterQual, aHCond, aS1Date, aS2Date, aS3Date, aS4Date, aStatus1, aStatus2, aS1Done, aS2Done, aS4Done, aP1, aP2, aStew, aNonTO, aNotes, aScrub, aSDW, aDWF, aHol, aCav, aOT1, aOT2, aOT3, aOT4, aYT1, aYT2, aYT3, aYT4, aG, aM, aU, aGD As Alias
Dim sSite, sPridet, sInterQual, sHCond, sStatus1, sStatus2, sS1Done, sS2Done, sP1, sP2, sStew, sNonTO, sNotes, sScrub, sSDW, sDWF, sHol, sCav, sOT1, sOT2, sOT3, sOT4, sYT1, sYT2, sYT3, sYT4, sG, sM, sU, sGD As String
Dim iRowID, iRows, iHabCon, iS1Date, iS2Date, iS3Date, iS4Date, iToday, iP2Blank, iLevelUp, iYOT, iDataAge As Integer

iRowID = 0
iLevelUp = 0
select * from sTableName order by Incid into Sort
Select * from Sort
iRows = SelectionInfo(SEL_INFO_NROWS)
Run Menu Command 304

Do While iRowID < iRows
Print "_______"
iRowID = iRowID+1
Select * from Sort Where RowID = iRowID
Print iRowID
iHabCon = 0
iP2Blank = 0
sSite = ""
sHCond = ""
sPridet = ""
sHabCon = ""
sStatus1 = ""
sStatus2 = ""
sS1Done = ""
sS2Done = ""
dToday = 0
iS1Date = 0
iS2Date = 0
iS3Date = 0
iS4Date = 0
iToday = 0
sP1 = ""
sP2 = ""
sStew = ""
sNonTO = ""
sNotes = ""
sScrub = ""
sSDW = ""
sDWF = ""
sHol = ""
sCav = ""
sOT1 = ""
sOT2 = ""
sOT3 = ""
sOT4 = ""
sYT1 = ""
sYT2 = ""
sYT3 = ""
sYT4 = ""
sG = ""
sM = ""
sU = ""

aSite = Sort+"."+ColumnInfo(Sort, "Col1", COL_INFO_NAME) 'Site name
aInterQual = Sort+"."+ColumnInfo(Sort, "Col5", COL_INFO_NAME) 'InterQual
aHCond = Sort+"."+ColumnInfo(Sort, "Col6", COL_INFO_NAME) 'Habitat Condition
aS1Done = Sort+"."+ColumnInfo(Sort, "Col7", COL_INFO_NAME) 'S1 survey
aS2Done = Sort+"."+ColumnInfo(Sort, "Col12", COL_INFO_NAME) 'S2 OOQ
aStatus1 = Sort+"."+ColumnInfo(Sort, "Col11", COL_INFO_NAME) 'StatusS1
aP1 = Sort+"."+ColumnInfo(Sort, "Col11", COL_INFO_NAME) 'PlantingS1
aStatus2 = Sort+"."+ColumnInfo(Sort, "Col15", COL_INFO_NAME) 'StatusS2
aP2 = Sort+"."+ColumnInfo(Sort, "Col16", COL_INFO_NAME) 'PlantingS2
aS1Date = Sort+"."+ColumnInfo(Sort, "Col8", COL_INFO_NAME) 'S1 date to Alias
aS2Date = Sort+"."+ColumnInfo(Sort, "Col13", COL_INFO_NAME) 'S2 date to Alias
aS3Date = Sort+"."+ColumnInfo(Sort, "Col17", COL_INFO_NAME) 'S3 date to Alias
aS4Date = Sort+"."+ColumnInfo(Sort, "Col24", COL_INFO_NAME) 'S4 date to Alias
aStew = Sort+"."+ColumnInfo(Sort, "Col33", COL_INFO_NAME) 'Agri-environment
aNonTO = Sort+"."+ColumnInfo(Sort, "Col39", COL_INFO_NAME) 'Marginal
aNotes = Sort+"."+ColumnInfo(Sort, "Col42", COL_INFO_NAME) 'No visibility
aScrub = Sort+"."+ColumnInfo(Sort, "Col67", COL_INFO_NAME) 'Scrub
aSDW = Sort+"."+ColumnInfo(Sort, "Col81", COL_INFO_NAME) 'StandingDeadwood
aDWF = Sort+"."+ColumnInfo(Sort, "Col82", COL_INFO_NAME) 'DeadwoodFloor
aHol = Sort+"."+ColumnInfo(Sort, "Col78", COL_INFO_NAME) 'Branch holes
aCav = Sort+"."+ColumnInfo(Sort, "Col80", COL_INFO_NAME) 'Cavities
aOT1 = Sort+"."+ColumnInfo(Sort, "Col49", COL_INFO_NAME) 'OT 1-10
aOT2 = Sort+"."+ColumnInfo(Sort, "Col50", COL_INFO_NAME) 'OT 11-30
aOT3 = Sort+"."+ColumnInfo(Sort, "Col51", COL_INFO_NAME) 'OT31-100
aOT4 = Sort+"."+ColumnInfo(Sort, "Col52", COL_INFO_NAME) 'OT100+
aYT1 = Sort+"."+ColumnInfo(Sort, "Col53", COL_INFO_NAME) 'YT 1-10
aYT2 = Sort+"."+ColumnInfo(Sort, "Col54", COL_INFO_NAME) 'YT 11-30
aYT3 = Sort+"."+ColumnInfo(Sort, "Col55", COL_INFO_NAME) 'YT31-100
aYT4 = Sort+"."+ColumnInfo(Sort, "Col56", COL_INFO_NAME) 'YT100+
aG = Sort+"."+ColumnInfo(Sort, "Col68", COL_INFO_NAME) 'Grazed
aM = Sort+"."+ColumnInfo(Sort, "Col69", COL_INFO_NAME) 'Mown
aU = Sort+"."+ColumnInfo(Sort, "Col70", COL_INFO_NAME) 'Unmanaged
aGD = Sort+"."+ColumnInfo(Sort, "Col76", COL_INFO_NAME) 'GrazingDamage

sSite = aSite
sInterQual = aInterQual
sHCond = aHCond
sS1Done = aS1Done
sS2Done = aS2Done
sStatus1 = aStatus1
sStatus2 = aStatus2
sP1 = aP1
sP2 = aP2
iS1Date = aS1Date
iS2Date = aS2Date
iS3Date = aS3Date
iS4Date = aS4Date
sStew = aStew
sNonTO = aNonTO
sNotes = aNotes
sScrub = aScrub
sSDW = aSDW
sDWF = aDWF
sHol = aHol
sCav = aCav
sOT1 = aOT1
sOT2 = aOT2
sOT3 = aOT3
sOT4 = aOT4
sYT1 = aYT1
sYT2 = aYT2
sYT3 = aYT3
sYT4 = aYT4
sG = aG
sM = aM
sU = aU
sGD = aGD
iYT = 0
iOT = 0
iYOT = 0
'Print "iToday A = "+iToday

dToday = CurDate()-1825
iToday = dToday
'Print "iToday B = "+iToday
Print sSite
If iS1Date <> 0 And iS1Date > iToday Then Print "Top1" GoTo Update1
Elseif iS2Date <> 0 And iS2Date > iToday Then Print "Top2" GoTo Update1
Elseif iS3Date <> 0 And iS3Date > iToday Then Print "Top3" GoTo Update1
Elseif iS4Date <> 0 And iS4Date > iToday Then Print "Top4" GoTo Update1
End If

dToday = CurDate()-3650
iToday = dToday
'Print "iToday C = "+iToday

If iS1Date <> 0 And iS1Date > iToday Then Print "Mid1" GoTo Update2
Elseif iS2Date <> 0 And iS2Date > iToday Then Print "Mid2" GoTo Update2
Elseif iS3Date <> 0 And iS3Date > iToday Then Print "Mid3" GoTo Update2
Elseif iS4Date <> 0 And iS4Date > iToday Then Print "Mid4" GoTo Update2
ElseIf iS1Date <> 0 And iS1Date <= iToday Then Print "Low1" GoTo Update3
Elseif iS2Date <> 0 And iS2Date <= iToday Then Print "Low2" GoTo Update3
Elseif iS3Date <> 0 And iS3Date <= iToday Then Print "Low3" GoTo Update3
Elseif iS4Date <> 0 And iS4Date <= iToday Then Print "Low4" GoTo Update3
End If
Update1:
If sInterQual = "1 (High)" Then Update Selection Set PriDet = "Definitely is Traditional Orchard priority habitat"
GoTo ContinueSub
Else GoTo Update3
Update2:
Update Selection Set PriDet = "Probably Traditional Orchard priority habitat but some uncertainty due to age of data source"
GoTo ContinueSub
Update3:
Update Selection Set PriDet = "Priority Traditional Orchard habitat may be present but evidence is either insufficient to determine presence confidently or is in the oldest allowable category"
GoTo ContinueSub
End If

ContinueSub:
Update Selection Set Habdefver = "v2.6"
Update Selection Set Area_Ha = Area(obj, "hectare")


If sOT1 = "Y" Or sOT2 = "Y" Or sOT3 = "Y" Or sOT4 = "Y" Then iOT = 1
End If
Print "iOT = "+iOT
If sYT1 = "Y" Or sYT2 = "Y" Or sYT3 = "Y" Or sYT4 = "Y" Then iYT = 10
Print "iYT = "+iYT
End If
iYOT = iOT+iYT

If iYOT = 11 Then Update Selection set S2Planting = "Young trees in gaps"
End If

If sS2Done = "Done" Then
'Update Selection set PriDet = "Definitely is Traditional Orchard priority habitat"
If sP2 = "" Then
iP2Blank = 1
GoTo Try1 End If
If Like(sStatus2,"%Unmanaged%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sP2,"%No young%","") = 1 Then sHabCon = sHabCon+"C" End If
If Like(sP2,"%gaps","") = 1 Then sHabCon = sHabCon+"A" End If
If Like(sP2,"%Young orchar%","") = 1 Then sHabCon = sHabCon+"B" End If
If Like(sP2,"%Established%","") = 1 Then sHabCon = sHabCon+"L" End If


ElseIf sS1Done = "Done" Then
Try1:
If Like(sNotes,"%No visibil%","") = 1 Then
Update Selection Set HabCondition = "" Print "HabCon set to blank"
'If iP2Blank = 0 Then Update Selection Set PriDet = "Priority Traditional Orchard habitat may be present but evidence is either insufficient to determine presence confidently or is in the oldest allowable category"
'End If
GoTo Cont
Else
'Update Selection set PriDet = "Definitely is Traditional Orchard priority habitat"
If Like(sStatus1, "%Unmanaged%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sP1,"%No young%","") = 1 Then sHabCon = sHabCon+"C" End If
If Like(sP1,"%gaps%","") = 1 Then sHabCon = sHabCon+"A" End If
If Like(sP1,"%Young orchar%","") = 1 Then sHabCon = sHabCon+"B" End If
If Like(sP1,"%Established%","") = 1 Then sHabCon = sHabCon+"L" End If
End If

Else
Update Selection Set HabCondition = ""
GoTo Cont

End If

If Like(sNonTO,"Los%","") = 1 Then sHabCon = sHabCon+"H" End If
If Like(sNonTO,"Misident%","") = 1 Then sHabCon = sHabCon+"J" End If
If Like(sNonTO,"Relic%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sNonTO,"Abandoned%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sNonTO,"Intensively%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sSDW,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sDWF,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sHol,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sCav,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sG,"%Y%","") = 1 Then sHabCon = sHabCon+"D" End If
If Like(sM,"%Y%","") = 1 Then sHabCon = sHabCon+"E" End If
If Like(sGD,"%Y%","") = 1 Then sHabCon = sHabCon+"G" End If
If Like(sU,"%Y%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sScrub,"%D%","") = 1 Then sHabCon = sHabCon+"F" End If

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Habitat condition assessment

'Print "'"+sHabCon+"'"
If Like(sHabCon, "%H%"," ") = 1 Then iHabCon = iHabCon -1000000 End If 'H Lost
If Like(sHabCon, "%J%"," ") = 1 Then iHabCon = iHabCon -100000 End If 'J Misidentified
If Like(sHabCon, "%F%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'F Unmanaged/scrub
If Like(sHabCon, "%G%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'G Grazing damage
If Like(sHabCon, "%K%"," ") = 1 Then iHabCon = iHabCon -10000 End If 'K NonTO
If Like(sHabCon, "%A%"," ") = 1 Then iHabCon = iHabCon +600 End If 'A Gapped up (old and young trees)
If Like(sHabCon, "%L%"," ") = 1 Then iHabCon = iHabCon +500 End If 'L Established/mostly stocked
If Like(sHabCon, "%C%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'C No young trees
If Like(sHabCon, "%B%"," ") = 1 Then iHabCon = iHabCon +500 End If 'B Young orchard
If Like(sHabCon, "%I%"," ") = 1 Then iHabCon = iHabCon +500 End If 'I Deadwood canopy, cavities, floor
If Like(sHabCon, "%D%"," ") = 1 Then iHabCon = iHabCon +1 End If 'D Grazed
If Like(sHabCon, "%E%"," ") = 1 Then iHabCon = iHabCon +1 End If 'E Mown
Print iHabCon

If iHabCon >1100 Then
Update Selection Set HabCondition = "Excellent" Print "Excellent" sHCond = "Ex"
ElseIf iHabCon >=501 and iHabcon <=1100 Then
Update Selection Set HabCondition = "Good" Print "Good" sHCond = "Go"
ElseIf iHabCon <501 and iHabCon >-4000 Then
Update Selection Set HabCondition = "Poor" Print "Poor" sHCond = "Po"
ElseIf iHabCon <-90000 and iHabCon >-200000 Then Print "Misident"
Update Selection set PriDet = "Not applicable"
Insert Into Misidentified ( COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104) Select COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104 From Selection
Delete From Sort Where RowID = iRowID
ElseIf iHabCon <-900000 Then Print "Lost"
Update Selection Set HabCondition = ""
Update Selection set PriDet = "Not applicable"
Insert Into Lost ( COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104) Select COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104 From Selection
Delete From Sort Where RowID = iRowID

Else Update Selection Set HabCondition = "" sHCond = ""
End If
GoTo Cont

Cont:
'Print "Running Agrienv bit"
If sStew <> "" Then
If sHCond = "" Or sHCond = "Po" Then
Update Selection Set HabCondition = "Good" iLevelUp = iLevelUp +1 'Print sSite+" Agri-Env Level-up!"
End If
End If

'temp exit sub for analysis
'Exit sub

Loop
Print "Sites in stewardship fudged to Good = "+sTableName+", "+iLevelUp
'Commit Table sTableName Interactive
'Commit Table Lost Interactive
'Commit Table Misidentified Interactive

End Sub

------------------------------
Steve Oram
Biodiversity Officer
PTES
London
------------------------------

Original Message:
Sent: 11-07-2022 01:40
From: Peter Møller
Subject: Geopackage problems

Hi Steve

>> I had to run a process to extract a value based on other values within the same attribute using a Do Loop

Would you be able to use SQL to do this?

Can you share your code for the process - just the Do Loop?
I don't have anything specific in mind but was wondering if seeing your loop could spark some ideas of why it is getting slow.

Maybe if you are fetching a specific record number instead of just fetching the next record can make the process slower. Not sure.

Are you updating the dataset through this loop too?

Thanks

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

Original Message:
Sent: 11-05-2022 07:30
From: Steve Oram
Subject: Geopackage problems

I manage several large vector layers but also use QGIS, so made the decision to change them all to gpkg files as Q seems to damage my tab files sometimes, and the theory is gpkg files work properly with both. The layers are edited both manually and using a bespoke MapBasic tool for various processes. The tool code runs to around 11000 lines so I'm not rewriting it in Python (I'm not really a programmer so the prospect makes me shudder, and I don't have the time anyway). The files vary in size from less than 1 MB to about 7 or 8 MB, and contain between 20 and 5000 polygons. Each attribute row has 104 columns of data, mostly character strings, a few dates, and a few float integers.

After saving new versions of the files in the gpkg format, I had to run a process to extract a value based on other values within the same attribute using a Do Loop. The smaller datasets were fine and the process runs through in seconds (each attribute taking fractions of a second), but the larger ones now take around 20 or 30 seconds per attribute. I have also noticed other parts of the MI tool have a similar or even longer delay. These are actions that took less than a second in the tab format.

I also can't modify the structure.

Any insights would be very welcome.

Steve

------------------------------
Steve Oram
Biodiversity Officer
PTES
London
------------------------------

Hi Steve,

I had a look at your code. I had no problem reading it so it's not really bad.

I have tried modifying your code a bit in the attached version, see the attached zip file.

Was the switch to GeoPackage the only change you made or did you also maybe upgrade from 32-bit to 64-bit? The first suggestion below is probably one of the best suggestions for improving performance on add-in using Select statements.

Suggestions:

Use NoSelect and Hide with Select

When using Select statements, try to use NoSelect and Hide when possible. You rarely need the highlighting of the selection so using named query results is recommended. Hide was added to MapBasic with v2019.3.

Use this
Select * from sTableName order by Incid into Sort NoSelect Hide
instead of
Select * from sTableName order by Incid into Sort

Looping through a table

When looping through a table, use Do Until EOT in combination with Fetch First and Fetch Next.

In your code, you use Select to select the individual records. That results in a large number of queries that not always are needed. You can place the cursor on the record you want to read from without using the Select statement.

I have used this structure instead:
Fetch First From Sort
   Print "_______"
   ***Get the RowID from the current record
   iRowID = Sort.ROWID
   '**Here goes your logical for each row

   Fetch Next From Sort
Loop

This will also mean that when writing values back, you need to include a Where element on the Update statement:
Update Sort
   Set PriDet = "Probably Traditional Orchard priority habitat but some uncertainty due to age of data source"
   Where ROWID = iRowID

Set your Alias variables outside the loop

This is probably just a minor thing but by moving the assignment of your Alias variables outside your loop, you only set these once instead of for each record in the table.
You will still keep the assignment to your other variables using the Alias variable inside the loop.

Limit the number of Update statements

The syntax for Update allows you to update multiple columns in one statement
Use this:
Update Sort
   Set PriDet = "Definitely is Traditional Orchard priority habitat"
      , Habdefver = "v2.6"
      , Area_Ha = Area(obj, "hectare")
   Where ROWID = iRowID
Instead of this:
Update Sort Set PriDet = "Definitely is Traditional Orchard priority habitat" Where ROWID = iRowID
Update Sort Set Habdefver = "v2.6" Where ROWID = iRowID
Update Sort Set Area_Ha = Area(obj, "hectare") Where ROWID = iRowID

Removing Not Applicable records after the loop

It might be a good idea to move the deletion of the records that aren't applicable until after you have looped through all records.

At this point, you can select all these records by looking for the string "Not Applicable" in the column PriDet.

You can then copy all these records to the Lost table and then delete them from the original table at once.

My guess is that it will be the second recommendation that might slow down the performance. 
But make to test whatever change of mine you implement as I haven't been able to test this at all.

Let us know if this has any effect.

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

Original Message:
Sent: 11-07-2022 11:33
From: Steve Oram
Subject: Geopackage problems

It's quite long, and please bear in mind that I don't claim to be a programmer so expect ugly. I couldn't upload the .mb so pasting below:

Sub ReHabCon

Dim aSite, aPridet, aInterQual, aHCond, aS1Date, aS2Date, aS3Date, aS4Date, aStatus1, aStatus2, aS1Done, aS2Done, aS4Done, aP1, aP2, aStew, aNonTO, aNotes, aScrub, aSDW, aDWF, aHol, aCav, aOT1, aOT2, aOT3, aOT4, aYT1, aYT2, aYT3, aYT4, aG, aM, aU, aGD As Alias
Dim sSite, sPridet, sInterQual, sHCond, sStatus1, sStatus2, sS1Done, sS2Done, sP1, sP2, sStew, sNonTO, sNotes, sScrub, sSDW, sDWF, sHol, sCav, sOT1, sOT2, sOT3, sOT4, sYT1, sYT2, sYT3, sYT4, sG, sM, sU, sGD As String
Dim iRowID, iRows, iHabCon, iS1Date, iS2Date, iS3Date, iS4Date, iToday, iP2Blank, iLevelUp, iYOT, iDataAge As Integer

iRowID = 0
iLevelUp = 0
select * from sTableName order by Incid into Sort
Select * from Sort
iRows = SelectionInfo(SEL_INFO_NROWS)
Run Menu Command 304

Do While iRowID < iRows
Print "_______"
iRowID = iRowID+1
Select * from Sort Where RowID = iRowID
Print iRowID
iHabCon = 0
iP2Blank = 0
sSite = ""
sHCond = ""
sPridet = ""
sHabCon = ""
sStatus1 = ""
sStatus2 = ""
sS1Done = ""
sS2Done = ""
dToday = 0
iS1Date = 0
iS2Date = 0
iS3Date = 0
iS4Date = 0
iToday = 0
sP1 = ""
sP2 = ""
sStew = ""
sNonTO = ""
sNotes = ""
sScrub = ""
sSDW = ""
sDWF = ""
sHol = ""
sCav = ""
sOT1 = ""
sOT2 = ""
sOT3 = ""
sOT4 = ""
sYT1 = ""
sYT2 = ""
sYT3 = ""
sYT4 = ""
sG = ""
sM = ""
sU = ""

aSite = Sort+"."+ColumnInfo(Sort, "Col1", COL_INFO_NAME) 'Site name
aInterQual = Sort+"."+ColumnInfo(Sort, "Col5", COL_INFO_NAME) 'InterQual
aHCond = Sort+"."+ColumnInfo(Sort, "Col6", COL_INFO_NAME) 'Habitat Condition
aS1Done = Sort+"."+ColumnInfo(Sort, "Col7", COL_INFO_NAME) 'S1 survey
aS2Done = Sort+"."+ColumnInfo(Sort, "Col12", COL_INFO_NAME) 'S2 OOQ
aStatus1 = Sort+"."+ColumnInfo(Sort, "Col11", COL_INFO_NAME) 'StatusS1
aP1 = Sort+"."+ColumnInfo(Sort, "Col11", COL_INFO_NAME) 'PlantingS1
aStatus2 = Sort+"."+ColumnInfo(Sort, "Col15", COL_INFO_NAME) 'StatusS2
aP2 = Sort+"."+ColumnInfo(Sort, "Col16", COL_INFO_NAME) 'PlantingS2
aS1Date = Sort+"."+ColumnInfo(Sort, "Col8", COL_INFO_NAME) 'S1 date to Alias
aS2Date = Sort+"."+ColumnInfo(Sort, "Col13", COL_INFO_NAME) 'S2 date to Alias
aS3Date = Sort+"."+ColumnInfo(Sort, "Col17", COL_INFO_NAME) 'S3 date to Alias
aS4Date = Sort+"."+ColumnInfo(Sort, "Col24", COL_INFO_NAME) 'S4 date to Alias
aStew = Sort+"."+ColumnInfo(Sort, "Col33", COL_INFO_NAME) 'Agri-environment
aNonTO = Sort+"."+ColumnInfo(Sort, "Col39", COL_INFO_NAME) 'Marginal
aNotes = Sort+"."+ColumnInfo(Sort, "Col42", COL_INFO_NAME) 'No visibility
aScrub = Sort+"."+ColumnInfo(Sort, "Col67", COL_INFO_NAME) 'Scrub
aSDW = Sort+"."+ColumnInfo(Sort, "Col81", COL_INFO_NAME) 'StandingDeadwood
aDWF = Sort+"."+ColumnInfo(Sort, "Col82", COL_INFO_NAME) 'DeadwoodFloor
aHol = Sort+"."+ColumnInfo(Sort, "Col78", COL_INFO_NAME) 'Branch holes
aCav = Sort+"."+ColumnInfo(Sort, "Col80", COL_INFO_NAME) 'Cavities
aOT1 = Sort+"."+ColumnInfo(Sort, "Col49", COL_INFO_NAME) 'OT 1-10
aOT2 = Sort+"."+ColumnInfo(Sort, "Col50", COL_INFO_NAME) 'OT 11-30
aOT3 = Sort+"."+ColumnInfo(Sort, "Col51", COL_INFO_NAME) 'OT31-100
aOT4 = Sort+"."+ColumnInfo(Sort, "Col52", COL_INFO_NAME) 'OT100+
aYT1 = Sort+"."+ColumnInfo(Sort, "Col53", COL_INFO_NAME) 'YT 1-10
aYT2 = Sort+"."+ColumnInfo(Sort, "Col54", COL_INFO_NAME) 'YT 11-30
aYT3 = Sort+"."+ColumnInfo(Sort, "Col55", COL_INFO_NAME) 'YT31-100
aYT4 = Sort+"."+ColumnInfo(Sort, "Col56", COL_INFO_NAME) 'YT100+
aG = Sort+"."+ColumnInfo(Sort, "Col68", COL_INFO_NAME) 'Grazed
aM = Sort+"."+ColumnInfo(Sort, "Col69", COL_INFO_NAME) 'Mown
aU = Sort+"."+ColumnInfo(Sort, "Col70", COL_INFO_NAME) 'Unmanaged
aGD = Sort+"."+ColumnInfo(Sort, "Col76", COL_INFO_NAME) 'GrazingDamage

sSite = aSite
sInterQual = aInterQual
sHCond = aHCond
sS1Done = aS1Done
sS2Done = aS2Done
sStatus1 = aStatus1
sStatus2 = aStatus2
sP1 = aP1
sP2 = aP2
iS1Date = aS1Date
iS2Date = aS2Date
iS3Date = aS3Date
iS4Date = aS4Date
sStew = aStew
sNonTO = aNonTO
sNotes = aNotes
sScrub = aScrub
sSDW = aSDW
sDWF = aDWF
sHol = aHol
sCav = aCav
sOT1 = aOT1
sOT2 = aOT2
sOT3 = aOT3
sOT4 = aOT4
sYT1 = aYT1
sYT2 = aYT2
sYT3 = aYT3
sYT4 = aYT4
sG = aG
sM = aM
sU = aU
sGD = aGD
iYT = 0
iOT = 0
iYOT = 0
'Print "iToday A = "+iToday

dToday = CurDate()-1825
iToday = dToday
'Print "iToday B = "+iToday
Print sSite
If iS1Date <> 0 And iS1Date > iToday Then Print "Top1" GoTo Update1
Elseif iS2Date <> 0 And iS2Date > iToday Then Print "Top2" GoTo Update1
Elseif iS3Date <> 0 And iS3Date > iToday Then Print "Top3" GoTo Update1
Elseif iS4Date <> 0 And iS4Date > iToday Then Print "Top4" GoTo Update1
End If

dToday = CurDate()-3650
iToday = dToday
'Print "iToday C = "+iToday

If iS1Date <> 0 And iS1Date > iToday Then Print "Mid1" GoTo Update2
Elseif iS2Date <> 0 And iS2Date > iToday Then Print "Mid2" GoTo Update2
Elseif iS3Date <> 0 And iS3Date > iToday Then Print "Mid3" GoTo Update2
Elseif iS4Date <> 0 And iS4Date > iToday Then Print "Mid4" GoTo Update2
ElseIf iS1Date <> 0 And iS1Date <= iToday Then Print "Low1" GoTo Update3
Elseif iS2Date <> 0 And iS2Date <= iToday Then Print "Low2" GoTo Update3
Elseif iS3Date <> 0 And iS3Date <= iToday Then Print "Low3" GoTo Update3
Elseif iS4Date <> 0 And iS4Date <= iToday Then Print "Low4" GoTo Update3
End If
Update1:
If sInterQual = "1 (High)" Then Update Selection Set PriDet = "Definitely is Traditional Orchard priority habitat"
GoTo ContinueSub
Else GoTo Update3
Update2:
Update Selection Set PriDet = "Probably Traditional Orchard priority habitat but some uncertainty due to age of data source"
GoTo ContinueSub
Update3:
Update Selection Set PriDet = "Priority Traditional Orchard habitat may be present but evidence is either insufficient to determine presence confidently or is in the oldest allowable category"
GoTo ContinueSub
End If

ContinueSub:
Update Selection Set Habdefver = "v2.6"
Update Selection Set Area_Ha = Area(obj, "hectare")


If sOT1 = "Y" Or sOT2 = "Y" Or sOT3 = "Y" Or sOT4 = "Y" Then iOT = 1
End If
Print "iOT = "+iOT
If sYT1 = "Y" Or sYT2 = "Y" Or sYT3 = "Y" Or sYT4 = "Y" Then iYT = 10
Print "iYT = "+iYT
End If
iYOT = iOT+iYT

If iYOT = 11 Then Update Selection set S2Planting = "Young trees in gaps"
End If

If sS2Done = "Done" Then
'Update Selection set PriDet = "Definitely is Traditional Orchard priority habitat"
If sP2 = "" Then
iP2Blank = 1
GoTo Try1 End If
If Like(sStatus2,"%Unmanaged%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sP2,"%No young%","") = 1 Then sHabCon = sHabCon+"C" End If
If Like(sP2,"%gaps","") = 1 Then sHabCon = sHabCon+"A" End If
If Like(sP2,"%Young orchar%","") = 1 Then sHabCon = sHabCon+"B" End If
If Like(sP2,"%Established%","") = 1 Then sHabCon = sHabCon+"L" End If


ElseIf sS1Done = "Done" Then
Try1:
If Like(sNotes,"%No visibil%","") = 1 Then
Update Selection Set HabCondition = "" Print "HabCon set to blank"
'If iP2Blank = 0 Then Update Selection Set PriDet = "Priority Traditional Orchard habitat may be present but evidence is either insufficient to determine presence confidently or is in the oldest allowable category"
'End If
GoTo Cont
Else
'Update Selection set PriDet = "Definitely is Traditional Orchard priority habitat"
If Like(sStatus1, "%Unmanaged%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sP1,"%No young%","") = 1 Then sHabCon = sHabCon+"C" End If
If Like(sP1,"%gaps%","") = 1 Then sHabCon = sHabCon+"A" End If
If Like(sP1,"%Young orchar%","") = 1 Then sHabCon = sHabCon+"B" End If
If Like(sP1,"%Established%","") = 1 Then sHabCon = sHabCon+"L" End If
End If

Else
Update Selection Set HabCondition = ""
GoTo Cont

End If

If Like(sNonTO,"Los%","") = 1 Then sHabCon = sHabCon+"H" End If
If Like(sNonTO,"Misident%","") = 1 Then sHabCon = sHabCon+"J" End If
If Like(sNonTO,"Relic%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sNonTO,"Abandoned%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sNonTO,"Intensively%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sSDW,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sDWF,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sHol,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sCav,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sG,"%Y%","") = 1 Then sHabCon = sHabCon+"D" End If
If Like(sM,"%Y%","") = 1 Then sHabCon = sHabCon+"E" End If
If Like(sGD,"%Y%","") = 1 Then sHabCon = sHabCon+"G" End If
If Like(sU,"%Y%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sScrub,"%D%","") = 1 Then sHabCon = sHabCon+"F" End If

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Habitat condition assessment

'Print "'"+sHabCon+"'"
If Like(sHabCon, "%H%"," ") = 1 Then iHabCon = iHabCon -1000000 End If 'H Lost
If Like(sHabCon, "%J%"," ") = 1 Then iHabCon = iHabCon -100000 End If 'J Misidentified
If Like(sHabCon, "%F%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'F Unmanaged/scrub
If Like(sHabCon, "%G%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'G Grazing damage
If Like(sHabCon, "%K%"," ") = 1 Then iHabCon = iHabCon -10000 End If 'K NonTO
If Like(sHabCon, "%A%"," ") = 1 Then iHabCon = iHabCon +600 End If 'A Gapped up (old and young trees)
If Like(sHabCon, "%L%"," ") = 1 Then iHabCon = iHabCon +500 End If 'L Established/mostly stocked
If Like(sHabCon, "%C%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'C No young trees
If Like(sHabCon, "%B%"," ") = 1 Then iHabCon = iHabCon +500 End If 'B Young orchard
If Like(sHabCon, "%I%"," ") = 1 Then iHabCon = iHabCon +500 End If 'I Deadwood canopy, cavities, floor
If Like(sHabCon, "%D%"," ") = 1 Then iHabCon = iHabCon +1 End If 'D Grazed
If Like(sHabCon, "%E%"," ") = 1 Then iHabCon = iHabCon +1 End If 'E Mown
Print iHabCon

If iHabCon >1100 Then
Update Selection Set HabCondition = "Excellent" Print "Excellent" sHCond = "Ex"
ElseIf iHabCon >=501 and iHabcon <=1100 Then
Update Selection Set HabCondition = "Good" Print "Good" sHCond = "Go"
ElseIf iHabCon <501 and iHabCon >-4000 Then
Update Selection Set HabCondition = "Poor" Print "Poor" sHCond = "Po"
ElseIf iHabCon <-90000 and iHabCon >-200000 Then Print "Misident"
Update Selection set PriDet = "Not applicable"
Insert Into Misidentified ( COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104) Select COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104 From Selection
Delete From Sort Where RowID = iRowID
ElseIf iHabCon <-900000 Then Print "Lost"
Update Selection Set HabCondition = ""
Update Selection set PriDet = "Not applicable"
Insert Into Lost ( COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104) Select COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104 From Selection
Delete From Sort Where RowID = iRowID

Else Update Selection Set HabCondition = "" sHCond = ""
End If
GoTo Cont

Cont:
'Print "Running Agrienv bit"
If sStew <> "" Then
If sHCond = "" Or sHCond = "Po" Then
Update Selection Set HabCondition = "Good" iLevelUp = iLevelUp +1 'Print sSite+" Agri-Env Level-up!"
End If
End If

'temp exit sub for analysis
'Exit sub

Loop
Print "Sites in stewardship fudged to Good = "+sTableName+", "+iLevelUp
'Commit Table sTableName Interactive
'Commit Table Lost Interactive
'Commit Table Misidentified Interactive

End Sub

------------------------------
Steve Oram
Biodiversity Officer
PTES
London

Original Message:
Sent: 11-07-2022 01:40
From: Peter Møller
Subject: Geopackage problems

Hi Steve

>> I had to run a process to extract a value based on other values within the same attribute using a Do Loop

Would you be able to use SQL to do this?

Can you share your code for the process - just the Do Loop?
I don't have anything specific in mind but was wondering if seeing your loop could spark some ideas of why it is getting slow.

Maybe if you are fetching a specific record number instead of just fetching the next record can make the process slower. Not sure.

Are you updating the dataset through this loop too?

Thanks

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

Original Message:
Sent: 11-05-2022 07:30
From: Steve Oram
Subject: Geopackage problems

I manage several large vector layers but also use QGIS, so made the decision to change them all to gpkg files as Q seems to damage my tab files sometimes, and the theory is gpkg files work properly with both. The layers are edited both manually and using a bespoke MapBasic tool for various processes. The tool code runs to around 11000 lines so I'm not rewriting it in Python (I'm not really a programmer so the prospect makes me shudder, and I don't have the time anyway). The files vary in size from less than 1 MB to about 7 or 8 MB, and contain between 20 and 5000 polygons. Each attribute row has 104 columns of data, mostly character strings, a few dates, and a few float integers.

After saving new versions of the files in the gpkg format, I had to run a process to extract a value based on other values within the same attribute using a Do Loop. The smaller datasets were fine and the process runs through in seconds (each attribute taking fractions of a second), but the larger ones now take around 20 or 30 seconds per attribute. I have also noticed other parts of the MI tool have a similar or even longer delay. These are actions that took less than a second in the tab format.

I also can't modify the structure.

Any insights would be very welcome.

Steve

------------------------------
Steve Oram
Biodiversity Officer
PTES
London
------------------------------

That's brilliant, thank you. That gives me a lot to work with. I'll let you know how I get on.

------------------------------
Steve Oram
Biodiversity Officer
PTES
London
------------------------------

Original Message:
Sent: 11-08-2022 04:29
From: Peter Møller
Subject: Geopackage problems

Hi Steve,

I had a look at your code. I had no problem reading it so it's not really bad.

I have tried modifying your code a bit in the attached version, see the attached zip file.

Was the switch to GeoPackage the only change you made or did you also maybe upgrade from 32-bit to 64-bit? The first suggestion below is probably one of the best suggestions for improving performance on add-in using Select statements.

Suggestions:

Use NoSelect and Hide with Select

When using Select statements, try to use NoSelect and Hide when possible. You rarely need the highlighting of the selection so using named query results is recommended. Hide was added to MapBasic with v2019.3.

Use this
Select * from sTableName order by Incid into Sort NoSelect Hide
instead of
Select * from sTableName order by Incid into Sort

Looping through a table

When looping through a table, use Do Until EOT in combination with Fetch First and Fetch Next.

In your code, you use Select to select the individual records. That results in a large number of queries that not always are needed. You can place the cursor on the record you want to read from without using the Select statement.

I have used this structure instead:
Fetch First From Sort
   Print "_______"
   ***Get the RowID from the current record
   iRowID = Sort.ROWID
   '**Here goes your logical for each row

   Fetch Next From Sort
Loop

This will also mean that when writing values back, you need to include a Where element on the Update statement:
Update Sort
   Set PriDet = "Probably Traditional Orchard priority habitat but some uncertainty due to age of data source"
   Where ROWID = iRowID

Set your Alias variables outside the loop

This is probably just a minor thing but by moving the assignment of your Alias variables outside your loop, you only set these once instead of for each record in the table.
You will still keep the assignment to your other variables using the Alias variable inside the loop.

Limit the number of Update statements

The syntax for Update allows you to update multiple columns in one statement
Use this:
Update Sort
   Set PriDet = "Definitely is Traditional Orchard priority habitat"
      , Habdefver = "v2.6"
      , Area_Ha = Area(obj, "hectare")
   Where ROWID = iRowID
Instead of this:
Update Sort Set PriDet = "Definitely is Traditional Orchard priority habitat" Where ROWID = iRowID
Update Sort Set Habdefver = "v2.6" Where ROWID = iRowID
Update Sort Set Area_Ha = Area(obj, "hectare") Where ROWID = iRowID

Removing Not Applicable records after the loop

It might be a good idea to move the deletion of the records that aren't applicable until after you have looped through all records.

At this point, you can select all these records by looking for the string "Not Applicable" in the column PriDet.

You can then copy all these records to the Lost table and then delete them from the original table at once.

My guess is that it will be the second recommendation that might slow down the performance.
But make to test whatever change of mine you implement as I haven't been able to test this at all.

Let us know if this has any effect.

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

Original Message:
Sent: 11-07-2022 11:33
From: Steve Oram
Subject: Geopackage problems

It's quite long, and please bear in mind that I don't claim to be a programmer so expect ugly. I couldn't upload the .mb so pasting below:

Sub ReHabCon

Dim aSite, aPridet, aInterQual, aHCond, aS1Date, aS2Date, aS3Date, aS4Date, aStatus1, aStatus2, aS1Done, aS2Done, aS4Done, aP1, aP2, aStew, aNonTO, aNotes, aScrub, aSDW, aDWF, aHol, aCav, aOT1, aOT2, aOT3, aOT4, aYT1, aYT2, aYT3, aYT4, aG, aM, aU, aGD As Alias
Dim sSite, sPridet, sInterQual, sHCond, sStatus1, sStatus2, sS1Done, sS2Done, sP1, sP2, sStew, sNonTO, sNotes, sScrub, sSDW, sDWF, sHol, sCav, sOT1, sOT2, sOT3, sOT4, sYT1, sYT2, sYT3, sYT4, sG, sM, sU, sGD As String
Dim iRowID, iRows, iHabCon, iS1Date, iS2Date, iS3Date, iS4Date, iToday, iP2Blank, iLevelUp, iYOT, iDataAge As Integer

iRowID = 0
iLevelUp = 0
select * from sTableName order by Incid into Sort
Select * from Sort
iRows = SelectionInfo(SEL_INFO_NROWS)
Run Menu Command 304

Do While iRowID < iRows
Print "_______"
iRowID = iRowID+1
Select * from Sort Where RowID = iRowID
Print iRowID
iHabCon = 0
iP2Blank = 0
sSite = ""
sHCond = ""
sPridet = ""
sHabCon = ""
sStatus1 = ""
sStatus2 = ""
sS1Done = ""
sS2Done = ""
dToday = 0
iS1Date = 0
iS2Date = 0
iS3Date = 0
iS4Date = 0
iToday = 0
sP1 = ""
sP2 = ""
sStew = ""
sNonTO = ""
sNotes = ""
sScrub = ""
sSDW = ""
sDWF = ""
sHol = ""
sCav = ""
sOT1 = ""
sOT2 = ""
sOT3 = ""
sOT4 = ""
sYT1 = ""
sYT2 = ""
sYT3 = ""
sYT4 = ""
sG = ""
sM = ""
sU = ""

aSite = Sort+"."+ColumnInfo(Sort, "Col1", COL_INFO_NAME) 'Site name
aInterQual = Sort+"."+ColumnInfo(Sort, "Col5", COL_INFO_NAME) 'InterQual
aHCond = Sort+"."+ColumnInfo(Sort, "Col6", COL_INFO_NAME) 'Habitat Condition
aS1Done = Sort+"."+ColumnInfo(Sort, "Col7", COL_INFO_NAME) 'S1 survey
aS2Done = Sort+"."+ColumnInfo(Sort, "Col12", COL_INFO_NAME) 'S2 OOQ
aStatus1 = Sort+"."+ColumnInfo(Sort, "Col11", COL_INFO_NAME) 'StatusS1
aP1 = Sort+"."+ColumnInfo(Sort, "Col11", COL_INFO_NAME) 'PlantingS1
aStatus2 = Sort+"."+ColumnInfo(Sort, "Col15", COL_INFO_NAME) 'StatusS2
aP2 = Sort+"."+ColumnInfo(Sort, "Col16", COL_INFO_NAME) 'PlantingS2
aS1Date = Sort+"."+ColumnInfo(Sort, "Col8", COL_INFO_NAME) 'S1 date to Alias
aS2Date = Sort+"."+ColumnInfo(Sort, "Col13", COL_INFO_NAME) 'S2 date to Alias
aS3Date = Sort+"."+ColumnInfo(Sort, "Col17", COL_INFO_NAME) 'S3 date to Alias
aS4Date = Sort+"."+ColumnInfo(Sort, "Col24", COL_INFO_NAME) 'S4 date to Alias
aStew = Sort+"."+ColumnInfo(Sort, "Col33", COL_INFO_NAME) 'Agri-environment
aNonTO = Sort+"."+ColumnInfo(Sort, "Col39", COL_INFO_NAME) 'Marginal
aNotes = Sort+"."+ColumnInfo(Sort, "Col42", COL_INFO_NAME) 'No visibility
aScrub = Sort+"."+ColumnInfo(Sort, "Col67", COL_INFO_NAME) 'Scrub
aSDW = Sort+"."+ColumnInfo(Sort, "Col81", COL_INFO_NAME) 'StandingDeadwood
aDWF = Sort+"."+ColumnInfo(Sort, "Col82", COL_INFO_NAME) 'DeadwoodFloor
aHol = Sort+"."+ColumnInfo(Sort, "Col78", COL_INFO_NAME) 'Branch holes
aCav = Sort+"."+ColumnInfo(Sort, "Col80", COL_INFO_NAME) 'Cavities
aOT1 = Sort+"."+ColumnInfo(Sort, "Col49", COL_INFO_NAME) 'OT 1-10
aOT2 = Sort+"."+ColumnInfo(Sort, "Col50", COL_INFO_NAME) 'OT 11-30
aOT3 = Sort+"."+ColumnInfo(Sort, "Col51", COL_INFO_NAME) 'OT31-100
aOT4 = Sort+"."+ColumnInfo(Sort, "Col52", COL_INFO_NAME) 'OT100+
aYT1 = Sort+"."+ColumnInfo(Sort, "Col53", COL_INFO_NAME) 'YT 1-10
aYT2 = Sort+"."+ColumnInfo(Sort, "Col54", COL_INFO_NAME) 'YT 11-30
aYT3 = Sort+"."+ColumnInfo(Sort, "Col55", COL_INFO_NAME) 'YT31-100
aYT4 = Sort+"."+ColumnInfo(Sort, "Col56", COL_INFO_NAME) 'YT100+
aG = Sort+"."+ColumnInfo(Sort, "Col68", COL_INFO_NAME) 'Grazed
aM = Sort+"."+ColumnInfo(Sort, "Col69", COL_INFO_NAME) 'Mown
aU = Sort+"."+ColumnInfo(Sort, "Col70", COL_INFO_NAME) 'Unmanaged
aGD = Sort+"."+ColumnInfo(Sort, "Col76", COL_INFO_NAME) 'GrazingDamage

sSite = aSite
sInterQual = aInterQual
sHCond = aHCond
sS1Done = aS1Done
sS2Done = aS2Done
sStatus1 = aStatus1
sStatus2 = aStatus2
sP1 = aP1
sP2 = aP2
iS1Date = aS1Date
iS2Date = aS2Date
iS3Date = aS3Date
iS4Date = aS4Date
sStew = aStew
sNonTO = aNonTO
sNotes = aNotes
sScrub = aScrub
sSDW = aSDW
sDWF = aDWF
sHol = aHol
sCav = aCav
sOT1 = aOT1
sOT2 = aOT2
sOT3 = aOT3
sOT4 = aOT4
sYT1 = aYT1
sYT2 = aYT2
sYT3 = aYT3
sYT4 = aYT4
sG = aG
sM = aM
sU = aU
sGD = aGD
iYT = 0
iOT = 0
iYOT = 0
'Print "iToday A = "+iToday

dToday = CurDate()-1825
iToday = dToday
'Print "iToday B = "+iToday
Print sSite
If iS1Date <> 0 And iS1Date > iToday Then Print "Top1" GoTo Update1
Elseif iS2Date <> 0 And iS2Date > iToday Then Print "Top2" GoTo Update1
Elseif iS3Date <> 0 And iS3Date > iToday Then Print "Top3" GoTo Update1
Elseif iS4Date <> 0 And iS4Date > iToday Then Print "Top4" GoTo Update1
End If

dToday = CurDate()-3650
iToday = dToday
'Print "iToday C = "+iToday

If iS1Date <> 0 And iS1Date > iToday Then Print "Mid1" GoTo Update2
Elseif iS2Date <> 0 And iS2Date > iToday Then Print "Mid2" GoTo Update2
Elseif iS3Date <> 0 And iS3Date > iToday Then Print "Mid3" GoTo Update2
Elseif iS4Date <> 0 And iS4Date > iToday Then Print "Mid4" GoTo Update2
ElseIf iS1Date <> 0 And iS1Date <= iToday Then Print "Low1" GoTo Update3
Elseif iS2Date <> 0 And iS2Date <= iToday Then Print "Low2" GoTo Update3
Elseif iS3Date <> 0 And iS3Date <= iToday Then Print "Low3" GoTo Update3
Elseif iS4Date <> 0 And iS4Date <= iToday Then Print "Low4" GoTo Update3
End If
Update1:
If sInterQual = "1 (High)" Then Update Selection Set PriDet = "Definitely is Traditional Orchard priority habitat"
GoTo ContinueSub
Else GoTo Update3
Update2:
Update Selection Set PriDet = "Probably Traditional Orchard priority habitat but some uncertainty due to age of data source"
GoTo ContinueSub
Update3:
Update Selection Set PriDet = "Priority Traditional Orchard habitat may be present but evidence is either insufficient to determine presence confidently or is in the oldest allowable category"
GoTo ContinueSub
End If

ContinueSub:
Update Selection Set Habdefver = "v2.6"
Update Selection Set Area_Ha = Area(obj, "hectare")


If sOT1 = "Y" Or sOT2 = "Y" Or sOT3 = "Y" Or sOT4 = "Y" Then iOT = 1
End If
Print "iOT = "+iOT
If sYT1 = "Y" Or sYT2 = "Y" Or sYT3 = "Y" Or sYT4 = "Y" Then iYT = 10
Print "iYT = "+iYT
End If
iYOT = iOT+iYT

If iYOT = 11 Then Update Selection set S2Planting = "Young trees in gaps"
End If

If sS2Done = "Done" Then
'Update Selection set PriDet = "Definitely is Traditional Orchard priority habitat"
If sP2 = "" Then
iP2Blank = 1
GoTo Try1 End If
If Like(sStatus2,"%Unmanaged%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sP2,"%No young%","") = 1 Then sHabCon = sHabCon+"C" End If
If Like(sP2,"%gaps","") = 1 Then sHabCon = sHabCon+"A" End If
If Like(sP2,"%Young orchar%","") = 1 Then sHabCon = sHabCon+"B" End If
If Like(sP2,"%Established%","") = 1 Then sHabCon = sHabCon+"L" End If


ElseIf sS1Done = "Done" Then
Try1:
If Like(sNotes,"%No visibil%","") = 1 Then
Update Selection Set HabCondition = "" Print "HabCon set to blank"
'If iP2Blank = 0 Then Update Selection Set PriDet = "Priority Traditional Orchard habitat may be present but evidence is either insufficient to determine presence confidently or is in the oldest allowable category"
'End If
GoTo Cont
Else
'Update Selection set PriDet = "Definitely is Traditional Orchard priority habitat"
If Like(sStatus1, "%Unmanaged%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sP1,"%No young%","") = 1 Then sHabCon = sHabCon+"C" End If
If Like(sP1,"%gaps%","") = 1 Then sHabCon = sHabCon+"A" End If
If Like(sP1,"%Young orchar%","") = 1 Then sHabCon = sHabCon+"B" End If
If Like(sP1,"%Established%","") = 1 Then sHabCon = sHabCon+"L" End If
End If

Else
Update Selection Set HabCondition = ""
GoTo Cont

End If

If Like(sNonTO,"Los%","") = 1 Then sHabCon = sHabCon+"H" End If
If Like(sNonTO,"Misident%","") = 1 Then sHabCon = sHabCon+"J" End If
If Like(sNonTO,"Relic%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sNonTO,"Abandoned%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sNonTO,"Intensively%","") = 1 Then sHabCon = sHabCon+"K" End If
If Like(sSDW,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sDWF,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sHol,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sCav,"%Y%","") = 1 Then sHabCon = sHabCon+"I" End If
If Like(sG,"%Y%","") = 1 Then sHabCon = sHabCon+"D" End If
If Like(sM,"%Y%","") = 1 Then sHabCon = sHabCon+"E" End If
If Like(sGD,"%Y%","") = 1 Then sHabCon = sHabCon+"G" End If
If Like(sU,"%Y%","") = 1 Then sHabCon = sHabCon+"F" End If
If Like(sScrub,"%D%","") = 1 Then sHabCon = sHabCon+"F" End If

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Habitat condition assessment

'Print "'"+sHabCon+"'"
If Like(sHabCon, "%H%"," ") = 1 Then iHabCon = iHabCon -1000000 End If 'H Lost
If Like(sHabCon, "%J%"," ") = 1 Then iHabCon = iHabCon -100000 End If 'J Misidentified
If Like(sHabCon, "%F%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'F Unmanaged/scrub
If Like(sHabCon, "%G%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'G Grazing damage
If Like(sHabCon, "%K%"," ") = 1 Then iHabCon = iHabCon -10000 End If 'K NonTO
If Like(sHabCon, "%A%"," ") = 1 Then iHabCon = iHabCon +600 End If 'A Gapped up (old and young trees)
If Like(sHabCon, "%L%"," ") = 1 Then iHabCon = iHabCon +500 End If 'L Established/mostly stocked
If Like(sHabCon, "%C%"," ") = 1 Then iHabCon = iHabCon -1002 End If 'C No young trees
If Like(sHabCon, "%B%"," ") = 1 Then iHabCon = iHabCon +500 End If 'B Young orchard
If Like(sHabCon, "%I%"," ") = 1 Then iHabCon = iHabCon +500 End If 'I Deadwood canopy, cavities, floor
If Like(sHabCon, "%D%"," ") = 1 Then iHabCon = iHabCon +1 End If 'D Grazed
If Like(sHabCon, "%E%"," ") = 1 Then iHabCon = iHabCon +1 End If 'E Mown
Print iHabCon

If iHabCon >1100 Then
Update Selection Set HabCondition = "Excellent" Print "Excellent" sHCond = "Ex"
ElseIf iHabCon >=501 and iHabcon <=1100 Then
Update Selection Set HabCondition = "Good" Print "Good" sHCond = "Go"
ElseIf iHabCon <501 and iHabCon >-4000 Then
Update Selection Set HabCondition = "Poor" Print "Poor" sHCond = "Po"
ElseIf iHabCon <-90000 and iHabCon >-200000 Then Print "Misident"
Update Selection set PriDet = "Not applicable"
Insert Into Misidentified ( COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104) Select COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104 From Selection
Delete From Sort Where RowID = iRowID
ElseIf iHabCon <-900000 Then Print "Lost"
Update Selection Set HabCondition = ""
Update Selection set PriDet = "Not applicable"
Insert Into Lost ( COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104) Select COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18, COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26, COL27, COL28, COL29, COL30, COL31, COL32, COL33, COL34, COL35, COL36, COL37, COL38, COL39, COL40, COL41, COL42, COL43, COL44, COL45, COL46, COL47, COL48, COL49, COL50, COL51, COL52, COL53, COL54, COL55, COL56, COL57, COL58, COL59, COL60, COL61, COL62, COL63, COL64, COL65, COL66, COL67, COL68, COL69, COL70, COL71, COL72, COL73, COL74, COL75, COL76, COL77, COL78, COL79, COL80, COL81, COL82, COL83, COL84, COL85, COL86, COL87, COL88, COL89, COL90, COL91, COL92, COL93, COL94, COL95, COL96, COL97, COL98, COL99, COL100, COL101, COL102, COL103, COL104 From Selection
Delete From Sort Where RowID = iRowID

Else Update Selection Set HabCondition = "" sHCond = ""
End If
GoTo Cont

Cont:
'Print "Running Agrienv bit"
If sStew <> "" Then
If sHCond = "" Or sHCond = "Po" Then
Update Selection Set HabCondition = "Good" iLevelUp = iLevelUp +1 'Print sSite+" Agri-Env Level-up!"
End If
End If

'temp exit sub for analysis
'Exit sub

Loop
Print "Sites in stewardship fudged to Good = "+sTableName+", "+iLevelUp
'Commit Table sTableName Interactive
'Commit Table Lost Interactive
'Commit Table Misidentified Interactive

End Sub

------------------------------
Steve Oram
Biodiversity Officer
PTES
London

Original Message:
Sent: 11-07-2022 01:40
From: Peter Møller
Subject: Geopackage problems

Hi Steve

>> I had to run a process to extract a value based on other values within the same attribute using a Do Loop

Would you be able to use SQL to do this?

Can you share your code for the process - just the Do Loop?
I don't have anything specific in mind but was wondering if seeing your loop could spark some ideas of why it is getting slow.

Maybe if you are fetching a specific record number instead of just fetching the next record can make the process slower. Not sure.

Are you updating the dataset through this loop too?

Thanks

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

Original Message:
Sent: 11-05-2022 07:30
From: Steve Oram
Subject: Geopackage problems

I manage several large vector layers but also use QGIS, so made the decision to change them all to gpkg files as Q seems to damage my tab files sometimes, and the theory is gpkg files work properly with both. The layers are edited both manually and using a bespoke MapBasic tool for various processes. The tool code runs to around 11000 lines so I'm not rewriting it in Python (I'm not really a programmer so the prospect makes me shudder, and I don't have the time anyway). The files vary in size from less than 1 MB to about 7 or 8 MB, and contain between 20 and 5000 polygons. Each attribute row has 104 columns of data, mostly character strings, a few dates, and a few float integers.

After saving new versions of the files in the gpkg format, I had to run a process to extract a value based on other values within the same attribute using a Do Loop. The smaller datasets were fine and the process runs through in seconds (each attribute taking fractions of a second), but the larger ones now take around 20 or 30 seconds per attribute. I have also noticed other parts of the MI tool have a similar or even longer delay. These are actions that took less than a second in the tab format.

I also can't modify the structure.

Any insights would be very welcome.

Steve

------------------------------
Steve Oram
Biodiversity Officer
PTES
London
------------------------------