Issue 101841 - Memory leak in SQL statement executeUpdate() method
Summary: Memory leak in SQL statement executeUpdate() method
Status: CONFIRMED
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: recent-trunk
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-05-12 18:05 UTC by leebert
Modified: 2013-08-07 15:45 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description leebert 2009-05-12 18:05:32 UTC
I'm using Gooo / OpenOffice 3.0.1, OOO300m15 Build 9379 / 2009-02-03.
Application is OO Calc. I'm using OO Calc to webscrape & download CSV files,
then act as a data loader, so this bug crops up quite readily in my work.
Running this routine leads to soffice.bin consuming RAM at the rate of
140k/second, ultimately consuming a great deal of RAM, then crashing.

Sub Main()

Dim dbContext, oDataSource, oDB, oStatement as Object, i, iMax as Integer

dbContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = dbContext.getByName("mydatabase")
oDB = oDataSource.GetConnection("username","password")
oStatement = oDB.createStatement

i = 1
iMax = 50

For i = 1  To iMax

   cSQL = "INSERT INTO dbo.ztest01 ( col1, col2, col3 ) VALUES " _
   & "(   "  _
   & "   '" & right( "000" & trim(cstr(i)),4) _
   & "' ,'" & right( "000" & trim(cstr(i)),4) _
   & "' ,'" & right( "000" & trim(cstr(i)),4) _
   & "'  )"
                                         
   ' memory leak here:
   iResult = oStatement.executeUpdate( cSQL )
     
         
Next i

oStatement.close
oStatement.dispose()

oDB.close
oDB.dispose()   


End Sub
Comment 1 leebert 2009-05-12 18:07:47 UTC
The SQL table structure is simply:

CREATE TABLE ztest01(
col1 varchar(50)
,col2 varchar(50)
,col3 varchar(50)
) ;
Comment 2 leebert 2009-05-13 15:42:23 UTC
One workaround I just discovered is to use the preparedStatement() method
instead. Although it means changing my SQL-building code & requires setting
specific types on the client side instead of implicit & explicit server-side
casting, it exhibits no memory leakage whatsoever. Another added advantage:
com.sun.star.sdb.OPreparedStatement.execute() is also vastly faster than
com.sun.star.sdb.OStatement.execute() or executeUpdate()

But it'd be really grand to have the memory leak in con.statement fixed.

Example:

dbContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = dbContext.getByName("mydb01")
oDBCon = oDataSource.GetConnection("userid","password")

oPrepStmt = oDBcon.prepareStatement("INSERT INTO dbo.ztest01 _
                  ( col1, col2, col3 ) VALUES ( ?, ?, ? )")

for i = 1 to 100
   oPrepStmt.setString(1, "blah")
   oPrepStmt.setString(2, "blah")
   oPrepStmt.setString(3, "blah") 
   oPrepStmt.execute()
      
next i

Comment 3 noel.power 2009-05-13 17:29:20 UTC
sounds like a database issue
Comment 4 Frank Schönheit 2009-05-13 19:44:25 UTC
putting into DBA's QA pool
Comment 5 leebert 2009-05-14 14:42:00 UTC
Just FYI,

I observed yesterday that a prepared statement also leaked memory if it was
issued (instantiated) repeatedly. This might be acceptable for now given that I
will be able to set up  a single prepared SQL statement & just clear & use only
the parameters as needed.

Thanks!

Comment 6 leebert 2009-05-15 15:22:22 UTC
For SQL Server, Sybase (& probably Oracle & DB/2) I found the best hybrid method
so far: Use a prepared statement hybrid using SQL Server's "exec()" command.

Simply prepare a typical SQL string (insert, whatever) and call SQL Server's
(originally Sybase's) "exec ( ... )" function with the SQL string simply
embedded as a prepared statement "?" arg marker.

FWIW this has both advantages of static string SQL (no field-by-field parameter
matching necessary) and being wicked fast, too. It appears to me to be 10x
faster than using macro-basic's oStatement.executeUpdate(...).

CODE
<CODE>:

    dbContext = createUnoService("com.sun.star.sdb.DatabaseContext")
    oDataSource = dbContext.getByName("mydb")
    oDBcon = oDataSource.GetConnection("userid","password")

    ' set up the SQL parameter using the prep'd statement "?" marker, calling
SQL Server's
    ' exec() dynamic runtime command function
    oPrepStmt = oDBcon.prepareStatement( "exec ( ? )" )

    for i = 1 to 1000
    ' match the "?" marker as parameter "1", using setString() to make an entire
    ' SQL insert string as a prep'd statement parameter.
    oPrepStmt.setString( 1, "insert into ztest01 ( col00 ) values ( '" &
cstr(now()) & "' )" )
    oPrepStmt.execute()

    ' clear out the old statement, ready for the next one....
    oPrepStmt.clearParameters()
    next i

    oPrepStmt.close
    oPrepStmt.dispose()

    oDBcon.close
    oDBcon.dispose()

</CODE>

============
I'm assuming the performance gain would be equivalent for all other SQL
operations as well. Oracle's PL-SQL equivalent is EXEC SQL EXECUTE IMMEDIATE
<sql command>. DB/2's SQL-PL probably has it too.

Obviously if one's DBMS-of-choice doesn't have an EXEC command handy there's
probably a way to make a stored procedure wrapper for SQL commands to take
advantage of the speed gain from preparedStatement that emulates the convenience
of "static" string-based SQL, avoiding the typical problem of parameter matching
of prepared statements (i.e. insert into ztable (col00, col001, col002) values (
? , ? , ? ).

FWIW there's also a problem somewhere in sdbc:odbc bridge or MS's ODBC that
oPreparedStatement.setDate( com.sun.star.util.Date ) will yield a
"option/feature not implemented" error, so string-built SQL is more than just a
convenience in OO Basic...
Comment 7 ocke.janssen 2010-12-02 12:35:11 UTC
I just tested it with a DEV300m94 which in that area is identical with a OOo 3.3
and I could not reproduce it with the embedded db (hsqldb). No leak which I can
see. Do you have a sample db which I could test? Or may the issue is fixed in
that release :-) Could you please check. Thanks.