Saving to database take a long time

[NICKYSUWANDI]

I am using Mysql database and .dbf, when i saving data to the database (mysql and .dbf) it took along time.
I need some advise to solve this problem, please help.

Thanks

Nicky

 

[mansii]

I think it depends on many things. You need to explain your problem in more detail. Perhaps, with a few lines of your code.

Regards,
mansii

 

[NICKYSUWANDI]

This is my sample code

*for mysql
Dim mycn As New MySqlConnection
mycn = iclcorp1.F_con(IclcorpMod.Strsvr, IclcorpMod.strusr, IclcorpMod.strpass, IclcorpMod.StrdbMis)
Dim mySQL As String, mycomm As MySqlCommand
mySQL = "INSERT INTO trdpiifa (user_edit,date_edit,grpe,upc,company,branch,divisi,trno,item,colli,stdq,stdu,trno2,prioritas) "
mySQL = mySQL & "VALUES('" & UCase(IclcorpMod.strusr) & "',Now(),'" & IclcorpMod.strgrp & "','1','" & IclcorpMod.strgcom & "','" & IclcorpMod.strgbra & "','" & IclcorpMod.strgdiv & "'"
mySQL = mySQL & ",'" & strtrno & "','" & stritem & "','" & strcolli & "','" & intsqty & "','" & strsunt & "','" & strtrno2 & "','" & strprior & "')"
mycn.Open()
mycomm = New MySqlCommand(mySQL, mycn)
mycomm.ExecuteNonQuery()
mycn.Close()

*for dbf
Dim mycndbf As New OdbcConnection(sConn)
Dim mySQL As String, mycommdbf As OdbcCommand
mySQL = "INSERT INTO trdmrfa1(arc,use,dte,tme,upc,company,branch,div,trno,item,colli,stdq,stdq_smpl,stdq_lots,stdu,area_fr,area_to,ldi_no) "
mySQL = mySQL & "VALUES('A','" & UCase(IclcorpMod.strusr) & "','" & intdate & "','" & inttime & "','1','" & IclcorpMod.strgcom & "','" & IclcorpMod.strgbra & "','" & IclcorpMod.strgdiv & "'"
mySQL = mySQL & ",'" & strtrno & "','" & stritem & "','" & strcolli & "','" & decsqtym & "','" & decsqtys & "','" & decsqtyl & "','" & strsunt & "','" & strarea1 & "','" & strarea2 & "','" & strtrno2 & "')"
mycndbf.Open()
mycommdbf = New OdbcCommand(mySQL, mycndbf)
mycommdbf.ExecuteNonQuery()
mycndbf.Close()

 

[ThatRickGuy]

What kind of load is on the database? What kind of hardware is running the database? What kind of relations are being affected by your data changes?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[NICKYSUWANDI]

i am using mysql 5 and database dbf (foxpro for windows 2.6), my database in my localhost(P 4 2.4 Ghz, memori 768), there are no relations beetwen database

Thanks

Nicky

 

[mansii]

Is the above code block in a kind of loop?
If so, this code:

For i = 0 to 100
    Dim mycndbf As New OdbcConnection(sConn)
    sConn.Open
    .
    .
    sConn.Close
Next i

takes longer than

sConn.Open
For i = 0 to 100
    Dim mycndbf As New OdbcConnection(sConn)
    .
    .
Next i
sConn.Close

because sConn is re-open and close for times.
Well, I was just curious.

Regards

 

[NICKYSUWANDI]

thanks mansii for u advise
i trying using you advise, this the result:

Test 1 : took 147 second to save 17 record
sConn.Open 'mysql connection
For i = 0 to 100
mycndbf.open 'dbf connection
.
mycndbf.close
Next i
sConn.Close


Test 2 : took 123 second to save 17 record
sConn.Open 'mysql connection
mycndbf.open 'dbf connection
For i = 0 to 100
.
.
.
Next i
mycndbf.close
sConn.Close

for information, my procedure update 5 table (dbf) and 2 table (mysql table in same database)

Thanks

 

[ThatRickGuy]

You might be able to eak some better times if you move the update commands to Stored Procs in the databases. That will allow the DB engine to precompile the command and bypass repetetive syntax checks.

For a large set of updates like this I would recommend SQL Loader or a DTS package, but I'm not sure what kind of options are available for dbf and MySQL. MySQL probrably has some sort of mass loader, you could check around on source forge or with the MySQL developers. But I have no idea on dbf, I usually run away quickly when the Fox Pro topic comes up.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[mansii]

Sorry for the long silence, NICKY.

Many experts here state that the design of the database affects the access time (i.e. read and write).
Defining Primary Keys speeds up the data transaction.

As far as I know, many db designers do not put any primary keys in foxpro 2.6 database.
Please check the time consume for each db transaction.

I never use MySql. But I'm sure Rick does. Please take his advice. And if you're not sure what to do, do not hesitate to ask him for more details.

Regards,
Iman

 

[NICKYSUWANDI]

thanks for you help friends

i had test a time to saving 1 record in 1 table, it took 1-2 second.
And i check again my code, i think this procedure couse saving time to long (F_Stock1= there 3 table to update in this procedure, and update 1 field each time).

<code>
'IclCorpTra1.F_Stock1("-EBQ", IclcorpMod.strgcom, IclcorpMod.strgbra, Microsoft.VisualBasic.Left(CBoxFrom.Text, 3), strarea1, stritem, strcolli, strsunt, decsqtym)
IclCorpTra1.F_Stock1("-QOH", IclcorpMod.strgcom, IclcorpMod.strgbra, Microsoft.VisualBasic.Left(CBoxFrom.Text, 3), strarea1, stritem, strcolli, strsunt, decsqtym)
IclCorpTra1.F_Stock1("-QOP", IclcorpMod.strgcom, IclcorpMod.strgbra, Microsoft.VisualBasic.Left(CBoxFrom.Text, 3), strarea1, stritem, strcolli, strsunt, decsqtym)
</code>

i had correct F_Stock1 procedure, when i run the program and saving, it took 82 second to save 17 record (old is 123 second).
But it's still to long for saving 1 transaction.
there's any idea (i just learn and make program in Vb in 2 month, i newbie, so i need you help to solve my problem friend)

Thanks

Nicky

 

[ThatRickGuy]

if its taking 2 seconds to save one record I'm going to guess some of that overhead is in the connection setup since the time per record drops when you run multiple updates. Beyond that, it looks like you are hitting performance limitations on the database side. I'd try posting on the fox pro forums to see if anyone can help you tweek some more performance out of your database and tables.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

foxpro was never that slow. must be the odbc link.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]