MS SQL Server 2008 performance within Mapinfo

[Brendan Stone]

Hi all,

We have various datasets stored in MS SQL Server 2008 which are used by various systems including MapInfo.

The performance of these tables, which are admittedly quite large (For example OS AddressBase with 1.8 million records/points) is performs terribly within Mapinfo.

I have tried the live / live with cache options when opening DBMS tables but to load even a tiny area, e.g scale of 1:200 viewing a few houses, can take a few minutes just to return a few points.

Panning the map results in more long waits. Frustratingly, you can wait a long time for the data to 'load' only for it to appear there is no data in this area even though there most definitely is - it's just not displaying it.

Having never used MS SQL spatial within Mapinfo I assumed this is just how it is and decided to store a local TAB file for this dataset in addition to the SQL version - this TAB version performs well.

However, recently we have been using Cadcorp SIS software to maintain our web based GIS system and the Cadcorp desktop software running on the same machine has absolutely no issues with these same SQL tables - in fact it's lightning quick in comparison to Mapinfo!

You could load and view the entire dataset in less than a minute, while Mapinfo will take several minutes to display just a tiny subset of the data.

So my question is, what sort of performance should be expected from Mapinfo when loading SQL spatial data? 

I don't expect Mapinfo to match Cadcorp's performance as that's pretty impressive and not even a local TAB file is that quick, but I still suspect something is not quite right with our Mapinfo setup or spatial index so am looking for advice and suggestions.

A few specs and details:

Mapinfo 12.0.2

Win XP

3ghz Core2Duo

3gb RAM

The AddressBase table in question has a geometry column, a mi_prinx column, a primary key (UPRN column), a spatial index on the geometry column and a few other indexes.

The Mapinfo.mapcatalog record for this table is populated and has the correct "Number_rows" value (I know this is sometimes an issue)

SpatialType is 17.3 which I'm not sure about, shouldn't it be 17.0 as it only contains points?

Thanks for reading.

Brendan

[Thomas Bacon]

I had similar issues when accessing an SQLite database. Interested to see whether any possible solution might resolve my problem as well.

 

Tom Bacon

GIS Engineer, Mouchel

T 01444 472380 │ E thomas...@mouchel.com │ W www.mouchel.com

Our values: innovation │ excellence │ integrity │ responsibility

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en
 
---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Mouchel Limited (Mouchel) is registered in England and Wales with registered number 01686040 at Export House, Cawsey Way, Woking, Surrey, UK, GU21 6QX.  The information in this e-mail is confidential and may be legally privileged. It is intended solely for the addressee. Access to this email by anyone else is unauthorised. Any views or opinions expressed in this e-mail may be solely those of the author and are not necessarily those of Mouchel. No contracts may be concluded on behalf of Mouchel by means of email communications. Mouchel reserves the right to monitor and intercept emails sent and received on our network.

[Mike Osbourn]

Brendan,

 

Try creating a linked table.  It is pretty much the same as a “native” table.  Editing/changing data will of course take a good while to refresh but if the majority of your work is to display the data then a linked table should work fine.

 

I think for live/live cached tables was changed in v 11 or 11.5 to include the MARS parameter in the connection string.  You may want to open your connection string in notepad to make sure it’s part of the string. This parameter does help a good bit for live tables but probably not to your satisfaction.

 

I don’t use live connections on hardly any of our files since the performance is so bad !!

 

I’ve found the bottleneck is probably the ODBC connection and speed that it’s limited to.  I’d be interested to see if the CadCorp software is utilizing an ODBC connection to the database.

 

Anyway, give it a shot and see how it works.

 

HTH

 

Mike

 

 

From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On Behalf Of Brendan Stone
Sent: Tuesday, February 04, 2014 8:42 AM
To: mapi...@googlegroups.com
Subject: [MI-L] MS SQL Server 2008 performance within Mapinfo

 

Hi all,

--

[Lars I. Nielsen (GisPro)]

Hi Brendan,

Have you checked whether there is a spatial index on the server table ?

In general, without proper indexing, live access will perform badly.

The TAB always perform good, because there's always a spatial index. On MS/SQL you have to add/build it manually.

Best regards / Med venlig hilsen
Lars I. Nielsen
GIS & DB Integrator
GisPro

[Peter Horsbøll Møller]

The problem could be the spatial index. And the MARS connection should definitely also give you a better performance.

If you reopen the table from the database, the MARS connection should be used automatically

I just did a test with a address dataset for Denmark holding close to 2.4 mio points.

I uploaded it to my SQL Server 2008 database (which took my around half an hour)

Now I can access this table from MapInfo Pro 12.0.2 with a live with cache table.

The initial opening of the table took me around 5 secs.

When I now pan around the map, it takes less than a second to get the adresses within the new area.

In the MapCatalog my SpatialType is set to 17.0 and I have chosen not to use the Per Row Style.

You could try to remove the registration from the MapCatalog and make it mappable again thru Table > Maintenance > Make DBMS Table Mappable.

For the spatial index, I just allowed EasyLoader to create it.

Peter Horsbøll Møller

Pitney Bowes Software

---

Date: Wed, 5 Feb 2014 09:30:09 +0100
From: L...@gispro.dk
To: mapi...@googlegroups.com
Subject: Re: [MI-L] MS SQL Server 2008 performance within Mapinfo

[Brendan Stone]

Hi All, 

Thanks for the replies and sorry for the delay in getting back - this had to be put on the back burner for a while.

Mike Osbourn, 

A linked table wouldn't give us any benefit really - the data doesn't update that often so we may as well just use a native TAB file.

It would just be nice to store all our data in SQL and not have copies for different software. 

I tried adding the MARS parameter but couldn't notice any improvement.

L...@gispro.dk,

Yes, the tables in question all have spatial indexes - These are required by Cadcorp so I know they are working (for Cadcorp at least).

Peter Horsbøll Møller, 

As above, I have tried adding the MARS parameter to the TAB file (it's not there even if I create a completely new TAB) but I didn't notice any improvements.

I have now unregistered the table from Mapinfo Mapcatalog and re-added it via Table > Maintenance > Make DBMS Table Mappable but this hasn't seemed to make any difference.

The datasets were not uploaded using EasyLoader (it was quicker to use SSIS) so perhaps Mapinfo doesn't like the indexes I created.

I shall try uploading a dataset via EasyLoader and see if that is the issue.

Thanks for all the suggestions thus far.

Brendan

[Brendan Stone]

Well I eventually solved this issue and now performance is massively improved.

Despite the SQL table definitely having a spatial index it seems it was not being used when opening the table in MapInfo.

To get around this, when registering the DBMS table I had to use the Expert Mode "SQL View" option and specify the full SQL query, e.g:

Select * From "GIS"."dbo"."AddressBase_Final"  WITH(INDEX(SX_Geom))

The important bit is at the end to force the use of the spatial index (SX_Geom)

No idea why it's not being used by default, I'm not sure if its a MapInfo or a SQL Server issue, but I'm glad this is resolved, we can now finally do a few things in MapBasic which in the past have been excruciatingly slow.

Hope this helps anyone who is may experience a similar problem in the future.

Brendan

[Eric Blasenheim]

Brendan, 

A few things to note about your issue and the way you solved it.  

1 ) When Professional opens a live table it does a number of things before any mapping is done and these never use the spatial index.  It does do a row count but perhaps your mapcatalog piece fixes that. It checks the columns and the types to set up the client side.  It also does a query of the table ordered by primary key. This is how the rows get their rowid. It then fetches 50 rows from that query.  It may do something else but that is what I remember.  You can check out if this is part of the problem by choosing to open the tab file created (with the query,etc) with "No view".  If that is slow then something in that initialization is not working well. 

2) If you map the data and this is the first layer in the map, the default bounds will be that of the entire table which for a large table is not good. There is a place in the MapCatalog to store a default view which you could try. If you add any other table first that is in that area and then add the spatial table, the default will be wherever you were at when you added the table.  If this makes a huge difference  then that points to the problem of us accessing the data. But you probably don't want all those records in the map at the same time. 

As to your workaround, I would caution you that there are other consequences and understanding the behavior of WITH(INDEX...) is critical.   Professional will now use this query as the basis for everything it does with this table.  For example, as you access data in a browser window or click on geometries in the map or use labels,  Professional will be sending queries to the database like  Select * From "GIS"."dbo"."AddressBase_Final"  WITH(INDEX(SX_Geom)) and where primary_key_Col = value.  

Basically anything we do to add to the query will contain the base expression you created. As I don't know the exact behavior of the WITH clause, I can't say what the result will be.  

As far as Linked tables go, I still think this is worth trying. Linked is no different really than a cache.The fact that you see the cache is perhaps something we should offer but in reality, you would not expect your web browser to go back to the server, every time you scrolled the page. Live with no cache will hit that database constantly. A click selection will query the database 3 times for the selected object and more if there are other objects in the area.  

Since your data does not change frequently, you are still guaranteed of the source and you get the best performance. The expense happens only when data changes and you can refresh whenever you want. Labels, themes, querying will all be much better. 

Regards, 

Eric Blasenheim

Pitney Bowes Software

On Friday, January 29, 2016 at 9:48:43 AM UTC-5, Brendan Stone wrote:

Well I eventually solved this issue anhen registering the DBMS table I had to use the Expert Mode "SQL View" option and specify the full SQL query, e.g:

[Mike Osbourn]

Brendan,

Could you take some screen shots of your process. I'm also assuming you are working with live tables. 

Sent from my T-Mobile 4G LTE Device

-------- Original message --------
From: Brendan Stone <nad...@gmail.com>
Date: 1/29/2016 9:48 AM (GMT-05:00)
To: MapInfo-L <mapi...@googlegroups.com>
Subject: Re: [MI-L] MS SQL Server 2008 performance within Mapinfo

Well I eventually solved this issue and now performance is massively improved.

Despite the SQL table definitely having a spatial index it seems it was not being used when opening the table in MapInfo.

To get around this, when registering the DBMS table I had to use the Expert Mode "SQL View" option and specify the full SQL query, e.g:

Select * From "GIS"."dbo"."AddressBase_Final"  WITH(INDEX(SX_Geom))

The important bit is at the end to force the use of the spatial index (SX_Geom)

For more options, visit https://groups.google.com/d/optout.

[Brendan Stone]

Hi Eric,

Thanks for the reply.

1) Opening with no view is very quick.

2) If I zoom right into a map to view a single property footprint and only then add the problematic table to the map it will then take several minutes to bring up the address record for this single property. Make the mistake of loading the layer while viewing at the scale of a small neighbourhood and you may as well forget it and go home for the day. It is clearly not using the spatial index.

You can recreate this behaviour within SQL server by disabling the index and then searching the table for any points within a bounding box - this takes a long time to process. Enable the spatial index, run the same query and it's almost instant.

I believe the WITH command is just hinting to SQL to use that particular index as for some reason it is not using it. I don't think that should cause any issues with subsequent queries, after all they are lightning quick unlike when you don't use this command which takes forever and puts a lot of strain on the server.

Using a linked table is not an option - the table in question now contains 38 million address records (we have now increased to include national coverage) and is approximately 13 gb in size. 

Anyway, with this fix, performance of this SQL table in MapInfo is now in line with performance of the same table in Cadcorp SIS and QGIS which have always been lighting quick out of the box.

Regards,

Brendan

[Brendan Stone]

Mike,

I'm adding this layer via MapBasic so there's nothing really to see.

I'm pulling records within approximately 1 km from a given point into a linked table and overlaying them on a map embedded in a dialog so it's a little complex but the important part is this

sub addNearbyAddressesToMap

Dim query as string

Dim x1,x2,y1,y2 as integer

x1 = MapperInfo(frontwindow(), MAPPER_INFO_MINX)-500

x2 = MapperInfo(frontwindow(), MAPPER_INFO_MAXX)+500

y1 = MapperInfo(frontwindow(), MAPPER_INFO_MINY)-500

y2 = MapperInfo(frontwindow(), MAPPER_INFO_MAXY)+500

query = "select ""UPRN"", ""OBJECT"" from ""GIS"".""dbo"".""AddressBase_Final"" WITH(INDEX(SX_Geom))  where ""geom"".STIntersects((geometry::STPolyFromText('POLYGON (("+x1+" "+y1+", "+x2+" "+y1+", "+x2+" "+y2+", "+x1+" "+y2+", "+x1+" "+y1+"))', 27700))) = 1"

Dim hdbc As Integer

hdbc =  Server_Connect("ODBC","DSN=xxxx;Trusted_Connection=Yes;DATABASE=xxxx;")

Server hdbc link table

query

Toolkit "ODBC"

Into nearbyAddresses

Autokey ON

ReadOnly

Add Map Window frontWindow() Auto Layer nearbyAddresses

Set Map Window frontWindow()  Layer 1 Display Global     Global Symbol (34,4227327,7)  

end sub

Without the  "WITH(INDEX(SX_Geom))" part this query will take an eternity to load but with it the whole sub takes less than a second.