Re: [sqlite] Scrolling through results of select

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 >>> 1. Is there any significant overhead on SQLite from my selecting from a >>> view representing the original arbitrary select? That is, will SQLite still >>> use any indexes etc correctly? Or do I need to dissect/parse the original >>> select

Re: [sqlite] COUNT() extremely slow first time!

On Mon, Feb 21, 2011 at 11:35 AM, Jim Wilcoxson wrote: > On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote: >> >> Thank you for your detailed explanation! >> First, can you please tell me how to purge the cache in Windows 7? This >> could be very useful for

Re: [sqlite] SQLite server using execnet ?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/21/2011 12:37 PM, Jay A. Kreibich wrote: > Yes, but in something like memcached, the database is not aware of > that structure, and can't take advantage of it. Memcached does understand some types and has atomic increment/decrement etc but

Re: [sqlite] Scrolling through results of select

On 21/02/2011, at 12:41 PM, Roger Binns wrote: >> How can I best scroll though the results of an arbitrary select query? > > If you have infinite time and memory then there is no problem. Memory and processor are limited (typically iPad/iPhone). Disk space would cope with creating temporary

Re: [sqlite] Scrolling through results of select

On 21/02/2011, at 12:41 PM, Simon Slavin wrote: >> How can I best scroll though the results of an arbitrary select query? > > Suppose the results of the SELECT change between your original decision to do > the scrolling and the time the user decides to scroll. Should what's shown > on the

Re: [sqlite] SQLite GUI comparison

On 22/02/2011, at 4:31 AM, skywind mailing lists wrote: > "Supports SQLite extension" would be an accurate feature description. And in > the cell (value) I suggest to put - if supported - FTS2, FTS3, RTree etc., > otherwise a "-". A yes or no is insufficient because some support RTree but >

Re: [sqlite] sqlite3_busy_handler

On 21 Feb 2011, at 9:46pm, Frank Chang wrote: > I was wondering why other selects who need to only read from a sqlite > database need to use the sqlite3_busy_handlers. Is it because database > connection which are writing to the sqlite database have a higher priority > then database

Re: [sqlite] EXT :Re: sqlite3_busy_handler

If I'm not mistaken only WAL mode supports simulaneous read/write. For any other mode any write function will lock the database. So...selects may run into a need to wait until a write finishes. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate

Re: [sqlite] sqlite3_busy_handler

Michael D. Black, I will try different batch sizes so that other processes do their thing potentially. I was wondering why other selects who need to only read from a sqlite database need to use the sqlite3_busy_handlers. Is it because database connection which are writing to the sqlite

Re: [sqlite] SQLite server using execnet ?

On Feb 21, 2011, at 9:37 PM, Jay A. Kreibich wrote: > I was once forced to look at SOAP over SMTP Ah, yes... double S! The S stands for Simple http://wanderingbarque.com/nonintersecting/2006/11/15/the-s-stands-for-simple/ ___ sqlite-users mailing

Re: [sqlite] SQLite server using execnet ?

On Sun, Feb 20, 2011 at 08:37:04PM -0800, Roger Binns scratched on the wall: > On 02/20/2011 06:23 PM, Jay A. Kreibich wrote: > > On Sun, Feb 20, 2011 at 05:23:09PM -0800, Roger Binns scratched on the wall: > >> If you want to use SQL then use Postfix. > > > > I might suggest PostgreSQL

Re: [sqlite] process monitor: wal file access

> I find especially the *-wal access attempt pretty strange as we do not have > WAL enabled for our database: it is set to the default journal mode (DELETE). > Is this normal behaviour? Or is this influenced by some setting I don't know > of? I think when SQLite opens the database it can't

Re: [sqlite] SQLite GUI comparison (was: ANN: Base 2.0, Mac SQLite GUI)

Hi Tom, "Supports SQLite extension" would be an accurate feature description. And in the cell (value) I suggest to put - if supported - FTS2, FTS3, RTree etc., otherwise a "-". A yes or no is insufficient because some support RTree but not FTS and vice versa. Alternatively you may have a row

Re: [sqlite] update of a blob

On Mon, 2011-02-21 at 15:13 +0100, Dietmar Hummel wrote: > std::string strStatement( "UPDATE persistence SET > name=\"blub\",expiration=\"2011-04-02\",value=\"?\" WHERE id=\"1\"" ); In addition to what Igor said, it isn't really proper (standard?) SQL to put double quotes around the value

Re: [sqlite] COUNT() extremely slow first time!

Thanks :) This did the trick: First make a copy of the database: copy HugeDatabase.db HugeDatabase_copy.db Then for each run, replace the database with its copy. This is why I thought the COUNT operation was somehow written to the database after its first run... :P > From:

Re: [sqlite] COUNT() extremely slow first time!

On 21 Feb 2011, at 4:35pm, Jim Wilcoxson wrote: > On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote: >> >> Thank you for your detailed explanation! >> First, can you please tell me how to purge the cache in Windows 7? This >> could be very useful for my tests! > > Sorry,

Re: [sqlite] COUNT() extremely slow first time!

On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote: > > Thank you for your detailed explanation! > First, can you please tell me how to purge the cache in Windows 7? This could > be very useful for my tests! Sorry, dunno for Windows. On Mac OSX it is the purge command, in the

Re: [sqlite] COUNT() extremely slow first time!

On Mon, Feb 21, 2011 at 03:37:50PM +0100, Sven L scratched on the wall: > I've learnt that COUNT(*) is slower than COUNT(ID), That's usually not true. > since * means the engine has to traverse all columns Actually, count(*) is the one case when the engine does *not* need to traverse

Re: [sqlite] COUNT() extremely slow first time!

On Mon, Feb 21, 2011 at 10:38 AM, Puneet Kishor wrote: > On Mon, Feb 21, 2011 at 10:17:03AM -0500, Jim Wilcoxson wrote: >> This is a common issue on the mailing list.  The first time you do >> count(*), SQLite (actually your OS) has to load data into memory. >> Most OS's will

Re: [sqlite] COUNT() extremely slow first time!

Thank you for your detailed explanation! First, can you please tell me how to purge the cache in Windows 7? This could be very useful for my tests! I'm quite sure my database itself is not fragmented, since I have only inserted data. The file system is in good shape too; Windows reports 0%

Re: [sqlite] COUNT() extremely slow first time!

On Mon, Feb 21, 2011 at 10:17:03AM -0500, Jim Wilcoxson wrote: > This is a common issue on the mailing list. The first time you do > count(*), SQLite (actually your OS) has to load data into memory. > Most OS's will keep the file in a buffer cache, so the 2nd count(*) > doesn't have to read from

Re: [sqlite] COUNT() extremely slow first time!

This is a common issue on the mailing list. The first time you do count(*), SQLite (actually your OS) has to load data into memory. Most OS's will keep the file in a buffer cache, so the 2nd count(*) doesn't have to read from disk. Here's a timing from my own system, after a purge command to

Re: [sqlite] COUNT() extremely slow first time!

Same result :( Note that I have compiled SQLite with the following switches: SQLITE_ENABLE_STAT2 SQLITE_THREADSAFE=2 I've learnt that COUNT(*) is slower than COUNT(ID), since * means the engine has to traverse all columns and it might even return another value if there are NULL-values...

Re: [sqlite] update of a blob

Dietmar Hummel wrote: > Maybe someone could help me with a little problem. I am trying to update > an existing entry in the db > with an update statement where one of the columns is a blob type. I have > code that looks like this: > > sqlite3_stmt* m_pStatement = NULL; >

Re: [sqlite] COUNT() extremely slow first time!

On 21 Feb 2011, at 2:23pm, Sven L wrote: > SELECT COUNT(ItemID) FROM Items; > > This takes around 40 seconds the first time! WHY?! Try again, doing everything identically except that instead of the above line use SELECT COUNT(*) FROM Items; Simon.

[sqlite] COUNT() extremely slow first time!

SQLite 3.7.5. I have a table (13 columns with 1 primary key, 1 index on a date column, 1 UNIQUE-constraint on two columns). I insert ~130 rows, 1000 at a time (transaction-wise). I close the database, reopen it and immediately perform a COUNT-operation: SELECT COUNT(ItemID) FROM Items;

[sqlite] process monitor: wal file access

hello, we recently switched from SQLite version 3.5.4 to 3.7.5. When I run process monitor on our machines I see a lot of failed file access attempts that weren't there before (v 3.5.4). It is failed QueryOpen (NAME NOT FOUND) events for DBASE-NAME.db3-wal and DBASE-NAME.db3-journal. I

[sqlite] update of a blob

Hi list! Maybe someone could help me with a little problem. I am trying to update an existing entry in the db with an update statement where one of the columns is a blob type. I have code that looks like this: sqlite3_stmt* m_pStatement = NULL; std::string strStatement( "UPDATE

Re: [sqlite] Upgrade from 3.5.8 -> 3.7.5 - increase memory usage

I am looking at the private byes counter from Windows (MSDN Description below) for my process. Is there another counter or stat I should be looking at? (from MSDN - Private Bytes: Displays the current number of bytes this process has allocated that cannot be shared with other processes.) I

Re: [sqlite] Contributing to System.Data.Sqlite

On Sat, Feb 19, 2011 at 10:11 PM, Alexander Spence < alexander.spe...@demandmedia.com> wrote: > Who would I talk to if I wanted to contribute to the System.Data.Sqlite > project? We use this code in an enterprise environment and would like to > contribute our changes to the community. > I need

Re: [sqlite] owner/permissions on wal/shm and journal files

On 21 Feb 2011, at 09:36, Simon Slavin wrote: > On 21 Feb 2011, at 9:35am, Philip Graham Willoughby wrote: > >> On 20 Feb 2011, at 16:18, thilo wrote: >> >>> My DB is owned by a different user (www) and I as root insert some data. >> ... >>> Are there any suggestions on how to make this

Re: [sqlite] owner/permissions on wal/shm and journal files

On 21 Feb 2011, at 9:35am, Philip Graham Willoughby wrote: > On 20 Feb 2011, at 16:18, thilo wrote: > >> My DB is owned by a different user (www) and I as root insert some data. > ... >> Are there any suggestions on how to make this workable? > > Don't do that. Unless you can guarantee that no

Re: [sqlite] Upgrade from 3.5.8 -> 3.7.5 - increase memory usage

On 21 Feb 2011, at 5:56am, Dan Kennedy wrote: > WAL file growth has (apparently) been a problem for people with > really busy systems. But so far it has been the actual size of > the file on disk that bothers them, not the memory mapped bit. If that's what you think is raising concern then

Re: [sqlite] owner/permissions on wal/shm and journal files

On 20 Feb 2011, at 16:18, thilo wrote: > My DB is owned by a different user (www) and I as root insert some data. ... > Are there any suggestions on how to make this workable? Don't do that. Unless you can guarantee that no malicious user has been able to alter your database it isn't safe to