ThinkGeo.com    |     Documentation    |     Premium Support

SQL Server 2008 speed, indexes and caching

#1

OK - I'm very new to all this - but one of the main things I'm evaluating is SQL 2008 functionality - and obviuosly key to all this is speed.


I've been testing with a table of relatively complex site boundaries scattered over the whole country (Shape file was c. 100MB and has 30K records) - so not small - but not particularly big either I'd suggest.


Initially I was rather disappointed with perfomance - slow to load, slow to draw entire layer (which I realise is never going to be that quick dragging all the data from the database - and is probably best addressed by limiting zoom) - but what I was particulalry disappointed with was a 5-7 second lag every time I panned the map - even when zoomed in quite close (i.e. only drawing 20-30 polygons).


Using query analyzer I had a look at what was going on - and to cut to the chase - I managed to improve the speed when close in from 7 seconds to almost instant by altering SQL server spatial index Grids and cells per object (in creased to 64 and set everything else at low).  Obvioulsy on my dataset - when zoomed in close the indexing was not excluding enough records before perfoming teh full spatial query.  Obviously different datasets may require different settings - but the improvements are dramatic - and I'm much happier now !


I appreciate this is is some ways nothing to do with MapSuite - but thought it worth reporting given the threads I've seen on SQL performance.  Also I wondered if you had any general guidelines on this (accepting that it will to some extent be dataset specific)


Reading the threads I have seen a couple of other techniques discussed that I have yet to try:



        
  • Holding the SQL connection open - to save open / close all the time.  How is this achieved?  My code just passes the connetcion string - is there a way to pass a connection object instead ?

    •     
  • Caching - I've seen this referred to a bit - is there a description of what this actually does please (data stored locally ?)
        

              
    • How to implement.

      •         
    • How to destroy (i.e. force a refresh for changed data)

      •         
    • When its likely to help (I'm thinking that if  repeatedly displaying all data from a table - e.g. zoomed to country level - then this could have some dramtic effects - but also consume lots of disk space !

      •         
    • Where is the cahce located - disk / memory ?  If disk is there any way of having users share it ?

      •     

        


Final question is about how if you aare updating spatial objects in SQL server (and presumably attribute data as well) - how is locking handled.  Or do I really need to look at handling all this outside of MapSuite.  Any best practice / examples


Many Thanks



#2

 


J,
 
In order to improve speed of SQL Server, there are three ways that all of them you have thought.
 
1. Create the index for SQL data.
2. Keep the connection open and only close at the end.
About your question, I think you don’t need to pass the connection object, you just need to control when the MsSql2008FeatureLayer open or close. Once you call MsSql2008FeatureLayer.Open() it will open the internal connection object and once you call MsSql2008FeatureLayer.Close() it will close the internal connection object.
 
3. Use our TileCache system to implement caching.
Here are the answers for your questions:


        

              
    • We use an abstract class TileCache object to implement caching, it has two abstract sub classes BitmapTileCache and NativeImageTileCache, BitmapTileCache is a property of DesktopEdition Overlay, NativeImageTileCache is a property of WebEdition Overlay, so you just need to set any concrete instances which inherited from BitmapTileCache to DesktopEdition Overlay for this property, we have some built-in concrete classes already, such as FileBitmapTileCache, ImMemoryBtimapTileCache. You also can create your own TileCache.

      •         
    • You can destroy cache by calling TileCache.ClearCache(), the ImMemoryBtimapTileCache is special, you also can set the maximum tiles count so that control the memory usage to avoid out of memory.  The recommended way is changing CacheId will generate new version cache that can keep the old version exists; you can change CacheId back to use the old version. If you use Map.Refresh API, you just need to pass in the overlays which you want to force refresh when you change the style or add some records. 

      •         
    • Yes, you are right. If you use FileBitmapTileCache and you don’t control the disk usage, it will increase endless until your disk is full.

      •         
    • You can choose it locate to disk or memory by different BitmapTileCaches, and the disk way can share by using FileBitmapTileCache, it will generate the same folders and files structure by the same setting. 

      •     


 
For more information please look at the following post, it also has additional links you can refer to.
gis.thinkgeo.com/Support/Dis...fault.aspx
 
We don’t handle locking when updating spatial objects by using MsSql2008FeatureLayer so far, if this is very important for you, I think you will consider add this feature some how. Sorry for inconvenience.
 
Please let me know if you have more questions.
 
Thanks
 
James

#3

Thanks James,


 


2) Keeping connection open.


When accessing a SQL layer I simply do:


Dim sql2008Layer As New MsSql2008FeatureLayer(connectString, "TestTable", "OBJECTID", 27700)


staticOverlay.Layers.Add("Sql2008Layer", sql2008Layer)


I must be missing something as I'm not explicitly opening / closing the layer (though I do do this elsewhere for other purposes)


Are you saying if I open the layer  (e.g. sql2008Layer.Open() ) - before adding it to the staticOverlay - then it won't be re-opened and I can control how long the connection is held open ?


 


3) TileCache - this seems to work as you describe thanks.


I assume you can use multiple caches - one for each layer - or can you have one cache for several static layers if they are always viewed together (e.g. roads, rivers etc)


Also - does this mean that the zoom levels are actually not infinite - but are constrained to set zooms (as with e.g. google maps) - or is the tile cache images resizing fractionally if required ?


As you say the tile cache can be shared across a network or by multiple users on terminal server presumably (I assume MapSuite works fine on Terminal Server - I can't see why it wouldn't ?)


Thanks


 



#4

Tile caching.


Trying to get my head around this - I can see in some situations this can make massive time savings, whereas in others it can almost slow things down.


I've been playing with:


        Dim bitmapTileCache As FileBitmapTileCache = New FileBitmapTileCache()

        bitmapTileCache.CacheDirectory = "c:\Temp\SampleCacheTiles"

        bitmapTileCache.CacheId = "World02CachedTiles"

        bitmapTileCache.TileAccessMode = TileAccessMode.ReadAddDelete

        bitmapTileCache.ImageFormat = TileImageFormat.Png


The static base layer that this applies to has a couple of layers on it - drawn from SQL server.  What I'm seeing is that when zoomed right out the tile cache massively speeds thinsg up (as you would expect as otherwise it has to drag down the whole layer out of SQL)


However when zoomed in a bit tighter (displaying 20-30 polygons) the cache can slow things down on the first it when it has to generate the cache - and obviously the chance of a second hit on the cache is less when zoomed right in.  Also with correct indexing on the SQL database this draw is quite quick anyway.


So - what I'd like to do is only create a cache down to a certain zoom level and then revert to getting teh data direct from the database as I think this will give optimum perfomance.


Is this possible - I see there is a  bitmapTileCache.TileMatrix - which looks as if it might do it - but I've no idea how to set the various properties.


Also - I'm surprised how slow generating the cache on the first view is on occasion - is this doing multiple hits on the database for each time - rather than getting one big image and then chopping it up - or is it over fetching the area (which often would make sense)


Again - any way to control these bits


Thanks


 



#5

J, 
  
 About connection open, I review the code of MsSql2008FeatureLayer and find the Open or Close of it is not the Connection’s, you can not keep connection open so far because it will open and close when do any operation by using MsSql2008FeatureLayer, I am sorry that I describe the wrong information. If you think this is the only way to improve your performance, we can consider enhance the MsSql2008FeatureLayer that make it can support your requirement. 
  
 About TileCache, it works for each overlay, overlay can contains several static layers, if you want to cache each layer, and you can add each layer to individual overlay. 
 The zoom levels of the default ZoomLevelSet is limited that is only 20, and it matches Google map zoom level set, if you use another zoom level set you also can cache the images for each level, furthermore you can turn off the zoom level that set your extent at any scale by setting ZoomLevelSnappingMode to none. 
  
 I am not very sure what the terminal server means, can you describe more and make your requirement clear. 
  
 If you want to create cache for certain zoom levels, you can pre-generate the certain cache images to disk, and then when you really to use it you set the TileAccessMode to ReadOnly. 
  
 It will fetch the data for extra area if turn on the TileCache, because each tile is fixed size, for example, the default size is 256*256, so if the map is 800*600, it will fetch the width 256*4=1024 greater than 800, if 256*3=768 is less than 800 can not render entire map. 
  
 I think you’re using the version 3.1.299 which has the performance problem when panning, so if you download the recently version 3.1.398 or later, you can fell the speed up. 
  
 TileCache is a complex system, I am afraid it’s tough to describe all things in a short word, so if you have any not cleared API, please let me know. 
  
 Thanks 
  
 James 


#6

James,


Thanks for the replies.


Terminal server - I mean microsoft terminal server - where multiple clients run concurrent remote desktop sessions on one server - e.g. microsoft.com/windowsser...-home.aspx - I wanted to know whether you had testes and supported the mapping component in this environment (it would be one way of easily sharing one cache between multiple users)


<<If you want to create cache for certain zoom levels, you can pre-generate the certain cache images to disk, and then when you really to use it you set the TileAccessMode to ReadOnly.>>


I assume from this that there is now ay of just telling the system to cache down to a certain zoom level and then not use the cache?


Can you dynamically change TileAccessMode when the user gets to a certain zoom level - or can that only be set when you first instantiate the cache.


I agree caching is a complex are - I've written caching algorithms before. Do you have a white paper / a doc describing it in more detail to save me from having to ask you for it bit by bit ?  At the moment - all I have is the example and then looking at the classes and methids and trying to guess what they do


Thanks


 


 


 



#7

J, 
  
   I cannot guarantee that sharing the same cache directory which a bunch of people on terminal services will work.  I would imaging that there is the possibility to get some errors.  I think we can test this scenario though if this is something you want to do. 
  
   I also want to possibly correct one thing in Jame’s post.  I am not 100% sure put pretty sure.  You are not limited to 20 zoom levels as you can so custom zoom levels which allows you to set any number of scales in a collection.  At the same time the more levels you have the more data the cache has and the less likelihood you will hit the cache.  We have done caching with people that do not snap to zoom levels and they get poor caching unless people zoom to the same place all the time. 
  
   As far as making the cache only work between certain levels I am fairly sure this can be done by overriding the GetTile and SaveTile.  Inside of there you can check the scale of what is being requested and if above or bellow a scale then just do nothing.  Return a set of empty tiles for get tiles and for the same just do nothing at all.  If you are interested in this we can work up a sample for you. 
  
   For the moment we do not have a white paper on this.  We are working on this though as part of a documentation project for a manual.  It is a bit of a slow process unfortunately.  If you have specific questions let me know. 
  
 David

#8

J, 
  
  One thing I want to mention on the connection state is that we open and close connection whenever we execute a series of statements.  As James mentioned it is not on the Open and Close.  The reasoning is that ADO.NET does the caching independent of the connection.  Internally they must have a static pool of connections and they mention in the link below that the connection pool is not destroyed until the active process ends.  They recommend you always close your connections as that are light weight are managed at a high level.  There is also some information about specifying the connection lifetime in the connection string etc.  Worse case you open a connection outside of the layer is the same connections string and that will guarantee the static cached connection remains open.  I do not think it is necessary but just something. 
  
 msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx 
  
 David 


#9

Thanks for the replies David.


Re: Terminal services.


1) Have you tested the control on terminal services (ignoring the cache for the moment) - this would be important


2) As for the shared cache - if you can do this accross a network - I don't see why it wouldn't work on TS (providing correct permissions were set) - or are you worried about 2 people visiting the same bit of map at the same time and both trying to write the same tile with the same file name ?  Otheriwse I would have thought it was potentialy a good solution - cache once - shared by many


Thanks



#10

We have customers who utilize Terminal Services on Windows Server 2003. Often they aren’t large groups of users and this is probably why we haven’t run into any permissions-related errors with the caching. We also don’t snap to zoomlevel so that probably has a decent amount to do with why we haven’t had this issue either. 
  
 I’m glad I read this thread though because its a rare opportunity to stop a bug before someone finds it! I’m envisioning a simple workaround in the Form_Load to the effect of: 
  
 cacheTiles.CacheDirectory = System.IO.Path.GetTempPath & My.User.Name 
  
 So long as your users are using unique Active Directory user names to log in, you should be free of any permission collisions. Our users are required to log in uniquely so this should fit our situation.

#11

J, 
  
 We still don’t have chance to test the map control on terminal services, we will try to fully test it. If you have any more detail guidance for how to deploy the application to terminal services that I will appreciate. 
  
 Thanks for your sharing, Nelson. I agree that your workaround can fix the problem, we will try to find the better way if possible after has testing environment. 
  
 James 


#12

James, 
  
   What he is talking about is multiple users using remote desktop into one machine and then running their mapping application but pointing the cache directory to one directory.  They would all be sharing the cache. 
  
 David

#13

Gentlemen, 
  
 I have done my testing, my step is like below: 
 1. I install our DesktopEdition in a Windows2003 machine, there is an EfficientlyMoveAPlaneImage sample in HowDoI samples, it contains a timer to change the position of plane and refresh the map. 
 2. Enable Remote Desktop on that machine, so we can use another two machines connect it to test. 
 3. I control machineA and my co-worker controls machineB, both connect windows2003 machine by Remote Desktop Connection by the same user account, and then run the sample to do some operation. 
  
 It works fine no matter we pan, zoom in/out etc because all things are operated in memory and they’re using different instance. So I can say they are isolated. 
 But if I update the sample in windows2003 to turn on the FileBitmapTileCache and set the same directory and cacheId, and then test again, it will throw exception, such as “The directory is not empty”, “2.png file is using by another process” or something like that. 
  
 Is that making any sense? Or I need to do some furthermore test case for certain scenarios. 
  
 Thanks 
 James 


#14

Hi James,


That sounds like exactly the right test - and interesting that you have hit an issue - but good to know that it appears to just be the tile cache and it works on TS otherwise - so presumably the workaround for the momet is to make sure tile caches are individual.


I would have thought that on a shared terminal server (or even clients on a network) there could be significant advantages to being able to share a cache e.g.


1) Saved disk space (especially on ts)


2) Improved performance as the cache would be much more likely to get hits - especially at the larger zoom levels


3) From an admin point - one cache to destroy if the base mapping changes.


Obviously without knowing how the caching code works - its difficult to comment further - but I would have thought this should be possible.  Presumably reading from the cahce need not lock the files for others.  The only issue I see is a simultaneous write of a new tile - and this should be quick - so if an error trap simply re-tried - checking if the tile now existed before attempting a second go at writing etc - should avoid the issue.


Bets wishes



#15

J,


I think the only issue can be fixed and the exception thrown hit rate is very small.


We use .png file to store the cached image, so the size of it is small that the speed of write it to disk is very quick, and before write if the file already exists, it won't write.


If there are many users try to save tile at the same moment, the exception would be thrown is your problem. You can create custom FileBitmapTileCache and override SaveTileCore method and add try catch for it, the exception will be caught and cache file won't be saved, next time the cache will save. This problem only happened few times, so I think this way can work.


    public class CustomFileBitmapTileCache : FileBitmapTileCache
    {
        protected override void SaveTileCore(Tile tile)
        {
            try
            {
                base.SaveTileCore(tile);
            }
            catch
            {

            }
        }
    }

Thanks

James