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); }