Re: [sqlite] How rebuild with larger page size from command line?

On 2008 Dec, 02, at 21:19, Thomas Briggs wrote: > Try removing the semi-colon at the end of the .read statement. The > semi-colon is the query terminator, but because dot-commands aren't > queries they don't require the semi. As such the .read command in > twoLiner.sh is either seeing a

Re: [sqlite] How rebuild with larger page size from command line?

Try removing the semi-colon at the end of the .read statement. The semi-colon is the query terminator, but because dot-commands aren't queries they don't require the semi. As such the .read command in twoLiner.sh is either seeing a third (and invalid) argument or an invalid file name

Re: [sqlite] How rebuild with larger page size from command line?

On 12/2/08, Jerry Krinock <[EMAIL PROTECTED]> wrote: > > On 2008 Dec, 02, at 19:44, Thomas Briggs wrote: > > > Put both commands (the pragma and the read) into a file (e.g. > > foo.txt) and then do: > > > > sqlite3 newDatabase.sqlite '.read foo.txt' > > > Looked like a great idea, Thomas

Re: [sqlite] How rebuild with larger page size from command line?

On 2008 Dec, 02, at 19:44, Thomas Briggs wrote: > Put both commands (the pragma and the read) into a file (e.g. > foo.txt) and then do: > > sqlite3 newDatabase.sqlite '.read foo.txt' Looked like a great idea, Thomas but it doesn't work for me: jk$ echo 'PRAGMA page_size=4096 ;' > twoLiner.sh

Re: [sqlite] SQLite performance woe

Thank you for the explanation! I now have primary keys and indices added to my tables, my exporter was not previously exporting primary keys correctly to SQLite and I just added index exporting. However I am not seeing any performance gain!! Using EXPLAIN QUERY PLAN is mostly coming back as

Re: [sqlite] How rebuild with larger page size from command line?

Put both commands (the pragma and the read) into a file (e.g. foo.txt) and then do: sqlite3 newDatabase.sqlite '.read foo.txt' -T On Tue, Dec 2, 2008 at 8:48 PM, Jerry Krinock <[EMAIL PROTECTED]> wrote: > I need a command-line script running on Mac OS 10.5 to rebuild sqlite > 3 database

[sqlite] How rebuild with larger page size from command line?

I need a command-line script running on Mac OS 10.5 to rebuild sqlite 3 database files with a page_size of 4096 bytes. The first line of my script dumps the database to a text file, then next line should read it create a new one. Since the default page size is 1024 bytes, documentation

Re: [sqlite] SQLite performance woe

To efficiently execute the SQL "SELECT * FROM mytab WHERE myid = '1234'" you must have an index on the "myid" colunm. Each row has an index which uses a rowid as a key, and that is how the row is accessed. A "primary key" is a column which is indexed and which has a unique value, duplicates

Re: [sqlite] SQL example using date()

Greg Robertson <[EMAIL PROTECTED]> wrote: > I would like to do a date search to find records that are between two > dates. The dates are stored in the table in the format DD-MM- but > I can change that to another format but I prefer something readable > rather than an int or double. Just do

Re: [sqlite] SQL example using date()

On 12/2/08, Greg Robertson <[EMAIL PROTECTED]> wrote: > I would like to do a date search to find records that are between two > dates. The dates are stored in the table in the format DD-MM- but > I can change that to another format but I prefer something readable > rather than an int or

Re: [sqlite] SQLite performance woe

All: For comparison I tried several combinations of query orders and indices. I found both indices and the join clause sequence make significant differences in execution time. Using SQLiteSpy with SQLite v3.6.1 on Windows XP. I have two tables: GPFB with 34830 rows, 10 columns with a 4-column

[sqlite] SQL example using date()

I would like to do a date search to find records that are between two dates. The dates are stored in the table in the format DD-MM- but I can change that to another format but I prefer something readable rather than an int or double. Could someone point me to some docs that could help me

Re: [sqlite] SQLite performance woe

-Original Message- Subject: Re: [sqlite] SQLite performance woe I maybe confused but indices sound similar to what I understand primary keys do, I already have primary keys on each table. Unless I'm mistaken as to what primary keys are? From your explanation I guess I'm slightly

Re: [sqlite] SQLite performance woe

-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Tuesday, December 02, 2008 5:03 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite performance woe Hello Donald & Others, I have primary keys set for each of

Re: [sqlite] SQLite performance woe

I maybe confused but indices sound similar to what I understand primary keys do, I already have primary keys on each table. Unless I'm mistaken as to what primary keys are? From your explanation I guess I'm slightly confused about the difference in primary keys and indices and that I need to

Re: [sqlite] SQLite performance woe

Databases work by using indices. A search for a row in a table of 1 million rows goes from having to do as many as a million row reads to a handful of index node accesses, from minutes to milliseconds. Note that Sqlite is "lite" and only uses one index at a time so thoughtful schema design

Re: [sqlite] SQLite performance woe

Hello Donald & Others, I have primary keys set for each of the table but no indicies (that I am aware of) as I simply converted the data from our existing database system which does not support indicies. As my current system only implements primary keys I have no real experience dealing with

Re: [sqlite] Journal files

Okay I updated to version 3.6.6.2 and now I am only seeing "PRAGMA main.journal_mode = OFF;" not work with my custom defined OS ( when I switch to windows it works ) I should note that I changed sqlite3.c to sqlite3.cpp and got the amalgamation to compile in C++ I also defined SQLITE_OS_OTHER

Re: [sqlite] SQLite performance woe

Hi Daniel, Regarding: "What I'd like to know is if there is anything we can do with our queries, SQLite set-up or library configuration to improve the speed? " Unless indicies would be inappropriate, did you mention whether you've defined any indicies and does EXPLAIN QUERY PLAN show

Re: [sqlite] Significance of Sqlite version?

On Dec 2, 2008, at 11:55 AM, Brandon, Nicholas (UK) wrote: > > I note recently that the SQLite version has gone from a 3 point number > (i.e. 3.6.2) to a 4 point number (i.e. 3.6.6.2). 3.6.6.1 and 3.6.6.2 were branch releases to address emergency issues. The next version will be 3.6.7. > >

[sqlite] Significance of Sqlite version?

I note recently that the SQLite version has gone from a 3 point number (i.e. 3.6.2) to a 4 point number (i.e. 3.6.6.2). Should I read any significance into this change? Is there going to be two strands to development/release of SQLite or will the current practice of the 'latest is the best'

Re: [sqlite] SQLite performance woe

> > I am not using the amalgamation version of the source as I > have our my > VFS implementations for two of the platforms I work with > based on the > original win_os.c VFS and the amalgamation does not provide > the > necessary header files (os_common.h and sqliteInt.h) to > make VFS >

Re: [sqlite] Implicit Indices on Subqueries used as "lookups" or joins ....???

I still consider it a work around for adhoc queries. Programatically I can of course use it easily, but when analysing data one runs many adhoc queires which you change minute on minute. Having to create temp tables for each change and give it a new name for each change is a real pain. Further

Re: [sqlite] Implicit Indices on Subqueries used as "lookups" or joins ....???

On 12/2/08, Da Martian <[EMAIL PROTECTED]> wrote: > Hi > > I have continious issues with subquery performance when subqueries are used > for joins. It crops up all the time my daily work. > > If you create a derived table using a subquery and use it in a join SQLite > performance is abysmal.

[sqlite] Implicit Indices on Subqueries used as "lookups" or joins ....???

Hi I have continious issues with subquery performance when subqueries are used for joins. It crops up all the time my daily work. If you create a derived table using a subquery and use it in a join SQLite performance is abysmal. However if you make a temp table from said subquery and index this

Re: [sqlite] C Function call in a Trigger

"Nadeem Iftikhar" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I am trying to call a C function in a sqlite 2 trigger. > > Here is the code [snipped] > trigger|example|contacts|0|CREATE TRIGGER example > AFTER INSERT ON contacts > BEGIN > SELECT altcaps('this is a test'); >

Re: [sqlite] query optimization for inner table join

"Jos van den Oever" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > There's two tables with the same problem. One has an undetermined > number of values: 'm' points to user-definable tag. > In the other table I have about 110 values. This could be spread over > two integer columns.

[sqlite] C Function call in a Trigger

I am trying to call a C function in a sqlite 2 trigger. Here is the code ''' #include #include #include void capitalize_alternate(sqlite_func *context, int argc, const char **argv) { int i; static char str[80]; for (i=0; i

Re: [sqlite] Getting the sqlite3_bind* result

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 schleg wrote: > Anyone know if there's a way to get the actual query that results from > calling sqlite3_bind*? The bindings don't change the query (ie there is no printf equivalent happening behind the scenes). If you want to know what bindings

[sqlite] Getting the sqlite3_bind* result

Anyone know if there's a way to get the actual query that results from calling sqlite3_bind*? I just want to be able to log it after the parameters have been evaluated. Thanks! -- View this message in context: http://www.nabble.com/Getting-the-sqlite3_bind*-result-tp20788369p20788369.html Sent

Re: [sqlite] query optimization for inner table join

2008/12/2 Igor Tandetnik <[EMAIL PROTECTED]>: > You could also try something more straightforward: > > select distinct n from map m1 where >exists (select 1 from map m2 where m1.n=m2.n and m2.m=3) and >exists (select 1 from map m2 where m1.n=m2.n and m2.m=5) and >not exists (select 1