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

[Jan Hartmann]

(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 
> 
>