[sqlite] planning to use sqlite for a project involving huge data

Hi all, I am planing to use sqlite for a project involving huge data. The planned sqlite file (which will contain a single table) will grow to more than 4g in size. Has anyone try that before and what problems have you encountered? I heard that the initial disk caching time might be a problem,

Re: [sqlite] Build static under Solaris

Anders Persson wrote: Phuah Yee Keat skrev: so do something like gcc --static -I../sqlite -L../sqlite/.libs -o db-test db-test.c -lsqlite3 -lrt Okej tested and i got ld: fatal: library -lrt: not found ld: fatal: File processing errors. No output written to db-test collect2: ld returned 1

Re: [sqlite] sqlite puzzle

And to correct myself one last time: * A. Pagaltzis <[EMAIL PROTECTED]> [2006-05-03 00:30]: > Assuming your client names are unique, this should work: > > SELECT > ( > SELECT > COUNT(*) > FROM clients c2 > WHERE c2.name < c1.name >

Re: [sqlite] More columns vs. several tables

If you have a wide table and want to optimize performance you would do better to increase the page size. JS Jay Sprenkle wrote: On 5/2/06, Felix Schwarz <[EMAIL PROTECTED]> wrote: Hi, Morning! "Put small and frequently used columns early in the table to minimize the need to follow the

[sqlite] SQLite timeout

Hi, What is the default busy_timeout in SQLite? When we set busy_timeout how often does it try to execute the query? What happens when we get a connection to the SQLite from Java? Does it get a filehandle or does it lock the file? Thanks, Raj

Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

> Does it only happen with a :memory: database or even > if you use a > file? When using a file the difference is much smaller, but still there: ~70 qps with sqlite3, ~90 qps with sqlite2. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the

Re: [sqlite] sqlite puzzle

* A. Pagaltzis <[EMAIL PROTECTED]> [2006-05-03 00:30]: > I tried to do it with a join to see if that would work better, > but I’m too frazzled to figure it out right now. I must be more frazzled than I thought. SELECT n1.name, COUNT( n2.name ) rank FROM names n1 CROSS

Re: [sqlite] sqlite puzzle

* JP <[EMAIL PROTECTED]> [2006-05-02 22:10]: > SQLite provides a way to get the N-th row given a SQL statement, with > LIMIT 1 and OFFSET . > > Can the reverse be done in an efficient way? For example, given a table > with 1million names, how can I return the row number for a particular >

Re: [sqlite] sqlite puzzle

* Kurt Welgehausen <[EMAIL PROTECTED]> [2006-05-02 22:15]: > No, you can't do that in SQL. You can. > The results of an SQL query are a set of rows; the rows are not > produced in any guaranteed order. If this was true, how would `LIMIT` work? Sure, the results do not have any implicit order,

Re: [sqlite] Read-only mode

Chris, I was going through the Java wrapper for SQLite. I noticed that they were setting an attribute called 'readonly' to false. The other attributes, timeout etc seemed to be associated with SQLite and I thought SQLite had some read-only mode though I couldn't find any documentation for it.

Re: [sqlite] sqlite puzzle

On 5/2/06, JP <[EMAIL PROTECTED]> wrote: Can the reverse be done in an efficient way? For example, given a table with 1million names, how can I return the row number for a particular element? i.e. something like is select rowid from table where name = 'foo' what you want?

Re: [sqlite] sqlite puzzle

JP <[EMAIL PROTECTED]> wrote: > SQLite provides a way to get the N-th row given a SQL statement, with > LIMIT 1 and OFFSET . > > Can the reverse be done in an efficient way? For example, given a table > with 1million names, how can I return the row number for a particular > element? No, you

[sqlite] Re: sqlite puzzle

JP wrote: SQLite provides a way to get the N-th row given a SQL statement, with LIMIT 1 and OFFSET . Can the reverse be done in an efficient way? For example, given a table with 1million names, how can I return the row number for a particular element? i.e. something like SELECT rownum FROM

[sqlite] sqlite puzzle

SQLite provides a way to get the N-th row given a SQL statement, with LIMIT 1 and OFFSET . Can the reverse be done in an efficient way? For example, given a table with 1million names, how can I return the row number for a particular element? i.e. something like SELECT rownum FROM

RE: [sqlite] Read-only mode

Raj, I could be wrong here... My understanding is that SQLite is 'Zero configuration' meaning that the underlying file system handles such issues as read/write access to a given database. I have not seen any DOCs on a "Read-Only mode". If you create a database, and later change the file

[sqlite] Read-only mode

Hi, What's the Read-only mode used for in SQLite? How do I specify that the SQLite database should be opened in the Read-Only mode? I'm accessing SQLite from Perl. Thanks, Raj

Re: [sqlite] More columns vs. several tables

Felix Schwarz <[EMAIL PROTECTED]> wrote: > > I'm wondering whether there is a big > performance hit for a simple > > SELECT binarydata FROM entries WHERE somehash = 27817298; > > when I use > > CREATE TABLE entries( > entry_id INTEGER PRIMARY KEY, >

Re: [sqlite] More columns vs. several tables

Felix Schwarz wrote: Hi, I have to decide on a table layout for storing the data of my upcoming project. Yesterday I have made my way through the excellent presentation at http://www.sqlite.org/php2004/page-036.html and read the sentence "Put small and frequently used columns early in

Re: [sqlite] More columns vs. several tables

On 5/2/06, Felix Schwarz <[EMAIL PROTECTED]> wrote: Hi, Morning! "Put small and frequently used columns early in the table to minimize the need to follow the overflow chain." Now, that's interesting! And I'm wondering whether there is a big performance hit for a simple SELECT

Re: [sqlite] Complile and connecting to SQLite

Hi Dennis, Doug, thanks a lot for this info, helps a lot Aaron. On 02/05/06, Doug Nebeker <[EMAIL PROTECTED]> wrote: Pre-compiled Windows DLL: http://www.sqlite.org/sqlitedll-3_3_5.zip Source for Windows: http://www.sqlite.org/sqlite-source-3_3_5.zip You can compile the source (I happen to

Re: [sqlite] How sqlite3_bind_parameter_count counts binding parameters

Aidan Reel wrote: if you prepare the following statement 'insert into Table (id, id1) values( ?3, :id ); ' and then use sqlite_bind_parameter on the prepared statement you will receive the number 4. I appreciate where that answer is coming from, it takes the largest unnamed argument and

[sqlite] More columns vs. several tables

Hi, I have to decide on a table layout for storing the data of my upcoming project. Yesterday I have made my way through the excellent presentation at http://www.sqlite.org/php2004/page-036.html and read the sentence "Put small and frequently used columns early in the table to minimize

RE: [sqlite] Complile and connecting to SQLite

Pre-compiled Windows DLL: http://www.sqlite.org/sqlitedll-3_3_5.zip Source for Windows: http://www.sqlite.org/sqlite-source-3_3_5.zip You can compile the source (I happen to do it with Microsoft Visual Studio) and get the DLL as well. The links above are available on the following page:

Re: [sqlite] Complile and connecting to SQLite

Aaron Jones wrote: there is no dll file in the sqlite source Aaron, To use SQLite on Windows you need to download 3 separate files. http://www.sqlite.org/sqlite-3_3_5.zip contains a command line shell statically linked to the sqlite library. You can use it to create, open, test sqlite

[sqlite] How sqlite3_bind_parameter_count counts binding parameters

Hi Using 3.3.5 if you prepare the following statement 'insert into Table (id, id1) values( ?3, :id ); ' and then use sqlite_bind_parameter on the prepared statement you will receive the number 4. I appreciate where that answer is coming from, it takes the largest unnamed argument and adds

Re: [sqlite] Vacuum Command

I testad on sqlite and sorry you was corrent i got en error messages. // Anders George Mavroudes skrev: I was with the impression that you cannot call VACUUM from within a Transaction. I conclude this by reading the code. Will this work? GM -Original Message- From: Anders

Re: [sqlite] Complile and connecting to SQLite

there is no dll file in the sqlite source On 02/05/06, Eric Bohlman <[EMAIL PROTECTED]> wrote: Aaron Jones wrote: > Windows XP, don't know what compiler to use. MinGW and all the common versions of Visual Studio work. > > I am creating a GUI to SQLite, so need my interface to connect to

Re: [sqlite] Vacuum Command

I am new to SQLITE but in some other database vacuum want to have full controll test with Transaction as BEGIN EXCLUSIVE TRANSACTION test VACUUM [tableName] END TRANSACTION test // Anders George Mavroudes skrev: Sorry for the omission and thanks for the quick response. -We are using

RE: [sqlite] Vacuum Command

I was with the impression that you cannot call VACUUM from within a Transaction. I conclude this by reading the code. Will this work? GM -Original Message- From: Anders Persson [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 02, 2006 10:33 AM To: sqlite-users@sqlite.org Subject: Re:

Re: [sqlite] Vacuum Command

George Mavroudes wrote: If I delete all records from a [tableName] and then execute the VACUUM command, will this temporarily delete the table (causing a SCHEMA_CHANGE) and then add this table back into the db? George, Yes, SQLite has an optimization which replaces a delete without a

RE: [sqlite] Vacuum Command

Sorry for the omission and thanks for the quick response. -We are using sqlite 2.8.16 -In all other cases I can get a human readable message but with this case the error message is blank More info to the Original Question: - The VACUUM command returns asynchronously success but later on other

Re: [sqlite] Vacuum Command

GM> hi, GM> - I am pretty new to SQLITE and I am having a problem with the GM> following sequence of commands: GM> - DELETE FROM [tableName] GM> - VACUUM [tableName] GM> The project I am working on has multiple threads Reading / Writing to this GM> table and other

[sqlite] Vacuum Command

hi, - I am pretty new to SQLITE and I am having a problem with the following sequence of commands: - DELETE FROM [tableName] - VACUUM [tableName] The project I am working on has multiple threads Reading / Writing to this table and other tables, and after

Re: Re[2]: [sqlite] where can i find the file "sqlite.h"

Yes, It is. Thank you Anders. :) 2006/5/2, Erik Jensen <[EMAIL PROTECTED]>: MS> Hi Anders, MS> But I cannot find "sqlite3.h" in all sqlite downloads either. MS> Is there any way to generate this file. it's definitely in here: http://www.sqlite.org/sqlite-source-3_3_5.zip Eric

Re[2]: [sqlite] where can i find the file "sqlite.h"

MS> Hi Anders, MS> But I cannot find "sqlite3.h" in all sqlite downloads either. MS> Is there any way to generate this file. it's definitely in here: http://www.sqlite.org/sqlite-source-3_3_5.zip Eric

Re: [sqlite] where can i find the file "sqlite.h"

Opps i make my version from source on a solaris machine. But download to the dll zipfile to get the dll.ex. sqlitedll-3_3_5 download sqlite-source-3_3_5.zip if you open this you found all headerfiles and yes... sqlite3.h // Anders Min Shi

Re: [sqlite] where can i find the file "sqlite.h"

Hi Anders, But I cannot find "sqlite3.h" in all sqlite downloads either. Is there any way to generate this file. Thanks Min 2006/5/2, Anders Persson <[EMAIL PROTECTED]>: Min Shi skrev: > Hi all, > Where can i find the file "sqlite.h" for coding on windows platform? > Thank you for your

Re: [sqlite] where can i find the file "sqlite.h"

Min Shi skrev: Hi all, Where can i find the file "sqlite.h" for coding on windows platform? Thank you for your suggestions. Min is is called sqlite3.h // Anders

[sqlite] where can i find the file "sqlite.h"

Hi all, Where can i find the file "sqlite.h" for coding on windows platform? Thank you for your suggestions. Min

Re: [sqlite] Build static under Solaris

Anders Persson wrote: i try to compile it with gcc --static -I../sqlite -L../sqlite/.libs -o db-test db-test.c -lsqlite3 But i get errors like Undefined first referenced symbol in file fdatasync

Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

--- Erik Jensen <[EMAIL PROTECTED]> wrote: > Does it only happen with a :memory: database or even > if you use a > file? I'll test it this evening (but I need a :memory: database for my application) > With version of the v3 lib did you use? It's 3.3.5. > I'm asking because i noticed a similar

Re: [sqlite] SQLite :memory: performance difference between v2 and v3?

IV> I've spent a long time debugging low performance of an IV> application that uses :memory: database and have found IV> that sqlite v2 is much faster than v3. After some IV> digging around it seems that even two proof-of-concept IV> programs that are identical except for used SQLite IV> version