MapInfo Pro Developers User Group

Expand all | Collapse all

Improve performance of linked TAB files in MapInfo

  • 1.  Improve performance of linked TAB files in MapInfo

    Posted 03-13-2020 13:23
    Hello, we have some tables in a SQL Server database that are read in MapInfo17 using tab files that have the DDBB connection string embedded (Windows Authentication). They have about 20-25 fields and a varying amount of records from 2000 to 250000. The problem is that it takes ages to load in MapInfo, while it is super fast in other environments like Spectrum or QGIS. This happens both with the small and the big tables. I have to set the view very close to the ground in order to load them fast, but then they are not useful for most purposes. Can't understand why it is so different from one environment to another, so my guess is that it is a matter of how MapInfo handles the DDBB tables...

    Can someone provide tips in order to improve the speed of loading these linked tab files in MapInfo? And one that does not require caching if possible.. Maybe using spatial indexes, changing some parameters in MapInfo client..

    Kind regards,

    ------------------------------
    Miquel Roy Sunyer
    Kirklees Council
    HUDDERSFIELD
    ------------------------------


  • 2.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-17-2020 06:41
    Hi Miquel

    When opening a DBMS table in MapInfo Pro, you have three options depending on your requirements:

    1. Live
    This method will always read data directly from the database. It won't cache any data. MapInfo Pro will query the database for every zoom and pan and if you use the Info tool on an object on the map.

    This can be used if your data is changing every minute or if your data is very sensitive.

    It's recommended to use zoom layerings for your layer.

    2. Live With Cache
    This works similarly to Live but it will cache data throughout the session. This means that have you already been in an area, it will not try to retrieve data from the database again.

    This can be used if your data is changing every hour or if your data is very sensitive.

    It's recommended to use zoom layerings for your layer.

    3. Linked
    A linked table is a native tab file that is connected to a DBMS table. With a Linked table, you typically connect to the database and then download the data you need in your session. Once the data has been downloaded, the performance is as good as any native table. You can refresh a linked table with the latest data from the database.

    This is the way most MapInfo Pro users are accessing and using data stored in a database. This is also the way to access the data if you need to analyze the data inside MapInfo Pro, for example via SQL Select or similar.

    There is some more insights in this (dated) presentation which however still is valid.

    An example

    I just did a few tests with a live table with cache against a database table holding 180,000 records. Zoom and panning in a close zoom, around 3 km, works quite fast. It takes around a second for the map to refresh. The further I zoom out, the slower it gets. When I zoom to the entire layer it takes around 30 seconds to load all the records from the table. But once they have been loaded, and cached, zooming and panning is as fast as a normal native table.

    What kind of waiting time are you experiencing? Have you considered using layer zooming to avoid fetching all records from your table?

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 3.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-18-2020 19:41
    Peter,

    I have the same doubt.
    The question is why other software download thouthands of records just in seconds and MapInfo Pro delay minutes.

    If I open in MIPro a linked table (that means the records will save in my local disk) of 200.000 records from a SQLServer, delay one or two minutes.
    But ...  If I use mitab.dll and create the MapInfo table and also create the .Tab with all "linked" sentences, I have the same result but in a few seconds.

    MFal and newer EFal are not being used when MapInfo Pro download a SQLServer table?

    Thank you


    ------------------------------
    Alejandro Hernandez
    Information Technology & Services
    ITS SA (GBD)
    Buenos Aires
    ------------------------------



  • 4.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-19-2020 04:19
    Hi Alejandro

    My initial thought is that there is something wrong with your connection to the database from MapInfo Pro.

    I ran a few tests using my local SQL Server and MapInfo Pro v2019 downloading all the records from a couple of database table into a linked Native tab file. I'm using this very basic script thru the MapBasic window that will use the DBSM Connection 1:

    Close All
    Print Time(24) & " Start"
    Dim sTab As String
    sTab = "Buildings"
    Server 1 Link Table "Select * From Municipality." + sTab toolkit "ODBC"

      Into sTab
      File "D:\3. Demo\1. DBMS\" + sTab + ".tab"
      TYPE NATIVE Charset "WindowsLatin1" Map From sTab
    Print Time(24) & " Done. " + FormatNumber$(TableInfo(0, TAB_INFO_NROWS)) + " records loaded"

    I did one test with a point table holding around 70,000 records. It opened into MapInfo Pro in 2 seconds:
    08:56:24 Start
    08:56:26 Done. 72.702 records loaded

    I also tried with a table holding polygons, buildings. It contains almost 180,000 records. It opened into MapInfo Pro is 7 seconds:
    09:04:53 Start
    09:05:00 Done. 179.785 records loaded

    I hope that's similar to what you see in other systems. Can you try running the script above to see if you get similar results? Here are some steps in order to make it run:

    1. Open a connection to your SQL Server database in MapInfo Pro.
    2. Copy the script into the MapBasic window
    3. Modify these elements in the script:
    • Change Buildings to the name of your database table
    • Change Municipality to the schema in your database
    • Change the path D:\3. Demo\1. DBMS\ to a path on your system where you want to save the table
    4. Select all the lines of code in your MapBasic window and hit enter to execute them.

    I'm using SQL Server Native Client 11.0 when in my ODBC connection.

    Let me know how it goes.

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 5.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-19-2020 04:04
    Hello Peter,

    Sorry, I missused the term Linked table. What I actually mean is Live table. The question is what @Alejandro Hernandez has pointed out ("The question is why other software download thouthands of records just in seconds and MapInfo Pro delay minutes."). While other software load same data in matter of seconds (e.g. 3 seconds in QGIS to load 250000 records), Mapinfo takes ages. When setting the view close to the ground it is a bit faster, but every zoom out it takes about 4 times more, so forget about loading the full extent in less than 5 or 10 minutes of MapInfo being stuck. Can you explain more about the layering zooming? I've never heard of it, or not sure what you mean.

    Kind regards,


    ------------------------------
    Miquel Roy Sunyer
    Kirklees Council
    HUDDERSFIELD
    ------------------------------



  • 6.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-19-2020 04:24
    Hi Miquel

    Yeah, I did expect that you were using a live connection and not a linked table (the MapInfo term).

    See my previous post about performance.

    Zoom Layering is a mechanism that turns on the display of a layer in a specific zoom interval only. Often you will use this to avoid your map to get cluttered with too much information as you zoom out.

    You access it via the Layer Settings of a layer. Double-click on a layer in the Explorer window and you will see this dialog:



    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 7.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-19-2020 05:00
    Oh, of course the zoom layering... No, I don't think this will sort it. My concern is about general MapInfo performance. Because we have plenty of users that will struggle opening these tables in their projects and they won't be setting up a zoom layering every time.

    Our goal is to enable general access to ddbb tables for many users, but this performance issue will be a problem.

    ------------------------------
    Miquel Roy Sunyer
    Kirklees Council
    HUDDERSFIELD
    ------------------------------



  • 8.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-20-2020 02:35
    Hi Miquel,

    Are you using the SQL Server Native Client 11.0 driver or the standard SQL Driver that comes with Windows?  I have had quite a few issues with the standard one.

    ------------------------------
    Duri Bradshaw
    Spatial IT Consultant
    Insight GIS
    ------------------------------



  • 9.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-20-2020 11:58
    Hello Duri, we always use the SQL Server Native Client 11.0

    ------------------------------
    Miquel Roy Sunyer
    Kirklees Council
    HUDDERSFIELD
    ------------------------------



  • 10.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-20-2020 12:55
    Sorry, I have to rectify. Just noticed that in the TAB file, the connection string uses "DRIVER=SQL Server;....". What's the string to use SQL Server Batuve Client 11.0?

    ------------------------------
    Miquel Roy Sunyer
    Kirklees Council
    HUDDERSFIELD
    ------------------------------



  • 11.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-20-2020 19:47
    Hi Miquel,

    I did a bit of performance testing today comparing the two drivers;

    I was using a small table with 10000 property polygons using Live link and no cache, initial load time is very quick as my default view is only a small area so I used the View Entire Layer option to test the performance.

    SQL Driver
    "\DATALINK" = ""
    "\DATALINK\Query" = "Select *  From ""GISDATA"".""dbo"".""GSBC_Cadastre"""
    "\DATALINK\ConnectionString" = "DRIVER=SQL Server;SERVER=IGAPPS\SQLEXPRESS;DATABASE=GISDATA;Trusted_Connection=Yes;MARS_Connection=yes"
    "\DATALINK\ToolKit" = "ODBC"
    "\CACHE" = "OFF"

    View Entire Layer takes 22 seconds (average of 3)

    SQL Server Native Client 11.0

    "\DATALINK" = ""
    "\DATALINK\Query" = "Select *  From ""GISDATA"".""dbo"".""GSBC_Cadastre"""
    "\DATALINK\ConnectionString" = "DRIVER=SQL Server Native Client 11.0;SERVER=IGAPPS\SQLEXPRESS;DATABASE=GISDATA;Trusted_Connection=Yes;MARS_Connection=yes"
    "\DATALINK\ToolKit" = "ODBC"
    "\CACHE" = "OFF"

    View Entire Layer takes 1.5 seconds (average of 3)

    You will need to install the Native Client if you don't already have it installed





    ------------------------------
    Duri Bradshaw
    Spatial IT Consultant
    Insight GIS
    ------------------------------



  • 12.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-25-2020 04:38
    Hi Duri, I have tried using the SQL Server Native Client 11.0 in the tabfile as you suggested but I get exactly the same performance.

    Kind regards,

    ------------------------------
    Miquel Roy Sunyer
    Kirklees Council
    HUDDERSFIELD
    ------------------------------



  • 13.  RE: Improve performance of linked TAB files in MapInfo

    Employee
    Posted 03-20-2020 08:31
    Have you tried using the RDBMS SetMBR tool that sets an MBR in the Map Catalog to just the area in mappable table that you are concerned with so not  getting entire table at once?

    Regards,
    -Bill

    ------------------------------
    Bill Wemple
    Principal QA Engineer
    Pitney Bowes
    Troy, NY
    ------------------------------



  • 14.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-20-2020 11:55
    Hello, I have setup the fields in the MapCatalog manually so the tables load initially in a very restricted location, so MapInfo doesn't get stuck loading a huge quantity of features, if this is what you mean... But this is only a partial solution because once I start zooming out, it gets more an more slow (I'd say 4 times slower in every zoom out).

    However, I insist that my question is more a general performance thing... Is it so slow because MapInfo has to link the target table to MapCatalog every time? While other software just don't need to link to MapCatalog table for anything, so they read from the target table straightaway? Is there a way to bypass the MapCatalog table in MapInfo to read from source table straightaway?

    Kind regards,

    ------------------------------
    Miquel Roy Sunyer
    Kirklees Council
    HUDDERSFIELD
    ------------------------------



  • 15.  RE: Improve performance of linked TAB files in MapInfo

    Employee
    Posted 03-20-2020 12:40
    Have you tried to enable MARS connection?

    To improve performance when accessing large SQL Server tables, add a Multiple Active Result Sets (MARS) connection setting to the connection string in the live access .TAB file. The connection attribute to update is \DATALINK\ConnectionString, for example:

    "\DATALINK\ConnectionString" = "DSN=local-native10;UID=username;Trusted_Connection=Yes;APP=MapInfo Pro;WSID=hostname;DATABASE=test;MARS_Connection=Yes;"

    You may also add the MARS connection setting to the connection string of MapBasic Server_Connect( ) command.



    ------------------------------
    Andrei Veselov
    Pitney Bowes Software Inc.
    Troy NY
    ------------------------------



  • 16.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-20-2020 13:11
    Hi Andrei,

    I have modified the live access tab file to include MARS_Connection=Yes but no luck. This is what I currently use:

    "\IsReadOnly" = "FALSE"
    "\DATALINK" = ""
    "\DATALINK\Query" = "select * from DATABASENAME.SCHEMANAME.TABLENAME"
    "\DATALINK\ConnectionString" = "DRIVER=SQL Server;SERVER=SERVERNAME;DATABASE=DATABASENAME;Trusted_connection=yes;MARS_Connection=Yes"
    "\CACHE" = "OFF"
    "\MBRSEARCH" = "ON"

    As I mentioned to Duri, the connection string seems to be using DRIVER=SQL Server instead of SQL Server Native Client 11.0. But not sure which string should I use if I need to change it.

    Kind regards,


    ------------------------------
    Miquel Roy Sunyer
    Kirklees Council
    HUDDERSFIELD
    ------------------------------



  • 17.  RE: Improve performance of linked TAB files in MapInfo

    Employee
    Posted 03-20-2020 16:09

    I am going to attempt to change the direction of this discussion a bit. While it is fine to discuss ways in which the connection string or other client side (MapInfo Pro) settings might affect performance, I have these observations and questions:

    1. Despite the name of the thread being about Linked Tables, we are talking about Live tables, correct?  I am not sure if this is with or without cache.
    2. Most importantly, I would ask why Live tables are being used.  Peter Møller started to address this in his very even handed discussion. I am a bit more aggressive about it which is that one should have an excellent reason to use Live. Otherwise, Linked is always better. Let me explain.

    One has to understand what Live access means in MapInfo Pro. Here's my primer on Linked vs Live. Linked tables download the data and store it in MapInfo Native (or NativeX/Extended ) TAB file that has a few behavioral differences from a regular TAB but mostly is the same. That download is just a straight download such as select * from .. .  The code has to inquire about the table schema and match that to the types in MapInfo Pro, create the local files, fetch the data, translate the geometry in particular and write the data to the local files which includes maintaining an accurate spatial index. But these are well known processes that are usually quite quick assuming the connection is ok. It does not use any where clause (unless you provide a filter in those advanced dialogs) including spatial operators and does not require a primary key unless you are going to edit. The time is affected by the number of records fetched at once, the complexity of the geometries, points being the simplest and, as always, your disk write speed. With linked table for read, this and Refresh Linked Table are the only times MapInfo Pro even connects to the database. Once MapInfo Pro has this linked table, you can use it, reference in workspaces and never really have to touch the database again except when you want updates to the data.  You can use this table when you are completely isolated from a network, for example.  Live Access works very differently and I will describe with Cache separately. When you Open a Live DB table, and by this I mean just open, no browser, no map, MapInfo Pro does a few things to ensure it can use this table. One is checks about the primary key and fetching a certain number of records in order based on the key. (I believe it is still 50) .  An integer type is required. This is because without that key MapInfo Pro has no way to associate any of the map data with the attribute data, for instance. There are cases, most of the time where MapInfo Pro does a count(*) on the table to determine the number of rows. This can be avoided by populating the map catalog .

    This is all before you have seen a drop of data. So try this with "no view" to get a sense of how long it takes.

    Now let us say you put the table in a new map. As Bill Wemple indicated, since we have no idea what area you want to look at we either look at the entire bounds or a subset if that is in the map catalog. However, none of that is relevant if you add the table to an existing map. You can easily test if this is an issue by having a local table, it could have a single record of a rectangle where you data lives and add the table to that map.  What is happening here is that MapInfo Pro is sending a spatial query to the server to retrieve all the records that might be in the current map view; a typical MBR or window search. If this is the entire data set not only is that perhaps more than you need but there is no advantage to a spatial query for ALL the data. Select * would do just as well. Sometimes, if this is inordinately slow, that may indicate faulty spatial indices on the server which you would never notice in a linked table or a normal "download" . (Caveat again about those special filters).

    However, the most important piece to remember is that without cache, all that data is immediately lost. If you have labels on your layer, then those are drawn later and downloaded again. If you pan or zoom the map, the data is downloaded again. If you click on the map to select something, you could be doing 3 or more queries and downloads before you see a highlighted geometry. If you take your mouse and hover over a map object, the Info Tip that appears is based on a MBM search around the mouse location and downloaded again. If you resize the window including docking and undocking, it is downloaded again. The browser and Info tool are all separate downloads. Each download is not necessarily full but is based on an appropriate query which might be a spatial query, query by key or both. Live thus has a very high cost in performance and user experience. Some might say that they like this idea of being "live" but in most cases this is severe overkill and if many users in the organization are doing similar things, intense load on the database.

     

    Live with cache means that on a "per session basis "(opening and closing of the table in MapInfo Pro) the data as it is downloaded is put in a cache so that many of those repeated situations noted above pull from the cache rather than going back to the database. This is certainly a possible middle ground. However, a cache needs to know when it has the data and when it does not. The only mechanisms we have are the bounding box and the primary key. When MapInfo Pro wants data from an area of the map, if we think we already have that data, you get what we have in the cache. It might be out of date. Same for a fetch by key. Of course, sometimes we have no idea whether we should go back to the db server or not. If you happen to zoom out too far, we don't know there is no data there. So we get it again.

    In my view, this "half live" option is usually not worth it. If you know your data is not changing enough so that you trust the cache, just use Linked. The cache disappears when you close the table to next time you havfe to start all over.

     

    Why would you use "Live"?

    In years past before there was NativeX tables, it was possible that a database table would be too big to fit into a native table but that is no longer the case.

    The table may be small enough that the issues raised above are just not serious.

    Then there is the question of data and whether it is up to date. My claim is that most people who use database data know when they need to refresh. So refreshing on a schedule is much more effective in Linked.

     

    If you really are in a situation where you have no way of knowing when the data will be changed and you just can't keep refreshing the Linked table just in case, then you might have to choose the Live option.

    However, there are a couple of important things to note about behavior if this live data is critical.

     

    Let's say you were viewing the data and it was on your screen. You were discussing the implications of the data with someone or just lost in your thoughts. You might suddenly turn back and click somewhere on the map. If nothing is returned you might be annoyed or frustrated as you are clearly clicking on the map object. Except that while you were thinking, that geometry moved and is no longer there. Or you could click and get different results than what you see as different data is occupying that space. You could select a row in the browser and the map might update the highlight that is not where the map shows it.

    The point here is that it is Live at the moment you asked for it and in MapInfo Pro, the data is connected in multiple views which are not necessarily connected. Perhaps like 2 tabs in a browser, and one is refreshed and the other not.
    If you really think you need it, I would experiment with some of the implied steps mentioned here to see what part of Live access is slowing you down in your use case.



    ------------------------------
    Eric Blasenheim
    Spectrum Spatial Technical Product Manager
    Troy, NY
    ------------------------------



  • 18.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-20-2020 18:28
    Hi Eric,

    In my case the problem is more simple.
    We are comparting the performance in download data from SQLServer using MapInfo and MapXtreme agains my own routine using MITab.dll

    For example, 1.000.000 polylines using MapInfo / MapXtreme delays almost 3 minutes.  Using my own routine with MITab, delays a few seconds.


    Thank you.

    ------------------------------
    Alejandro Hernandez
    Information Technology & Services
    ITS SA (GBD)
    Buenos Aires
    ------------------------------



  • 19.  RE: Improve performance of linked TAB files in MapInfo

    Employee
    Posted 03-20-2020 18:41
    When you say downloading in MapInfo Pro, can you be a bit clearer what you are doing? Creating a linked table is the closest thing we have to "download".

    I will say that one million polylines in a few seconds is pretty fast regardless.

    ------------------------------
    Eric Blasenheim
    Spectrum Spatial Technical Product Manager
    Troy, NY
    ------------------------------



  • 20.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-20-2020 19:54
    Yes, opening a table like Linked Table.  So the data is downloaded locally.
    Using MITab, we create the binary files directly.  But I imagine MapInfo / MX shoud do something similar so I don't understand why is slower.

    ------------------------------
    Alejandro Hernandez
    Information Technology & Services
    ITS SA (GBD)
    Buenos Aires
    ------------------------------



  • 21.  RE: Improve performance of linked TAB files in MapInfo

    Employee
    Posted 03-21-2020 15:52
    Alejandro,
    Peter's example mapbasic above would be the best thing to show us. It literally just does the download and should have no other overhead.
    As far as your code, what are you using to read the translate the data (SQL geometry to MiTab)? Are you also using ODBC or possibly something that might be more efficient more direct to SQL?
    I have never compared writing speeds of our code to mitab. However, over the years I have been sent some TAB files created with MiTab that had a very flat and horrible single level r-tree index.  Our code never does this but we can read it just fine.  For small tables it does not have much impact but for bigger ones the performance difference is huge. Uffe Kousgaard told me that he has used this flat option in Mitab because he said it was much faster and then when needed he could fix the table in MapInfo Pro later via Pack or Save Copy as.
    I have no personal knowledge of the difference however.
    So it might be helpful if we could figure if the difference in creating the Linked table as Peter's code shows above to what you are doing and see if can figure out if it is the read from SQL,  the translate ( I think we use the WKB option) or the write of the .tab file. Could be bits of each.
    In case this is unclear, this discussion here is about the creation of the linked table, not the preference for it vs Live in almost all cases.
    Thanks for your input.

    ------------------------------
    Eric Blasenheim
    Spectrum Spatial Technical Product Manager
    Troy, NY
    ------------------------------



  • 22.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-23-2020 05:33
    Alejandro,

    I just uploaded a table with almost 2.3 million polylines to my SQL Server instance.

    When I use the sample code above to create a linked table for this table, it takes MapInfo Pro v2019 1:19 minutes to "download" all  2,266,958 records. If I limit the query to only fetch those with a MI_PRINX smaller than 1,000,000, these 999,999 records are downloaded in 30 seconds.

    Close All
    Print Time(24) & " Start"
    Dim sTab As String
    sTab = "FOT_Roads"
    Server 1 Link Table "Select * From dbo." + sTab + " Where MI_PRINX < 1000000" toolkit "ODBC"
    Into sTab
    File "C:\Data\" + sTab + ".tab"
    TYPE NATIVE Charset "WindowsLatin1" Map From sTab
    Print Time(24) & " Done. " + FormatNumber$(TableInfo(0, TAB_INFO_NROWS)) + " records loaded"

    As Duri wrote earlier there is a huge difference between using the SQL Server driver and the SQLServer Native driver. Can you check that you are using the SQL Server Native Driver?

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 23.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-25-2020 05:07
    Edited by Miquel Roy Sunyer 03-25-2020 05:08

    Hi Eric,

    Thanks for your comments. I acknowledge that there are mistakes in the formulation of the initial question, and I apologise as I am not entirely confident with MapInfo terminology. So yes, I meant to ask about Live tables, and without cache. The original point for the question was to understand why it is so time demanding loading a table from database in MapInfo in comparison with other software, including PB's Spectrum Spatial Analyst.

    To put an example, in MapInfo, we find it impossible to load the full extent of a table with 250000 records, while same table in our Spectrum Spatial Analyst loads in less than 3 seconds. Same happens in QGIS. But this doesn't happen only with full extent. It is enough to do some zoom outs to start having performance issues in MapInfo with this kind of datasource.

    The reason of using live tables is that, in our case the demand isn't that high, so there are never many people doing such big requests at the same time, hence enabling this kind of query shouldn't be a problem in our case. And we want to make sure that users are working everyday with the most updated data. This is particularly important for Gazetteer data coming from our LLPG, which changes every day. We don't want to be telling our users to refresh their cash every day, if possible, because this adds a step in the whole process which we'd like to avoid, and we don't have a reliable way of knowing that they're actually using the refresh cash option, or if they have been using outdated data for the last two months.

    Hope this makes sense.



    ------------------------------
    Miquel Roy Sunyer
    Kirklees Council
    HUDDERSFIELD
    ------------------------------



  • 24.  RE: Improve performance of linked TAB files in MapInfo

    Employee
    Posted 03-25-2020 16:14
    Miguel,
    Here's a couple of thoughts and things to try. The answers to each of this might change the next step but just listing them out.
    First of all to see if there is something that can improve the Live access.
    1. I assume you have that .tab file with the connection to the database table. If you can show us or share the contents of that .tab so we can see the full connection information.
    2. Open that .tab file with the option of "No View". MapInfo Pro should only be doing the things I mentioned above about the columns and the primary key and the first 50 rows, etc.  How long does that take?
    3. Next step is to open the table in the browser. This is fetching records by key so no Spatial query. Can the browser keep up with the Page Down key in a reasonable way?
    4. Next step would be to create a map with some local .tab file in the area of your data.  It could be Bing or OS tile map was well. But add the table to the map when zoomed in. Clearly less data would be faster as you are only rendering that much less as well as fetching.
    5. One way to test a pure fetch without rendering is to do a SQL query when the table not visible in a map.   In  the MapBasic window. Select * from table where obj intersects (or within) <rectangle variable>  into somename noselect . You can create rectangle via the Create Rect command or if you have a map window open in your area (without your SQL table), draw a rectangle in the cosmetic layer, select it and in the MB window assign it to a object variable (dim r as object, r= selection.obj Then use "r" as the variable ). This should give you some idea of the spatial access that is happening.  It is possible there is something about the spatial index that MapInfo Pro does not like but since the other programs are Ok that seems unlikely.
    6. Now add that table to a new map. Probably this is a an entire view of the data assuming the map catalog does not have a more limited initial view. You implied that this either times out or takes so long you did not wait and QGIS and Spatial Analyst do the same in a few seconds. 250000 records. Is that what happens? Are these points?  Does this succeed and how long does it take?
    7. If fetch is ok but rendering is slow we can look at other possibilities.
    8. You said this is MapInfo Pro 17 so there is a setting about rendering points (again not sure we are dealing with points) in preferences.
    9. Also what coordinate system is the data in? The map catalog information might be useful too.
    Like I said each answer might send us in different directions. Have you opened a case for this so all the information can be tracked and seen by the team?
    There also are ways to share or distribute a linked table to everyone if you go down that route. As I said earlier, depending on what you are doing with the data (ediing, creating thematics, labels.... can change the Linked/Live approach.


    ------------------------------
    Eric Blasenheim
    Spectrum Spatial Technical Product Manager
    Troy, NY
    ------------------------------



  • 25.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-23-2020 03:24
    Hi Miquel

    You should use this string: "DRIVER=SQL Server Native Client 11.0"

    But also make sure, as Duri said, that you have the driver installed on your system. If you are using a Data Source I'd recommend changing your existing Data Source to use the SQL Server Native Client.

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------



  • 26.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-24-2020 22:38
    Thank you Peter,  I will try with that driver.

    Thank you!

    ------------------------------
    Alejandro Hernandez
    Information Technology & Services
    ITS SA (GBD)
    Buenos Aires
    ------------------------------



  • 27.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-25-2020 04:43
    Hi Peter,

    I've tried using the SQL Server Native Client 11.0, as suggested by Duri, but no luck. I get exactly the same performance. This is the code I use:

    "\IsReadOnly" = "FALSE"
    "\DATALINK" = ""
    "\DATALINK\Query" = "select * from DATABASE.dbo.TABLE"
    "\DATALINK\ConnectionString" = "DRIVER=SQL Server Native Client 11.0;SERVER=SERVERNAME;DATABASE=DATABASENAME;Trusted_connection=yes;MARS_Connection=Yes"
    "\DATALINK\ToolKit" = "ODBC"
    "\CACHE" = "OFF"


    ------------------------------
    Miquel Roy Sunyer
    Kirklees Council
    HUDDERSFIELD
    ------------------------------



  • 28.  RE: Improve performance of linked TAB files in MapInfo

    Posted 03-25-2020 07:18
    Hi Miquel

    Please try these steps just to make sure we are at the same level

    1. Make sure that you have installed the "SQL Server Native Client 11.0" driver onto your computer
    2. Start MapInfo Pro and open a connection to your database using the "SQL Server Native Client 11.0" driver. I'd create a data source and use this to connect to the database.
    3. When you are connected try to run these statements from the MapBasic window as I mentioned above. Modify the statements to your table and paths

    Close All
    Print Time(24) & " Start"
    Dim sTab As String
    sTab = "FOT_Roads"
    Server 1 Link Table "Select * From dbo." + sTab + " Where MI_PRINX < 1000000" toolkit "ODBC"
    Into sTab
    File "C:\Data\" + sTab + ".tab"
    TYPE NATIVE Charset "WindowsLatin1" Map From sTab
    Print Time(24) & " Done. " + FormatNumber$(TableInfo(0, TAB_INFO_NROWS)) + " records loaded"

    Do you see improved performance?

    ------------------------------
    Peter Horsbøll Møller
    Distinguished Engineer
    Pitney Bowes Software & Data
    ------------------------------