Re: [sqlite] Speed regression after 3.6.17

"ve3meo" wrote in message news:hiivpn$7f...@ger.gmane.org... > > "Simon Slavin" wrote in > message > news:ad11c649-d23a-46ff-8545-85ffb5219...@bigfraud.org... >> >> The database structure has not changed. It is optimisation of a >>

[sqlite] Writes during sleep of backup_step

I've finally implemented the backup API and it works like a charm except on an important point. The example given on the site clearly says: "If another thread writes to database connection pDb while this function is sleeping, then the backup database (database connection pFile) is

Re: [sqlite] Help with Transaction Error.

On 10 Jan 2010, at 4:56am, Vathir wrote: >I am trying to use SQL Jet within my java application, but I consistently get >the following error when I run this section of code. For some reason I >cannot open this particular database. I can open up a separate database >with in my program, but this

Re: [sqlite] Speed regression after 3.6.17

"Simon Slavin" wrote in message news:ad11c649-d23a-46ff-8545-85ffb5219...@bigfraud.org... > > The database structure has not changed. It is optimisation of a > particular kind of query which seems to be working differently -- and not > as well. Unfortunately, the

[sqlite] PRAGMA reverse_unordered_selects=1 causes "malformed database schema" with 3.6.16

When I run the Tcl script below with SQLite 3.6.16, I get this error message: malformed database schema (T1_idx) - no such table: main.T1 Here is the script: file delete "DB1.dat" sqlite3 DB1 "DB1.dat" DB1 eval {CREATE TABLE T1 (name text NOT NULL);CREATE INDEX

Re: [sqlite] after recover lost data still lost

On Tue, 12 Jan 2010 14:27:16 -0800 (PST), puk wrote: > >Hello, >i deleted a db-file from SD-card1. (because i copied it to SD-card2 but >SD-card2 is broken now) >i didn't use SD-card1. >Recovering all files from SD-card1 works. >But the sqlite file is now malformed. >Even

Re: [sqlite] simple question about database lookup

I forgot one question. On Tue, 12 Jan 2010 16:12:01 -0500, Qianqian Fang wrote: >One more question, will this work if you have a >long list of ids (say a few hundred)? There is a maximum list size the parser can cope with. Internally "id IN (val1,val2,...valN)" is

[sqlite] after recover lost data still lost

Hello, i deleted a db-file from SD-card1. (because i copied it to SD-card2 but SD-card2 is broken now) i didn't use SD-card1. Recovering all files from SD-card1 works. But the sqlite file is now malformed. Even the PRAGMA integrity_check; prints out that the db is malformed. I know that the db

Re: [sqlite] simple question about database lookup

On Tue, 12 Jan 2010 16:12:01 -0500, Qianqian Fang wrote: >On 1/12/2010 4:08 PM, Jay A. Kreibich wrote: >> SELECT group_concat( value ) FROM mydata WHERE id IN ( 'id_1', 'id_2', ... ) >> > >hi Jay > >thank you very much for your quick response. I guess this will be

Re: [sqlite] simple question about database lookup

On Tue, Jan 12, 2010 at 3:12 PM, Qianqian Fang wrote: > On 1/12/2010 4:08 PM, Jay A. Kreibich wrote: >> SELECT group_concat( value ) FROM mydata WHERE id IN ( 'id_1', 'id_2', ... ) >> > > hi Jay > > thank you very much for your quick response. I guess this will be >

Re: [sqlite] Limitation on Column count

> When doing so: Any idea of what would be the worst case column select > strategy on the huge table to compare with? Take some query from your typical usage where only last column of the huge table is used and all other columns either not used at all or just one-two of them used for other

Re: [sqlite] Limitation on Column count

Hi Pavel, thanks a lot; I will reconsider this option and run some test cases to compare. But that will be tomorrow I guess... When doing so: Any idea of what would be the worst case column select strategy on the huge table to compare with? Stefan Pavel Ivanov-2 wrote: > >> so normalization

Re: [sqlite] simple question about database lookup

On 1/12/2010 4:08 PM, Jay A. Kreibich wrote: > SELECT group_concat( value ) FROM mydata WHERE id IN ( 'id_1', 'id_2', ... ) > hi Jay thank you very much for your quick response. I guess this will be significantly faster than looping through each id. One more question, will this work if you

Re: [sqlite] simple question about database lookup

On Tue, Jan 12, 2010 at 04:00:51PM -0500, Qianqian Fang scratched on the wall: > hi > > My knowledge to sql is very limited. So forgive me if this sounds > really simple to you. I want to do a lookup operation with sqlite: > I have a table (id, value), where id is an index field, now I have > a

Re: [sqlite] Q. about core SQLite library

On Tue, Jan 12, 2010 at 02:53:55PM -0500, Ray Gold scratched on the wall: > Hello. > > My name is Ray Gold with Berliner, Corcoran & Rowe, LLP in Washington, DC. > > I am writing to ask if the core SQLite library (not the proprietary > SQLite Extensions) has any encryption/decryption

[sqlite] ANN: C#-SQLite 3.6.22

C#-SQLite has been updated to release 3.6.22 and is now ready for review. It currently runs 30428 tests with 0 errors. There are currently issues with recursive triggers so the project is compiled with SQLITE_OMIT_TRIGGER The project is located at http://code.google.com/p/csharp-sqlite/

[sqlite] simple question about database lookup

hi My knowledge to sql is very limited. So forgive me if this sounds really simple to you. I want to do a lookup operation with sqlite: I have a table (id, value), where id is an index field, now I have a list of ids, and I want to retrieve their values from the database and separate them with

[sqlite] Q. about core SQLite library

Hello. My name is Ray Gold with Berliner, Corcoran & Rowe, LLP in Washington, DC. I am writing to ask if the core SQLite library (not the proprietary SQLite Extensions) has any encryption/decryption capabilities. Thanks very much for your help! Ray

Re: [sqlite] Limitation on Column count

One may be able to make "measurement type" a column, thus eliminating the need for a column for each type. Some speed may be recoverable with indexing. regards, Adam On Tue, Jan 12, 2010 at 1:21 PM, Pavel Ivanov wrote: > > so normalization would lead to a doubling > > of

Re: [sqlite] Limitation on Column count

> so normalization would lead to a doubling > of the storage space (add a measurement_id to each measurement). My strong belief is that when you try this normalization you'll see that such doubling of storage is a good enough trade-off for the speed you'll achieve. I don't think that speed of

Re: [sqlite] Limitation on Column count

Hi Adam, thanks for your suggestion. Unfortunately, it doesn't help in my case. Essentially, we are talking about a time series (rows) of n different measurements (columns) - so normalization would lead to a doubling of the storage space (add a measurement_id to each measurement). Second, the

Re: [sqlite] How can I know which table is operated by one SQL?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 zhangzhenggui wrote: > When I execute a SQL with C/C++ API, I want to know which table is > operated by this SQL. Is there any way to do this except parse the SQL? Using "explain" in front of your query will give the byte code and be most

[sqlite] Performace regression bug of sqlite 3.6.18

*I just checked the **explain result of **sql below under 3.6.17 and 3.6.22, the result is quite different. SQL: -- romermb's incredible combination of events, shared events plus 'marriage' and alternate names as Facts 2010-01-06 -- revised by ve3meo to to bring out RoleName from RoleTableand

Re: [sqlite] Limitation on Column count

Good day, In general I try to work within the limits of any database engine that I am using. Often, the limits are there for good reasons (such as speed problems). I would suggest seeing if there is a way to normalize the big tables such that infrequently used columns are split into tables that

Re: [sqlite] How can I know which table is operated by one SQL?

On Tue, Jan 12, 2010 at 10:22 AM, zhangzhenggui wrote: > Dear friend, >When I execute a SQL with C/C++ API, I want to know which table is > operated by this SQL. Is there any way to do this except parse the SQL? > > Every approach is not perfect, here is another one.

Re: [sqlite] Speed regression after 3.6.17

On 12 Jan 2010, at 1:33pm, ve3meo wrote: > Attention needs to be paid to what changed between 3.6.17 and 3.6.18 that > has adversely affected performance and why is it that ANALYZE is > deleterious. Is it just this database structure that has been affected? The database structure has not

Re: [sqlite] Speed regression after 3.6.17

"Max Vlasov" wrote in message news:7cb963ca1001120149u550da7fr56cfc0ed261a9...@mail.gmail.com... > ... Are you sure you have > identical tests for your comparison? I mean these are the same data sets > with the same scheme? Absolutely, errr, to the best of my knowledge.

Re: [sqlite] How can I know which table is operated by one SQL?

No, in general. Only parsing. You can use sqlite3_column_table_name API call in case you have SELECT statement. But it returns the result of parsing SQL query internally when prepare. -- Best regards, Ilya N. Pokolevmailto:i.poko...@compassplus.ru -Original Message- From:

Re: [sqlite] Reasons for SQLITE_CANTOPEN

On Jan 12, 2010, at 6:58 AM, Ian Jackson wrote: > Nikolaus Rath writes ("Re: [sqlite] Reasons for SQLITE_CANTOPEN"): >> Edzard Pasma writes: >>> Hope strace (see Roger Binns' post) will help you further. Also lsof >>> may help. >> >> The problem was indeed that I reached

Re: [sqlite] How can I know which table is operated by one SQL?

zhangzhenggui wrote: >When I execute a SQL with C/C++ API, I want to know which table is > operated by this SQL. Is there any way to do this except parse the SQL? Note that, in the presence of triggers and/or foreign keys, more than one table may be modified when running a single statement.

Re: [sqlite] way to get a list with column names

Nothing with just SQL alone, although you can get close: http://www.sqlite.org/faq.html#q7 You could use a command pipeline, but that only works if the table has at least one record: $ sqlite3 -separator ", " -header sample.db 'select * from sqlite_master limit 1; ' | head -1 type, name,

Re: [sqlite] Reasons for SQLITE_CANTOPEN

Nikolaus Rath writes ("Re: [sqlite] Reasons for SQLITE_CANTOPEN"): > Edzard Pasma writes: > > Hope strace (see Roger Binns' post) will help you further. Also lsof > > may help. > > The problem was indeed that I reached the maximum number of fds. Thanks > for the help! If

Re: [sqlite] Unexplained "disk i/o error", Unix [and 1 more messages]

Nikolaus Rath writes ("Re: [sqlite] Unexplained "disk i/o error", Unix"): > You can try to run the program under strace and check for failed system > calls. This is in principle possible but given that the problem is intermittent it might just make it go away. Also strace has some process- and

[sqlite] way to get a list with column names

Hello out there, is there a quick way to get a comma separated list of column names as a result of a query? (I couldn't find a PRAGMA or a dot command for this). Example: col01,col02,col03 Greetings Oliver WEB.DE

Re: [sqlite] Speed regression after 3.6.17

On Tue, Jan 12, 2010 at 5:28 AM, ve3meo wrote: > Max Vlasov writes: > > For 3.5.4 and 3.6.17 which executed in ~240s: > "order", "from", "detail" > > And for 3.6.20 which executed the same query in ~2500s: > "order","from","detail" > If you remove