Mostly viewing and editing, not so may queries but I will try and find some time to do some tests.
Original Message:
Sent: 03-20-2024 03:55
From: Peter Møller
Subject: MapInfo Monday: Live versus Linked DBMS Tables
Thanks, Duri.
That's great feedback. It's hard to determine performance issues as my database is on my local PC and my data is on my local PC. Good with some real-life validation.
Do you also run queries on your live tables or is it more for viewing?
And are you using Live with or without Cache?
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
Original Message:
Sent: 02-23-2024 01:41
From: Duri Bradshaw
Subject: MapInfo Monday: Live versus Linked DBMS Tables
Good write up Peter.
I have started using LIVE more and more recently and found that it works just about as well as LINKED when your linked tables are stored on a network drive, one major advantage of LIVE over LINKED is you don't need to refresh your LINKED tables if the source data has changed, which can be a major issue if you have other systems modifying your SQL data. Granted if the data is large it will be much faster to use LINKED on a local drive, so if performance is key and you are working with large data this is certainly the way to go.
------------------------------
Duri Bradshaw
Spatial IT Consultant
Insight GIS
Original Message:
Sent: 02-19-2024 03:53
From: Peter Møller
Subject: MapInfo Monday: Live versus Linked DBMS Tables
Happy #MapInfoMonday!
This is yet an article on the topic of DBMS in MapInfo Pro. This time, we'll dive a bit into the differences between Linked and Live DBMS Tables.
- Download the data into a Linked Table
- Live Access with Cache
- Live Access
I have listed them intentionally in this way. Let's inspect the various options a bit closer.
Download Data into a Linked Table
This is probably the method I would suggest using in most situations.
You can now access the data by opening the tab file that you saved. Access to the database is only needed when you create the table, save changes to the table, or refresh the table.
It is possible to work offline as your data is locally available, ie. where you saved the table. If you make changes to the data, long transactions are supported meaning you don't have to save these changes to the database but they can be maintained in local transactions files until you get online again.
The speed is as fast as a normal MapInfo table once you have downloaded the data. Using a query to get just the data you need, will speed up download time. Queries are set when you create the linked table via the Row Picker dialog and the Column Picker dialog, or the Enter SQL Query dialog.
Data can be accessed read-only without a primary key.
You will have to refresh the table to get potential new or changed data downloaded into the local table.
When you create a table using a Live with or without Cache, MapInfo Pro will not save any data with the table. When you create the table, MapInfo Pro will query the data using the extent of the map window it will be added to. Only the data intersecting the map extent will get downloaded.
This also means that it may be faster the first time you open the table in MapInfo Pro compared to a Linked Table. However, this depends on the zoom of your map window as that determines the data that will be fetched from the database table.
We recommend setting zoom levels on your live tables to prevent them from fetching all data from the database by accident if you do a Zoom Entire Map.
It is not recommended to run SQL Select statements against live tables. MapInfo Pro will fetch all records from the data and you would be far better off using a linked table here.
The database table must have a primary key as this is required for a Live Table.
As the data is read "live" from the database, you also need to have access to the database. This doesn't work offline.
Let's inspect the difference between using a Live With Cache table and a Live Without Cache Table.
If you enable cache, MapInfo Pro keeps a local temporary copy of the data that has been fetched from the database. The cache is progressive. As you zoom and pan around the map, the cache will grow and hold more and more data.
In many cases, MapInfo Pro will access the local cache. This is for example the case when using the Info tool in the map window.
The cache is cleared when the table is closed.
MapInfo Pro will always read directly from the database.
Every redraw of the map and every zoom or pan will fetch data from the database table. When you use the Info tool to query a point on the map, MapInfo Pro will fetch data from the database.
It's not recommended to use Snap with a Live without Cache Table.
Recommendations
Let's try to summarize some recommendations:
Live Access can be a good choice if your data is highly volatile, ie it changes by the minute. But be cautious as it may get slow with a high volume of data. Use zoom levels to prevent MapInfo Pro from fetching too large portions from the database.
If you have a very small data size, you may not be able to see any difference in using Live Access, Live Access with Cache, and Linked Table.
If you need to run queries on your data inside MapInfo Pro, use a Linked Table.
To prevent unauthorized access to data, Live Access tables can be a good choice as they require the user to connect to the database.
There's also a good section on Live versus Linked in the Precisely Help Center: Deciding to work with Linked or Live Access Tables
What are your recommendations and how do you decide to use a Live Access Table or a Linked Table?
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------