MapInfo Pro Developers User Group

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

!table!version 1000!charset WindowsLatin1Definition Table  Type ODBCbegin_metadata"\IsReadOnly" = "FALSE""\MapInfo" = """\DATALINK" = """\DATALINK\Query" = "Select *  From ""GazetteerDB"".""dbo"".""AddressGaz""""\DATALINK\ConnectionString" = "DRIVER=SQL Server Native Client 11.0;DSN=GazetteerDB;Description=GazetteerDB;Trusted_Connection=Yes;SERVER=SPATIAL;DATABASE=GazetteerDB;MARS_Connection=yes""\DATALINK\ToolKit" = "ODBC""\CACHE" = "OFF""\MBRSEARCH" = "ON"end_metadata​

Secondly, I have been making sure that I am using the right driver, as suggested by many users in this discussion. I can say 100% that I am using SQL Server Native Client 11.0. I'd like to include some images in my answer but apparently the image uploader here doesn't work. However, I still have the same performance issue.

Then I've managed to make your code above work with some modifications. Except from the last line "FormatNumber$(TableInfo(0, TAB_INFO_NROWS)) + " records loaded"", because TAB_INFO_NROWS is not recognized.. So I've used:

Close AllPrint Time(24) & " Start"Dim sTab As StringsTab = "AddressGaz"Server 1 Link Table "Select * From dbo." + sTab toolkit "ODBC" Into sTab File "C:\Users\Miquelroy-sunyer\Desktop\" + sTab + ".tab" TYPE NATIVE Charset "WindowsLatin1" Map From sTabPrint Time(24) & " Done. 

This code creates a tab file in my desktop, which is about 315MB, and it takes about 10 minutes. After that, what I have is a linked tab file, which works fast because data are local.

BTW, this table in particular has NUM RECORDS: 240000 and NUM COLUMNS: 25

And I can include what's in the Catalog table, as suggested above by @William Wemple:
SPATIALTYPE     17
TABLENAME      AddressGaz
OWNERNAME      dbo
SPATIALCOLUMN      SP_GEOMETRY
DB_X_LL      400363
DB_Y_LL      403681
DB_X_UR      428365
DB_Y_UR      429832
VIEW_X_LL      414150
VIEW_Y_LL      417181
VIEW_X_UR      414184.41
VIEW_Y_UR      417203
COORDINATESYSTEM      Earth Projection 8, 79, "m", -2, 49, 0.9996012717, 400000, -100000 Bounds (0, -100000) (2000000, 1900000)
SYMBOL      Symbol (34, 16777087, 12)
XCOLUMNNAME      NO_COLUMN
YCOLUMNNAME      NO_COLUMN
RENDITIONTYPE      1
RENTITIONCOLUMN      MI_STYLE
RENDITIONTABLE
NUMBER_ROWS      NULL


The same table in QGIS loads in <5 seconds, full extent.

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.

!table!version 1000!charset WindowsLatin1Definition Table  Type ODBCbegin_metadata"\IsReadOnly" = "FALSE""\MapInfo" = """\DATALINK" = """\DATALINK\Query" = "Select *  From ""GazetteerDB"".""dbo"".""AddressGaz""""\DATALINK\ConnectionString" = "DRIVER=SQL Server Native Client 11.0;DSN=GazetteerDB;Description=GazetteerDB;Trusted_Connection=Yes;SERVER=SPATIAL;DATABASE=GazetteerDB;MARS_Connection=yes""\DATALINK\ToolKit" = "ODBC""\CACHE" = "OFF""\MBRSEARCH" = "ON"end_metadata​

Secondly, I have been making sure that I am using the right driver, as suggested by many users in this discussion. I can say 100% that I am using SQL Server Native Client 11.0. I'd like to include some images in my answer but apparently the image uploader here doesn't work. However, I still have the same performance issue.

Then I've managed to make your code above work with some modifications. Except from the last line "FormatNumber$(TableInfo(0, TAB_INFO_NROWS)) + " records loaded"", because TAB_INFO_NROWS is not recognized.. So I've used:

Close AllPrint Time(24) & " Start"Dim sTab As StringsTab = "AddressGaz"Server 1 Link Table "Select * From dbo." + sTab toolkit "ODBC" Into sTab File "C:\Users\Miquelroy-sunyer\Desktop\" + sTab + ".tab" TYPE NATIVE Charset "WindowsLatin1" Map From sTabPrint Time(24) & " Done. 

This code creates a tab file in my desktop, which is about 315MB, and it takes about 10 minutes. After that, what I have is a linked tab file, which works fast because data are local.

BTW, this table in particular has NUM RECORDS: 240000 and NUM COLUMNS: 25

And I can include what's in the Catalog table, as suggested above by @William Wemple:
SPATIALTYPE     17
TABLENAME      AddressGaz
OWNERNAME      dbo
SPATIALCOLUMN      SP_GEOMETRY
DB_X_LL      400363
DB_Y_LL      403681
DB_X_UR      428365
DB_Y_UR      429832
VIEW_X_LL      414150
VIEW_Y_LL      417181
VIEW_X_UR      414184.41
VIEW_Y_UR      417203
COORDINATESYSTEM      Earth Projection 8, 79, "m", -2, 49, 0.9996012717, 400000, -100000 Bounds (0, -100000) (2000000, 1900000)
SYMBOL      Symbol (34, 16777087, 12)
XCOLUMNNAME      NO_COLUMN
YCOLUMNNAME      NO_COLUMN
RENDITIONTYPE      1
RENTITIONCOLUMN      MI_STYLE
RENDITIONTABLE
NUMBER_ROWS      NULL


The same table in QGIS loads in <5 seconds, full extent.

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.

!table!version 1000!charset WindowsLatin1Definition Table  Type ODBCbegin_metadata"\IsReadOnly" = "FALSE""\MapInfo" = """\DATALINK" = """\DATALINK\Query" = "Select *  From ""GazetteerDB"".""dbo"".""AddressGaz""""\DATALINK\ConnectionString" = "DRIVER=SQL Server Native Client 11.0;DSN=GazetteerDB;Description=GazetteerDB;Trusted_Connection=Yes;SERVER=SPATIAL;DATABASE=GazetteerDB;MARS_Connection=yes""\DATALINK\ToolKit" = "ODBC""\CACHE" = "OFF""\MBRSEARCH" = "ON"end_metadata​

Secondly, I have been making sure that I am using the right driver, as suggested by many users in this discussion. I can say 100% that I am using SQL Server Native Client 11.0. I'd like to include some images in my answer but apparently the image uploader here doesn't work. However, I still have the same performance issue.

Then I've managed to make your code above work with some modifications. Except from the last line "FormatNumber$(TableInfo(0, TAB_INFO_NROWS)) + " records loaded"", because TAB_INFO_NROWS is not recognized.. So I've used:

Close AllPrint Time(24) & " Start"Dim sTab As StringsTab = "AddressGaz"Server 1 Link Table "Select * From dbo." + sTab toolkit "ODBC" Into sTab File "C:\Users\Miquelroy-sunyer\Desktop\" + sTab + ".tab" TYPE NATIVE Charset "WindowsLatin1" Map From sTabPrint Time(24) & " Done. 

This code creates a tab file in my desktop, which is about 315MB, and it takes about 10 minutes. After that, what I have is a linked tab file, which works fast because data are local.

BTW, this table in particular has NUM RECORDS: 240000 and NUM COLUMNS: 25

And I can include what's in the Catalog table, as suggested above by @William Wemple:
SPATIALTYPE     17
TABLENAME      AddressGaz
OWNERNAME      dbo
SPATIALCOLUMN      SP_GEOMETRY
DB_X_LL      400363
DB_Y_LL      403681
DB_X_UR      428365
DB_Y_UR      429832
VIEW_X_LL      414150
VIEW_Y_LL      417181
VIEW_X_UR      414184.41
VIEW_Y_UR      417203
COORDINATESYSTEM      Earth Projection 8, 79, "m", -2, 49, 0.9996012717, 400000, -100000 Bounds (0, -100000) (2000000, 1900000)
SYMBOL      Symbol (34, 16777087, 12)
XCOLUMNNAME      NO_COLUMN
YCOLUMNNAME      NO_COLUMN
RENDITIONTYPE      1
RENTITIONCOLUMN      MI_STYLE
RENDITIONTABLE
NUMBER_ROWS      NULL


The same table in QGIS loads in <5 seconds, full extent.

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.

!table!version 1000!charset WindowsLatin1Definition Table  Type ODBCbegin_metadata"\IsReadOnly" = "FALSE""\MapInfo" = """\DATALINK" = """\DATALINK\Query" = "Select *  From ""GazetteerDB"".""dbo"".""AddressGaz""""\DATALINK\ConnectionString" = "DRIVER=SQL Server Native Client 11.0;DSN=GazetteerDB;Description=GazetteerDB;Trusted_Connection=Yes;SERVER=SPATIAL;DATABASE=GazetteerDB;MARS_Connection=yes""\DATALINK\ToolKit" = "ODBC""\CACHE" = "OFF""\MBRSEARCH" = "ON"end_metadata​

Secondly, I have been making sure that I am using the right driver, as suggested by many users in this discussion. I can say 100% that I am using SQL Server Native Client 11.0. I'd like to include some images in my answer but apparently the image uploader here doesn't work. However, I still have the same performance issue.

Then I've managed to make your code above work with some modifications. Except from the last line "FormatNumber$(TableInfo(0, TAB_INFO_NROWS)) + " records loaded"", because TAB_INFO_NROWS is not recognized.. So I've used:

Close AllPrint Time(24) & " Start"Dim sTab As StringsTab = "AddressGaz"Server 1 Link Table "Select * From dbo." + sTab toolkit "ODBC" Into sTab File "C:\Users\Miquelroy-sunyer\Desktop\" + sTab + ".tab" TYPE NATIVE Charset "WindowsLatin1" Map From sTabPrint Time(24) & " Done. 

This code creates a tab file in my desktop, which is about 315MB, and it takes about 10 minutes. After that, what I have is a linked tab file, which works fast because data are local.

BTW, this table in particular has NUM RECORDS: 240000 and NUM COLUMNS: 25

And I can include what's in the Catalog table, as suggested above by @William Wemple:
SPATIALTYPE     17
TABLENAME      AddressGaz
OWNERNAME      dbo
SPATIALCOLUMN      SP_GEOMETRY
DB_X_LL      400363
DB_Y_LL      403681
DB_X_UR      428365
DB_Y_UR      429832
VIEW_X_LL      414150
VIEW_Y_LL      417181
VIEW_X_UR      414184.41
VIEW_Y_UR      417203
COORDINATESYSTEM      Earth Projection 8, 79, "m", -2, 49, 0.9996012717, 400000, -100000 Bounds (0, -100000) (2000000, 1900000)
SYMBOL      Symbol (34, 16777087, 12)
XCOLUMNNAME      NO_COLUMN
YCOLUMNNAME      NO_COLUMN
RENDITIONTYPE      1
RENTITIONCOLUMN      MI_STYLE
RENDITIONTABLE
NUMBER_ROWS      NULL


The same table in QGIS loads in <5 seconds, full extent.

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.

!table!version 1000!charset WindowsLatin1Definition Table  Type ODBCbegin_metadata"\IsReadOnly" = "FALSE""\MapInfo" = """\DATALINK" = """\DATALINK\Query" = "Select *  From ""GazetteerDB"".""dbo"".""AddressGaz""""\DATALINK\ConnectionString" = "DRIVER=SQL Server Native Client 11.0;DSN=GazetteerDB;Description=GazetteerDB;Trusted_Connection=Yes;SERVER=SPATIAL;DATABASE=GazetteerDB;MARS_Connection=yes""\DATALINK\ToolKit" = "ODBC""\CACHE" = "OFF""\MBRSEARCH" = "ON"end_metadata​

Secondly, I have been making sure that I am using the right driver, as suggested by many users in this discussion. I can say 100% that I am using SQL Server Native Client 11.0. I'd like to include some images in my answer but apparently the image uploader here doesn't work. However, I still have the same performance issue.

Then I've managed to make your code above work with some modifications. Except from the last line "FormatNumber$(TableInfo(0, TAB_INFO_NROWS)) + " records loaded"", because TAB_INFO_NROWS is not recognized.. So I've used:

Close AllPrint Time(24) & " Start"Dim sTab As StringsTab = "AddressGaz"Server 1 Link Table "Select * From dbo." + sTab toolkit "ODBC" Into sTab File "C:\Users\Miquelroy-sunyer\Desktop\" + sTab + ".tab" TYPE NATIVE Charset "WindowsLatin1" Map From sTabPrint Time(24) & " Done. 

This code creates a tab file in my desktop, which is about 315MB, and it takes about 10 minutes. After that, what I have is a linked tab file, which works fast because data are local.

BTW, this table in particular has NUM RECORDS: 240000 and NUM COLUMNS: 25

And I can include what's in the Catalog table, as suggested above by @William Wemple:
SPATIALTYPE     17
TABLENAME      AddressGaz
OWNERNAME      dbo
SPATIALCOLUMN      SP_GEOMETRY
DB_X_LL      400363
DB_Y_LL      403681
DB_X_UR      428365
DB_Y_UR      429832
VIEW_X_LL      414150
VIEW_Y_LL      417181
VIEW_X_UR      414184.41
VIEW_Y_UR      417203
COORDINATESYSTEM      Earth Projection 8, 79, "m", -2, 49, 0.9996012717, 400000, -100000 Bounds (0, -100000) (2000000, 1900000)
SYMBOL      Symbol (34, 16777087, 12)
XCOLUMNNAME      NO_COLUMN
YCOLUMNNAME      NO_COLUMN
RENDITIONTYPE      1
RENTITIONCOLUMN      MI_STYLE
RENDITIONTABLE
NUMBER_ROWS      NULL


The same table in QGIS loads in <5 seconds, full extent.

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.