It turns out that prepared statements are not deallocated until the database connection closes. In other words, if I create and nil 1,000 PostgreSQLPreparedStatements, they will all be around until the connection closes.
Shouldn’t they be deallocated? From what I’ve read, these take up server memory during their lives.
I ran a test and stopped after about 11,000 prepared statements were accumulated. Memory usage jumped to about 250 MB.
Why are you creating so many prepared statements? Can’t you re-use the same statement in a loop?
That was just for testing but in real life, long standing, server side software. i.e. a server process that handles tens of thousands of requests every day and lives for 2-3 weeks between any type of restart. Just seems that Xojo should be deallocating the prepared statement on the server when the prepared statement is destroyed instead of leaving it out there on the server in lala land never to be used again.
You could use MBS SQL Plugin as an alternative…
PS: And fill a feedback case to have this eventually fixed.
How should the Xojo framework know when to release a prepared statement? This is up to the developer. There are two ways: set the prepared statement to nil (directly or indirectly by not having any references to it anymore) or close the connection. You could also create a pool to reuse them.
You do not need so many prepared statements on the same time. You need them in sequence. So close the connection, set them to nil or reuse them from a pool. This is an OOP question, not a prepared statement issue.
in my code the prepared statement is created in a method and always released on the end of the method when the variable goes out of scope.
But if the class doesn’t free the underlaying postgres statement object, you will leak resources.
Eli, in SQL, a “prepared statement” is a thing. If you were only working in SQL, you would create it, use it, then deallocate it. Xojo is not doing the last step so setting the Xojo PreparedSQLStatement to nil is not deallocating the prepared statement at the server.
So, just as an example, suppose you do this:
dim ps1 as PreparedSQLStatement = psqlDb.Prepare( "some sql" )
ps1.SQLExecute( params )
ps1 = nilps1 is no longer accessible in Xojo, but the underlying prepared statement is still open on the server. If the above code is in a method that gets called, say, 1,000 times over the life of the connection, there will be 1,000 identical prepared statements left open on the server, all inaccessible in Xojo, and that’s the point.
I’d say no. If the SQL string is the same in each iteration, you’ll have one prepared statement on the server side. SQL string meaning “some sql” in your post (so without the values you hand over in the Bind method).
How would you deallocate without having access to the underlying prepared statement? Those things are named and you can’t learn through Xojo what the corresponding name is.
And when you say, “I’d say no”, it makes it sound like I’m stating an opinion. I’m not. We spent a lot of time last night testing this and what I wrote is true. Now perhaps the engineers will tell us that, over time, there is some garbage collection that cleans these up, but we didn’t observe anything like that. Once a prepared statement is created on the server by instantiating a PreparedSQLStatement, it stays around until the database connection is closed.
This is not possible as you can’t give a name to the PREPARE statement:
// INVALID CODE:
dim ps1 as PreparedSQLStatement = psqlDb.Prepare( "aName", "some sql" ) // name needed for deallocation
Right, Xojo assigns the names internally without giving you access. “Statement0”, “Statement1”, and the like.
So in the end, we are back to Xojo needing to deallocate the prepared statement when the prepared statement object is destroyed.
Yes, if it really needs to be destroyed.
But I’m still thinking that it is a mistake two create the same prepared statement 11.000 times for the same SQL string. The thing about a prepared statement on the server side is, that it is reused if you feed the values to the same prepared statement on the client side. So make the prepared statement a property of the module or the class or even make it static.
Example:
[code]For i As Integer = 0 To 11.000
dim ps1 as PreparedSQLStatement = psqlDb.Prepare( “some sql” )
ps1.SQLExecute( params )
Next
> you have 11.000 prepared statements on the server
dim ps1 as PreparedSQLStatement = psqlDb.Prepare( “some sql” )
For i As Integer = 0 To 11.000
ps1.SQLExecute( params )
Next
> you have 1 prepared statements on the server and a drastic performance advantage[/code]
Reusing the same PS would be ideal but is not always possible.
[quote=231083:@Eli Ott]
[code]For i As Integer = 0 To 11.000
dim ps1 as PreparedSQLStatement = psqlDb.Prepare( “some sql” )
ps1.SQLExecute( params )
Next
> you have 11.000 prepared statements on the server
dim ps1 as PreparedSQLStatement = psqlDb.Prepare( “some sql” )
For i As Integer = 0 To 11.000
ps1.SQLExecute( params )
Next
> you have 1 prepared statements on the server and a drastic performance advantage[/code][/quote]
You’re exactly right. No one would do the first. That was just a test to see if they were being destroyed.
Now, imagine the second method… being on a server, called by hundreds of people, hundreds of times each through the day.
There is no reason why the plugin shouldn’t deallocate the prepared statement or let me ask it another way… why should the plugin leave memory/resources hanging out on the server which can never be accessed again, because the original object was destroyed? Even if it is only 1 prepared statement? Don’t we fix every memory leak we could possibly find? Especially in long running processes?
I don’t argue against the possibility to deallocate them (I think it the framework should send a DEALLOCATE on nilling the prepared statement), but I would like to see the real-life code where it is not possible to re-use the same prepared statement on the client side. Even in the most complex application with hundreds of users you can instantiate the prepared statement and reuse it. Make it a global variable, or make it a singleton.
That would create a mess in and of itself. You can not run multiple transactions on one database connection, so each user connected to the middleware server gets their own database connection. So, you have hundreds of methods which all create their own prepared statements, many creating 5 or 6 each. You then need to create, store and reuse all of these statements on every single database connection.
That’s just not practical.
Oh… I just read where you said client side. That is pretty dangerous, opening up a database connection to the client. In a n-tier system, the client never has access to the database. The middleware handles all connectivity to the database, all authentication, validation, authorization, business rules, etc… The client never gets a connection to the database, nor should it ever.