Re: [sqlite] Need help with query

[Kai Peters]

On Tue, 15 Jan 2013 14:57:42 +0900, Yongil Jang wrote:
> SELECT t1.key1, t1.key2, t2.description FROM MASTERLANGUAGES as t1, 
> MASTERLANGUAGES as t2 WHERE
> t1.ISOCode = 'DEU' and t2.ISOCode = 'ENG' and t1.key1 = t2.key1 and t1.key2 = 
> t2.key2;

that is very close - just needed to add t1.description

Thanks,
Kai
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need help with query

[Yongil Jang]

like this?

sqlite> SELECT t1.key1, t1.key2, t2.description FROM MASTERLANGUAGES as t1,
MASTERLANGUAGES as t2 WHERE t1.ISOCode = 'DEU' and t2.ISOCode = 'ENG' and
t1.key1 = t2.key1 and t1.key2 = t2.key2;
FORM1|SAVE_BUTTON|Save
FORM1|HELP_BUTTON|Help

Sorry if my try is wrong.


2013/1/15 Kai Peters 

> Hi all,
>
> given
>
> CREATE TABLE masterlanguages (
>   ID  integer primary key autoincrement,
>   Key1varchar not null,
>   Key2varchar not null,
>   ISOCode varchar not null,
>   Description varchar not null,
>   MaxCharsinteger default 0
> );
>
>
> insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'ENG',
> 'Save', 0);
> insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'ENG',
> 'Help', 0);
> insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'DEU',
> 'Speichern', 0);
> insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'DEU',
> 'Hilfe', 0);
>
>
>
> In addition to the data from
>
> SELECT * FROM MASTERLANGUAGES WHERE ISOCode = 'DEU'
>
> I also need the Description field for the corresponding record (based on
> Key1 + Key2) in English so
> that I can display the original English description as well as its German
> translation.
>
>
> How can I achieve this?
>
> TIA,
> Kai
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need help with query

[Kai Peters]

Hi all,

given

CREATE TABLE masterlanguages (
  ID  integer primary key autoincrement,
  Key1varchar not null, 
  Key2varchar not null,
  ISOCode varchar not null,
  Description varchar not null,
  MaxCharsinteger default 0
);


insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'ENG', 
'Save', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'ENG', 
'Help', 0);
insert into masterlanguages values (null, 'FORM1', 'SAVE_BUTTON', 'DEU', 
'Speichern', 0);
insert into masterlanguages values (null, 'FORM1', 'HELP_BUTTON', 'DEU', 
'Hilfe', 0);



In addition to the data from 

SELECT * FROM MASTERLANGUAGES WHERE ISOCode = 'DEU' 

I also need the Description field for the corresponding record (based on Key1 + 
Key2) in English so 
that I can display the original English description as well as its German 
translation.


How can I achieve this?

TIA,
Kai
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent read performance

[Howard Chu]

Wayne Bradney wrote:

Howard,Thanks for the links - mdb/sqlightning looks interesting.Before I

dive in, however:1. As I understand it, sqlightning is a drop-in replacement
for SQLite.Interop.dll, and I can still use System.Data.SQLite as my
high-level interface?

Right.

2. Is there a compiled release available?

No.

3. How often is sqlightning updated against SQLite? We're at SQLite 3.7.15.2 /
System.Data.SQLite 1.0.84.0 right now

There is no set schedule, basically as interest and demand dictate.

4. Does sqlightning allow lock-free
reads, even against a SQLite memory-backed, shared cache database?

No. But you can get the same effect simply by putting the MDB database onto a 
RAMdisk.



You are putting programming effort into making your code fast, and this is 
costing you (or your employer) programmer time.


For any reasonably useful piece of software, every moment of programmer time
invested in proper coding saves eons of user time. Putting programmer effort
into making correct code fast is always The Right Thing to Do. Software that
delivers the correct answer, late, is still wrong.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Concurrent read performance

[Wayne Bradney]

Howard,Thanks for the links - mdb/sqlightning looks interesting.Before I dive 
in, however:1. As I understand it, sqlightning is a drop-in replacement for 
SQLite.Interop.dll, and I can still use System.Data.SQLite as my high-level 
interface?2. Is there a compiled release available?3. How often is sqlightning 
updated against SQLite? We're at SQLite 3.7.15.2 / System.Data.SQLite 1.0.84.0 
right now4. Does sqlightning allow lock-free reads, even against a SQLite 
memory-backed, shared cache database?
> Date: Sun, 13 Jan 2013 10:25:06 -0800
> From: h...@symas.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Concurrent read performance
> 
> Wayne Bradney wrote:
> >>> All access in SQLite is serialized. Apologies if I'm missing something 
> >>> fundamental here, but that's not what I'm seeing with a file-backed 
> >>> database when shared cache is OFF.My test has a single table with 1M 
> >>> rows, and four queries that each yield 100K different rows. I run them 
> >>> two ways: 1. All queries in a loop on the same thread in the same 
> >>> connection.2. Each query in parallel on separate threads, each with its 
> >>> own connection. If all access were serialized, I would expect these two 
> >>> tests to take about the same amount of time overall, wouldn't I?In fact, 
> >>> with a file-backed database and shared cache OFF, the second run takes 
> >>> about 70% less time.With shared cache ON, they're the same. As to your 
> >>> second point, I probably should have made it clear that this isn't an 
> >>> internal project, it's a software product, and we don't control where it 
> >>> runs. I understand what an SSD is and why it's better than a spindle 
> >>> drive, but my question wasn't really meant to solicit suggestions for 
> >>> perfor
 ma
>  n
> c
> >   e improvements outside the proposal at hand, which was to retire our 
> > existing home-grown in-memory cache implementation (which is very fast for 
> > concurrent reads, but is extremely limited in how it can be queried), and 
> > replace it with a SQL-capable, relational store and still get roughly the 
> > same performance. Our expectation was that we could achieve this with 
> > SQLite, but were surprised by the apparent lack of read-concurrency, and 
> > wanted to get some input on what our options might be in terms of SQLite 
> > configuration of memory-backed databases. > From: slav...@bigfraud.org
> 
> You should look into MDB, which does no locking for read operations. Reads 
> scale perfectly across arbitrarily many CPUs. More info here
> 
> http://symas.com/mdb/
> 
> and SQLite ported to use MDB as its backend is available here
> 
> https://gitorious.org/mdb/sqlightning
> 
> >> Date: Sat, 12 Jan 2013 17:48:56 +
> >> To: sqlite-users@sqlite.org
> >> Subject: Re: [sqlite] Concurrent read performance
> >>
> >>
> >> On 12 Jan 2013, at 5:38pm, Wayne Bradney  wrote:
> >>
> >>> "mode=memory=shared"
> >>
> >>
> >>> 1. when shared cache is enabled, all reads are serialized, and
> >>
> >> All access in SQLite is serialised.  All transactions require locking the 
> >> entire database.  SQLite is very simple -- 'lite' -- so queries run 
> >> extremely quickly, so you don't normally realise that any locking has 
> >> taken place.
> >>
> >>> 2. there doesn't seem to be any way to have a memory-backed database that 
> >>> can be accessed by multiple connections without using a shared cache,  
> >>> then I guess I MUST use a file-backed database to get concurrent reads, 
> >>> even though I don't need the persistence and don't want to take the I/O 
> >>> hit. Am I making any sense? Anything I'm missing?
> >>
> >> You are putting programming effort into making your code fast, and this is 
> >> costing you (or your employer) programmer time.
> 
> For any reasonably useful piece of software, every moment of programmer time 
> invested in proper coding saves eons of user time. Putting programmer effort 
> into making correct code fast is always The Right Thing to Do. Software that 
> delivers the correct answer, late, is still wrong.
> 
> -- 
>-- Howard Chu
>CTO, Symas Corp.   http://www.symas.com
>Director, Highland Sun http://highlandsun.com/hyc/
>Chief Architect, OpenLDAP  http://www.openldap.org/project/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query planning with concatened vs single index

[Selen Schabenberger]

By the way I am using the SQLite version 3.7.15.2. I do not see the same 
behaviour with the version 3.6.

Selen



 From: Selen Schabenberger 
To: Simon Slavin ; General Discussion of SQLite Database 
 
Sent: Monday, January 14, 2013 3:49 PM
Subject: Re: [sqlite] Query planning with concatened vs single index
 
The results I wrote was after executing the ANALYZE. 

But If I drop the stat1 and stat3 tables, the query is faster and I see in the 
output of the "explain query plan" that my index is used.

Selen



From: Simon Slavin 
To: Selen Schabenberger ; General Discussion of SQLite 
Database  
Sent: Monday, January 14, 2013 3:33 PM
Subject: Re: [sqlite] Query planning with concatened vs single index


On 14 Jan 2013, at 2:30pm, Selen Schabenberger  wrote:

> As far as I know, the index (column1, column2, column3) is more useful than 
> the index (column1). Why does the analyzer not like the concatened index for 
> that query? Did I misunderstand the whole idea with the multi-column indexes?

You can get clues about what indexes will be used using the 'EXPLAIN QUERY 
PLAN' command:



Just out of interest, with the same setup and the data you describe in your 
question already entered, execute the SQL command 'ANALYZE' and then do your 
SELECTS again.  See if SQLite chooses the same strategies.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query planning with concatened vs single index

[Selen Schabenberger]

The results I wrote was after executing the ANALYZE. 

But If I drop the stat1 and stat3 tables, the query is faster and I see in the 
output of the "explain query plan" that my index is used.

Selen



 From: Simon Slavin 
To: Selen Schabenberger ; General Discussion of SQLite 
Database  
Sent: Monday, January 14, 2013 3:33 PM
Subject: Re: [sqlite] Query planning with concatened vs single index
 

On 14 Jan 2013, at 2:30pm, Selen Schabenberger  wrote:

> As far as I know, the index (column1, column2, column3) is more useful than 
> the index (column1). Why does the analyzer not like the concatened index for 
> that query? Did I misunderstand the whole idea with the multi-column indexes?

You can get clues about what indexes will be used using the 'EXPLAIN QUERY 
PLAN' command:



Just out of interest, with the same setup and the data you describe in your 
question already entered, execute the SQL command 'ANALYZE' and then do your 
SELECTS again.  See if SQLite chooses the same strategies.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query planning with concatened vs single index

[Simon Slavin]

On 14 Jan 2013, at 2:30pm, Selen Schabenberger  wrote:

> As far as I know, the index (column1, column2, column3) is more useful than 
> the index (column1). Why does the analyzer not like the concatened index for 
> that query? Did I misunderstand the whole idea with the multi-column indexes?

You can get clues about what indexes will be used using the 'EXPLAIN QUERY 
PLAN' command:



Just out of interest, with the same setup and the data you describe in your 
question already entered, execute the SQL command 'ANALYZE' and then do your 
SELECTS again.  See if SQLite chooses the same strategies.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query planning with concatened vs single index

[Selen Schabenberger]

Hi,

Having an index on 3 integer columns (column1, column2, column3), the analyse 
command generates the stat1 and stat3 tables. I see the following statistics 
for this index in the sqlite_stat1 table:

"4600132 1289 1275 1"

When I execute the following SQL query, this index is not used but the query 
optimizer prefers to use the primary index which is on the column3, which is 
about 60 times slower than using the index. ( 2 ms vs 300 ms)

"select column1, column2, column3 from table where column1 IN ( integer1, 
integer2, ..., integer30 ) and column2 = 1 order by column3"

When I put less integer values in the IN operator OR remove the "order by" part 
from the query, my index is used. 

OR If I create an index only on the column1, then this index is used for the 
original query I wrote above, which is also very efficient.

As far as I know, the index (column1, column2, column3) is more useful than the 
index (column1). Why does the analyzer not like the concatened index for that 
query? Did I misunderstand the whole idea with the multi-column indexes?

Thanks!

Selen
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users