Kevin,
Here is the test. Generally, we simulate different number of concurrent users accessing the same database at the same time. Every user calls the method GetFeaturesInsideBoundingBox() 100 times with a random input extent. In our test, we uses a remote Sql2008 Server and a remote shape file (on the same machine of the Sql2008 Server) which has the same data. We get the average time for one Operation (layer.Open / layer.GetFeaturesInsideBoundingBox / layer.Close) and here is the result.
User Count
Remote Sql2008 (ms)
Remote ShapeFile(ms)
1
9.77
11.9
10
52.12
84.8
20
103
177.6
50
273
459
100
583
1431
So we can see, remote Sql2008 server is (1.5 to 2.5) times faster than a remote Shapefile under the same circumstance.
We also compared the average time between a Local ShapeFile and a Local Sql2008 Server. The result is interesting that the average time for an operation with a local shapeFile is almost the same no matter how many concurrent users are there, but for the local sql2008 server, it still grows linearly. I think that's because the operating system did pretty good caching for a local file, but didn't do much caching for a database. In the test we also found if we change the file path to a network path like “\\192.168.0.10\Share\Test.shp” instead of a local one like “c:\Share\Test.shp”, although the file is still the same , the performance will be much slower, in fact it will be about 1.5 to 2 times slower than the local Sql2008 server.
User Count
Local ShapeFile(ms)
Local Sql2008 (ms)
1
4.8
6.28
10
6.68
61.13
20
4.84
127
50
7.89
360
100
7.09
762
So we can say, the local shape file has the best performance because it is well cached. Other than that, Sql Server2008 will be around 2 times faster than a Shape File under the same circumstance.
Here is the key codes in the test. You can try if you are interested.
List<long> results = new List<long>();
// Start Sql Test
private void btnSql2008Test_Click(object sender, EventArgs e)
{
results.Clear();
int times = Convert.ToInt32(txtSql2008TestTimes.Text.Trim());
TestMultiThread(new ThreadStart(TestOneThreadOnSql2008), times);
}
// Start Shape File Test
private void btnShapeFileTest_Click(object sender, EventArgs e)
{
results.Clear();
int times = Convert.ToInt32(txtShapeTestTimes.Text.Trim());
TestMultiThread(new ThreadStart(TestOneThreadOnShapeFileFeatureLayer), times);
}
// Show Result
private void btnShowResult_Click(object sender, EventArgs e)
{
long sum = 0;
foreach (long item in results)
{
sum += item;
}
MessageBox.Show(((double)sum/(double)results.Count).ToString());
}
private void TestMultiThread(ThreadStart threadStart, int clients)
{
Thread[] threads = new Thread[clients];
for (int i = 0; i < threads.Length; i++)
{
threads[i] = new Thread(threadStart);
threads[i].Start();
}
}
private void TestOneThreadOnSql2008()
{
string connectString = @"Data Source=192.168.0.191\TGSQLSERVER; Initial Catalog=InternalDB; User ID=userId; Password=password;";
TempMsSql2008FeatureLayer sql2008Layer = new TempMsSql2008FeatureLayer(connectString, "states", "recid");
Take100Operations(sql2008Layer);
}
private void TestOneThreadOnShapeFileFeatureLayer()
{
ShapeFileFeatureLayer shapeFileFeatureLayer = new ShapeFileFeatureLayer(@"C:\Share\USStates.shp");
Take100Operations(shapeFileFeatureLayer);
}
private void Take100Operations(FeatureLayer sql2008Layer)
{
Random random = new Random(DateTime.Now.Millisecond);
// Do 100 operations
for (int i = 0; i < 10; i++)
{
RectangleShape extent = new RectangleShape(-126.4, 48.8, -67.0, 19.0);
extent.ScaleDown(50);
for (int j = 0; j < 10; j++)
{
extent.TranslateByOffset(random.Next(10), random.Next(10), GeographyUnit.Meter, DistanceUnit.Meter);
GetFeaturesWithinRectangle(sql2008Layer, extent);
}
}
}
private Collection<Feature> GetFeaturesWithinRectangle(FeatureLayer layer, RectangleShape extent)
{
Stopwatch sw = new Stopwatch();
sw.Start();
layer.Open();
Collection<Feature> features = layer.QueryTools.GetFeaturesInsideBoundingBox(extent, ReturningColumnsType.AllColumns);
layer.Close();
sw.Stop();
Console.WriteLine(sw.ElapsedMilliseconds);
results.Add(sw.ElapsedMilliseconds);
return features;
}
Thanks,
Ben
