public void TestGetGeometriesInViewNOLOCK(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.NoLockHint = true;
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.AreEqual(sq.ValidateGeometries ? _numValidatedGeoms : _numValidGeoms, geometries.Count);
}
public void TestGetGeometriesInView(SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(spatialType);
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
Assert.AreEqual(sq.ValidateGeometries ? _numValidatedGeoms : _numValidGeoms, geometries.Count);
}
public void TestGetGeometriesInViewDefinitionQuery(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.DefinitionQuery = "NAME LIKE 'A%'";
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
Assert.LessOrEqual(geometries.Count, _numValidGeoms);
}
public void TestGetGeometriesInViewFORCEINDEX(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.ForceIndex = spatialType == SqlServerSpatialObjectType.Geometry ? GeometrySpatialIndex : GeographySpatialIndex;
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
// NOTE ValidateGeometries is ignored when using ForceIndex
Assert.AreEqual(_numValidGeoms, geometries.Count);
}
public void TestGetGeometriesInViewFORCESEEK(SqlServerProviderMode providerMode, SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.ForceSeekHint = true;
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
// NOTE ValidateGeometries is ignored when using ForceSeek
Assert.AreEqual(_numValidGeoms, geometries.Count);
}
public void TestGetGeometriesInViewAllHints(SqlServerSpatialObjectType spatialType, string indexName, bool validateGeoms)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(spatialType);
sq.NoLockHint = true;
sq.ForceSeekHint = true;
sq.ForceIndex = indexName;
sq.ValidateGeometries = validateGeoms;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
// Note: ValidateGeometries is ignored when using ForceSeek or ForceIndex
Assert.AreEqual(_numValidGeoms, geometries.Count);
}
public void TestGetGeometriesInViewAllHints(SqlServerProviderMode providerMode, SharpMap.Data.Providers.SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(providerMode, spatialType);
sq.NoLockHint = true;
sq.ForceSeekHint = true;
sq.ForceIndex = spatialType == SharpMap.Data.Providers.SqlServerSpatialObjectType.Geometry ? GeometrySpatialIndex : GeographySpatialIndex;
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
NUnit.Framework.Assert.IsNotNull(geometries);
// Note: ValidateGeometries is ignored when using ForceSeek or ForceIndex
NUnit.Framework.Assert.AreEqual(_numValidGeoms, geometries.Count);
}
//[NUnit.Framework.TestCase(SqlServerSpatialObjectType.Geography, false)]
//[NUnit.Framework.TestCase(SqlServerSpatialObjectType.Geography, true)]
public void TestGetGeometriesInViewEx(SqlServerSpatialObjectType spatialType, bool validateGeometries)
{
// Note:
// This test may fail with an InvalidCastException. This is caused by multiple versions of the
// Microsoft.SqlServer.Types assembly being available (e.g. SQL 2008 and 2012).
// This can be solved with a <bindingRedirect> in the .config file.
// http://connect.microsoft.com/SQLServer/feedback/details/685654/invalidcastexception-retrieving-sqlgeography-column-in-ado-net-data-reader
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProviderEx();
sq.ValidateGeometries = validateGeometries;
var geometries = sq.GetGeometriesInView(GetTestEnvelope(spatialType));
Assert.IsNotNull(geometries);
Assert.AreEqual(validateGeometries ? _numValidatedGeoms : _numValidGeoms, geometries.Count);
}
public void TestPerformanceSqlServer2008ExProvider()
{
// Note:
// This test may fail with an InvalidCastException. This is caused by multiple versions of the
// Microsoft.SqlServer.Types assembly being available (e.g. SQL 2008 and 2012).
// This can be solved with a <bindingRedirect> in the .config file.
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(SqlServerSpatialObjectType.Geometry);
SharpMap.Data.Providers.SqlServer2008 sqex = GetTestProviderEx();
GeoAPI.Geometries.Envelope envelope = GetTestEnvelope(SqlServerSpatialObjectType.Geometry);
List <TimeSpan> measurements = new List <TimeSpan>(200);
List <TimeSpan> measurementsex = new List <TimeSpan>(200);
System.Diagnostics.Stopwatch timer;
// 10 "startup" runs, followed by 200 measured runs
for (int i = -10; i < 200; i++)
{
timer = System.Diagnostics.Stopwatch.StartNew();
sq.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurements.Add(timer.Elapsed);
}
timer = System.Diagnostics.Stopwatch.StartNew();
sqex.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurementsex.Add(timer.Elapsed);
}
}
// Remove 10 slowest and 10 fastest times:
measurements = measurements.OrderBy(x => x).Skip(10).Take(measurements.Count - 20).ToList();
measurementsex = measurementsex.OrderBy(x => x).Skip(10).Take(measurementsex.Count - 20).ToList();
// Average time:
TimeSpan avg = TimeSpan.FromTicks((long)measurements.Average(x => x.Ticks));
TimeSpan avgex = TimeSpan.FromTicks((long)measurementsex.Average(x => x.Ticks));
// The SqlServer2008Ex provider should be faster:
Assert.Less(avgex, avg);
}
public void TestPerformanceSqlServer2008ExProvider()
{
// Note:
// This test may fail with an InvalidCastException. This is caused by multiple versions of the
// Microsoft.SqlServer.Types assembly being available (e.g. SQL 2008 and 2012).
// This can be solved with a <bindingRedirect> in the .config file.
var spatialType = SqlServerSpatialObjectType.Geometry;
// testing with both providers using ExtentsMode = QueryIndividualFeatures (ie the "heaviest" lifting)
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(SqlServerProviderMode.WellKnownBinary, spatialType);
SharpMap.Data.Providers.SqlServer2008 sqex = GetTestProvider(SqlServerProviderMode.NativeSqlBytes, spatialType);
// Note: SqlServer2008Ex provider overrides ValidateGeometries behaviour, forcing this to true.
// So to compare apples-with-apples, the SqlServer2008 provider must also set ValidateGeometries = true.
// From my testing, SqlServer2008 performance is slightly faster when using ValidateGeometries = true,
// as the SQL where clause is simpler (does not require explicitly excluding invalid geometries)
sq.ValidateGeometries = true;
GeoAPI.Geometries.Envelope envelope = GetTestEnvelope(spatialType);
List <TimeSpan> measurements = new List <TimeSpan>(200);
List <TimeSpan> measurementsex = new List <TimeSpan>(200);
System.Diagnostics.Stopwatch timer;
// 10 "startup" runs, followed by 200 measured runs
for (int i = -10; i < 200; i++)
{
timer = System.Diagnostics.Stopwatch.StartNew();
sq.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurements.Add(timer.Elapsed);
}
timer = System.Diagnostics.Stopwatch.StartNew();
sqex.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurementsex.Add(timer.Elapsed);
}
}
// Remove 10 slowest and 10 fastest times:
measurements = measurements.OrderBy(x => x).Skip(10).Take(measurements.Count - 20).ToList();
measurementsex = measurementsex.OrderBy(x => x).Skip(10).Take(measurementsex.Count - 20).ToList();
// Average time:
TimeSpan avg = TimeSpan.FromTicks((long)measurements.Average(x => x.Ticks));
TimeSpan avgex = TimeSpan.FromTicks((long)measurementsex.Average(x => x.Ticks));
// The SqlServer2008Ex provider should be faster:
// Update Nov 2018: apparently this is no longer the case. Multiple tests following recent updates have SqlServer2008
// consistently outperforming SqlServer2008ex (100 - 3600 records). I'm not sure if this is due to improvments in later
// releases of SqlServer, or perhaps WKB payload smaller than SqlBytes (even though requires database CPU for WKB conversion)
// for local instance SqlExpress, SqlServer2008 is consistently 30% faster than SqlServer2008Ex
// for SqlServer on local database server, - I don't have one to test against
// for Azure SQL (50DTU limit, test peaking at 26DTU), SqlServer2008 is about 5% faster than SqlServer2008Ex
Assert.Less(avgex, avg);
}
public void TestPerformanceSqlServer2008ExProvider()
{
// Note:
// This test may fail with an InvalidCastException. This is caused by multiple versions of the
// Microsoft.SqlServer.Types assembly being available (e.g. SQL 2008 and 2012).
// This can be solved with a <bindingRedirect> in the .config file.
var spatialType = SharpMap.Data.Providers.SqlServerSpatialObjectType.Geometry;
// testing with both providers using ExtentsMode = QueryIndividualFeatures (ie the "heaviest" lifting)
SharpMap.Data.Providers.SqlServer2008 sq = GetTestProvider(SqlServerProviderMode.WellKnownBinary, spatialType);
SharpMap.Data.Providers.SqlServer2008 sqex = GetTestProvider(SqlServerProviderMode.NativeSqlBytes, spatialType);
// Note: SqlServer2008Ex provider overrides ValidateGeometries behaviour, forcing this to true.
// So to compare apples-with-apples, the SqlServer2008 provider must also set ValidateGeometries = true.
// From my testing, SqlServer2008 performance is slightly faster when using ValidateGeometries = true,
// as the SQL where clause is simpler (does not require explicitly excluding invalid geometries)
sq.ValidateGeometries = true;
GeoAPI.Geometries.Envelope envelope = GetTestEnvelope(spatialType);
List <System.TimeSpan> measurements = new List <System.TimeSpan>(200);
List <System.TimeSpan> measurementsex = new List <System.TimeSpan>(200);
System.Diagnostics.Stopwatch timer;
// 10 "startup" runs, followed by 200 measured runs
for (int i = -10; i < 200; i++)
{
timer = System.Diagnostics.Stopwatch.StartNew();
sq.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurements.Add(timer.Elapsed);
}
timer = System.Diagnostics.Stopwatch.StartNew();
sqex.GetGeometriesInView(envelope);
timer.Stop();
if (i >= 0)
{
measurementsex.Add(timer.Elapsed);
}
}
// Remove 10 slowest and 10 fastest times:
measurements = measurements.OrderBy(x => x).Skip(10).Take(measurements.Count - 20).ToList();
measurementsex = measurementsex.OrderBy(x => x).Skip(10).Take(measurementsex.Count - 20).ToList();
// Average time:
System.TimeSpan avg = System.TimeSpan.FromTicks((long)measurements.Average(x => x.Ticks));
System.TimeSpan avgex = System.TimeSpan.FromTicks((long)measurementsex.Average(x => x.Ticks));
// The SqlServer2008Ex provider should be faster:
// Update Nov 2018: "should".... highly dependent on client computer specs, and dataset characteristics
// may also have an affect in real-world usage (eg num records, OGC geom type, geom complexity).
// For the test dataset @ 100 records and also @ 3600 records on an AVERAGE computer, SqlServer2008 is
// consistently out-performing SqlServer2008Ex as follows:
// for local instance SqlExpress, SqlServer2008 is consistently 30% faster than SqlServer2008Ex
// for SqlServer on local database server, - I don't have one to test against
// for Azure SQL (50DTU limit, test peaking at 26DTU), SqlServer2008 is about 5% faster than SqlServer2008Ex
NUnit.Framework.Assert.Less(avgex, avg);
}