Hi all,
I converting one of my apps from using mysql via odbc to postgres (cos handling the ODBC is a p.i.t.a )
Everything works as it should, but it's made me think of a few things and wonder what should be best practice.
1. Should a connection be opened at the start and closed at the end of the program or opened and closed after every operation ? is there a performance hit.
2. My app is so large that i decided to break it into smaller programs that are called from the main program ( using runprogram ) - is there a way to carry an open database connection over between programs ?
3. is there any way of secruring the connection to the db other than using ssh ( which is also a p.i.t.a ) ?
Any thoughts ?
Cheers
DB questions
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
- RSBasic
- Moderator
- Posts: 1218
- Joined: Thu Dec 31, 2009 11:05 pm
- Location: Gernsbach (Germany)
- Contact:
Re: DB questions
If you want to execute many SQL queries in a short time, leave the SQL connection open, otherwise it will be very slow if you always establish and close the connection.captain_skank wrote:1. Should a connection be opened at the start and closed at the end of the program or opened and closed after every operation ? is there a performance hit.
Maybe with Shared Memory?captain_skank wrote:2. My app is so large that i decided to break it into smaller programs that are called from the main program ( using runprogram ) - is there a way to carry an open database connection over between programs ?
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
Re: DB questions
Thanks for the reply.
Again, thanks for the info.
cheers
Of course - but is there any advantage/disadvantage in opening and closing connections at need rather than leaving a connection open throughout the operation of the program even though it may not be in use for 70% of the time ?If you want to execute many SQL queries in a short time, leave the SQL connection open, otherwise it will be very slow if you always establish and close the connection.
Oooh that's a bit above my pay grade - how would that work with an open db connection ?Maybe with Shared Memory?
Again, thanks for the info.
cheers
Re: DB questions
In the case of a multi-user application, it is always preferable to close the connection as soon as possible after a SQL query sequence.
Indeed, for each open connection, the server blocks a certain amount of RAM.
In general we limit the number of concurrent connections on a server so as not to saturate the RAM.
If the application crashes before manual disconnection, it will be necessary to wait for the timeout for the more active connections to be closed.
On a normal network, opening is almost instantaneous, so close and open on demand and keep the connection active only for a sequence of requests.
Indeed, for each open connection, the server blocks a certain amount of RAM.
In general we limit the number of concurrent connections on a server so as not to saturate the RAM.
If the application crashes before manual disconnection, it will be necessary to wait for the timeout for the more active connections to be closed.
On a normal network, opening is almost instantaneous, so close and open on demand and keep the connection active only for a sequence of requests.
- captain_skank
- Enthusiast
- Posts: 636
- Joined: Fri Oct 06, 2006 3:57 pm
- Location: England
Re: DB questions
I'd never considered the preallocation of RAM on the server ( mainly becasue the server I run this on has RAM to burn ).Marc56us wrote:In the case of a multi-user application, it is always preferable to close the connection as soon as possible after a SQL query sequence.
Indeed, for each open connection, the server blocks a certain amount of RAM.
In general we limit the number of concurrent connections on a server so as not to saturate the RAM.
If the application crashes before manual disconnection, it will be necessary to wait for the timeout for the more active connections to be closed.
On a normal network, opening is almost instantaneous, so close and open on demand and keep the connection active only for a sequence of requests.
Also by opening and closing the connection as required makes my life easier in the long run now I've thought about it a little more - so I think i'm going with this option at the moment.
Thanks for the input / advice.
Now all i need to do is secure the communication to the server and all's good.
Cheers
Re: DB questions
For that reason I wrote a PostgreSQL_SSL_Proxy
At the moment it uses the gnutls dlls to do the ssl stuff.
But it is only 99.5% stable and ... I'm not allowed to share it.
As soon as I start this Proxy I can connect via localhost:5432 to the database.
The Proxy is a pbi file which creates a thread for handling the in/out packages.
Since gnutls are more then 1 dll and it don't want to tell me how many data is available,
I want to switch to libssl or cryptlib. But up to now with no success.
If everything is working fine, we offer this pbi as a 'product'.
a small snippet:
Or Fred add a lot of ssl stuff and it is no longer needed to write something arround.
Bernd
At the moment it uses the gnutls dlls to do the ssl stuff.
But it is only 99.5% stable and ... I'm not allowed to share it.
As soon as I start this Proxy I can connect via localhost:5432 to the database.
The Proxy is a pbi file which creates a thread for handling the in/out packages.
Since gnutls are more then 1 dll and it don't want to tell me how many data is available,
I want to switch to libssl or cryptlib. But up to now with no success.
If everything is working fine, we offer this pbi as a 'product'.
a small snippet:
Code: Select all
Structure PostgreSQL_SSLProxy_ParameterStructure
PostgresServer$
PostgresPort.i
LocalPort.i
Con.i
Thread.i
Connected.i
Exit.i
EndStructure
If SSLWanted
If PostgreSQL_SSLProxyStart(@SSLProxyParameter)
DB = OpenDatabase(#PB_Any, "host=127.0.0.1 port=" + Str(SSLProxyParameter\LocalPort) + " dbname="+ DBName$, DBUser$, DBPassword$, #PB_Database_PostgreSQL)
If DB = 0
MessageRequester("Error", Str(#PB_Compiler_Line) + ": Not able to open SSL database: " + DBName$)
End
EndIf
Else
MessageRequester("Error", Str(#PB_Compiler_Line) + ": SSL connection failed: " + SSLProxyParameter\PostgresServer$)
End
EndIf
Else
DB = OpenDatabase(#PB_Any, "host=" + DBServer$ + " port=5432 dbname="+ DBName$, DBUser$, DBPassword$, #PB_Database_PostgreSQL)
If DB = 0
MessageRequester("Error", Str(#PB_Compiler_Line) + ": Not able to open database: " + DBName$)
End
EndIf
EndIf
Or Fred add a lot of ssl stuff and it is no longer needed to write something arround.
Bernd