So i replace the Word "INSERT" with "BEGIN TRANSACTION"?
If the code is Do recs = File.ReadText(-3) def= Split(recs,",") abc = Split(def(3),"""") cmd="INSERT OR REPLACE INTO ""NameDB2"" VALUES(""" & def(1) & """,""" & abc(1) &""")" Set r=db.Execute(cmd) combobox1.additem def(1) & " - " & abc(1) Loop Until file.EOS Where do I use the Begin/Commit? --- In nsbasic...@yahoogroups.com, Thomas Gruber <computerhu...@...> wrote: > > Hello, > I did a few performance tests with SQLite, with the desktop version though, > not CE. I suspect, but can't test it at the moment, that the CE version will > behave in a similar way roughly. > First of all, I ran a loop of 1000 inserts to a table (I used the SQLite > example program from NSBasic with its database definition), this took about > 18s. The loop without the db.execute statement was too fast to measure by the > way, so the time was taken almost entirely by the database insert statement. > Then I changed the "INSERT OR REPLACE" to INSERT, but this made no difference. > Then I removed the index (the primary key), this speed up the program > slightly to 16s; using INSERT only then was another 2 seconds faster. > Eventually I used a transaction, with "BEGIN TRANSACTION" before starting the > loop of 1000 inserts, "COMMIT TRANSACTION" at the end. The time became 0! > I then did the same for 10.000 records, and the time was just under 1s! I > checked it, the records were really inserted! > I don't really know how big (how many inserts) a transaction can be on a CE > device, this is certainly memory dependent, but for small records you should > be able to do a COMMIT only every 1000 records. And this should speed up mass > inserts considerably! > Of course, I'd be very interested to hear results from a CE/Windows Mobile > device! > Kind regards > Thomas > > Am 23.02.2010 um 20:28PM schrieb bigp...@...: > > > Hey, Thanks for all the reply's. Only been using this about two weeks now > > and never done programming before. > > Not using the Inert command does not speed it up much, it inserts about > > another 100 recs per minute. Would take 45 mins to insert all 30,000 recs > > > > What I doing now is giving the user the option to select the "customer" > > from a list .ie (C0AAA006) and then using a File.Find command to search the > > file for matching records. The code looks something like this > > > > Do > > pos = File.Find(ClientName) > > If pos >= 0 Then > > rec= file.readtext(-3) > > SplitString = Split(rec,",") > > EquipmentNumber = Split( SplitString(2),"""") > > > > combobox1.AddItem EquipmentNumber(1) > > File.Pos = File.Pos + 1 > > Loop Until pos < 0 > > End If > > > > With this code it searches the entire file for a record that matches what > > was typed in the textbox "ClientName" > > > > Then it populates a combo box with the Equipment Numbers that are apart of > > the records that have the client name match the textboxes client name. > > Luckly this only takes about 3 minutes to search the entire 30,000 record > > file....BUT I still ran into a problem > > > > The program "hangs" while it is doing its search, which is understandable. > > What i would like to know is if there is a way to display to the user that > > the records are being read and the program simply isn't just "frozen" > > > > I tried to add a "i = i + 1" and display the count of records being read in > > a label so the user atleast knows something is going on, but that does not > > seem to work. > > > > Is there a way to display any sort of message or notifier to the user while > > the program is doing its "File.Find" command? > > > > --- In nsbasic...@yahoogroups.com, Thomas Gruber <computerhusky@> wrote: > > > > > > Hello, > > > I assume that the 1st value you insert is some sort of key in the > > > database table? Is it indexed/unique? It may be worth removing the index > > > before the insert, inserting the 30.000 records, then re-creating the > > > index, if you can be sure that the values are all unique and no REPLACE > > > operation will take place. Also, do you need "INSERT OR REPLACE", or can > > > you just use INSERT, in other words can you be sure you never want to > > > replace a record? If that's the case it may save some time, as there's no > > > need for the system to check for an existing record, and update the index > > > for every record. > > > Kind regards > > > Thomas > > > > > > Am 23.02.2010 um 08:37AM schrieb bigpete@: > > > > > > > Yes, with some testing it is the INSERT that is slowing it down... A > > > > LOT. Without the insert the file with 30,000 records gets read within 5 > > > > minutes. > > > > > > > > Yes the quotes are always present. > > > > > > > > Is there no faster way to INSERT records from a text file to the > > > > database? > > > > > > > > --- In nsbasic...@yahoogroups.com, "joespan123" <joes@> wrote: > > > > > > > > > > > > > > > > > > > > Hi, > > > > > > > > > > Are you sure that reading the file is the slowest part. > > > > > > > > > > I would think that performing the INSERT to the database would be > > > > > slowest part. > > > > > > > > > > The use of "Split" may be a slow function call. > > > > > > > > > > Can you guarentee the quotes are all ways present, if so you may be > > > > > able to use the "Mid" function to strip off the quotes. > > > > > > > > > > > > > > > Also I use the "file.LineInputString()" to read a line from a file, > > > > > maybe try using that to see if it is faster than "File.ReadText(-3)". > > > > > > > > > > Cheers > > > > > Joe > > > > > > > > > > --- In nsbasic...@yahoogroups.com, "bigpete@" <bigpete@> wrote: > > > > > > > > > > > > I have a file with This type of record > > > > > > > > > > > > 1,C0AAA006,"AAA MOBILE STORAGE(ON)",,,,,,,,,,,,0001,,,,,, > > > > > > > > > > > > > > > > > > > > > > > > Now I have this following code > > > > > > > > > > > > Do > > > > > > recs = File.ReadText(-3) > > > > > > SplitRecs= Split(recs,",") > > > > > > NoQuotes = Split(def(2),"""") > > > > > > > > > > > > i = i+1 > > > > > > txtrecords.Text = i > > > > > > cmd="INSERT OR REPLACE INTO ""NameDB"" VALUES( """ & SplitRecs(1) & > > > > > > """,""" & NoQuotes(1) &""")" > > > > > > showstatus SplitRecs(1) & " - " & NoQuotes(1) > > > > > > > > > > > > > > > > > > It takes a while for the file to get read in (About 5 minutes to > > > > > > read 1000 records :s) > > > > > > > > > > > > Is there a way that I can get File.ReadText or some other way to > > > > > > only read the first 3 piece of data after the comma's... > > > > > > 1,C0AAA006,"AAA MOBILE STORAGE(ON)"???...instead of the entire > > > > > > line. I think that may speed up the searching. This file has about > > > > > > 30,000 records. > > > > > > > > > > > > OR is there another way I can get that data from a file read into > > > > > > the SQL lite database? > > > > > > > > > > > > > > > > > > > > > > > > > > > -- You received this message because you are subscribed to the Google Groups "nsb-ce" group. To post to this group, send email to nsb...@googlegroups.com. To unsubscribe from this group, send email to nsb-ce+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/nsb-ce?hl=en.