Oracle Spatial Performance

[Judd, Chris]

You could see if it is an issue w/ Oracle by running a trace and see if it
has significant wait times.  Also if you have ArcSDE and ArcView you could
register the layer with ArcSDE, draw it in with ArcView and see if you still
have a performace issue.  You wouldn't suspect it was an issue with scale,
project or extent would you?
 
You could also test with a very small layer.  FDEP's experience with Oracle
and Mapserver is that performance is at least as good with Oracle Spatial as
it was with ArcSDE. 
 
Hope this helps, 
 
-Chris

	-----Original Message----- 
	From: UMN MapServer Users List on behalf of Smith, Michael
ERDC-CRREL-NH 
	Sent: Sun 9/26/2004 8:47 AM 
	To: MAPSERVER-USERS at LISTS.UMN.EDU 
	Cc: 
	Subject: Re: [UMN_MAPSERVER-USERS] Oracle Spatial Performance
	
	

	It is also very important for performance reasons to increase the
array size in maporaclespatial.c and recompile. We generally recommend
setting array size to ~ 1000. We generally see an order of magnitude increase
in speed with this change. 

	Mike Smith 
	USACE-CRREL 
	Remote Sensing GIS Center of Expertise 
	Hanover, NH 

	-----Original Message----- 
	From: Frank Warmerdam 
	To: MAPSERVER-USERS at LISTS.UMN.EDU 
	Sent: 9/25/2004 7:34 PM 
	Subject: Re: [UMN_MAPSERVER-USERS] Oracle Spatial Performance 

	Vladimir Guzmán wrote: 
	> Hello, List. 
	> 
	> I don't know if this is an off-topic question but, I really need
some 
	help. 
	> I've worked a lot with mapserver, specially with phpmapscript, and 
	with 
	> shape files and postgis data. 
	> Finally, about 6 months ago, we received a request from a customer
in 
	> order to develop a web application that would be able to connect
with 
	> oracle data. 
	> Well, after face all kinds of issues with oracle spatial,
everything 
	is 
	> working fine now.  Except the speed. 
	> Every map request to oracle spatial takes at least 20 seconds, with
very 
	> small geometries, and in some cases it takes 2 minutes or more. 
	> I would like you to give me some clue, because I've worked with 
	postgis 
	> and shps and I've never had this kind of problems. 
	> All the geometries are indexed. 
	> The time responses are almost the same working with ORACLESPATIAL
and 
	> OGR connection. 
	> 
	> For example: 
	> The command: 
	> ogrinfo OCI:xxx/xxx at xxx -sql "SELECT ID, ORA_GEOMETRY, NOMBRE FROM 
	> SIG_DEPTOS" 
	> Takes 1 minute. 
	> The same geometry in shp: 
	> ogrinfo -al departamentos.shp 
	> Takes less than one second. 

	Vladimir, 

	I can think of a couple of issues. 

	First, the connection overhead to Oracle tends to be pretty
expensive. 
	In my work it was several seconds to a local Oracle instance.  When 
	using 
	MapServer in "cgi" mode where a new process is forked and needs to 
	connect 
	to Oracle for each map request the extra overhead can be quite 
	devestating. 
	The presume solution to this is to keep the process resident, for 
	instance 
	by using FastCGI and caching the oracle connection.  This is
currently 
	not supported by MapServer, but I am working on FastCGI support again
on 
	behalf of Refractions and so a solution may become available in the 
	coming 
	months. 

	Second, by default OGR will collect schema information from all the 
	tables (or at least spatial tables) in your database. This can be
very 
	expensive for a database with alot of tables.  However, this issue is
unique to OGR connections to Oracle, and should not occur with the more 
	direct Oracle support in MapServer. 

	Best regards, 
	-- 
	
---------------------------------------+-------------------------------- 
	------ 
	I set the clouds in motion - turn up   | Frank Warmerdam, 
	warmerdam at pobox.com 
	light and sound - activate the windows | http://pobox.com/~warmerdam 
	and watch the world go round - Rush    | Geospatial Programmer for
Rent