Re: [sqlite] optimizing out function calls

[Darren Duncan]

According to my understanding of standard SQL, you should be able to say:

 SELECT arbitrary_expression() AS bar FROM foo ORDER BY bar;

... and the expression is only evaluated once per row, not twice.

Your actual example seems confusing, since you appear to alias your 
'vectors' table to 'match' in the from clause, which is also the name 
of your function, and the name of what you sort by.  Perhaps having 
different names for each thing that is actually different will make 
your question easier to answer.


For example:

 SELECT uid, match_func("complex", "function", vector) AS match_res
 FROM vectors AS match_tbl
 ORDER BY match_res DESC
 LIMIT 20;

-- Darren Duncan

At 10:01 PM -0700 11/12/05, Nathan Kurz wrote:

Hello --

I'm trying to figure out how to optimize a query a bit, and think I've
hit a case that could easily be optimized by sqlite but isn't.  I'm
wondering if it would be an easy optimization to add, or whether there
is some way I can 'hint' the optization into being.

I'm using a computationally expensive user defined function called
'match()'.  In case it makes a difference, match() is written in C,
and for testing, I'm loading it as a shared library into the sqlite3
shell application.  I want to return the value of match(), and also
order by it.  So my query looks something like this:

SELECT uid, match("complex", "function", vector) FROM vectors AS match
 ORDER BY match DESC LIMIT 20;

I had expected that match() would only be called once per row, but it
turns out to be called twice: once for the select, and once for the
ordering.  I've confirmed this both by putting in a counter, and by
using 'EXPLAIN'.  Is there any way to tell SQLite to reuse the value
of the first call rather than calling the function again?

I'm a comfortable C programmer, but only superficially familiar with
the SQLite code so far.  If I'm not missing something obvious, hints
on where to look at writing a patch for this would be appreciated.

Thanks!

Nathan Kurz
[EMAIL PROTECTED]

Re: [sqlite] optimizing out function calls

[Nathan Kurz]

On Sat, Nov 12, 2005 at 10:01:29PM -0700, Nathan Kurz wrote:
> SELECT uid, match("complex", "function", vector) FROM vectors AS match
>  ORDER BY match DESC LIMIT 20;

Please pardon the silly typo.  I do have the AS in the right spot.

SELECT uid, match("complex", "function", vector) AS match FROM vectors 
  ORDER BY match DESC LIMIT 20;

And in case it bolsters my case, here's the EXPLAIN output I see:

sqlite> EXPLAIN SELECT uid, match("complex", "function", vector) AS match  
   ...> FROM vectors ORDER BY match DESC LIMIT 20;
0|OpenVirtual|1|3|keyinfo(1,-BINARY)
1|Integer|20|0|
2|MustBeInt|0|0|
3|Negative|0|0|
4|MemStore|0|1|
5|Goto|0|37|
6|Integer|0|0|
7|OpenRead|0|14984|
8|SetNumColumns|0|2|
9|Rewind|0|25|
10|Column|0|0|
11|String8|0|0|complex
12|String8|0|0|function
13|Column|0|1|
14|Function|3|3|match(3)
15|MakeRecord|2|0|
16|String8|0|0|complex
17|String8|0|0|function
18|Column|0|1|
19|Function|3|3|match(3)
20|Sequence|1|0|
21|Pull|2|0|
22|MakeRecord|3|0|
23|IdxInsert|1|0|
24|Next|0|10|
25|Close|0|0|
26|Sort|1|36|
27|MemIncr|0|36|
28|Column|1|2|
29|Integer|2|0|
30|Pull|1|0|
31|Column|-1|0|
32|Column|-2|1|
33|Callback|2|0|
34|Pop|2|0|
35|Next|1|27|
36|Halt|0|0|
37|Transaction|0|0|
38|VerifyCookie|0|116|
39|Goto|0|6|
40|Noop|0|0|

--nate

[sqlite] optimizing out function calls

[Nathan Kurz]

Hello --

I'm trying to figure out how to optimize a query a bit, and think I've
hit a case that could easily be optimized by sqlite but isn't.  I'm
wondering if it would be an easy optimization to add, or whether there
is some way I can 'hint' the optization into being.

I'm using a computationally expensive user defined function called
'match()'.  In case it makes a difference, match() is written in C,
and for testing, I'm loading it as a shared library into the sqlite3
shell application.  I want to return the value of match(), and also
order by it.  So my query looks something like this:

SELECT uid, match("complex", "function", vector) FROM vectors AS match
 ORDER BY match DESC LIMIT 20;

I had expected that match() would only be called once per row, but it
turns out to be called twice: once for the select, and once for the
ordering.  I've confirmed this both by putting in a counter, and by
using 'EXPLAIN'.  Is there any way to tell SQLite to reuse the value
of the first call rather than calling the function again?

I'm a comfortable C programmer, but only superficially familiar with
the SQLite code so far.  If I'm not missing something obvious, hints
on where to look at writing a patch for this would be appreciated.

Thanks!

Nathan Kurz
[EMAIL PROTECTED]

Re: [sqlite] uSQLiteServer Source code available

[Andrew Piskorski]

On Sat, Nov 12, 2005 at 05:24:47PM -0700, [EMAIL PROTECTED] wrote:

> http://users.iol.it/irwin

> 4) Am I doing the right thing? Of course I think the uSQLiteServer is
> the best thing since sliced bread, but then it was designed to meet my
> criteria :-) OTOH reception has been mixed. I have had a couple of nice
> replies on this list but overall feedback has been lukewarm to icy. I

Well, it can't possibly be a BAD thing to have your uSQLiteServer code
out there and available, so I fully encourage you to keep at it!  :)
Your focus on simplicity and easy of writing client code could
certainly be valuable to many people.  I suspect the lukewarm reaction
is more that the people who might most benefit from your project just
haven't noticed it yet.

What might help interest and uptake, is to give us more detail on why
YOU found it so useful.  For example, in your use of uSQLiteServer on
embedded systems, why didn't you just use an existing client-server
RDBMS?

You needed something smaller footprint?  You were using a weird client
platform for which there was no working PostgreSQL client code at all,
and you wanted something simpler to implement?  You had to also run
the database server on a very resource constrained embedded system,
rather than a general purpose server box?  Or?

-- 
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/

Re: [sqlite] uSQLiteServer Source code available

[Alfredo Cole]

El Sábado, 12 de Noviembre de 2005 18:24, [EMAIL PROTECTED] escribió:
 > I have reorganized the archive and got all the source into it this time.
 >
 > http://users.iol.it/irwin
 >
 >

It's an interesting concept. I downloaded it and will try it.

Thank you, Roger.

-- 
Alfredo J. Cole
Grupo ACyC

[sqlite] uSQLiteServer Source code available

[roger]

I have reorganized the archive and got all the source into it this time.

http://users.iol.it/irwin


A few notes:

1) This has nothing to do with the RPC based uSQLite project, which I
have found has the same name! That project does seem a bit dead thougth
so I shall not worry about it.

2) I have found a sec flaw where a read only user can write to the db.
Have you spotted it? There will be a fix soon

3) When designing uSQLite, portability and the ease of implementing
clients where prime considerations rather than performance. Thats why I
used ASCII on sockets rather than a binary protocol or RPC's. However, I
have found that the system is nontheless **very** fast. Thinking it
througth, ASCII is not a handicap. Strings take the same space whilst
numbers require 1 byte per digit plus terminator, i.e. they may often
require less (int=42 would require 3 bytes to be sent). ASCII does make
things so much simpler at the framing level however, and it's easier to
debug.

4) Am I doing the right thing? Of course I think the uSQLiteServer is
the best thing since sliced bread, but then it was designed to meet my
criteria :-) OTOH reception has been mixed. I have had a couple of nice
replies on this list but overall feedback has been lukewarm to icy. I
would be interested to know if anybody out there really is interested
in the concept, otherwise I shall just keep it to myself ;-)

RE: [sqlite] Organizing large database into multiple files

[roger]

>  Original Message 
> Subject: [sqlite] Organizing large database into multiple files
> From: "Rajan, Vivek K" <[EMAIL PROTECTED]>
> Date: Sat, November 12, 2005 5:09 am
> To: 
> 
> Hello- 
> 
>  
> 
> I have a need to store large volumes of data (~5-10G) in SQLite
> database. The data which I am storing is organized hierarchically. The
> schema for my database has foreign-key constraints, the tables are
> interrelated. My questions:
> 
>   - How can I organize the entire database into multiple files? 
> 
>   - How do manage such huge databases with SQLite 
> 
>  
> 
> I am looking for suggestions/advice on how to organize large volumes
> of
> data. Please help. 
> 
>  
> 
> Vivek
> 
> 

If your tables are spread over databases in several files you can use
the 'ATTACH DATABASE' command to make them appear as a single database.
If the schema don't conflict you can merge them. Otherwise you can use
the db.table syntax, which I think is what you are looking for.

http://www.sqlite.org/lang_attach.html

Re: [sqlite] Organizing large database into multiple files

[Jay Sprenkle]

> I have a need to store large volumes of data (~5-10G) in SQLite
> database. The data which I am storing is organized hierarchically. The
> schema for my database has foreign-key constraints, the tables are
> interrelated. My questions:
>
>   - How can I organize the entire database into multiple files?
>
>   - How do manage such huge databases with SQLite
>
>
>
> I am looking for suggestions/advice on how to organize large volumes of
> data. Please help.

We usually break the data into subsets and place them into different tables
or databases. One per fiscal year, geographic region, etc.
We seldom needed reporting over the full data set but it was still
possible to do that way.

Re: [sqlite] qmark style updates

[Jay Sprenkle]

> I tried this to and got some strange behavior, like if
> I entered a value like "333" it would give me a

All text constants are entered with single quotes.
 insert into mytable(five) values( 'data' )

[sqlite] Re: Thanks Alexander

[Dan McDaniel]

Yes!
This works Thanks very much Alexander.

--- Alexander Kozlovsky <[EMAIL PROTECTED]>
wrote:

> The second parameter of cursor.execute() accept
> **sequence** of
> bindings. Try this:
> 
>  c.execute(toDo, [s1])
>  
> 
> > from pysqlite2 import dbapi2 as sqlite
> > 
> > con = sqlite.connect("mydb.db")
> > c = con.cursor()
> > 
> > s1 =3
> > toDo ="Update ex set amount = ? where ex_id = 1"
> > c.execute(toDo,s1)
> > con.commit()
> > c.close()
> > 
> > 
> > Can anyone help?
>   
> 
> Best regards,
>  Alexander   
> mailto:[EMAIL PROTECTED]
> 
> 





__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

Re: [sqlite] qmark style updates

[Alexander Kozlovsky]

The second parameter of cursor.execute() accept **sequence** of
bindings. Try this:

 c.execute(toDo, [s1])
 

> from pysqlite2 import dbapi2 as sqlite
> 
> con = sqlite.connect("mydb.db")
> c = con.cursor()
> 
> s1 =3
> toDo ="Update ex set amount = ? where ex_id = 1"
> c.execute(toDo,s1)
> con.commit()
> c.close()
> 
> 
> Can anyone help?
  

Best regards,
 Alexandermailto:[EMAIL PROTECTED]

Re: [sqlite] How to speed up create index on temp database?

[黄涛]

Jay Sprenkle wrote:


On 11/10/05, Huang Tao <[EMAIL PROTECTED]> wrote:
 


Hello:

I run sqlite in embedded system which use nand flash. So I have to
reduce write count. Save index in master database will cause much
write. I try to dynamic create index on temp database. But the speed
is not very well.
example:
create table employee (id int primary key not null, name);
insert 1 record to table;
create index temp.idx_name on employee (name);
create index will use 24.519761 sec

with gprof, I see most time was used in sqlite3BtreeInsert, which
called 10001 times.
Anyone can give me suggestion to improve the speed?
   



Can you move your temp tables to in an in-memory(RAM) database?
 


You may not understand my question, the _table_ must be in NAND Flash.
and the most time is spend in create index.
In my test case, all table and index are in RAM(table in tmpfs, which is 
ram base file system in Linux, index in temp database, which is ram base 
in SQLite)
To create a big btree, may be just need so much time, but I hope I can 
speed it up. I believe I can, but I need some hint.