[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] Small benchmark with PostGIS and MS
From:       "Sufficool, Stanley" <ssufficool () sbcounty ! gov>
Date:       2009-11-23 17:52:33
Message-ID: C2F174F99918D54CA2A96E57C5079B6F01827DA7 () sbc-exmsg2 ! sbcounty ! gov
[Download RAW message or body]

#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: postgis-users-bounces@postgis.refractions.net 
> [mailto:postgis-users-bounces@postgis.refractions.net] On 
> Behalf Of rkrummen@hsr.ch
> Sent: Monday, November 23, 2009 9:46 AM
> To: postgis-users@postgis.refractions.net
> 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 postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]