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.
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.
Original Message:
Sent: 03-25-2020 05:06
From: Miquel Roy Sunyer
Subject: Improve performance of linked TAB files in MapInfo
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
Original Message:
Sent: 03-20-2020 16:09
From: Eric Blasenheim
Subject: Improve performance of linked TAB files in MapInfo
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:
- 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.
- 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
Original Message:
Sent: 03-20-2020 13:10
From: Miquel Roy Sunyer
Subject: Improve performance of linked TAB files in MapInfo
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
Original Message:
Sent: 03-20-2020 12:39
From: Andrei Veselov
Subject: Improve performance of linked TAB files in MapInfo
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:
<madcap:concept term="MapInfo">"\DATALINK\ConnectionString" = "DSN=local-native10;UID=username;Trusted_Connection=Yes;APP=MapInfo Pro;WSID=hostname;DATABASE=test;MARS_Connection=Yes;"</madcap:concept>
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
Original Message:
Sent: 03-20-2020 11:55
From: Miquel Roy Sunyer
Subject: Improve performance of linked TAB files in MapInfo
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
Original Message:
Sent: 03-20-2020 08:30
From: Bill Wemple
Subject: Improve performance of linked TAB files in MapInfo
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
Original Message:
Sent: 03-13-2020 13:23
From: Miquel Roy Sunyer
Subject: Improve performance of linked TAB files in MapInfo
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
------------------------------