[postgis-users] Re: [mapserver-users] SDE performance

[Jan Hartmann]

(Forwarded. What DO the PostGIS folks think? JH)

Steve Lime wrote:
> Ok, I (call me Steve) will take a look at your proposal. It's sort of
> along the
> lines I was thinking although I was thinking of storing connection
> objects
> themselves and using a connection strings (or portions thereof) as
> keys.
> Any reaction from the PostGIS folks?
> 
> Steve
> 
> Stephen Lime
> Data & Applications Manager
> 
> Minnesota DNR
> 500 Lafayette Road
> St. Paul, MN 55155
> 651-297-2937
> 
> 
>>>>Jan Hartmann <jhart at frw.uva.nl> 11/21/02 07:34AM >>>
> 
> (cross-posted to mapserver-dev)
> 
> Using one database connection for multiple layers could be easier than
> 
> you think with callback functions. Just create TWO arrays: one with the
> 
> CONNECTION strings and one an array of function pointers. All pointers
> 
> are initialized to NULL. With each new CONNECTION, the main program 
> copies the CONNECTION string into the CONNECTION array and passes the 
> array number to the database subprogram (SDE, PostGIS, Oracle). This 
> checks if this connection duplicates a prior one and uses it if it
> finds 
> one. Else it opens a new connection. In the case of a new CONNECTION it
> 
> puts the address of a closing function into the second array at the
> same 
> position as where it got the CONNECTION string from. In the case of an
> 
> existing CONNECTION it leaves this array element NULL. At the end of
> the 
> main program the main application loops through the array of function 
> pointers and executes every one that is not NULL.
> 
> There are two benefits to this:
> - The main programmer (let's call him Steve) would have to add only a 
> dozen or so lines of pretty trivial code and can put his mind to more 
> urgent matters. Everything else, including the closing mechanisms, has
> 
> to be done by the database programmers, which is the way it should be
> - Even more important: database programmers would not be forced to use
> 
> this mechanism. If they want to close each layer themselves, they are 
> free to do so, as long as they don't touch the functions array.
> Existing 
> code can be used alongside the new mechanism and adapted when the 
> individual programmers can find time. The only thing that would have to
> 
> be changed initially would be the manner of passing the CONNECTION to 
> the database program: not a string, but a number pointing into the 
> CONNECTIONstrings array.
> 
> The following is a working demo:
> 
> #include <stdio.h>
> 
> // CONNECTION string array
> char *connStrings[50];
> // good old-fashioned C: array of funtion pointers returning int and
> //                       accepting int as parameter
> int (*funcs[50])(int);
> 
> 
> // closing functions, to be defined by database programmers
> int closeSDE(i) {
>      printf("SDE closed from connection opened with:
> %s\n",connStrings[i] );
> }
> 
> int closePostGIS(i) {
>      printf("PostGIS closed from connection opened with: 
> %s\n",connStrings[i] );
> }
> 
> int closeOracle(i) {
>      printf("Oracle closed from connection opened with: 
> %s\n",connStrings[i] );
> }
> 
> main() {
> 	int i;
> 
> // initialize arrays
>      for (i=0;i<50;i++) {
>          connStrings[i] = NULL;
>          funcs[i] = NULL;
>      }
> 
> // First connection	(SDE)
>      connStrings[0] = "Connection string for SDE";
> // SDE program opens connection and sets:
>      funcs[0] = closeSDE;
> 
> // Second connection (PostGIS)
>      connStrings[1] = "Connection string for PostGIS";
> // PostGIS program opens connection and sets:
>      funcs[1] = closePostGIS;	
> 
> // Third and following connections (all SDE)
>      connStrings[2] = "Connection string for SDE";
>      connStrings[3] = "Connection string for SDE";
>      connStrings[4] = "Connection string for SDE";
> // SDE program uses existing connection and doesn't set closing
> function
> 
> // Sixth connection
>      connStrings[5] = "Connection string for Oracle";
> // Oracle program opens connection and sets:
>      funcs[5] = closeOracle;	
> 
> // Seventh connection
>      connStrings[6] = "Connection string for PostGIS";
> // PostGIS program uses existing connection and doesn't set closing
> function
> 
> 
> // main program closes all connections at the end
>      for (i=0;i<50;i++) {
>          if (funcs[i]) (*funcs[i])(i);  //obfuscated but working!
>      }
> }
> 
> How about it?
> 
> Jan Hartmann
> Department of Geography
> University of Amsterdam
> jhart at frw.uva.nl 
> 
> Steve Lime wrote:
> 
>>I think folks will take it seriously, but the timing may a bit bad
> 
> with
> 
>>folks
>>rushing to polish of work in 3.7. At a minimum this should be logged
>>in
>>Bugzilla so it doesn't get lost. If someone would like to work on a
> 
> fix
> 
>>that doesn't involve global variables, but rather a connection pool
>>that
>>becomes part of the main mapObj then I (as the SDE guy) would be
>>happy to work to implement it. I don't think it's too hard, just a
>>bunch
>>of bookeeping since you might leverage multiple SDE/Oracle or
> 
> PostGIS
> 
>>connections in a single run.
>>
>>Steve
>>
>>Stephen Lime
>>Data & Applications Manager
>>
>>Minnesota DNR
>>500 Lafayette Road
>>St. Paul, MN 55155
>>651-297-2937
>>
>>
>>
>>>>>"C F" <gis_consultant at hotmail.com> 11/20/02 12:24PM >>>
>>
>>Regarding MapServer creating a NEW db connection for each db
> 
> layer....
> 
>>is 
>>anyone else taking this seriously?  I thought this would be just the
> 
> 
>>beginning of the discussion but there doesn't seem to be too much
>>interest 
>>in this besides us few.  This seems like a pretty unnecessary
>>performance 
>>constraint.  I would think that it would be a fairly easy fix, but
> 
> it's
> 
>>not 
>>my place to be making those assumptions... But I just thought I'd
> 
> bring
> 
>>it 
>>up again in case everyone forgot :)
>>
>>
>>
>>
>>>From: Jan Hartmann <jhart at frw.uva.nl>
>>>To: mapserver-users at lists.gis.umn.edu 
>>>Subject: Re: [mapserver-users] SDE performance
>>>Date: Wed, 13 Nov 2002 11:57:40 +0100
>>>
>>>My impression is that this would not be very hard to implement: just
> 
> a
> 
>>
>>>global array with pointers to every CONNECTION opened in all LAYERS.
>>
>>The 
>>
>>
>>>applications itself (PostGIS, SDE, Oracle) could test for already
> 
> open
> 
>>
>>>connections. The only thing MapServer itself would have to do is
>>
>>closing 
>>
>>
>>>all connections at the end.
>>>This would certainly be of great help for Oracle and SDE users;
> 
> large
> 
>>
>>>connection overheads have been mentioned regularly on this list.
> 
> Even
> 
>>for 
>>
>>
>>>PostGIS there are advantages; think about reusing complex queries
> 
> with
> 
>>
>>>spatial operators and TMP tables. IMHO certainly worth looking into!
>>>
>>>Jan Hartmann
>>>
>>>
>>>Paul Ramsey wrote:
>>>
>>>
>>>>One possibility would be to abstract the connection making process
> 
> a
> 
>>bit, 
>>
>>
>>>>so that a 'getConnection' function first checks a hash to see if
>>
>>there is 
>>
>>
>>>>already a connection with parameters which match the requested
>>
>>connection. 
>>
>>
>>>>It would probably have to reside on the mapObj though...
>>>>
>>>>P.
>>>>
>>>
>>>Jan Hartmann
>>>Department of Geography
>>>University of Amsterdam
>>
>>
>>
>>_________________________________________________________________
>>Help STOP SPAM with the new MSN 8 and get 2 months FREE*  
>>http://join.msn.com/?page=features/junkmail 
>>
>>
> 
> 
>