Speedup Inserting Row

Hi,
I have a strange case while inserting row into a database (sqlite).
normally its inserting 5000 data less than a minute, now its increasing almost 4 mins. is there any other way to inserting in the faster method.

I drop the table because I need the data sorted manually, not based from alphabet or numbers.

[code]numbering
dim dbfile as folderitem
dim totalrow as integer
totalrow = Customerslist1.listcount-1
if not app.Gudangstock.connect() then
msgbox “error while connecting”
else
app.Gudangstock.SQLExecute(“Delete from master”)
'app.Gudangstock.commit

  dim rec as DatabaseRecord
  rec = New DatabaseRecord
  for x as integer = 0 to totalrow
    rec.Column("ID") = Customerslist1.cell(x,0)
    rec.Column("barcode") = Customerslist1.cell(x,1)
    rec.Column("code_item") =  Customerslist1.cell(x,2)
    rec.Column("item_name") =  Customerslist1.cell(x,3)
    rec.Column("grp") = Customerslist1.cell(x,4)
    rec.Column("year") =  Customerslist1.cell(x,5)
    rec.Column("type_item") =  Customerslist1.cell(x,6)
    rec.Column("country") =  Customerslist1.cell(x,7)
    rec.Column("volume") =  Customerslist1.cell(x,8)
    rec.Column("pst") =  Customerslist1.cell(x,9)
    rec.Column("foc") =  Customerslist1.cell(x,10)
    rec.Column("gstl") =  Customerslist1.cell(x,11)
    rec.Column("total") =  Customerslist1.cell(x,12)
    app.Gudangstock.InsertRecord("master",rec)
  next
  app.Gudangstock.commit[/code]

thanks
regards,
arief

Would’nt it be possible to just add an order column where you set the order of rows? And then just update this one (and other values if needed) ?

For speed, i’d recommend to do your inserts/updates in transactions with prepared statements.

Your master table is used to populate the list?
The list has 5000 items in it, and people can manually change the sort order somehow on screen?
And next time you load the list you need to keep the last used sort order?

remember that the order of records in the database is not important.
You can retrieve it in any order based on your query.

It seems you are relying on the order you add the records to the master table, to determine the order you get them back.
That is bad design.

Add a column ‘sortorder’, as integer, and initially set the value of that field to be the same as ID

Load your list into the listbox.
When a sort change is made, loop through the list and just do this:

for x as integer = 0 to totalrow app.Gudangstock.SQLExecute( "update master set sortorder = " + format( x,"0") + " where ID = '" + Customerslist1.cell(x,0) + "'") next

When you next load the listbox, your SELECT statement changes to this:

"Select * from master order by sortorder"

Hi,

Yes, I am thinking too, this is the bad idea. but if I can explained about the goals, its really hard to do.
I need to do copy paste the total value rec.Column("total") = Customerslist1.cell(x,12) into the same list (same sort order) in spreadsheet like libreoffice calc identically. If the database on my app is 5000, then this will be 5000 data’s too in spreadsheet with the same sequences.

so I was thinking, dropping the table and inserting the data again from listbox is the only solution till now.
if there is any other solution would be great.

thanks
regards,
arief

[quote=428806:@Arief Sarjono]so I was thinking, dropping the table and inserting the data again from listbox is the only solution till now.
if there is any other solution would be great.

[/quote]
yes the update sentence explained by Jeff just above.

oh ya, I understand now, will tried to create the new one…
I am forgot to tell that, this moment, what I did is inserting row in the listbox, by using listbox.addrow.

I will change and create the new one. will play with mr. tullin’s solution.

thanks
regards,
arief

also bear in mind that regardless of the order in which you insert data, you may or may not get it back in that same order

and if the table is indexed, and you are inserting a large # of records, drop the index first, otherwise it is attempting to adjust the index on every insert, which is a waste of time.