Hello, I've uploaded a modified SQLite example including a performance test option in the files section of this group and of the nsbasic-desktop group. I tested it on a desktop PC, and created a text file which I could compile with NSBasic CE, but I currently can't test it. The program is based on the SQLite example that comes with NSBasic, I added a 2nd form for the performance tests. You can select the number of records and the grouping of the insert statements into transactions, or not use transactions at all for speed comparison. On a PC, 1000 records seems to be a reasonable number if you work without transactions; try for yourself how the result looks if you use transactions with fairly large numbers of records, like 10.000 records total, 1000 per transaction! On CE devices I recommend to start with fairly small numbers to get a feeling for the times. I'm very curious to see results! Kind regards Thomas
--- In [email protected], 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 [email protected], 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 [email protected], "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 [email protected], "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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nsb-ce?hl=en.
