I have an application that shows ships in 1 layer and trails of previous ship positions in another layer.
Normally when the application starts I just display the ships without the trails, but give the option to display the trails later.
If I later turn on the trails layer, the first refresh is REALLY slow - it takes 22 minutes. But subsequent refreshes are fine - they only take a second or 2.
My code is:
LogDebug(“About to WinformsMap1.Refresh()”, “High”)
WinformsMap1.Refresh()
LogDebug(“Completed WinformsMap1.Refresh()”, “High”)
(logdebug is just a routine that writes debug information to a file for debugging purposes).
I also put a handler in to
AddHandler DirectCast(ShipLayer.FeatureSource, MsSql2008FeatureSource).ExecutingSqlStatement, AddressOf DisplayShapeMap_ExecutingSqlStatement
Private Sub DisplayShapeMap_ExecutingSqlStatement(ByVal sender As Object, ByVal e As ExecutingSqlStatementMsSql2008FeatureSourceEventArgs)
System.Diagnostics.Debug.WriteLine(“e.SqlStatement=” & e.SqlStatement)
End Sub
Here is the initial run. I typed in the handler information that appears in the Visual Studio Immediate Window:
27/08/2013 16:18:20: About to WinformsMap1.Refresh()
e.SqlStatement=SELECT allTypes.name FROM sys.columns allColumns, sys.types allTypes, sys.all_views allViews WHERE allColumns.object_id=allViews.object_id AND allColumns.user_type_id=allTypes.user_type_id AND allTypes.system_type_id=240 AND allViews.name='Vessels’union SELECT allTypes.name FROM sys.columns allColumns, sys.tables allTables, sys.types allTypes WHERE allColumns.object_id=allTables.object_id AND allColumns.user_type_id=allTypes.user_type_id AND allTypes.system_type_id=240 AND allTables.name='Vessels’
e.SqlStatement=SELECT sys.tables.name from sys.spatial_index_tessellations, sys.tables where sys.tables.name=‘Vessels’ AND sys.tables.object_id=sys.spatial_index_tessellations.object_id
e.SqlStatement=SELECT [VCMG],[Name],[WGS84Loc].STAsBinary() as [WGS84Loc],[ID] FROM Vessels WHERE (geography::STGeomFromText(@geography,4326).STIntersects(WGS84Loc)=1) AND VDATETIME >= GETUTCDATE()-0.0416666666666667;
e.SqlStatement=SELECT allTypes.name FROM sys.columns allColumns, sys.types allTypes, sys.all_views allViews WHERE allColumns.object_id=allViews.object_id AND allColumns.user_type_id=allTypes.user_type_id AND allTypes.system_type_id=240 AND allViews.name='Vessels’union SELECT allTypes.name FROM sys.columns allColumns, sys.tables allTables, sys.types allTypes WHERE allColumns.object_id=allTables.object_id AND allColumns.user_type_id=allTypes.user_type_id AND allTypes.system_type_id=240 AND allTables.name='Vessels’
e.SqlStatement=SELECT sys.tables.name from sys.spatial_index_tessellations, sys.tables where sys.tables.name=‘Vessels’ AND sys.tables.object_id=sys.spatial_index_tessellations.object_id
e.SqlStatement=SELECT [VCMG],[Name],[WGS84Loc].STAsBinary() as [WGS84Loc],[ID] FROM Vessels WHERE (geography::STGeomFromText(@geography,4326).STIntersects(WGS84Loc)=1) AND VDATETIME >= GETUTCDATE()-0.0416666666666667;
27/08/2013 16:40:37: Completed WinformsMap1.Refresh()
As mentioned above, subsequent refreshes only take a second or two:
27/08/2013 17:27:12: About to WinformsMap1.Refresh()
e.SqlStatement=SELECT allTypes.name FROM sys.columns allColumns, sys.types allTypes, sys.all_views allViews WHERE allColumns.object_id=allViews.object_id AND allColumns.user_type_id=allTypes.user_type_id AND allTypes.system_type_id=240 AND allViews.name='Vessels’union SELECT allTypes.name FROM sys.columns allColumns, sys.tables allTables, sys.types allTypes WHERE allColumns.object_id=allTables.object_id AND allColumns.user_type_id=allTypes.user_type_id AND allTypes.system_type_id=240 AND allTables.name='Vessels’
e.SqlStatement=SELECT sys.tables.name from sys.spatial_index_tessellations, sys.tables where sys.tables.name=‘Vessels’ AND sys.tables.object_id=sys.spatial_index_tessellations.object_id
e.SqlStatement=SELECT [VCMG],[Name],[WGS84Loc].STAsBinary() as [WGS84Loc],[ID] FROM Vessels WHERE (geography::STGeomFromText(@geography,4326).STIntersects(WGS84Loc)=1) AND VDATETIME >= GETUTCDATE()-0.0416666666666667;
e.SqlStatement=SELECT allTypes.name FROM sys.columns allColumns, sys.types allTypes, sys.all_views allViews WHERE allColumns.object_id=allViews.object_id AND allColumns.user_type_id=allTypes.user_type_id AND allTypes.system_type_id=240 AND allViews.name='Vessels’union SELECT allTypes.name FROM sys.columns allColumns, sys.tables allTables, sys.types allTypes WHERE allColumns.object_id=allTables.object_id AND allColumns.user_type_id=allTypes.user_type_id AND allTypes.system_type_id=240 AND allTables.name='Vessels’
e.SqlStatement=SELECT sys.tables.name from sys.spatial_index_tessellations, sys.tables where sys.tables.name=‘Vessels’ AND sys.tables.object_id=sys.spatial_index_tessellations.object_id
e.SqlStatement=SELECT [VCMG],[Name],[WGS84Loc].STAsBinary() as [WGS84Loc],[ID] FROM Vessels WHERE (geography::STGeomFromText(@geography,4326).STIntersects(WGS84Loc)=1) AND VDATETIME >= GETUTCDATE()-0.0416666666666667;
27/08/2013 17:27:14: Completed WinformsMap1.Refresh()
Hope that is understandable!
1) Why would the initial refresh take so long? There is no change in code and it is only ever that first refresh that ever takes a long time. But my users can’t sit and wait 10 minutes for a screen to pop up. On average there are about 200 ships and 1500 trail points to be displayed on the map so 22 minutes to load them all in seems excessive.
2) I also notice that there are 6 sql statements doing during the refresh, but actually there are only 3 statements which are executed two times each. That also seems strange. Is that normal? Also, even though there are trails clearly visible in my map, I don’t see any reference to the Trails layer in the SQL statements. I have no idea why that is - MapSuite is generating those sql statements and possibly some of them aren’t visible in my handler.
Thanks,
Dave
Speeding up map refresh()
Hi David,
I agree with you that maybe some probblems here, but I’m not sure what exactly is. Could you share some code about your overlay’s defination and some refresh operations here? Actually, the Map Suite doesn’t do nothing else after getting the sql command, it just reads the data from the executed result. Could you help us checking the things as following:
1. Could you check how long the “SqlConnection.Open()” takes for the first time?? Maybe for the first time, it takes a bit long time to initialize database connection. I just want to make sure there isn’t any problem here.
2.The layer for ships and the layers for trails are in the same LayerOverlay or in 2 different overlays? They works as SingleTile or multiTiles? Could you try SingleTile with 1 LayerOverlay??
3. Could you try creating a console application and put the sql statements for the first refresh into it to check what the time it takes?
Waiting for your further information.
Johnny