Re: [sqlite] decimal function not found

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 20/03/12 21:20, YAN HONG YE wrote: > my sqlite sql sentens is : "select '88', > Convert(decimal(5,1),AVG(qph))" The list of supported functions supplied with SQLite is here: http://www.sqlite.org/lang_corefunc.html You can add your own

[sqlite] decimal function not found

my sqlite sql sentens is : "select '88', Convert(decimal(5,1),AVG(qph))" but build the project in c shows the error: Error: no such function: decimal how to process this problem? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] VACUUMing large DBs

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 20/03/12 20:08, Udi Karni wrote: > Thanks! I got one and tried - and it seems to improve overall > performance about 2X. Very cool. Depending on your backups and tolerance for data loss, you can also do things like RAID 0 striping across

Re: [sqlite] VACUUMing large DBs

On Tue, Mar 20, 2012 at 01:59:59PM -0700, Udi Karni scratched on the wall: > Hello, > > I am creating large DBs - each with a single table (magnitude of a few > hundred million rows / 100GB). It takes a few transformations to get to the > final product. When done - I VACUUM the final result. > >

Re: [sqlite] VACUUMing large DBs

Thanks! I got one and tried - and it seems to improve overall performance about 2X. Very cool. The 240GB SSD drives are pretty reasonably priced and would suffice for most tables. I'm just wondering how long before Flash Write Fatigue sets in and you need a replacement. On Tue, Mar 20,

Re: [sqlite] Optimize Table Access Efficiency

On 20 Mar 2012, at 9:33pm, Tim Morton wrote: > So it seems the index is no help; a second topics table is a significant > help; and a separate file with topics table a negligible help; and creation > order is no help. Interesting and a little unexpected. Good to see

Re: [sqlite] Optimize Table Access Efficiency

I did some quick tests: I took a 100,000 entry dictionary and made a single table "create table dict (id INTEGER PRIMARY KEY, topics, def)" I ran a "for loop" on the cursor object to extract all the topic column data and put in in a Python list . cur.execute('SELECT topics FROM dict') start

Re: [sqlite] VACUUMing large DBs

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 20/03/12 13:59, Udi Karni wrote: > And a more general question. My PC has 8GB of RAM. I am considering > getting a much larger machine that can take upwards of 100-200GB of > RAM. I'd recommend getting one or more SSDs instead (also a lot

Re: [sqlite] sqlite3 command line shell bug?

Am 20.03.2012 21:28, schrieb Baruch Burstein: I just noticed that the dot-commands are tested by the length of the input, not of the correct term. For example, '.e' will match '.exit' (it would also match '.explain', except it matches '.exit' first). Is this intended behavior? And if so, why

[sqlite] sqlite3 command line shell bug?

I just noticed that the dot-commands are tested by the length of the input, not of the correct term. For example, '.e' will match '.exit' (it would also match '.explain', except it matches '.exit' first). Is this intended behavior? And if so, why should '.e' match '.exit' any more than '.explain'?

Re: [sqlite] Optimize Table Access Efficiency

Ah, very good. Thanks to you both. I will definitly try this. Sounds like it may help. Tim On 3/20/2012 2:36 PM, Simon Slavin wrote: On 20 Mar 2012, at 6:11pm, Tim Morton wrote: I may try this, but the topics are usually just one word each so I doubt an index

Re: [sqlite] Optimize Table Access Efficiency

On 20 Mar 2012, at 6:11pm, Tim Morton wrote: > I may try this, but the topics are usually just one word each so I doubt an > index could reduce it much. Max's trick is something that takes advantage of how SQLite works entirely. If you do a SELECT like SELECT

[sqlite] Small addition to shell.c

I find I was often trying to exit the shell while at the continuePrompt. This usually happens because I accidentally entered 'exit' without the period first, but sometimes for other reasons (I started to enter a SQL command and realized I didn't need to, or whatever). I have added a small fix to

Re: [sqlite] How to raise errors from sqlite extension code?

On 3/20/2012 2:05 PM, Wei Song 2 wrote: Do you have any example of using sqlite3_mprintf() to create error messages into *pzErrMsg? There's not much to it: if (pzErrMsg) { *pzErrMsg = sqlite3_mprintf("My error message: some parameter=%d", 42); } -- Igor Tandetnik

Re: [sqlite] Optimize Table Access Efficiency

Thanks, I may try this, but the topics are usually just one word each so I doubt an index could reduce it much. Tim On 3/20/2012 1:52 PM, Max Vlasov wrote: Hi, Tim On Tue, Mar 20, 2012 at 6:21 PM, Tim Morton wrote: Is there a way to read only the part of the

Re: [sqlite] How to raise errors from sqlite extension code?

Do you have any example of using sqlite3_mprintf() to create error messages into *pzErrMsg? Thank you! Igor Tandetnik wrote: > > On 3/20/2012 11:11 AM, Wei Song wrote: >> The following code is part of my sqlite extension code I used to >> persist the ‘locale’ setting for a database. Based on

Re: [sqlite] Optimize Table Access Efficiency

Hi, Tim On Tue, Mar 20, 2012 at 6:21 PM, Tim Morton wrote: > > Is there a way to read only the part of the file that has the queried > table/column thus saving time and hard dive grinding? > > There is also a trick. You can create an index on 'topics' column and perform

[sqlite] 3.7.11 Shouldn't the new API calls also be added to sqlite3ext.h?

Just a question about the latest release: shouldn't the new API calls be added to sqlite3ext.h?  For that matter, the new API calls from 3.7.10 haven't been added, either.   Is this one of those things that only gets updated on larger releases? Best regards, Peter

Re: [sqlite] Optimize Table Access Efficiency

Thanks, Simon, for your detailed answer. I will try the suggestions you and Michael supplied and see if there is any significant inprovement. Tim On 3/20/2012 11:13 AM, Simon Slavin wrote: On 20 Mar 2012, at 2:21pm, Tim Morton wrote: My app reads dozens of SQLite

Re: [sqlite] How to raise errors from sqlite extension code?

On 3/20/2012 11:11 AM, Wei Song wrote: The following code is part of my sqlite extension code I used to persist the ‘locale’ setting for a database. Based on this, I’ve build a sqlite shell that I can set the locale value using pragma statement. The question is: how to raise errors

Re: [sqlite] Optimize Table Access Efficiency

On 20 Mar 2012, at 2:21pm, Tim Morton wrote: > My app reads dozens of SQLite databases ranging in size from 1MB to 100MB > with a simple table structure like, > > "create table dictionary(id INTEGER PRIMARY KEY, topics, definition)" > > On startup the app reads all

[sqlite] How to raise errors from sqlite extension code?

Hello, The following code is part of my sqlite extension code I used to persist the ‘locale’ setting for a database. Based on this, I’ve build a sqlite shell that I can set the locale value using pragma statement. The question is: how to raise errors instead of using ‘printf("SQL error: %s\n",

Re: [sqlite] Optimize Table Access Efficiency

You may also want to try pragma cache_size and bump it up a LOT just to see what happens to your timings. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From:

Re: [sqlite] Optimize Table Access Efficiency

Thanks for the reply, "Lengthy" varies according to the system, but from a fresh, initial start on this one it can be 15 to 20 seconds. The system cache speeds up subsequent starts to around 3-4 seconds. As mentioned in the post, I have two tables, one for topics and one for definitions. I

Re: [sqlite] Optimize Table Access Efficiency

Try creating 2 tables, one for topics, one for definitions. Then insert all the topics at once followed by all the definitions. That should give you the same disk layout as two databases. And you don't say what "lengthy" means. Michael D. Black Senior Scientist Advanced Analytics

[sqlite] Optimize Table Access Efficiency

Greetings, My app reads dozens of SQLite databases ranging in size from 1MB to 100MB with a simple table structure like, "create table dictionary(id INTEGER PRIMARY KEY, topics, definition)" On startup the app reads all the databases to extract the "topics" column data. With the DB

Re: [sqlite] creating virtual tables

Kit, Do you have an example for that? On Tue, Mar 20, 2012 at 7:48 AM, Kit wrote: > Create next table with columns min, max, count and create triggers for > insert, delete and modification. > -- > Kit > > 2012/3/20, Rita : > > Hello, > > > > I have a

Re: [sqlite] creating virtual tables

Create next table with columns min, max, count and create triggers for insert, delete and modification. -- Kit 2012/3/20, Rita : > Hello, > > I have a single table which has close to 4 millions rows. I write once and > read many times with SELECT. I mainly work with

Re: [sqlite] creating virtual tables

On 20 Mar 2012, at 10:45am, Rita wrote: > I have a single table which has close to 4 millions rows. I write once and > read many times with SELECT. I mainly work with operations like max, min, > and count so I was wondering instead of creating views is there a way I can >

[sqlite] creating virtual tables

Hello, I have a single table which has close to 4 millions rows. I write once and read many times with SELECT. I mainly work with operations like max, min, and count so I was wondering instead of creating views is there a way I can get the count() of a table and place it in a SQL variable or

[sqlite] how to show sqlite database in vc6 listctrl control?

I have a sqlite database, and wish to create a gui application to show the database use vc6 listctrl control, I don't know how to write c++ cource, anyone could tell me the source in the codeproject web or any other website have the resource about this topic? thank you!

Re: [sqlite] VIsual Studio 2005. slow and high CPU in debug

Hello Larry and Ted: I'm working on what you have told me. Thank you very much. A greeting. El 19 de marzo de 2012 16:10, Teg escribió: > Hello Juan, > > Debug mode is unusable for production code. Even it you add > optimization, MS builds in a debug memory