#2 will always be faster on SQL Server because it is not *entirely* ACID compliant. It has a cache rather than a full table scan for row counts IIRC.
> -----Original Message----- > From: [email protected] > [mailto:[email protected]] On > Behalf Of [email protected] > Sent: Monday, November 23, 2009 9:46 AM > To: [email protected] > Subject: [postgis-users] Small benchmark with PostGIS and MS > SQL-ServerSpatial: Adjustment > > > Two weeks ago, my advisor Stefan Keller posted some queries > about database benchmarking. We would like to thank you for > the useful suggestions. Wherever possible we adapted our > queries. Due to restrictions in time, data and the limited > spatial functionality of MS SQL Server, we could not > implement any proposed refinement, but we like to show you > what we got now. Feel free to comment. > > - Roland > > 1. Loading data and generating indices > > SELECT * INTO {dataset} FROM {original dataset} > WHERE ST_Intersects(@geom, the_geom) ; > > ALTER TABLE {dataset} ADD PRIMARY KEY (gid); > > CREATE INDEX gidx ON {dataset} USING gist (the_geom); > > 2. Non-spatial selection query > > SELECT Count(*) FROM {dataset lines} l > WHERE l.roadflg='Y'; > > 3. Spatial query I: > > a) > SELECT Count(*) FROM {dataset points} p > WHERE ST_Intersects(@poly, p.the_geom) ; > > b) > SELECT Count(*) FROM {dataset polygons} pg > WHERE ST_Intersects(@geom, pg.the_geom) ; > > c) > SELECT Count(*) FROM {dataset lines} l > WHERE ST_Intersects(@geom, l.the_geom) ; > > 4. Spatial query II: > > a) > SELECT Count(*) FROM {dataset points} p > WHERE ST_DWITHIN(@point, p.the_geom, x) > > b) > SELECT Count(*) FROM {dataset polygons} pg > WHERE ST_DWITHIN(@point, pg.the_geom, x) > > c) > SELECT Count(*) FROM {dataset lines} l > WHERE ST_DWITHIN(@point, l.the_geom, x) > > 5. Join of a linestring and a polygon table > > SELECT SUM(ST_Length(pg.the_geom)) FROM {dataset lines} l > JOIN {dataset polygons} pg ON ST_DWITHIN(pg.the_geom, > l.the_geom, 10) WHERE l.railflg = 'Y' > _______________________________________________ > postgis-users mailing list [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
