MapInfo Pro

 View Only

  • 1.  Same query huge performance gaps between MapBasic and Python

    Posted 07-22-2024 05:21

    The situation I found is:

    I have a query to calculate population for the network coverage, I have the pops points MapInfo tab file and the network coverage polygons.

    I did the query in MapBasic windows:

    Dim start as Integer
Dim end_t as Integer
Dim elapsed as Integer

start = Timer()

Select AllSites_NR_C_ID_20240715.Transmitte, sum(PopEst_2021_Density) from GNAF_AUS_202005_EST2021, AllSites_NR_C_ID_20240715 where GNAF_AUS_202005_EST2021.Obj Intersects AllSites_NR_C_ID_20240715.Obj group by AllSites_NR_C_ID_20240715.Transmitte into nr_c_id_pops
Export "nr_c_id_pops" Into "C:\Users\{username}\Documents\nr_c_id_pops_mapinfo.csv" Type "ASCII" Overwrite Delimiter "," Titles
Close Table nr_c_id_pops Interactive

end_t = Timer()
elapsed = end_t - start
print "NR_C_ID time cost is: " + str$(elapsed)

    and did the same query in Python:

    import time
import os

tables = pro.Catalog.Tables
tables_list = []
# check if points table exists
table_points = 'GNAF_AUS_202005_EST2021'
for table in tables:
    tables_list.append(table.Alias)

if table_points in tables_list:
    print('GNAF table has been opened!')
    tables_list.remove(table_points)
   
else:
    print("GNAF table doesn't exist.")
    exit()
home_path = os.path.expanduser("~")
path = os.path.join(home_path, "Downloads")

for table in tables_list:
    start_time = time.time()
    table_polygon = table
    table_pops = table + "_pops"
    file = table_pops + "_mapinfo_python.csv"
    file_name = os.path.join(path, file)

    do("Select {0}.Transmitte,sum({1}.PopEst_2021_Density) from {0}, {1} where {0}.Obj Intersects {1}.Obj into {2} group by {0}.Transmitte".format( table_polygon, table_points, table_pops))
    do("Export {0} Into \"{1}\" Type \"ASCII\" Overwrite Delimiter \",\" Titles".format(table_pops, file_name))
    do("Close Table {0} Interactive".format(table_pops))

    end_time = time.time()
    execution_time = end_time - start_time
    formatted_exec_time = f'{execution_time:.2f}'
    print(table_pops + ' execution time is: ' + formatted_exec_time + ' seconds')

    The results: it costed 330 seconds in MapBasic, while in Python just costed 84 seconds in average.

    NR_C_ID query

    Any reasons behind it? I thought Python is calling MapBasic to do the query, the performance should be similar.



    ------------------------------
    Yong Zhang
    Knowledge Community Shared Account
    Burlington MA
    ------------------------------


  • 2.  RE: Same query huge performance gaps between MapBasic and Python

    Employee
    Posted 07-22-2024 12:07

    Hi

    Can you check that the order of the two tables is the same in the two queries?

    To me, it seems as if the order is reversed.



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

    Original Message


  • 3.  RE: Same query huge performance gaps between MapBasic and Python

    Posted 07-23-2024 02:03
    Edited by Yong Zhang 07-23-2024 02:11

    Thank you Peter.

    After adjusted the order of the two tables, in from and where statements, time cost by MapBasic reduced to the similar level as Python query.



    ------------------------------
    Yong Zhang
    Knowledge Community Shared Account
    Burlington MA
    ------------------------------

    Original Message


  • 4.  RE: Same query huge performance gaps between MapBasic and Python

    Employee
    Posted 07-23-2024 03:24

    Perfect!

    If you are comparing point and polygons, I would recommend using Within/Contains instead of Intersects.

    That should be faster, unless Pro automatically switches to these when it detects that you are comparing points and polygons.

    give it a try and see if it makes a difference performancewise.



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

    Original Message