Re: [sqlite] Peculiar activity in an SQLite-using InDesign plug-in on the Macintosh

[Simon Slavin]

On 5 Aug 2011, at 1:05am, john darnell wrote:

> As a matter of fact, Simon, for some reason, whenever I call it in this 
> particular function (and I call this function a lot), it returns an error.  
> The error is (both surprisingly and unsurprisingly) "unable to close due to 
> unfinalized statements."
> 
> Here's the code, as simplified as I know how to make it (you will see that 
> the one prepared statement is finalized):

And now you're beyond what I know so I help someone else can help you.

Not that I think they're the problem, but just for completeness, do some error 
reporting on _initialize and _open and _prepare too.

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

Re: [sqlite] Peculiar activity in an SQLite-using InDesign plug-in on the Macintosh

[john darnell]

> Can you
> make absolutely sure sqlite3_close() has been called correctly and does not 
> return
> an error ?

As a matter of fact, Simon, for some reason, whenever I call it in this 
particular function (and I call this function a lot), it returns an error.  The 
error is (both surprisingly and unsurprisingly) "unable to close due to 
unfinalized statements."

Here's the code, as simplified as I know how to make it (you will see that the 
one prepared statement is finalized):

   int  Result;

   std::string InsertStatement = "Insert into blah blah blah";  //  The Insert 
statement works whenever I can get a connection, which is the first 523 
attempts.

   Result = sqlite3_initialize();

   sqlite3 *db_ptr;
   Result = 0;

   Result = sqlite3_open_v2(DBEnginePath, _ptr, SQLITE_OPEN_READWRITE, 
NULL); 

   sqlite3_stmt *ResultStmt;
   Result = sqlite3_prepare_v2(db_ptr, InsertStatement.c_str(), -1, 
, NULL);


   Result = sqlite3_step(ResultStmt);
   if((Result != SQLITE_DONE) && (Result != SQLITE_ROW))
   {
Do some error stuff
   }

  sqlite3_reset(   ResultStmt);

//  based on your comments earlier, the tests were added as you see here.
   Result = sqlite3_finalize(ResultStmt);
   if(Result != SQLITE_OK)
   {
   fprintf(stderr, "Did not finalize"):   //  Never saw this
   }

   sqlite3_close(   db_ptr);
   if(Result != SQLITE_OK)
   {
   fprintf(stderr, "failed to close sqlite db ptr"):   //  As far as I can 
tell, this msg showed up with the processing of every record.
   }


I'm not sure where I've gone wrong.

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

Re: [sqlite] Threading makes SQLite 3x slower??

[Seth Price]

The table with the first five columns is the R*tree table, and thus already has 
an index. I just tried adding an index to col5 also, but no change in 
performance.
~Seth 


On Aug 4, 2011, at 3:27 PM, Simon Slavin wrote:

> 
> On 4 Aug 2011, at 9:59pm, Seth Price wrote:
> 
>> Those fields span two tables.
> 
> Then create one index for each table.
> 
> 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] Peculiar activity in an SQLite-using InDesign plug-in on the Macintosh

[Simon Slavin]

On 4 Aug 2011, at 11:02pm, john darnell wrote:

> The documentation says that when I close a database transactions in progress 
> are rolled back, but I cannot find a way of testing for whether a transaction 
> is completed.

SQLite does not do any tasks in the background.  There's no need to pause for 
something to complete.  There are two transaction models you can use:

A) Declared transactions explicitly using BEING TRANSACTION and END TRANSACTION 
with any number of operations between the two.
B) Do an INSERT or UPDATE operation without having used BEGIN TRANSACTION.

If you do (B), then SQLite automatically wraps that single operation in its own 
transaction.  This is slower than (A) if you're making lots of changes since it 
involves more work.

So if you didn't already know about transactions, you were doing (B).

>   Is that the role of the finalize call?

The finalize call completes the operation you're finalizing.  You do it for 
each _prepare, and you should check the returned value to see you didn't get an 
error.

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

Re: [sqlite] Peculiar activity in an SQLite-using InDesign plug-in on the Macintosh

[john darnell]

> 
> I'm not sure where you close your database handle, and what you mean by 'end 
> of
> a run', but when you have used sqlite3_close() to close all handles to a 
> SQLite
> database that file should no longer exist.  If you still have a file with 
> that name on
> your disk, something has gone wrong, and you should figure out why.  Can you
> make absolutely sure sqlite3_close() has been called correctly and does not 
> return
> an error ?

My apologies for my lack of clarity.  What I mean by "end of a run" is simply 
when I exit InDesign (and all plugins attached thereto).

I close the database frequently--at the end of the module where it is opened.

The documentation says that when I close a database transactions in progress 
are rolled back, but I cannot find a way of testing for whether a transaction 
is completed.  Is that the role of the finalize call?

Here's the sequence of events I use when I use sqlite3_close:

Result = sqlite3_step(ResultStmt);
if(Result != SQLITE_DONE)
{
Do some error/loop stuff as appropriate
}

sqlite3_finalize(ResultStmt);
sqlite3_close(db_ptr);
sqlite3_shutdown();

If there is a better/safer method, please let me know.

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

Re: [sqlite] Threading makes SQLite 3x slower??

[Simon Slavin]

On 4 Aug 2011, at 9:59pm, Seth Price wrote:

> Those fields span two tables.

Then create one index for each table.

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

Re: [sqlite] Threading makes SQLite 3x slower??

[Seth Price]

Those fields span two tables.

~Seth


via iPhone

On Aug 4, 2011, at 2:45 PM, Simon Slavin  wrote:

> 
> On 4 Aug 2011, at 8:40pm, Seth Price wrote:
> 
>> SELECT class FROM data_r JOIN data USING (rowNum) WHERE ?1 < col0min AND 
>> col0max < ?2 AND ?3 < col1min AND col1max < ?4 AND ?5 < col2min AND col2max 
>> < ?6 AND ?7 < col3min AND col3max < ?8 AND ?9 < col4min AND col4max < ?10 
>> AND ?11 < col5 AND col5 < ?12;
> 
> Create one index on all these fields:
> 
> (col0max,col1min,col1max,col2min,col2max,col3min,col3max,col4min,col4max,col5)
> 
> 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] Threading makes SQLite 3x slower??

[Simon Slavin]

On 4 Aug 2011, at 8:40pm, Seth Price wrote:

> SELECT class FROM data_r JOIN data USING (rowNum) WHERE ?1 < col0min AND 
> col0max < ?2 AND ?3 < col1min AND col1max < ?4 AND ?5 < col2min AND col2max < 
> ?6 AND ?7 < col3min AND col3max < ?8 AND ?9 < col4min AND col4max < ?10 AND 
> ?11 < col5 AND col5 < ?12;

Create one index on all these fields:

(col0max,col1min,col1max,col2min,col2max,col3min,col3max,col4min,col4max,col5)

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

Re: [sqlite] Peculiar activity in an SQLite-using InDesign plug-in on the Macintosh

[Simon Slavin]

On 4 Aug 2011, at 8:28pm, john darnell wrote:

> The name of the DB file I use is IndexData.db.  On certain versions (but not 
> all versions) of the plugin, during the processing, an IndexData.db-Journal 
> file is created. It is always empty at the end of a run.

I'm not sure where you close your database handle, and what you mean by 'end of 
a run', but when you have used sqlite3_close() to close all handles to a SQLite 
database that file should no longer exist.  If you still have a file with that 
name on your disk, something has gone wrong, and you should figure out why.  
Can you make absolutely sure sqlite3_close() has been called correctly and does 
not return an error ?

> I have had several instances where I scan several documents and build up, 
> say, 1000 records in my SQLite table.  I then select several more documents 
> for scanning and the number of records are halved in my SQLite table.  This 
> is always associated with the advent of a system File Open dialog that is 
> empty of all files in the folder I am working, when it should have between 60 
> and 100 files.
> 
> Has anyone working a Mac ever experienced something like this?

Not me.  Please use Disk Utility to check your disk for corrupt structure.

Things like you describe remind me of cases where the OS has run out of file 
handles but I can't imagine this happening under OS X.

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

Re: [sqlite] Example of implementing undo/redo using sqlite3?

[Stephan Beal]

On Thu, Aug 4, 2011 at 9:44 PM, Alexey Pechnikov wrote:

>
> http://sqlite.mobigroup.ru/artifact/265e408b4352d66cfc79a9990cb2c22fb390d3b6
>
> http://sqlite.mobigroup.ru/artifact/2250bbbc83f80eff73ce003ab7a30293c688ae9b


That is one of the coolest things i've seen months :).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example of implementing undo/redo using sqlite3?

[Alexey Pechnikov]

2011/8/4 Stephan Beal :
> i'm wondering if anyone can point me to an example of implementing such a
> beast?

http://sqlite.mobigroup.ru/artifact/265e408b4352d66cfc79a9990cb2c22fb390d3b6
http://sqlite.mobigroup.ru/artifact/2250bbbc83f80eff73ce003ab7a30293c688ae9b

P.S. Many of the design ideas were cribbed from:
http://www.sqlite.org/cvstrac/wiki?p=UndoRedo

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help! -- SQlite database on server

[Alexey Pechnikov]

2011/8/4 Vinoth raj :
> So, the requirement is to save sqlite database on a server from a C++
> application.

There are a lot of ways to copy file to server. SQLite database is single file.
And you can send SQL dump. And you can send diff of SQL dump.
And you can export your data and send RDF or other data format.
I don't see your problem. May be you want any different?..

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Peculiar activity in an SQLite-using InDesign plug-in on the Macintosh

[john darnell]

Hello people.

I apologize for the vagueness of this email, but all I can really hope for is 
some ideas to pursue, I think.

I have an InDesign plug-in that scans numerous InDesign documents (there are no 
limits, but a common number would be around 100) for certain names and stores 
that information plus the page number in an SQLite table.   Each record is 
stored as it is found so there is no massive storage even at the end of a block 
or anything.

The name of the DB file I use is IndexData.db.  On certain versions (but not 
all versions) of the plugin, during the processing, an IndexData.db-Journal 
file is created.  It is always empty at the end of a run.

I have had several instances where I scan several documents and build up, say, 
1000 records in my SQLite table.  I then select several more documents for 
scanning and the number of records are halved in my SQLite table.  This is 
always associated with the advent of a system File Open dialog that is empty of 
all files in the folder I am working, when it should have between 60 and 100 
files.

Has anyone working a Mac ever experienced something like this?

John A.M. Darnell
Senior Programmer
john.darn...@walsworth.com
660.258.2104 ext.4108 OFFICE
www.walsworth.com




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

Re: [sqlite] Example of implementing undo/redo using sqlite3?

[Eduardo Morras]

At 19:58 04/08/2011, Stephan Beal wrote:
>Hi, all!
>
>http://www.sqlite.org/whentouse.html
>
>says:
>
>"Temporary triggers can be added to the database to record all changes into
>a (temporary) undo/redo log table. These changes can then be played back
>when the user presses the Undo and Redo buttons. Using this technique, an
>unlimited depth undo/redo implementation can be written in surprisingly
>little code."
>
>i'm wondering if anyone can point me to an example of implementing such a
>beast?

I have not do it but you can mimic/implement it using 2 colums in 
each table and inserting there the transaction number (insert, 
update, delete only) and delete status. Updates duplicates rows with 
different transaction number (bigger one), Delete duplicate row and 
marks it as delete. To restore a previous point

select * from table where transaction_idhttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example of implementing undo/redo using sqlite3?

[Richard Hipp]

On Thu, Aug 4, 2011 at 1:58 PM, Stephan Beal  wrote:

> Hi, all!
>
> http://www.sqlite.org/whentouse.html
>
> says:
>
> "Temporary triggers can be added to the database to record all changes into
> a (temporary) undo/redo log table. These changes can then be played back
> when the user presses the Undo and Redo buttons. Using this technique, an
> unlimited depth undo/redo implementation can be written in surprisingly
> little code."
>
> i'm wondering if anyone can point me to an example of implementing such a
> beast?
>

http://www.sqlite.org/cvstrac/wiki?p=UndoRedo

>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Threading makes SQLite 3x slower??

[Seth Price]

Removing "COUNT(*) AS count" and "GROUP BY class" and doing it in-program 
shaved ~10% off of the time. I'll keep it. :)
~Seth

On Aug 4, 2011, at 11:30 AM, Eduardo Morras wrote:

> 
> Oks, another let's try another thing/think.
> 
> Try the select without the COUNT(*):
> 
> SELECT class FROM data_r JOIN data USING (rowNum) WHERE 57 < col0min 
> AND col0max < 61 AND 52 < col1min AND col1max < 56 AND 66 < col2min 
> AND col2max < 70 AND 88 < col3min AND col3max < 92 AND 133 < col4min 
> AND col4max < 137 AND 57 < col0 AND col0 < 61 AND 52 < col1 AND col1 
> < 56 AND 66 < col2 AND col2 < 70 AND 88 < col3 AND col3 < 92 AND 133 
> < col4 AND col4 < 137 AND 81 < col5 AND col5 < 85 GROUP BY class;
> 
> In some rdbms (don' know in sqlite), count, avg, sum, etc... implies 
> a table scan, making the select very slow.
> 
> HTH
> 
> 
> ___
> 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] Need Help! -- SQlite database on server

[Tim Butterfield]

On Thu, Aug 4, 2011 at 12:52 PM, Stephan Beal  wrote:
> On Thu, Aug 4, 2011 at 7:50 PM, Stephan Beal  wrote:
>
>> http://www.sqlite.org/whentouse.html
>>
>
> Specifically: 2nd section, 1st list item.

It may depend on the usage.  After reading about the possible issues,
I have just implemented something like this for use across multiple
networks, which also had a requirement for using a file share and not
a DB server.  This usage is to distribute versioned data between
different environments, whether on different networks in the same
building or between different cities/countries.  The usage is
non-constant.  No SQLite files remain open for more than the one
action being performed.  There is periodic polling/reading by a
service along with some occasional user initiated read/write action.
There is a relatively small number of total SQLite connections,
roughly three per end point.  The main db is relatively small with the
versioned data package/bundle each being in separate SQLite files.
The smaller files are to make opening the file happen more quickly.
To reduce risk of the mentioned issues, I implemented a separate file
locking mechanism around the SQLite open/close to make each access
exclusive.

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

Re: [sqlite] Example of implementing undo/redo using sqlite3?

[Stephan Beal]

On Thu, Aug 4, 2011 at 8:13 PM, Dustin Sallings  wrote:

>https://github.com/dustin/kvtest/blob/master/sqlite-base.cc#L160
>

The code is short and to the point. Thank you :).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help! -- SQlite database on server

[Kevin Martin]

On 4 Aug 2011, at 18:15, Vinoth raj wrote:

> I have been using SQlite database since three years. Suddenly I have a
> requirement for client/server support for my project.
> So, the requirement is to save sqlite database on a server from a C++
> application.

We use sqlite in a client/server situation. However, the main focus of  
the server is data analysis, it just happens to store the data/results  
in sqlite database. The client contains minimal code and just allows  
us to submit data and export analysis results.

The set up is essentially:

Server Side:
Database is a file on server
RPCServer runs on server on localhost:35790
Server runs sshd.

Client Side
Client runs ssh and establishes tunnel to 35790 on the server
RPCClient connects to localhost:35790

The RPC Client/Server are built with boost iostreams and boost  
serialization. However, if you take this approach it may be better to  
use something like Ice (http://zeroc.com/) if the license suits you.

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

Re: [sqlite] Example of implementing undo/redo using sqlite3?

[Simon Slavin]

On 4 Aug 2011, at 6:58pm, Stephan Beal wrote:

> http://www.sqlite.org/whentouse.html
> 
> says:
> 
> "Temporary triggers can be added to the database to record all changes into
> a (temporary) undo/redo log table. These changes can then be played back
> when the user presses the Undo and Redo buttons. Using this technique, an
> unlimited depth undo/redo implementation can be written in surprisingly
> little code."
> 
> i'm wondering if anyone can point me to an example of implementing such a
> beast?

Yeah.  That's actually a pretty good way of synchronising a multi-copy 
database.  I'd be interested in seeing it.

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

[sqlite] Example of implementing undo/redo using sqlite3?

[Stephan Beal]

Hi, all!

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

says:

"Temporary triggers can be added to the database to record all changes into
a (temporary) undo/redo log table. These changes can then be played back
when the user presses the Undo and Redo buttons. Using this technique, an
unlimited depth undo/redo implementation can be written in surprisingly
little code."

i'm wondering if anyone can point me to an example of implementing such a
beast?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help! -- SQlite database on server

[Stephan Beal]

On Thu, Aug 4, 2011 at 7:50 PM, Stephan Beal  wrote:

> http://www.sqlite.org/whentouse.html
>

Specifically: 2nd section, 1st list item.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help! -- SQlite database on server

[Stephan Beal]

On Thu, Aug 4, 2011 at 7:15 PM, Vinoth raj  wrote:

> It would be a great help if you can shed some light on my problem. Is it
> possible at all to save SQLite database on a server?
>

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


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Threading makes SQLite 3x slower??

[Eduardo Morras]

Oks, another let's try another thing/think.

Try the select without the COUNT(*):

SELECT class FROM data_r JOIN data USING (rowNum) WHERE 57 < col0min 
AND col0max < 61 AND 52 < col1min AND col1max < 56 AND 66 < col2min 
AND col2max < 70 AND 88 < col3min AND col3max < 92 AND 133 < col4min 
AND col4max < 137 AND 57 < col0 AND col0 < 61 AND 52 < col1 AND col1 
< 56 AND 66 < col2 AND col2 < 70 AND 88 < col3 AND col3 < 92 AND 133 
< col4 AND col4 < 137 AND 81 < col5 AND col5 < 85 GROUP BY class;

In some rdbms (don' know in sqlite), count, avg, sum, etc... implies 
a table scan, making the select very slow.

HTH


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

[sqlite] Need Help! -- SQlite database on server

[Vinoth raj]

Dear SQLite users,

I have been using SQlite database since three years. Suddenly I have a
requirement for client/server support for my project.
So, the requirement is to save sqlite database on a server from a C++
application.
I explored the SQLite API with no success. Even numeours queries on the
google did not yield any result.
It would be a great help if you can shed some light on my problem. Is it
possible at all to save SQLite database on a server?

Eagerly looking forward to your valuable advice.

Thanks and Regards,
Vinoth
New Delhi, India
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Threading makes SQLite 3x slower??

[Seth Price]

I tried one index on all columns originally, but it didn't help nearly as much 
as the R*tree.
~Seth


On Aug 4, 2011, at 10:26 AM, Simon Slavin wrote:

> 
> On 4 Aug 2011, at 5:13pm, Seth Price wrote:
> 
>> They were all simple indices on one or more columns, so if you have an idea 
>> on a more complex index, I'd apply it and test it out.
> 
> That's not going to be much use, is it ?  A single SELECT which tests all 
> those different columns can only use one index, and if it chooses any of 
> those indexes it will help only with one column.
> 
> Seems like you need one index on all the columns mentioned in your SELECT.  
> But save yourself some time and effort and try one with just five of the 
> columns first.  See if that speeds things up.  Delete all the indexes on just 
> one column.
> 
> 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] Threading makes SQLite 3x slower??

[Simon Slavin]

On 4 Aug 2011, at 5:13pm, Seth Price wrote:

> They were all simple indices on one or more columns, so if you have an idea 
> on a more complex index, I'd apply it and test it out.

That's not going to be much use, is it ?  A single SELECT which tests all those 
different columns can only use one index, and if it chooses any of those 
indexes it will help only with one column.

Seems like you need one index on all the columns mentioned in your SELECT.  But 
save yourself some time and effort and try one with just five of the columns 
first.  See if that speeds things up.  Delete all the indexes on just one 
column.

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

Re: [sqlite] Threading makes SQLite 3x slower??

[Seth Price]

> Any threads or processes would still be in contention for the same resource: 
> access to that file on disk.


Midway through the run, `iostat` is showing me exactly 0 disk accesses, so I'm 
assuming it's a locking problem and not a physical I/O bottleneck. I'm hoping 
that you folks could give me an idea of what I'm doing wrong. (It could also be 
a problem with the Mac OS locking.)

> The best indexes to create will depend on whether this is a one-time job or 
> whether you're going to have to do it repeatedly with similar or completely 
> different data.


For each row, I'm making a query for similar rows. The full DB is 17 million 
rows. I've tried adding all the indexes I can think of, but the only one that 
helps this query is the R*tree index. They were all simple indices on one or 
more columns, so if you have an idea on a more complex index, I'd apply it and 
test it out.

~Seth


On Aug 4, 2011, at 9:54 AM, Simon Slavin wrote:

> 
> On 4 Aug 2011, at 4:44pm, Seth Price wrote:
> 
>> I was hoping that SQLite was doing that with the JOIN statement. It's 
>> narrowing down the results with the query from the R*table (col*min & 
>> col*max). A large query will have ~14k rows at this point. Then JOINs with 
>> the original table (data) and it's narrowing results further with the 
>> remaining part of the WHERE (col*). The large query will have ~11k rows. 
>> After this it groups by the 'class' column in 'data' and counts how many 
>> rows per class.
>> 
>> I'm really skeptical that I can chop this up into many queries to make it 
>> run faster. I've always assumed that if I can get "final" data back from a 
>> query, then it's exactly the query I'm looking for. I would think that 
>> running three queries would use three times the resources.
> 
> Threading is a red herring.  Any threads or processes would still be in 
> contention for the same resource: access to that file on disk.  This is what 
> caused the observation in your 'Subject' header.  All the threads are doing 
> is waiting for another thread to get out the way.
> 
> Speed increases can come from adding one or more indexes.  The best indexes 
> to create will depend on whether this is a one-time job or whether you're 
> going to have to do it repeatedly with similar or completely different data.
> 
> 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] Threading makes SQLite 3x slower??

[Seth Price]

> With SQLITE_THREADSAFE=2
> with SQLITE_THREADSAFE=1

With 2 threads and THREADSAFE=2 I get 167 seconds.
With 2 threads and THREADSAFE=1, I get 177 seconds.
With 1 thread and THREADSAFE=1, I get 53 seconds.
With 1 thread and THREADSAFE=2, I get 52 seconds.

One thing I'm worried about is I have two differing definitions of 
SQLITE_THREADSAFE when I compile via the command line with 
"-DSQLITE_THREADSAFE=2", so I'm worried that one is clobbering the other. Here 
is a clip from the compile log, you can see *both* THREADSAFE=2 and 
THREADSAFE=1:

 gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.7.7.1\" "-DPACKAGE_STRING=\"sqlite 3.7.7.1\"" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\" 
-DPACKAGE=\"sqlite\" -DVERSION=\"3.7.7.1\" -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 -DHAVE_STRING_H=1 
-DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 -DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 
-DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 -DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 
-DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 
-DHAVE_STRERROR_R=1 -DHAVE_READLINE=1 -I.@am__isrc@ -D_REENTRANT=1 
-DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -O2 
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT -DSQLITE_ENABLE_RTREE -DSQLITE_THREADSAFE=2 
-DSQLITE_OPEN_NOMUTEX -DSQLITE_ENABLE_LOCKING_STYLE 
-DSQLITE_DEFAULT_TEMP_CACHE_SIZE=5000 -DSQLITE_DEFAULT_CACHE_SIZE=2 -MT 
sqlite3.lo -MD -MP -MF .deps/sqlite3.Tpo -c sqlite3.c  -fno-common -DPIC -o 
.libs/sqlite3.o
: warning: "SQLITE_THREADSAFE" redefined
: warning: this is the location of the previous definition


> The way you're splitting your work is bad.  You can see that your row counts 
> for 1 thread are 3 and for 2 threads 45,000 so you're not gaining a lot.  
> 3 threads is 90,000 so you've tripled your workload totally defeating 
> multi-threading.

When I run with 1 thread, it queries all 99k examples. When I use two threads, 
each thread query 45k of the examples. Three threads query 33k of the examples 
each. The total should always be 99k examples, which is what I'm seeing on my 
machine.

Thanks,
Seth

On Aug 4, 2011, at 6:36 AM, Black, Michael (IS) wrote:

> Also..
> 
> .
> 
> Your elapsed time is using clock() which tells you processor time.
> 
> 
> 
> With SQLITE_THREADSAFE=2
> 
> 59.855 - 1 thread
> 
> 49.535 - 2 threads
> 
> 92.789 - 3 threads
> 
> 
> 
> with SQLITE_THREADSAFE=1
> 
> 61.146 - 1 thread
> 
> 49.568 - 2 threads
> 
> 64.932 - 3 threads
> 
> 
> 
> The way you're splitting your work is bad.  You can see that your row counts 
> for 1 thread are 3 and for 2 threads 45,000 so you're not gaining a lot.  
> 3 threads is 90,000 so you've tripled your workload totally defeating 
> multi-threading.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> Michael D. Black
> 
> Senior Scientist
> 
> NG Information Systems
> 
> Advanced Analytics Directorate
> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Seth Price [s...@pricepages.org]
> Sent: Wednesday, August 03, 2011 9:07 PM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] Threading makes SQLite 3x slower??
> 
> Hey all, I have a small DB bound application that I'm working on. I expect 
> that it will take 10-20 days to run when I'm done with it. So I'm trying to 
> make it multithreaded. But after spending all afternoon getting threading 
> going, it runs on the order of 3x slower per query when I go from one to two 
> threads.
> 
> Is this expected?
> 
> The full DB has around 17 million rows in it, and for each row I am trying to 
> count all rows with similar characteristics, divided into different 
> classifications. I was already able to improve speed 10x by using the R*tree 
> extension to narrow my search. My queries look like this:
> 
> SELECT class, COUNT(*) AS count FROM data_r JOIN data USING (rowNum) WHERE 57 
> < col0min AND col0max < 61 AND 52 < col1min AND col1max < 56 AND 66 < col2min 
> AND col2max < 70 AND 88 < col3min AND col3max < 92 AND 133 < col4min AND 
> col4max < 137 AND 57 < col0 AND col0 < 61 AND 52 < col1 AND col1 < 56 AND 66 
> < col2 AND col2 < 70 AND 88 < col3 AND col3 < 92 AND 133 < col4 AND col4 < 
> 137 AND 81 < col5 AND col5 < 85 GROUP BY class;
> 
> They take around 0.04 seconds each (times 17 million). I thought that I 
> should be able to access the DB from each thread without penalty because they 
> are read-only queries. I also tried making copies of the DB file to access a 
> different file with each thread (also slower). Oddly, there are no frequent 
> disk access while I run the program, so there must be caching somewhere.
> 
> How can I make threading work? Download the example set and code here:
> http://seth.bluezone.usu.edu/sqlite/
> 
> There is info on how I compile and run the program in the header. Use 
> NUM_THREADS to change the number. The stats on which thread is taking how 
> much time is printed under 

Re: [sqlite] Threading makes SQLite 3x slower??

[Simon Slavin]

On 4 Aug 2011, at 4:44pm, Seth Price wrote:

> I was hoping that SQLite was doing that with the JOIN statement. It's 
> narrowing down the results with the query from the R*table (col*min & 
> col*max). A large query will have ~14k rows at this point. Then JOINs with 
> the original table (data) and it's narrowing results further with the 
> remaining part of the WHERE (col*). The large query will have ~11k rows. 
> After this it groups by the 'class' column in 'data' and counts how many rows 
> per class.
> 
> I'm really skeptical that I can chop this up into many queries to make it run 
> faster. I've always assumed that if I can get "final" data back from a query, 
> then it's exactly the query I'm looking for. I would think that running three 
> queries would use three times the resources.

Threading is a red herring.  Any threads or processes would still be in 
contention for the same resource: access to that file on disk.  This is what 
caused the observation in your 'Subject' header.  All the threads are doing is 
waiting for another thread to get out the way.

Speed increases can come from adding one or more indexes.  The best indexes to 
create will depend on whether this is a one-time job or whether you're going to 
have to do it repeatedly with similar or completely different data.

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

Re: [sqlite] datetime('now') has only per-second resolution?

[Igor Tandetnik]

On 8/4/2011 11:35 AM, Sean Hammond wrote:
> Hey, I've been recording timestamped log messages in sqlite3 by using
> datetime('now') in INSERT queries, e.g.:
>
> INSERT INTO Logs (...,time) VALUES (...,datetime('now'));
>
> (The time column has type DATETIME.)

That's irrelevant. You are storing strings. SQLite doesn't have a 
dedicated timestamp format. See http://sqlite.org/datatype3.html

> I noticed that if I retrieve these rows with a SELECT query the
> datetimes only have per-second resolution, no fractions of a second,
> e.g.: "2011-07-31 16:04:48"

datetime(x) is just a shorthand for strftime('%Y-%m-%d %H:%M:%S', x)

> I tried to retrieve fractions of a second with a SELECT query containing
> a strftime with %f, e.g.:
>
> SELECT strftime("%Y-%m-%d %H:%M:%f",time) FROM Logs;
>
> but in the table returned all of the fractions of seconds are 000.

Where would non-zero milliseconds come from? They are not stored with 
the data.

> Am I correct in thinking that sqlite3 stores datetime('now') as a string
> without milliseconds?

To be precise, datetime('now') returns a string in a format that doesn't 
include milliseconds. You then store this string in the table.

> Are the resolutions of sqlite's date and time
> functions explicitly documented anywhere?

http://sqlite.org/lang_datefunc.html

> The solution seems to be to use strftime with %f in the INSERT query:
>
> INSERT INTO Logs (...,time) VALUES (...,strftime('%Y-%m-%d
> %H:%M:%f','now'));

That's one way to do it, yes.

> then the results from SELECT queries will contain fractions of a second.

Of course. You get back out exactly what you put in.
-- 
Igor Tandetnik

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

Re: [sqlite] Threading makes SQLite 3x slower??

[Seth Price]

On Aug 4, 2011, at 1:54 AM, Eduardo Morras wrote:
> 
> They block each other not trying to get a lock, but trying to get 
> access to disk and cache. Disk access time and cache is shared 
> between all threads and if all threads needs access to different 
> parts of the db they will figth like barbarians, you should convert 
> them to roman cohorts instead. You can make your page cache size 
> bigger using pragmas, check maillist and documentation.

Using a 10x larger temp cache and default cache at compile time increases my 
runtime of that section from 166 to 167. I'm using two threads still.

> You don't need to update your table, you can use a temp memory table 
> for that with candidates. First select candidates where 57 then you delete candidates from temp table where col0min<62 and so 
> on. You don't need to copy the original table schema for temp table, 
> use pk only. The select of the first candidates is important, use the 
> one that minimizes the number of initial data.

I was hoping that SQLite was doing that with the JOIN statement. It's narrowing 
down the results with the query from the R*table (col*min & col*max). A large 
query will have ~14k rows at this point. Then JOINs with the original table 
(data) and it's narrowing results further with the remaining part of the WHERE 
(col*). The large query will have ~11k rows. After this it groups by the 
'class' column in 'data' and counts how many rows per class.

I'm really skeptical that I can chop this up into many queries to make it run 
faster. I've always assumed that if I can get "final" data back from a query, 
then it's exactly the query I'm looking for. I would think that running three 
queries would use three times the resources.
Thanks,
Seth
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Threading makes SQLite 3x slower??

[Seth Price]

> You didn't show your timing results or say what kind of machine you're 
> running on.


I'm running on a 2.26 GHz Mac Pro with 8 physical cores and 16 GB of RAM.

> SQL error (635): near "ORDER": syntax error


You're probably seeing that error because you need to recompile with 
"-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT". You would need to rebuild the parser, 
but I don't think it affects the results that we're looking at. So ignore the 
error. :)

> What I would do is one query and split the results of that query between 
> threads.


As is, the result from each query is ~1-3 rows that don't require much 
processing. A profiler says I spend about 99% of my time in SQLite.
~Seth

On Aug 4, 2011, at 6:33 AM, Black, Michael (IS) wrote:

> You didn't show your timing results or say what kind of machine you're 
> running on.
> I'm also seeing
> Calculating Subset Sample...
> SQL error (635): near "ORDER": syntax error
> 
> I'm running Linux, sqlite 3.7.5, E5520 2.27Ghz Intel with 16 threads possible.
> 
> Here's my timings just on the first few rows of your timing output shows 2 
> threads is a touch better.
> 1 thread
> ( 0) Rows Proc: 5000 Avg: 0.00144 Elapsed:   7.20
> ( 0) Rows Proc:1 Avg: 0.00104 Elapsed:  10.37
> ( 0) Rows Proc:15000 Avg: 0.00098 Elapsed:  14.77
> ( 0) Rows Proc:2 Avg: 0.00092 Elapsed:  18.36
> ( 0) Rows Proc:25000 Avg: 0.00084 Elapsed:  21.08
> 
> 2 threads
> ( 1) Rows Proc: 5000 Avg: 0.00103 Elapsed:   5.20
> ( 1) Rows Proc:1 Avg: 0.00104 Elapsed:  10.45
> ( 1) Rows Proc:15000 Avg: 0.00103 Elapsed:  15.53
> ( 0) Rows Proc: 5000 Avg: 0.00419 Elapsed:  20.96
> ( 1) Rows Proc:2 Avg: 0.00106 Elapsed:  21.23
> 
> 3 threads
> ( 1) Rows Proc: 5000 Avg: 0.00536 Elapsed:  26.91
> ( 2) Rows Proc: 5000 Avg: 0.00598 Elapsed:  30.05
> ( 1) Rows Proc:1 Avg: 0.00535 Elapsed:  53.67
> ( 2) Rows Proc:1 Avg: 0.00612 Elapsed:  61.36
> ( 1) Rows Proc:15000 Avg: 0.00537 Elapsed:  80.75
> 
> What I would do is one query and split the results of that query between 
> threads.
> 
> You can use OpenMP to do that.
> 
> 
> Michael D. Black
> Senior Scientist
> NG Information Systems
> Advanced Analytics Directorate
> 
> 
> 
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Seth Price [s...@pricepages.org]
> Sent: Wednesday, August 03, 2011 9:07 PM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] Threading makes SQLite 3x slower??
> 
> 
> Hey all, I have a small DB bound application that I'm working on. I expect 
> that it will take 10-20 days to run when I'm done with it. So I'm trying to 
> make it multithreaded. But after spending all afternoon getting threading 
> going, it runs on the order of 3x slower per query when I go from one to two 
> threads.
> 
> Is this expected?
> 
> The full DB has around 17 million rows in it, and for each row I am trying to 
> count all rows with similar characteristics, divided into different 
> classifications. I was already able to improve speed 10x by using the R*tree 
> extension to narrow my search. My queries look like this:
> 
> SELECT class, COUNT(*) AS count FROM data_r JOIN data USING (rowNum) WHERE 57 
> < col0min AND col0max < 61 AND 52 < col1min AND col1max < 56 AND 66 < col2min 
> AND col2max < 70 AND 88 < col3min AND col3max < 92 AND 133 < col4min AND 
> col4max < 137 AND 57 < col0 AND col0 < 61 AND 52 < col1 AND col1 < 56 AND 66 
> < col2 AND col2 < 70 AND 88 < col3 AND col3 < 92 AND 133 < col4 AND col4 < 
> 137 AND 81 < col5 AND col5 < 85 GROUP BY class;
> 
> They take around 0.04 seconds each (times 17 million). I thought that I 
> should be able to access the DB from each thread without penalty because they 
> are read-only queries. I also tried making copies of the DB file to access a 
> different file with each thread (also slower). Oddly, there are no frequent 
> disk access while I run the program, so there must be caching somewhere.
> 
> How can I make threading work? Download the example set and code here:
> http://seth.bluezone.usu.edu/sqlite/
> 
> There is info on how I compile and run the program in the header. Use 
> NUM_THREADS to change the number. The stats on which thread is taking how 
> much time is printed under "Pruning Conflicting Examples..." and the 
> threading code is under "pruneEx(ExPtr ex)". I'm on a Mac Pro running 10.6.8.
> 
> Thoughts? Comments? Ideas?
> 
> Thanks,
> Seth
> ___
> 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-users mailing list
sqlite-users@sqlite.org

[sqlite] datetime('now') has only per-second resolution?

[Sean Hammond]

Hey, I've been recording timestamped log messages in sqlite3 by using 
datetime('now') in INSERT queries, e.g.:

INSERT INTO Logs (...,time) VALUES (...,datetime('now'));

(The time column has type DATETIME.)

I noticed that if I retrieve these rows with a SELECT query the 
datetimes only have per-second resolution, no fractions of a second, 
e.g.: "2011-07-31 16:04:48"

I tried to retrieve fractions of a second with a SELECT query containing 
a strftime with %f, e.g.:

SELECT strftime("%Y-%m-%d %H:%M:%f",time) FROM Logs;

but in the table returned all of the fractions of seconds are 000.

Am I correct in thinking that sqlite3 stores datetime('now') as a string 
without milliseconds? Are the resolutions of sqlite's date and time 
functions explicitly documented anywhere?

The solution seems to be to use strftime with %f in the INSERT query:

INSERT INTO Logs (...,time) VALUES (...,strftime('%Y-%m-%d 
%H:%M:%f','now'));

then the results from SELECT queries will contain fractions of a second.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Threading makes SQLite 3x slower??

[Black, Michael (IS)]

Also..

.

Your elapsed time is using clock() which tells you processor time.



With SQLITE_THREADSAFE=2

59.855 - 1 thread

49.535 - 2 threads

92.789 - 3 threads



with SQLITE_THREADSAFE=1

61.146 - 1 thread

49.568 - 2 threads

64.932 - 3 threads



The way you're splitting your work is bad.  You can see that your row counts 
for 1 thread are 3 and for 2 threads 45,000 so you're not gaining a lot.  3 
threads is 90,000 so you've tripled your workload totally defeating 
multi-threading.









Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Seth Price [s...@pricepages.org]
Sent: Wednesday, August 03, 2011 9:07 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Threading makes SQLite 3x slower??

Hey all, I have a small DB bound application that I'm working on. I expect that 
it will take 10-20 days to run when I'm done with it. So I'm trying to make it 
multithreaded. But after spending all afternoon getting threading going, it 
runs on the order of 3x slower per query when I go from one to two threads.

Is this expected?

The full DB has around 17 million rows in it, and for each row I am trying to 
count all rows with similar characteristics, divided into different 
classifications. I was already able to improve speed 10x by using the R*tree 
extension to narrow my search. My queries look like this:

SELECT class, COUNT(*) AS count FROM data_r JOIN data USING (rowNum) WHERE 57 < 
col0min AND col0max < 61 AND 52 < col1min AND col1max < 56 AND 66 < col2min AND 
col2max < 70 AND 88 < col3min AND col3max < 92 AND 133 < col4min AND col4max < 
137 AND 57 < col0 AND col0 < 61 AND 52 < col1 AND col1 < 56 AND 66 < col2 AND 
col2 < 70 AND 88 < col3 AND col3 < 92 AND 133 < col4 AND col4 < 137 AND 81 < 
col5 AND col5 < 85 GROUP BY class;

They take around 0.04 seconds each (times 17 million). I thought that I should 
be able to access the DB from each thread without penalty because they are 
read-only queries. I also tried making copies of the DB file to access a 
different file with each thread (also slower). Oddly, there are no frequent 
disk access while I run the program, so there must be caching somewhere.

How can I make threading work? Download the example set and code here:
http://seth.bluezone.usu.edu/sqlite/

There is info on how I compile and run the program in the header. Use 
NUM_THREADS to change the number. The stats on which thread is taking how much 
time is printed under "Pruning Conflicting Examples..." and the threading code 
is under "pruneEx(ExPtr ex)". I'm on a Mac Pro running 10.6.8.

Thoughts? Comments? Ideas?

Thanks,
Seth
___
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] Threading makes SQLite 3x slower??

[Black, Michael (IS)]

You didn't show your timing results or say what kind of machine you're running 
on.
I'm also seeing
Calculating Subset Sample...
SQL error (635): near "ORDER": syntax error

I'm running Linux, sqlite 3.7.5, E5520 2.27Ghz Intel with 16 threads possible.

Here's my timings just on the first few rows of your timing output shows 2 
threads is a touch better.
1 thread
( 0) Rows Proc: 5000 Avg: 0.00144 Elapsed:   7.20
( 0) Rows Proc:1 Avg: 0.00104 Elapsed:  10.37
( 0) Rows Proc:15000 Avg: 0.00098 Elapsed:  14.77
( 0) Rows Proc:2 Avg: 0.00092 Elapsed:  18.36
( 0) Rows Proc:25000 Avg: 0.00084 Elapsed:  21.08

2 threads
( 1) Rows Proc: 5000 Avg: 0.00103 Elapsed:   5.20
( 1) Rows Proc:1 Avg: 0.00104 Elapsed:  10.45
( 1) Rows Proc:15000 Avg: 0.00103 Elapsed:  15.53
( 0) Rows Proc: 5000 Avg: 0.00419 Elapsed:  20.96
( 1) Rows Proc:2 Avg: 0.00106 Elapsed:  21.23

3 threads
( 1) Rows Proc: 5000 Avg: 0.00536 Elapsed:  26.91
( 2) Rows Proc: 5000 Avg: 0.00598 Elapsed:  30.05
( 1) Rows Proc:1 Avg: 0.00535 Elapsed:  53.67
( 2) Rows Proc:1 Avg: 0.00612 Elapsed:  61.36
( 1) Rows Proc:15000 Avg: 0.00537 Elapsed:  80.75

What I would do is one query and split the results of that query between 
threads.

You can use OpenMP to do that.


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Seth Price [s...@pricepages.org]
Sent: Wednesday, August 03, 2011 9:07 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Threading makes SQLite 3x slower??


Hey all, I have a small DB bound application that I'm working on. I expect that 
it will take 10-20 days to run when I'm done with it. So I'm trying to make it 
multithreaded. But after spending all afternoon getting threading going, it 
runs on the order of 3x slower per query when I go from one to two threads.

Is this expected?

The full DB has around 17 million rows in it, and for each row I am trying to 
count all rows with similar characteristics, divided into different 
classifications. I was already able to improve speed 10x by using the R*tree 
extension to narrow my search. My queries look like this:

SELECT class, COUNT(*) AS count FROM data_r JOIN data USING (rowNum) WHERE 57 < 
col0min AND col0max < 61 AND 52 < col1min AND col1max < 56 AND 66 < col2min AND 
col2max < 70 AND 88 < col3min AND col3max < 92 AND 133 < col4min AND col4max < 
137 AND 57 < col0 AND col0 < 61 AND 52 < col1 AND col1 < 56 AND 66 < col2 AND 
col2 < 70 AND 88 < col3 AND col3 < 92 AND 133 < col4 AND col4 < 137 AND 81 < 
col5 AND col5 < 85 GROUP BY class;

They take around 0.04 seconds each (times 17 million). I thought that I should 
be able to access the DB from each thread without penalty because they are 
read-only queries. I also tried making copies of the DB file to access a 
different file with each thread (also slower). Oddly, there are no frequent 
disk access while I run the program, so there must be caching somewhere.

How can I make threading work? Download the example set and code here:
http://seth.bluezone.usu.edu/sqlite/

There is info on how I compile and run the program in the header. Use 
NUM_THREADS to change the number. The stats on which thread is taking how much 
time is printed under "Pruning Conflicting Examples..." and the threading code 
is under "pruneEx(ExPtr ex)". I'm on a Mac Pro running 10.6.8.

Thoughts? Comments? Ideas?

Thanks,
Seth
___
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] SQLite and Java

[Sylvain Pointeau]

> my program language is Java. And the www.sqlite.org not supply API for
Java. What should I do?

I think this one is the best
http://code.google.com/p/sqlite-jdbc/

Best regards,
Sylvain
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Threading makes SQLite 3x slower??

[Eduardo Morras]

At 06:53 04/08/2011, you wrote:
>I was hoping they wouldn't block each other because it's a read 
>lock. I tried making an index on all the columns, but R*tree table + 
>JOIN that I'm using runs about 10x faster. I might have done 
>something wrong, so I'm open to suggestions on a better index, though.

They block each other not trying to get a lock, but trying to get 
access to disk and cache. Disk access time and cache is shared 
between all threads and if all threads needs access to different 
parts of the db they will figth like barbarians, you should convert 
them to roman cohorts instead. You can make your page cache size 
bigger using pragmas, check maillist and documentation.

>I don't think that UPDATEing 99.99% of 17 million columns is going 
>to run faster than the pure select statement that I have. Each 
>select statement is fairly random, a small selection out of an 
>N-dimensional space.

You don't need to update your table, you can use a temp memory table 
for that with candidates. First select candidates where 57Am I understanding you? Any other ideas?
>~Seth


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