Hello,
no, the INSERT statement remains as it was.
This is the idea (using your code):
db.execute("BEGIN TRANSACTION")
Do
recs = File.ReadText(-3)
...
...
...
Loop Untile file.EOS
db.execute("COMMIT TRANSACTION")
This may not work for 30.000 records, you may have to commit more frequently
than that. Then you can put a counter inside the loop, like this, to commit the
changes after every 1000 records written:
i = 0
db.execute("BEGIN TRANSACTION")
Do
i = i + 1
if i> 1000 then
db.execute("COMMIT TRANSACTION")
i = 0
db.execute("BEGIN TRANSACTION")
end if
recs = File.ReadText(-3)
...
...
...
Loop Until file.EOS
db.execute("COMMIT TRANSACTION")
Kind regards
Thomas
Am 23.02.2010 um 21:47PM schrieb [email protected]:
> 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.
