For the following code where do I use the Begin/Commit transaction? Do I
replace the word INSERT?
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)
Loop Until file.EOS
--- 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.
