[sqlite] Sqlite crashes when i imort huge list

[onemind]

Hi,

I am using the sqlite gui and click import table from csv. I select a txt
file that contain over 200,000 words in a list. Sqlite works fine with a
smaller list of 200-300 words but when i import my big list, it hangs for
ages or completely crashes my computer.

Does anyone know how i can import this list into a table successfully?

Thanks for any help.
--
View this message in context: 
http://www.nabble.com/Sqlite-crashes-when-i-imort-huge-list-t1842991.html#a5030925
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] SQLite and Linux problem

[Alan Mead]

Ashish Singh wrote:
> 1:After the installation how do I where can i go and acess SQLite 
> program on RedHat Linux

After installation (of the binary packages, at least), the command line
tools will be available.

You also (and most importantly) have the sqlite libraries for use in
your own code... including Perl which I've used very successfully... to
use Perl, I think you just become root and type 'cpan' then 'install
DBD::sqlite'  You can see a tiny bit of example Perl code after
installation of the sqlite DBD module by typing 'perldoc DBD::sqlite'
Also see the 'Performance' section of that page.

> Can anybody guide me how do I do all these things in sequence or  if 
> you can direct me towards
> some with where I can get appropriate help.

Are you asking for help to write a program that inserts several thousand
records and then performs some queries?

-- 
Alan D. Mead, Ph.D. : [EMAIL PROTECTED] : 815-588-3846

A statistician, who refused to fly after reading of the alarmingly high
probability that there will be a bomb on any given plane, realized that
the probability of there being two bombs on any given flight is very
low.  Now, whenever he flies, he carries a bomb with him.

Re: [sqlite] calling sqlite3_interrupt in another thread

[drh]

"Roger Binns" <[EMAIL PROTECTED]> wrote:
> Is it safe to have one sqlite3* in one thread doing various operations
> and then calling sqlite3_interrupt from another thread on that same
> sqlite3* pointer?  Or does sqlite3_interrupt always have to be called
> in the same thread as the operations?
> 
> The doc doesn't say anything either way.  The code is pretty trivial
> only changing (sqlite3*)db->flags but that isn't done under any form
> of mutex and looks like race conditions could cause problems.
> 

No, it is not safe to invoke sqlite3_interrupt from a different
thread.  Though now that you mention it, I can see where that
might be useful.  I might change it so that sqlite3_interrupt can
be safely invoked from a different thread for the next release.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] SQLite and Linux problem

[Ashish Singh]

Hello Everybdoy
I am a  new user to linux, I am trying to install SQLite on RedHat 
linux.
My questions are
1:After the installation how do I where can i go and acess SQLite 
program on RedHat Linux
2:Is there anything else I need to do after $ make install $ /sbin 
/ldconfig
3: I need to populate the DB  with upto 5 million entries starting with 
10K entries then I need to
run queries aganist the DB to check its scalability.

Can anybody guide me how do I do all these things in sequence or  if 
you can direct me towards
some with where I can get appropriate help.

Thanking you all  in advance
Ashish Singh
323-404-8621



Happy Coding!!!

  Warm regards 
  Ashish Singh 
  [EMAIL PROTECTED], [EMAIL PROTECTED] 
Graduate Student (MS-Software Engineering) 
University of Southern California www.cs.usc.edu  
Los angeles California USA 
  323 404 8621(M)  
  213-746-4142-(R)
   

Re: [sqlite] Improving insert speed?

[Kurt Welgehausen]

> ... should I be using commit before end ...

Commit and end are synonyms; you don't need both.

> ... this is single user, I assume using immediate or exclusive is  ok ...

It's OK but not necessary. A simple begin is just as good, since
no one else can apply a lock before yours is upgraded.

Regards

[sqlite] calling sqlite3_interrupt in another thread

[Roger Binns]

Is it safe to have one sqlite3* in one thread doing various operations
and then calling sqlite3_interrupt from another thread on that same
sqlite3* pointer?  Or does sqlite3_interrupt always have to be called
in the same thread as the operations?

The doc doesn't say anything either way.  The code is pretty trivial
only changing (sqlite3*)db->flags but that isn't done under any form
of mutex and looks like race conditions could cause problems.

Roger

Re: [sqlite] Improving insert speed?

[Tony Harris]

OMG!  I can't believe the speed difference!

I wrapped around 3000 transactions, executing them 3 times, so I did a

for y=0, y<3, y++
{
begin immediate transaction testtrans
{
for x=0, x<3000, x++
insert record
}
end transaction testtrans
}

and inserting those 9000 records took maybe a second!  Wow, much more 
impressive!! Thanks for the suggestions!



Now my only question is looking at the reference, it talks about begin, end, 
commit, rollback - should I be using commit before end, or does end imply a 
commit?


also, since this is single user, I assume using immediate or exclusive is 
ok.


-Tony

- Original Message - 
From: "A. Pagaltzis" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, June 24, 2006 12:07 PM
Subject: Re: [sqlite] Improving insert speed?



* Tony Harris <[EMAIL PROTECTED]> [2006-06-24 19:05]:

Is this about average, or is there a way I might be able to get
a little more speed out of it?


Put a transaction around your INSERTs, at least around batches of
a few thousand each, and you’ll get much better speed.

Regards,
--
Aristotle Pagaltzis //  

Re: [sqlite] Improving insert speed?

[John Stanton]

Tony Harris wrote:

Hi all,

I have a database where one table stores the bulk of logged data, it has 14 
columns, 11 integers and 3 reals.

Initially I'm gonna have to import a lot of data into this database - about 
9,000 entries per months worth of data.  I already know 3K entries takes about 
192KBytes in the db file.  The biggest problem I am having is insert speed.  I 
know the initial amount will cover 3-4 months worth of data that will need to 
be inserted.  Right now I'm clocking raw inserts of data w/ no unique checking 
at about 1 min 26 sec / 1K entries.  Is this about average, or is there a way I 
might be able to get a little more speed out of it?

Granted, I might be hitting a ceiling of any db api, but I was hoping to squeeze a little more speed if possible ;)  


-Tony

Are you inserting in a transaction?

Re: [sqlite] Improving insert speed?

[A. Pagaltzis]

* Tony Harris <[EMAIL PROTECTED]> [2006-06-24 19:05]:
> Is this about average, or is there a way I might be able to get
> a little more speed out of it?

Put a transaction around your INSERTs, at least around batches of
a few thousand each, and you’ll get much better speed.

Regards,
-- 
Aristotle Pagaltzis // 

[sqlite] Improving insert speed?

[Tony Harris]

Hi all,

I have a database where one table stores the bulk of logged data, it has 14 
columns, 11 integers and 3 reals.

Initially I'm gonna have to import a lot of data into this database - about 
9,000 entries per months worth of data.  I already know 3K entries takes about 
192KBytes in the db file.  The biggest problem I am having is insert speed.  I 
know the initial amount will cover 3-4 months worth of data that will need to 
be inserted.  Right now I'm clocking raw inserts of data w/ no unique checking 
at about 1 min 26 sec / 1K entries.  Is this about average, or is there a way I 
might be able to get a little more speed out of it?

Granted, I might be hitting a ceiling of any db api, but I was hoping to 
squeeze a little more speed if possible ;)  

-Tony

Re: [sqlite] Virtual Table: xRowID shortcommings

[Dan Kennedy]

> I have played with the new Virtual Table interface from CVS and found some 
> shortcommings for the
> current implementation of the xRowID method:
> 
>   int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid);
> 
> As far as I understand, this function is called by SQLite whenever it needs a 
> unique identifer
> for a row/item as a signed 64 bit integer type. SQLite uses this to pass it 
> back to other
> Virtual Table methods (most notably xUpdate) so the Virtual Table 
> implementation can use this
> identifier to locate a particular row/item for deletes, inserts, or updates.

Quite correct. It will also be accessible as the "rowid" field via SQL.

> I can see two problems here:
> 
> 1. Using a signed 64 bit integer type works fine for SQLite, but might be 
> insufficient for other
> Virtual Table implementations. Imagine a Virtual Table which maps the files 
> of a disk/directoy.
> In Windows, for example, a unique ITEMIDLIST can be generated for a file or 
> folder. However,
> this ITEMIDLIST is not a signed 64 bit integer, so mapping an ITEMIDLIST to 
> the current
> implementation is not easily possible. I can even imaginge other scenarios 
> where the virtual
> table implementation must allocate a special memory structure larger than 64 
> bit to uniquely
> identify a row/item.

> 2. In case the virtual table implementation needs to allocate memory in order 
> to uniquely
> describe a row/item, this memory needs to be freed when no longer used. As I 
> see it, there is no
> such method in the Virtual Table implementation.

Maybe the transaction part of the virtual table API is useful in
this context (xBegin/xSync/xCommit/xRollback). SQLite will only store
values retrieved via xRowid for the duration of a transaction. So
if you need to create a mapping between the integer id's used by
SQLite and the complex identifiers, you can throw the table away
at the end of the transaction.

A linked list and a willingness to cast pointers to 64-bit integers
seems like it would do the trick :)

Of course that won't help with sqlite apps that expect the rowid
field to remain persistent between queries that span multiple 
transactions (i.e. MS Access style GUIs etc.).


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] Need Regression Testing Insight

[drh]

Chris Werner <[EMAIL PROTECTED]> wrote:
> So... 
> 
> sqlite-3.3.5
> 
> Would you expect ./testfixture ./test/quick.test to pass with "0 errors out
> of 24922 tests"
> 
> But ./testfixture ./test/all.test to bail abruptly with:
> 
> btree2-2.5... Ok
> btree2-2.6...
> Error: invalid command name "btree_cursor_info"

This error is because you did not compile with -DSQLITE_DEBUG=1,
which is a requirement (apparently) if the test infrastructure is
to work.  The test infrastructure is omitted from ordinary
builds since it would bloat the code.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Problem with OpenVB on Win98

[shivaranjani]

Hello,

 

I am using the SQLite for the database support in my application.

 

Does anyone has encountered this problem of openVB() function getting failed
when trying to create a new

 Database file. Basically the actual error I am getting is 

 

"QueryInterface for interface DSSQLiteWrap.ISQLiteWrap failed." 

 

What could be the reason.