Huge memory increase when updating many records

Huge memory increase when updating many records

Postby accessor » Fri 29 Jun 2007 10:58

Hello,

I encounterd an strange problem when using postgresql 8.2.4 and crlab postgredirect 3.00.

On vb.net 2005.

While doing 200.000 updates from an textfile in a loop the memory consumption on the server goes trough the roof, resulting in an out of memory error.

When i add only once a msgbox in my code after 10.000 updates the memory-inrease climbs until it processed the 10.000 updates.

after that all runs smooth with no memory increase.

But this msgbox cannot remain afcourse.

Any suggestions?

thanks

Martijn

Code:

Connect_DB()
cMd = New PgSqlCommand()
'Trans = GCon.BeginTransaction()
'cMd.Transaction = Trans
' cMd.Connection = GCon

Do
RDline = sr.ReadLine()
If RDline = "" Then Exit Do
MutCode = RDline.Substring(0, 1)
Levcode = RDline.Substring(1, 20)

If Levcode = "2" Then
Delete_recordPR()
Else
Dim NettoPR, BrutoPR As String
BrutoPR = RDline.Substring(52, 10)
NettoPR = RDline.Substring(62, 10)

If BrutoPR = " " Then BrutoPR = "0.00"
If NettoPR = " " Then NettoPR = "0.00"


SQL1 = "UPDATE store.artikelen SET brutoprijs = '" + BrutoPR + "',nettoprijs = " & _
"'" + NettoPR + "',prijseenheid = " & "'" + RDline.Substring(82, 3) + "' ,br_pr_bew_toeslag = " & "'" + RDline.Substring(88, 10) + _
"' where artikelcode = " & "'" + Levcode + "'"
Try

' MsgBox(SQL1)
cMd = New PgSqlCommand(SQL1, GCon)
'cMd = New PgSqlCommand
'cMd.CommandText = SQL1
cMd.ExecuteNonQuery()
Teller2 = Teller2 + 1
Label5.Text = Teller2 & " - " & Teller & " - " & Teller3
Label5.Refresh()

If Teller2 >= 10000 Then
Teller3 = Teller3 + 1
' Trans.Commit()
GCon.Close()
Teller2 = 0
' Teller = 0
terug:
Label5.Text = Teller
Label5.Refresh()
If Teller <= 2 Then
Teller = Teller + 1
MsgBox("5000")
End If

Connect_DB()
cMd = New PgSqlCommand()
' Trans = GCon.BeginTransaction()
' cMd.Transaction = Trans
' cMd.Connection = GCon
End If

' MsgBox(NumAffected.ToString & " records updated ", MsgBoxStyle.Critical)
Catch Err As Exception
Sw.WriteLine(SQL1 & " : " & Err.Message)
MsgBox(SQL1 & " : " & Err.Message)
End Try
End If
accessor
 
Posts: 10
Joined: Fri 29 Jun 2007 10:48

Postby Alexey » Mon 02 Jul 2007 08:48

Please send me a small test project to reproduce the problem.
Include the definition of your own database objects.
Use e-mail address provided in the Readme file.
Do not use third party components.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby accessor » Thu 05 Jul 2007 18:38

Files are emailed to your supportteam.

Thank you for any given help
accessor
 
Posts: 10
Joined: Fri 29 Jun 2007 10:48

Postby Alexey » Fri 06 Jul 2007 09:22

Your database script is too big. I can't execute it because of errors raised. Please create a simplified script with only needed objects.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL