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 [email protected]: > 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" <j...@...> 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.
