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]
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:
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.
[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.
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.