ThinkGeo.com    |     Documentation    |     Premium Support

SQL Server Performance

#1

I've been putting 3.0 through a series of tests to see if we can use it as a replacement for MapObjects.  Everything has gone well until I began comparing the performance of shapefiles to SQL Server.  Below I'll briefly describe my test and hopefully someone can tell me if the performance can be improved.


I'm comparing the post-zoom redraw times to shapefiles, basically going from a scale of 1.6M to 800K, etc. and stopping at 700.  I'm using four shapefiles of road features, and none of them is terribly large.  The biggest layer contains local roads and has about 70,000 records.  On the other extereme is the highway layer with about 700. 


I have SQL Server 2008 Express running on my machine.


The initial load takes 3 seconds for shapefiles and 12 seconds for SQL Server.  This is probably OK when connecting to the server, etc. is taken into consideration.


The range of redraw times for shapefiles is .003 to 3.2 seconds.  All but two redraws are < 500 ms and all but 4 are < 75 ms.


Initially the redraw times for SQL Server were 9.9 to 11.7 seconds.  I discovered "GeoCaching" and this dropped the times to 1.9 to 2.8 seconds.  I tried "IsExtentCached" upon initial load with no considerable difference, then at each zoom, but there was no notable difference either.


I tried tweaking the spatial indexing in Management Studio.  I found the program that uploaded the data had set the extent to -90, 90, -180, 180, so I adjusted that to the extent of my data, under the presumption that the grids were so large that my entire dataset was in one and all the data was being retrieved.  No difference.  I bumped up the all of the spatial index settings and saw no difference.  Finally I DELETED the spatial indexes and there was no change in the performance either.  It seems after observing this that the indexes aren't even used.


So my question is this: can SQL Server performance get any better?  At certain zoom levels I'm facing 2 second redraws for SQL Server WITH caching vs. 3 millisecond draws for shapefiles, and these scales are where our application is used most of the time, so this is not acceptable.  We would like to get away from shapefiles, so getting reasonably equivalent SQL Server is considerable.


I appreciate any comments...


Allen Huber



#2

Allen, 
  
   Thank you for the post!  We are glad to see people using the SQL Server layers.  First let me say we are more than happy to help you tweak the performance and do whatever we can to help.  Our experience with the spatial aspects of SQL Server is not that extensive and as we are at the tail end of releasing a bunch of 3.0 platform stuff we haven’t had the time until now to do allot of performance tuning on some of the newer technology.  Having said that we do have resource that can help on that. 
  
   While you will probably never see the same performance out of SQL as you will with shape files we should be able to make it better.  Shape files are engineered to be really fast and there is very little overhead to reading them.  They are flat on the disk and the format is optimized for retrieval.  We have our own sptail index based on the fastest tree systems out there.  SQL adds some overhead even when it is running on the same box.  I think back to my Foxpro & Clipper days and SQL still isn’t as fast as raw disk power!   
  
   What we can do in the short term as we get some resources freed up is share the code with you for the SQL Server FeatureSource and FeatureLayer.  This will give you total insight into how we make all of the SQL calls.  At the same time I will see what we can do on our end.  Sound good?  Could you write an e-mail to support@thinkgeo.com and have them forward it to Ben and reference this post? 
  
   I know the Microsoft spatial stuff is pretty new and we want to make sure we call the new optimal APIs to ensure the spatial indexing is used etc.  I look forward to working with you on this one and sorry that it’s not as optimized as you would have liked out of the box.  We will work hard to make it great! 
  
 David

#3

David, 
  
 Thanks for the offers of assistance.  I’m sure others will be trying to use SQL Server eventually and maybe we can make things a little easier for them.   
  
 It seems to me that the first issue to dig into is the fact that Map Suite is not using the spatial index even if it exists.  This scenario has been documented in blogs by early testers.  The issue of having to give SQL Server a “hint” to use the spatial index is described here: 
  
 bostongis.com/PrinterFriendly.aspx?content_name=sql2008_tut03 
  
 In general the belief of the person who wrote this article is that the spatial stuff in general is new to Microsoft and there are a little quirks that we need to work around.  I don’t know how the Map Suite code actually gets to SQL Server, but perhaps in the hands of one of your developers this information might help in getting Map Suite to use the index.  Last fall I did some testing of SQL Server with and without the index.  I was generating a list of all the intersections between roads in a county.  Without the index I killed the process after 20 minutes; with the index it ran in a minute or two.  So the index really seems to help if it’s being used.  Everything I see on the Web seems to confirm that as well. 
  
 After discussing this issue in the office yesterday, I came to the conclusion that some of the functionality available with shapefiles and indexing would be REALLY nice for SQL Server.  With shapefiles you are required to have an index unless you override the default setting, and there is a method call to actually build the index.  This would make the SQL Server index implementation more equivalent to shapefiles.  Of course here Map Suite would not be actually building the index, but instead send the request to SQL Server. 
  
 I will be more than happy to continue to use this forum to discuss this issue so others know what’s going on, unless we get into the more intricate details you suggested in your response.  I saw there were about a dozen and a half views, so there are obviously others interested in the topic. 
  
 Allen

#4

Allen, 
  
   We see SQL Server performance as being a critical item in our framework. While Microsoft may be new they usually come late to the party then taking it over. Thanks for the post I will have someone look into it today. I hope we can do a little tweak and magically it will work better.  
  
   I read the article and absolutely agree that we need to encourage and lead the user to building the index through the API. In our 3.X framework one target was to really enhance our error messages and always try to the build the API to help people do the right thing even though we may throw allot of exceptions. This follows right with that philosophy.  
  
   With the article in hand we can get you the speed increases you are looking for.  If we can find something out today I will let you know and it will be just in time for the Beta 2 of the Desktop Edition. If we can’t find it today then it may take a week or so. We have allot of vacations scheduled for right after the release. :-) I hope to have some more details soon. 
  
  
 David

#5

David, 
  
 Great…I’ll be ready to test whenever you’ve got something to try.  Nobody here expects the performance to match shapefiles (but in reality we don’t know quite what to expect) so if there is a significant reduction in those two-second redraws I think everyone will be satisfied. 
  
 Yes, that article was quite an eyeopener as to how important these spatial indexes are going to be.  I’m glad you liked my idea about building the index through the API.  Somehow I manage a good one now and then. 
  
 Allen

#6

Allen, 
  
   We are still working on this.  The developer who is on this goes on vacation next week.  After he gets back in a week I expect he can finish it in a few days.  He has read the article and is in the process of making the changes. 
  
 David

#7

Allen, 



Just let you know we have figured it out and it will be available in the upcoming version. Thanks for your insights again!



Thanks, 



Ben



#8

Ben, 
  
 Super! I’ve been hearing that this version will be out around the end of the quarter…is that still a reasonable expectation?  We have not yet committed to putting everything in SQL Server, but it would be nice to know it’s up to the job if we do. 
  
 Allen

#9

Allen, 
  
 We will have a version late this week or early next week and that feature will be integrated then.  Good news, isn’t it:) 
  
 Ben.

#10

Allen, 
  
   I think you will pleased with the performance of the new code.  There were a number of things we learned about the spatial nature of SQL.  We found out how to not use hints to get the index to be used.  We also removed our dependency on a constraint to determine the SRID projection of the layer.  Now if there are any or multiple spatial indexes it will use them without having to specify what index to use.  We have also taken your suggestion and added a BuildIndex method to build the spatial index.   
  
   Speaking of the SRID we have added a property to specify this, and there is also a constructor.  In this way we will limit our search to the items with this SRID.  It is possible for SQL to have tables with multiple records of different SRIDs but the query will only process the ones that are the type you specify in the query.  I think this is to support multiple versions of projected data in the same table.  For example I think you can have roads in decimal degrees and also in UTM.  You specify the correct SRID and have indexes built for both and away you go. 
  
   I know you were on the fence with committing to SQL.  We are in a much better position to encourage you to use it.  Our support for SQL will just get better and faster over time. 
  
 David

#11

Any news on when this will be available?  I didn’t see it mentioned in the Beta 2 release, but that was about the same time as these messages went out so I didn’t really expect it to be in there.  I’ve seen some mention of a Beta 3; any chance it’ll be in there? 
  
 Allen

#12

Allen, 
  
 In fact, it is available in the current version (MapSuiteCore:3.1.19;DesktopEdition:3.0.199). Very sorry we forgot to add this feature to our enhancements list.  Please have a try with the 199 one and let me know if you have more issues. 
  
 Here is how to get the version number by the way. 
 string a = ThinkGeo.MapSuite.DesktopEdition.WinformsMap.GetVersion(); 
  
 Thanks, 
  
 Ben

#13

Ben, 
  
 Great…I was tempted to try just to see if there was a difference, but now that I know for sure I definitely will. 
  
 Allenl

#14

Ben, 
  
 Just a quick update…yes, things are significantly better now.  Shapefiles are still faster, but SQL Server loading of a very complex map only takes about twice as long, and zooming in parallels that instead of having multi-second zooms compared to millisecond ones with shapefiles.  It seems like using Geocaching actually degrades performance considerably now.  I’ve discovered how to do BuildIndex and see the index for the layer over in Management Studio.  If there are any other tricks, let me know, and if I find any other ways of speeding up things I’ll report back as well. 
  
 Allen

#15

Allen, 
  
 It’s awesome that works for you, I think the performance is pretty good (for SQL2008) already.  
  
 One possible way to rise the performance more is using GeoCache. The default GeoCache is a general one and cannot provide much power, if you can find a proper one for your application, that might greatly improve the performance.  It is usually difficult though to find a good algorithm for the caching, have a look and let us know what you find out. 
  
 Thanks, 
  
 Ben