MapInfo 12.5 64-bit 'stops responding' fetching view table from SQL Server.

[Mubashar Ahmed]

MapInfo 12.5 64-bit on 64-bit Windows Server 2008

MapInfo Pro is (Not Responding) when I try to download (linked with cache) a view table in MS SQL Server 2008. The table is not made mappable. The view script (see below) in Server performs spatial joins with mappable tables.

I have not downloaded this table before so am unsure if the time is being taken by Mapinfo or the server. However, if MapInfo is waiting for the server why does it display (Not responding)?

It has happened with some other large tables. MapInfo says (Not Responding) and after some time returns with the table.

Has it crashed this time? I am still waiting...

Here is the view script which executes fine in the server (I did not change the script or the tables).

CREATE VIEW [dbo].[vw_VM_Parcel_Zones]

AS

SELECT      Q.PARCEL_PFI

,Q.PARCEL_CREFNO

,Q.LGA_CODE

,Q.SCHEME_CODE

,Q.ZONE_CODE

, CAST(Q.P_Proportion_Overlap AS VarChar) + '%' AS  PARCEL_Proportion_Overlap

, CAST(Q.HA as VARCHAR) + 'ha | ' + CAST(Q.m2 AS VARCHAR) +' m2' as PARCEL_Area_Overlap

, PLL.DESCRIPTION

FROM 

(SELECT     p_mp.PARCEL_PFI

, p_mp.PARCEL_CREFNO

, p_mp.PARCEL_LGA_CODE AS LGA_CODE

, pz.SCHEME_CODE

, pz.ZONE_CODE

, CASE p_mp.SP_Geometry.STIntersection(pz.SP_GEOMETRY ).STArea() 

WHEN 0 THEN 0 

ELSE Round( (p_mp.SP_Geometry.STIntersection(pz.SP_GEOMETRY ).STArea()) / p_mp.SP_GEOMETRY.STArea() * 100,2)  

END AS P_Proportion_Overlap

, ROUND(p_mp.SP_GEOMETRY.STIntersection(pz.SP_GEOMETRY ).STArea() / 10000,3) AS HA

,Round(p_mp.SP_GEOMETRY.STIntersection(pz.SP_GEOMETRY ).STArea(),3) as M2 

  

FROM         dbo.DSE_PARCEL_MP AS p_mp CROSS JOIN

 dbo.DSE_PLAN_ZONE AS pz 

WHERE     (p_mp.SP_GEOMETRY.STIntersects(pz.SP_GEOMETRY) = 1)

) AS Q INNER JOIN

                      dbo.DSE_RW_PLANNING_LGA_LIST AS PLGA ON Q.LGA_CODE = PLGA.MUN_NUM INNER JOIN

                      dbo.DSE_RW_PLANNING_LINK_LIST AS PLL ON Q.ZONE_CODE = PLL.ZONE_CODE

                     

WHERE q.P_Proportion_Overlap > 0.01 or Q.HA > 0.01 

[Mubashar Ahmed]

I had to go to Task manager and abort 'Not responding' MapInfo. Will try again later.

[Mubashar Ahmed]

In Windows Explorer I can observe the MapInfo files size changing slowly. So it is happening slowly. I think there may be 30000 records which explains the delay.

I still don't like that Mapinfo 'Not Responding' title in top bar. How about changing it to 'Busy'?

[Ross Nixon]

"Not Responding" Is Microsoft Windows taking a guess at what is happening.
Checking files sizes or Task Manager CPU usage and I/O will indicate if a process is frozen, or not.

Ross

[Mubashar Ahmed]

LOL! Perhaps it would help if MapInfo could tell Windows what to display in the top bar instead of '(Not Responding)'

Mapinfo is not frozen just very very slow. In the last two hours the *.dat file size has gone up to 9,165kB. I forgot to mention, this time I am just refreshing the existing linked table, not creating a new downloaded one from SQL Server because that was taking too long yesterday.

I would like to know why so much delay in MapInfo in refreshing a table when the actual spatial joins are processed (I think) in the SQL Server View table.

In SQL Server Management Studio the query results show within a minute or two. I obviously don't know much about how SQL Server queries are translated into MapInfo tables. It appears to me as if the raw data is being fetched across the network but spatial queries are being handled in MapInfo.

Mubashar Ahmed
GIS OFFICER
------------------------------------------
Email: mubasha...@ngshire.vic.gov.au
  
PO Box 580, Stawell, VIC, 3380
Phone: 03 5358 8778
Mobile: 0404 877 041
www.ngshire.vic.gov.au

--
--
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 a topic in the Google Groups "MapInfo-L" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mapinfo-l/q2L-gU_PfjY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mapinfo-l+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mubashar Ahmed]

OK the table refresh process has finished successfully after ~ 4 hours. *.dat file size is 14,643KB. The table is aspatial and has 30,600 records in 11 columns. Is that too big a file?

[Peter Horsbøll Møller]

That's in no way a big table

You were using the 64bit release of MapInfo Pro 12.5, right?

Can you try to do the same with the 32bit release?

You can have them both installed on the same computer at the same time.

Also make sure that you have a 32bit as well as a 64bit Data Source pointing at your database.

I would recommend that you give them the same name. In that waay you can use the DBMS table from the 32bit version and the 64bit version

Peter Horsbøll Møller

Pitney Bowes Software

---

Date: Wed, 10 Dec 2014 18:16:26 -0800
From: mubasha...@ngshire.vic.gov.au
To: mapi...@googlegroups.com
Subject: Re: [MI-L] Re: MapInfo 12.5 64-bit 'stops responding' fetching view table from SQL Server.

OK the table refresh process has finished successfully after ~ 4 hours. *.dat file size is 14,643KB. The table is aspatial and has 30,600 records in 11 columns. Is that too big a file?

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

[Mubashar Ahmed]

Thank Peter.  

I had decided not to un-install the 32-bit Mapinfo 12.0.3 when I installed the 64-bit MapInfo. I will perform the same table refresh using the 32-bit version and report back. 

In fact you have reminded me of the troubles I had at another place and time and a different application with 32-bit and 64-bit ODBC drivers. I have just checked and the system although 64-bit Windows Server 2008 has 32-bit ODBC SQL driver which MapInfo uses to connect! I wonder if that was causing the issue.

I will install a 64-bit ODBC driver, and create (as an administrator) another connection and see if that helps.

Mubashar Ahmed
GIS OFFICER
------------------------------------------
Email: mubasha...@ngshire.vic.gov.au
  
PO Box 580, Stawell, VIC, 3380
Phone: 03 5358 8778
Mobile: 0404 877 041
www.ngshire.vic.gov.au

You received this message because you are subscribed to a topic in the Google Groups "MapInfo-L" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/mapinfo-l/q2L-gU_PfjY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to mapinfo-l+...@googlegroups.com.

[Mubashar Ahmed]

No luck either table refresh with the 32-bit Mapinfo. It is equally slow.

Following ODBC settings were alreday here before I joined.

The SQL driver (SQLSRV32.DLL) is v6.01.7601.17514 dated 21/11/2010.

The SQL Server Native Client 10.0 (SQLNCLI10.DLL)) is v2007.100.1600.22 dated 10/07/2008.

Connection Pooling Timeout for both drivers is 60. PerfMon is Disabled. Retry wait Time is 120.

System Data Sources (Name = NGSC_GIS. Driver = SQL Driver)

I have 8GB RAM. In Windows Task Manager (which also reports MapInfo Not Responding) only 1.79GB is being used. System Idle process is 98-99%! So whatever is delaying the refreshing process is not taxing the system resources.

Both the SQL Server and Mapinfo are installed on the same machine (virtual desktop).

Also I do not see any "MARS Connection = yes" in any of the *.tab files downloaded from the SQL Server database. The table I am trying to refresh has the following metadata content.

begin_metadata

"\IsReadOnly" = "FALSE"

"\MapInfo" = ""

"\MapInfo\TableID" = "7e67a56a-06d7-49b5-a994-3d68d5a50361"

"\DATALINK" = ""

"\DATALINK\DataReadOnly" = "TRUE"

"\DATALINK\ConnectionString" = "DSN=NGSC_GIS;Description=SQL 2008 Spatial Database;UID=administrator;Trusted_Connection=Yes;APP=MapInfo Professional®;WSID=ANJIE;DATABASE=NGSC_GIS;LANGUAGE=us_english"

"\DATALINK\Query" = "select ""PARCEL_PFI"", ""PARCEL_CREFNO"", ""LGA_CODE"", ""SCHEME_CODE"", ""ZONE_CODE"", ""PARCEL_Proportion_Overlap"", ""PARCEL_Area_Overlap"", ""DESCRIPTION"", ""DocumentLink"", ""ScheduleDocumentLink"", ""Long_Desc"" from ""NGSC_GIS""."

"\DATALINK\Query\+1" = """dbo"".""vw_VM_Parcel_Zones"""

end_metadata

I wonder if instead of using the SQL Server Driver in ODBC I should try creating another system data resource using the Native Client driver and use that to download the same table and see if that refreshed faster.

Mubashar Ahmed
GIS OFFICER
------------------------------------------
Email: mubasha...@ngshire.vic.gov.au
  
PO Box 580, Stawell, VIC, 3380
Phone: 03 5358 8778
Mobile: 0404 877 041
www.ngshire.vic.gov.au

[Mubashar Ahmed]

I tried the SQL Server Native Client 10.0 Driver. Still no improvement.

I installed the 64-bit ODBC Driver 11 and used that to create a new system data source for connection in 64-bit MapInfo. Still no significant improvement.

Very puzzling!

Mubashar Ahmed
GIS OFFICER
------------------------------------------
Email: mubasha...@ngshire.vic.gov.au
  
PO Box 580, Stawell, VIC, 3380
Phone: 03 5358 8778
Mobile: 0404 877 041
www.ngshire.vic.gov.au