large number of execute statements / memory leak ? 
Author Message
 large number of execute statements / memory leak ?

Hello all,

i wrote a procedure to read all records from a local table, convert it into
a standard SQL insert into statements
and execute it as a pass-through query.
Now.... everything works fine as long as not too many records are in the
sourcetable
if there are, the system will run out of memory, at least my system does.
While code is running and observing the system monitor i can see available
physical memory dropping (slowly but still)

any idea what may be causing this ?

TIA

Iskander

Public Sub SyncTable(ByVal strSourceTable As String, ByVal strTargetTable As
String)
Dim strSQl As String, db As Database, rs As DAO.Recordset, TableField
Dim strFieldList As String, strValueList As String, Account As DBaccount,
Qdf As QueryDef

With Account
    .IpAdres = "server_ip_adres"
    .Database = "databasename"
    .uid = "username"
    .Passwd = "passwd"
    .DSN = "dsn_name"
End With

Set db = CurrentDb
Set Qdf = db.CreateQueryDef("")
    strConnect = "ODBC;DATABASE=" & Account.Database & ";UID=" & Account.uid
& ";PWD=" & Account.Passwd & ";DSN=" & Account.DSN
    Qdf.Connect = strConnect
    Qdf.ReturnsRecords = False

'reset remote table
strSQl = "DELETE FROM " & strTargetTable & ";"
    Qdf.sql = strSQl
    Qdf.Execute

'read local table
strSQl = "SELECT * FROM " & strSourceTable & ";"
    Set rs = db.OpenRecordset(strSQl, dbOpenSnapshot)

'loop through local table records
If Not rs.EOF Then
    rs.MoveFirst
    Do Until rs.EOF

        For Each TableField In rs.Fields
            'create field list separated by a comma after each value
            strFieldList = strFieldList & TableField.name & Chr(44)
            'create value list separated by a comma after each value
            'the function escapequote replaces all single quotes in a given
string with two single quotes
            strValueList = strValueList & "'" & EscapeQuote(TableField) &
"'" & Chr(44)
        Next

        'chop off the last inserted chr(44
        strFieldList = Left(strFieldList, Len(strFieldList) - 1)
        strValueList = Left(strValueList, Len(strValueList) - 1)

        'create the SQL string to pass to the sql server
        strSQl = "INSERT INTO " & strTargetTable & " (" & strFieldList & ")
values(" & strValueList & ");"

        strFieldList = ""
        strValueList = ""

        Qdf.sql = strSQl
        Qdf.Execute
        rs.MoveNext
        strSQl = ""
        DoEvents
    Loop
    rs.Close
End If

Set rs = Nothing
Set db = Nothing
Set Qdf = Nothing

End Sub



Sun, 30 Nov 2003 17:34:31 GMT  
 large number of execute statements / memory leak ?
Hello all,

i wrote a procedure to read all records from a local table, convert it into
a standard SQL insert into statements
and execute it as a pass-through query.
Now.... everything works fine as long as not too many records are in the
sourcetable
if there are, the system will run out of memory, at least my system does.
While code is running and observing the system monitor i can see available
physical memory dropping (slowly but still)

any idea what may be causing this ?

TIA

Iskander

Public Sub SyncTable(ByVal strSourceTable As String, ByVal strTargetTable As
String)
Dim strSQl As String, db As Database, rs As DAO.Recordset, TableField
Dim strFieldList As String, strValueList As String, Account As DBaccount,
Qdf As QueryDef

With Account
    .IpAdres = "server_ip_adres"
    .Database = "databasename"
    .uid = "username"
    .Passwd = "passwd"
    .DSN = "dsn_name"
End With

Set db = CurrentDb
Set Qdf = db.CreateQueryDef("")
    strConnect = "ODBC;DATABASE=" & Account.Database & ";UID=" & Account.uid
& ";PWD=" & Account.Passwd & ";DSN=" & Account.DSN
    Qdf.Connect = strConnect
    Qdf.ReturnsRecords = False

'reset remote table
strSQl = "DELETE FROM " & strTargetTable & ";"
    Qdf.sql = strSQl
    Qdf.Execute

'read local table
strSQl = "SELECT * FROM " & strSourceTable & ";"
    Set rs = db.OpenRecordset(strSQl, dbOpenSnapshot)

'loop through local table records
If Not rs.EOF Then
    rs.MoveFirst
    Do Until rs.EOF

        For Each TableField In rs.Fields
            'create field list separated by a comma after each value
            strFieldList = strFieldList & TableField.name & Chr(44)
            'create value list separated by a comma after each value
            'the function escapequote replaces all single quotes in a given
string with two single quotes
            strValueList = strValueList & "'" & EscapeQuote(TableField) &
"'" & Chr(44)
        Next

        'chop off the last inserted chr(44
        strFieldList = Left(strFieldList, Len(strFieldList) - 1)
        strValueList = Left(strValueList, Len(strValueList) - 1)

        'create the SQL string to pass to the sql server
        strSQl = "INSERT INTO " & strTargetTable & " (" & strFieldList & ")
values(" & strValueList & ");"

        strFieldList = ""
        strValueList = ""

        Qdf.sql = strSQl
        Qdf.Execute
        rs.MoveNext
        strSQl = ""
        DoEvents
    Loop
    rs.Close
End If

Set rs = Nothing
Set db = Nothing
Set Qdf = Nothing

End Sub



Sun, 30 Nov 2003 17:34:31 GMT  
 large number of execute statements / memory leak ?
Why not just use a single INSERT statement to load the whole table instead
of one INSERT per record?  Use something like:
strSQL = "INSERT INTO " & strTargetTable & "SELECT * FROM " & strSourceTable

after you do the DELETE query.  You'll also find that this performs much
faster.

Good Luck,
Tom


Quote:
> Hello all,

> i wrote a procedure to read all records from a local table, convert it
into
> a standard SQL insert into statements
> and execute it as a pass-through query.
> Now.... everything works fine as long as not too many records are in the
> sourcetable
> if there are, the system will run out of memory, at least my system does.
> While code is running and observing the system monitor i can see available
> physical memory dropping (slowly but still)

> any idea what may be causing this ?

> TIA

> Iskander

> Public Sub SyncTable(ByVal strSourceTable As String, ByVal strTargetTable
As
> String)
> Dim strSQl As String, db As Database, rs As DAO.Recordset, TableField
> Dim strFieldList As String, strValueList As String, Account As DBaccount,
> Qdf As QueryDef

> With Account
>     .IpAdres = "server_ip_adres"
>     .Database = "databasename"
>     .uid = "username"
>     .Passwd = "passwd"
>     .DSN = "dsn_name"
> End With

> Set db = CurrentDb
> Set Qdf = db.CreateQueryDef("")
>     strConnect = "ODBC;DATABASE=" & Account.Database & ";UID=" &
Account.uid
> & ";PWD=" & Account.Passwd & ";DSN=" & Account.DSN
>     Qdf.Connect = strConnect
>     Qdf.ReturnsRecords = False

> 'reset remote table
> strSQl = "DELETE FROM " & strTargetTable & ";"
>     Qdf.sql = strSQl
>     Qdf.Execute

> 'read local table
> strSQl = "SELECT * FROM " & strSourceTable & ";"
>     Set rs = db.OpenRecordset(strSQl, dbOpenSnapshot)

> 'loop through local table records
> If Not rs.EOF Then
>     rs.MoveFirst
>     Do Until rs.EOF

>         For Each TableField In rs.Fields
>             'create field list separated by a comma after each value
>             strFieldList = strFieldList & TableField.name & Chr(44)
>             'create value list separated by a comma after each value
>             'the function escapequote replaces all single quotes in a
given
> string with two single quotes
>             strValueList = strValueList & "'" & EscapeQuote(TableField) &
> "'" & Chr(44)
>         Next

>         'chop off the last inserted chr(44
>         strFieldList = Left(strFieldList, Len(strFieldList) - 1)
>         strValueList = Left(strValueList, Len(strValueList) - 1)

>         'create the SQL string to pass to the sql server
>         strSQl = "INSERT INTO " & strTargetTable & " (" & strFieldList &
")
> values(" & strValueList & ");"

>         strFieldList = ""
>         strValueList = ""

>         Qdf.sql = strSQl
>         Qdf.Execute
>         rs.MoveNext
>         strSQl = ""
>         DoEvents
>     Loop
>     rs.Close
> End If

> Set rs = Nothing
> Set db = Nothing
> Set Qdf = Nothing

> End Sub



Sun, 30 Nov 2003 23:01:08 GMT  
 large number of execute statements / memory leak ?
Oh .... I just created the construction to test if a large number of execute
statements would cause a problem.
obviously, if sending all table data to the remote server was it's only
purpose, your suggestion would be the way to go

Thanks for your time anyway :)

iskander


Quote:
> Why not just use a single INSERT statement to load the whole table instead
> of one INSERT per record?  Use something like:
> strSQL = "INSERT INTO " & strTargetTable & "SELECT * FROM " &
strSourceTable

> after you do the DELETE query.  You'll also find that this performs much
> faster.

> Good Luck,
> Tom



> > Hello all,

> > i wrote a procedure to read all records from a local table, convert it
> into
> > a standard SQL insert into statements
> > and execute it as a pass-through query.
> > Now.... everything works fine as long as not too many records are in the
> > sourcetable
> > if there are, the system will run out of memory, at least my system
does.
> > While code is running and observing the system monitor i can see
available
> > physical memory dropping (slowly but still)

> > any idea what may be causing this ?

> > TIA

> > Iskander

> > Public Sub SyncTable(ByVal strSourceTable As String, ByVal
strTargetTable
> As
> > String)
> > Dim strSQl As String, db As Database, rs As DAO.Recordset, TableField
> > Dim strFieldList As String, strValueList As String, Account As
DBaccount,
> > Qdf As QueryDef

> > With Account
> >     .IpAdres = "server_ip_adres"
> >     .Database = "databasename"
> >     .uid = "username"
> >     .Passwd = "passwd"
> >     .DSN = "dsn_name"
> > End With

> > Set db = CurrentDb
> > Set Qdf = db.CreateQueryDef("")
> >     strConnect = "ODBC;DATABASE=" & Account.Database & ";UID=" &
> Account.uid
> > & ";PWD=" & Account.Passwd & ";DSN=" & Account.DSN
> >     Qdf.Connect = strConnect
> >     Qdf.ReturnsRecords = False

> > 'reset remote table
> > strSQl = "DELETE FROM " & strTargetTable & ";"
> >     Qdf.sql = strSQl
> >     Qdf.Execute

> > 'read local table
> > strSQl = "SELECT * FROM " & strSourceTable & ";"
> >     Set rs = db.OpenRecordset(strSQl, dbOpenSnapshot)

> > 'loop through local table records
> > If Not rs.EOF Then
> >     rs.MoveFirst
> >     Do Until rs.EOF

> >         For Each TableField In rs.Fields
> >             'create field list separated by a comma after each value
> >             strFieldList = strFieldList & TableField.name & Chr(44)
> >             'create value list separated by a comma after each value
> >             'the function escapequote replaces all single quotes in a
> given
> > string with two single quotes
> >             strValueList = strValueList & "'" & EscapeQuote(TableField)
&
> > "'" & Chr(44)
> >         Next

> >         'chop off the last inserted chr(44
> >         strFieldList = Left(strFieldList, Len(strFieldList) - 1)
> >         strValueList = Left(strValueList, Len(strValueList) - 1)

> >         'create the SQL string to pass to the sql server
> >         strSQl = "INSERT INTO " & strTargetTable & " (" & strFieldList &
> ")
> > values(" & strValueList & ");"

> >         strFieldList = ""
> >         strValueList = ""

> >         Qdf.sql = strSQl
> >         Qdf.Execute
> >         rs.MoveNext
> >         strSQl = ""
> >         DoEvents
> >     Loop
> >     rs.Close
> > End If

> > Set rs = Nothing
> > Set db = Nothing
> > Set Qdf = Nothing

> > End Sub



Mon, 01 Dec 2003 18:29:11 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. for each statement leaks memory (BUG)

2. Memory Leaks/Resource Leaks

3. OLE Automation Problems - MEMORY HOG (Memory Leak?)

4. memory leak in vitual memory

5. memory leak on vitual memory

6. Memory Leak -- Out of Memory

7. ADO Memory Leak results in out of memory

8. Inserting large number of records into a large table with SQL/DAO

9. Managing/processing a large number of large arrays

10. Very simple - How to execute sub statements before the end statement

11. Expected:line number or label or statement or end of statement

12. Follow up on leak in For Next statement

 

 
Powered by phpBB® Forum Software