MapInfo Pro

 View Only
  • 1.  MapInfo Monday: Live versus Linked DBMS Tables

    Employee
    Posted 02-19-2024 03:54

    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.

    As we discussed in an earlier article, you can access data from a database in three ways:
    1. Download the data into a Linked Table
    2. Live Access with Cache
    3. 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.
    The data is copied from the database table to the MapInfo table when you create the linked table and when you refresh it. The data is downloaded to the same location as the TAB file. This does of course also mean that you have duplicate data: one set in the database and another in the locale TAB file.
    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.

    Access Data via a Live 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.

    Access Data via a Live with 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.

    Access Data via a Live without Cache Table

    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
    ------------------------------


  • 2.  RE: MapInfo Monday: Live versus Linked DBMS Tables

    Posted 02-23-2024 01:42

    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
    ------------------------------



  • 3.  RE: MapInfo Monday: Live versus Linked DBMS Tables

    Employee
    Posted 26 days ago
    Edited by Peter Møller 26 days ago

    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
    ------------------------------



  • 4.  RE: MapInfo Monday: Live versus Linked DBMS Tables

    Posted yesterday

    Hi Peter,

    Mostly viewing and editing, not so may queries but I will try and find some time to do some tests.

    Always LIVE with Cache



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