Re: [sqlite] Why would batched write operations NOT be faster than individual ones

Hi, Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] > At 21:35 03/03/2014, you wrote: > ´¯¯¯ > >RAID3-4-5 was great when disks were expensive, in 80's an 90's. Now > >not. A minimal RAID5 needs 3 disks. A minimal RAID10 4. An enterprise > >disk SAS 15Krpm 146 GB 6G

Re: [sqlite] SQLITE_MAX_PAGE_COUNT

I will make the changes as per my knowledge and send it to all on this list to see if it helps in anyway. On Thu, Mar 6, 2014 at 5:25 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 05/03/14 10:59, Raheel Gupta wrote: > > If you point

Re: [sqlite] Issues after upgrade to 3.8.2

Denis Gladkikh wrote: > > I still have this issue with SQLite 3.8.3.1, I could fix my case my > changing line > > "LastPlayed] = (select case when [UserPlaylist].[LastPlayed] > > s.[LastPlayed] then [UserPlaylist].[LastPlayed] else s.[LastPlayed] end > from [Song] as s where s.[SongId] =

Re: [sqlite] Troubleshooting query performance

> Don't put the PRIMARY KEY as the first column of your index. Ever. This > applies to all SQL database engines, not just SQLite. > > For that matter, don't put the PRIMARY KEY anywhere in your index. The > PRIMARY KEY will be added automatically at the end, where it belongs. > > If you

Re: [sqlite] Troubleshooting query performance

On 6 Mar 2014, at 12:20am, Joseph L. Casale wrote: > SELECT a.name, p.value > FROM p_attribute p > JOIN attribute a > ON a.id=p.aid > WHERE p.pid=? > > This returns all relevant rows I need, where table profile has ~6000 rows, > p_attribute has ~ 170k and

Re: [sqlite] Troubleshooting query performance

On Wed, Mar 5, 2014 at 7:20 PM, Joseph L. Casale wrote: > Hey guys, > > I have a query that's giving me abysmal performance and it's not > immediately > obvious to me as to what's wrong with the table structure to cause this. > > CREATE TABLE profile ( > id

[sqlite] Troubleshooting query performance

Hey guys, I have a query that's giving me abysmal performance and it's not immediately obvious to me as to what's wrong with the table structure to cause this. CREATE TABLE profile ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE NOT NULL ); CREATE INDEX profile_idx_0 ON

Re: [sqlite] SQLITE_MAX_PAGE_COUNT

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/03/14 10:59, Raheel Gupta wrote: > If you point out to me the changes required I will do it and have it > reviewed. The changes required are to update the test suites (there are several) to hit/cross the current limit, to modify all relevant

Re: [sqlite] About "speed"

Hi *Elefterios, Simon,* *Wes McKinney gave us :- a fully detailed benchmark case (data + reproducible test),* *- where SQLite was : . abnormally less good than Postgresql (so could be better),* * . SQLdatabase in general were abnormally less good, . a hint "vertica"was given.* *Maybe

Re: [sqlite] SQLite version 3.8.4 release schedule

I noticed the message: Enter SQL statements terminated with a ";" was removed from the shell. Is this supposed to be in the right direction when a red message is added to warn newbies to SQLite about working with in-memory database, yet they are assumed to know that SQL commands end with

Re: [sqlite] Syntax diagram missing in sqlite3 docs

On 2/20/14, Zsbán Ambrus wrote: > The page "http://sqlite.org/lang_transaction.html; should show the > syntax diagrams for commit-stmt and rollback-stmt. It seems that these bugs are now fixed in the draft documentation for sqlite 3.8.4. Thank you, sqlite maintainers.

Re: [sqlite] About "speed"

ok, Just updated with 3.8.4beta of 2014-03-05. I also re-did some previous measures as : - testing method improved a little, - I measured more carefully that SQLite has also a sort of caching benefit, when you run a query twice on windows7. Regards,

Re: [sqlite] Virtual table API performance

On 05.03.2014 11:02, RSmith wrote: On 2014/03/05 10:41, Dominique Devienne wrote: On Tue, Mar 4, 2014 at 9:41 PM, Elefterios Stamatogiannakis One thing that IMHO long term might improve the situation would be if SQLite's own "native" tables would use the same Virtual Table API,//... ...//Of

Re: [sqlite] About "speed"

On Wed, Mar 5, 2014 at 7:25 PM, Richard Hipp wrote: > MySQL does very well on query 8 which is a repeat of query 6. This might > be because MySQL implements a query cache. It remembers the result of each > query and if that query occurs again, without an intervening INSERT, >

Re: [sqlite] SQLITE_MAX_PAGE_COUNT

Sir, will it require much change ? If you point out to me the changes required I will do it and have it reviewed. AT the moment I am checking code related to the following variables : mxPgno sqlite3PagerMaxPageCount (Function) pageSize On Thu, Mar 6, 2014 at 12:06 AM, Richard Hipp

Re: [sqlite] SQLITE_MAX_PAGE_COUNT

On Wed, Mar 5, 2014 at 1:24 PM, Raheel Gupta wrote: > SQLITE_MAX_PAGE_COUNT is 2147483646. > After looking at the code, Pgno is a u32. > So isnt SQLITE_MAX_PAGE_COUNT capable of actually being 4294967292 ? > We have your request. But as it will require a lot of testing,

Re: [sqlite] About "speed"

On Wed, Mar 5, 2014 at 9:29 AM, big stone wrote: > Timing updates with Mysql 5.6.16 > MySQL does very well on query 8 which is a repeat of query 6. This might be because MySQL implements a query cache. It remembers the result of each query and if that query occurs again,

[sqlite] SQLITE_MAX_PAGE_COUNT

SQLITE_MAX_PAGE_COUNT is 2147483646. After looking at the code, Pgno is a u32. So isnt SQLITE_MAX_PAGE_COUNT capable of actually being 4294967292 ? Please correct me if I am wrong ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] sqlite issue when using AIX

On Tue, Mar 4, 2014 at 11:00 AM, Peter Simpson wrote: > Hi, > > We are currently using SQLite 3.8.2. > > When using this on AIX, we encountered an issue with an "unsigned char" in > the sqliteProcessJoin procedure in sqlite3.c. > Can you please try the latest 3.8.4

Re: [sqlite] SQLite version 3.8.4 release schedule

On Wed, Mar 5, 2014 at 12:53 PM, big stone wrote: > > ==> Is it expected to have a big speed-up in index creation ? > Yes. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] SQLite version 3.8.4 release schedule

Hello, Comparing 3.8.3 to 3.8.4beta fo today : - both compiled in -o2 mode and running in ":memory:" , - on a windows pc. Changes : * 8% quicker on Recursive CTE (sudoku test of http://www.sqlite.org/lang_with.html) * 12% quicker on Index Creation over a 5M record table

Re: [sqlite] sqlite issue when using AIX

On Tue, Mar 4, 2014 at 10:00 AM, Peter Simpson wrote: > When using this on AIX, we encountered an issue with an "unsigned char" in > the sqliteProcessJoin procedure in sqlite3.c. > > For cases where "pSrc->nSrc" was 0, then "pSrc->nSrc-1" was being treated > as

[sqlite] SQLite version 3.8.4 release schedule

A number of bugs have recently been revealed in SQLite. All are obscure. Nevertheless, we want to accelerate the release of version 3.8.4 in order to get the fixes in circulation. To this end, SQLite version 3.8.4 should now be considered "in beta". Amalgamation snapshots are now available on

Re: [sqlite] select where field in ($tcl_list) ?

On 2014/03/05 17:05, Chris wrote: Ok, fair enough. I thought that in the same way that sqlite looks for binary vs. string representations of referenced vars and has alternative ways of specifying variable to bind to ('@', ':'), it might also spot a list object and internally expand it to

Re: [sqlite] select where field in ($tcl_list) ?

> On Wed, Mar 5, 2014 at 4:59 AM, Chris wrote: > > > I'm a relative novice to sqlite (or sql in general), but I do understand > > the value of variable substitution when building queries: > > > > e.g. > > set someValue 23 > > db eval {SELECT something FROM myTable WHERE value=$someValue}

Re: [sqlite] About "speed"

On Wed, Mar 5, 2014 at 9:29 AM, big stone wrote: > Timing updates with Mysql 5.6.16 > I wonder if you could update the timings for the current SQLite 3.8.4 beta? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list

Re: [sqlite] select where field in ($tcl_list) ?

On Wed, Mar 5, 2014 at 4:59 AM, Chris wrote: > I'm a relative novice to sqlite (or sql in general), but I do understand > the value of variable substitution when building queries: > > e.g. > set someValue 23 > db eval {SELECT something FROM myTable WHERE

Re: [sqlite] select where field in ($tcl_list) ?

On Wed, Mar 5, 2014, at 09:59 AM, Chris wrote: > I'm a relative novice to sqlite (or sql in general), but I do understand > the value of variable substitution when building queries: > > e.g. > set someValue 23 > db eval {SELECT something FROM myTable WHERE value=$someValue} > > It feels

Re: [sqlite] About "speed"

Timing updates with Mysql 5.6.16 test = https://raw.github.com/stonebig/ztest_donotuse/master/benchmark_test01.txt results = https://github.com/stonebig/ztest_donotuse/blob/master/benchmark_test01_measures.GIF?raw=true ___ sqlite-users mailing list

Re: [sqlite] sqlite issue when using AIX

Peter Simpson wrote: > When using this on AIX, we encountered an issue with an "unsigned char". > > For cases where "nSrc" was 0, then "nSrc-1" was being treated as positive > (since nSrc is defined as an unsigned char, u8) This violates the C standard (any C standard). Does you compiler

[sqlite] sqlite issue when using AIX

Hi, We are currently using SQLite 3.8.2. When using this on AIX, we encountered an issue with an "unsigned char" in the sqliteProcessJoin procedure in sqlite3.c. The issue occurred with the following line (99798) :- for(i=0; inSrc-1; i++, pRight++, pLeft++){ For cases where

Re: [sqlite] Solved Weird out of memory problem a prepare

On Wed, 05 Mar 2014 11:12:45 +0200 RSmith wrote: > If that query fails in an SQLite tool too, then maybe there is a > problem, or if you use a custom altered version of the SQLite code. > Barring that, you need to hunt down the corrupting code - Good luck! I find it and

Re: [sqlite] How to write a query

On 2014/03/05 12:24, Igor Korot wrote: With UNION I will have 2 DB hits, correct? Meaning I execute the part on the left side of the UNION and then execute the right side of the UNION and then add the results together. Do I understand correctly? Thank you. Yes. Obviously you need to have the

Re: [sqlite] How to write a query

On 2014/03/05 12:04, Igor Korot wrote: Hi, ALL, Let's say I have a table with following data: field1field2field3 field4 12 3 4 5 6 7 8 How do I write a query which will produce the output as: 1 2 5 6 3 4 7 8 Is it possible

Re: [sqlite] How to write a query

On 5 March 2014 10:24, Igor Korot wrote: > Hi, Simon, > . . . >> >> Possibly: >> select v1, v2 from ( select 1 as ordr, f1 as v1, f2 as v2 from t union >> all select 2, f3, f4 from t ) order by ordr, v1; >> > > With UNION I will have 2 DB hits, correct? > Meaning I execute the

Re: [sqlite] How to write a query

Hi, Simon, On Wed, Mar 5, 2014 at 2:21 AM, Simon Davies wrote: > On 5 March 2014 10:04, Igor Korot wrote: > > Hi, ALL, > > Let's say I have a table with following data: > > > > field1field2field3 field4 > > 12 3

Re: [sqlite] How to write a query

On 5 March 2014 10:04, Igor Korot wrote: > Hi, ALL, > Let's say I have a table with following data: > > field1field2field3 field4 > 12 3 4 > 5 6 7 8 > > How do I write a query which will produce the output as:

[sqlite] How to write a query

Hi, ALL, Let's say I have a table with following data: field1field2field3 field4 12 3 4 5 6 7 8 How do I write a query which will produce the output as: 1 2 5 6 3 4 7 8 Is it possible to write a single query which will produce

[sqlite] select where field in ($tcl_list) ?

I'm a relative novice to sqlite (or sql in general), but I do understand the value of variable substitution when building queries: e.g. set someValue 23 db eval {SELECT something FROM myTable WHERE value=$someValue} It feels like there should be a comparable solution for IN, passing

Re: [sqlite] Weird out of memory problem a prepare

On 2014/03/04 22:05, Eduardo Morras wrote: The tables have 4 rows each one, that's why I got suprised with the Out of Memory error. The biggest row has 12KB and with the join I do, shouldn't use more than 200KB. Changing the ',' with the join you propose, gives Out of Memory too. It happens

Re: [sqlite] Weird out of memory problem a prepare

On 03/05/2014 03:05 AM, Eduardo Morras wrote: On Tue, 4 Mar 2014 15:19:24 + Simon Slavin wrote: On 4 Mar 2014, at 3:15pm, Simon Slavin wrote: On 4 Mar 2014, at 3:09pm, Eduardo Morras wrote: zSql= "SELECT r.name,

Re: [sqlite] Virtual table API performance

On 2014/03/05 10:41, Dominique Devienne wrote: On Tue, Mar 4, 2014 at 9:41 PM, Elefterios Stamatogiannakis One thing that IMHO long term might improve the situation would be if SQLite's own "native" tables would use the same Virtual Table API,//... ...//Of course, the above is a "naive"

Re: [sqlite] Virtual table API performance

On Tue, Mar 4, 2014 at 9:41 PM, Elefterios Stamatogiannakis wrote: > [...] Nevertheless, having people do crazy/clever hacks like that to avoid the > inefficiencies of an API, gives a hint that something might be problematic. > > In a previous email of mine, i had said half