April 25, 2007 at 9:20 am
Our company is looking at spatially enabling SQL Server 2005. My application would take a shapefile, three files combined that contain geographic information, and dump the data into its own unique table. This application could be used with some frequency, so the possibility of a large number of tables being created is very real. I was just wondering what the upper bounds, in terms of number of tables, that SQL Server can handle without some major degradation in the performance of our queries would be.
April 25, 2007 at 9:38 am
Can't you use the same table (partitioned maybe)?
Adding a new table each time sounds like a recipe for disaster to me.
April 25, 2007 at 11:39 am
I think it is almost endless. At some point there will most likely be a degraded performance. We have a 95 gig db with 4,800 tables in it right now.
April 25, 2007 at 8:03 pm
There isn't a table limit per se, but there is a limit to the number of objects (tables, views, stored procedures, UDFs, triggers, rules, detaults, & constraints) that can exist in a database.
Fortunately, the limit is 2,147,483,647. So I suspect that you're okay. I hope so! :o)
April 27, 2007 at 6:51 am
Hi Jim,
I think that you will need to follow the same rules that you would use for any kind of database design. If the table(s) all represent the same thing, then they really (usually) belong in the same table.
In the real world, however, you might need to evaluate what these objects represent to your application. If the table(s) are just a temporary storage place for similar but unrelated objects then it may make more sense to keep them in their own tables.
So, if you are loading shp files from the TIGER data, county by county, with the intent to build a geodatabase covering the entire US, I'd say they all go in 1 table (for each feature, that is). If the groups of shp files are always going to be accessed by themselves, then it will be faster (slightly) to keep them separate.
April 27, 2007 at 9:13 am
Thank you everyone for your replies.
Each shapefile is going to describe a fire perimeter that is the result of a fire spread prediction simulation. Essentially, it is going to return the same schema every time, so keeping to normal forms and good database design I should probably see if I can figure out how to dump everything in one table. We will be using SQL Server 2005 with MsSpatial Extensions that creates an individual table for each shapefile import.
Now, my second question would be, wouldn't it be best to have the import create and populate a single table, then select into all of the records from that import to another table that contains all of the data from all of the resultant simulations, then drop the imported table?
April 28, 2007 at 9:33 pm
ESRI has the ArcSDE extensions for SQL Server as well, not sure if you've looked at that at all. I know of it, but not more than that I'm afraid, so can't really give you advice other than to say it exists as an option for spatial data in 2005.
---------------------------------------
elsasoft.org
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply