Re: [sqlite] performance question: SELECT max(rowid) - 1

[Guy Hindell]

Trey Mack wrote:

> I have a fairly large table (10million rows) with a simple INTEGER
> PRIMARY KEY AUTOINCREMENT field.
>
> Executing 'SELECT  max(rowid) FROM MyTable' is very fast, as is
> 'SELECT  min(rowid) FROM MyTable'.
>
> However, 'SELECT  max(rowid) - min(rowid) FROM MyTable' is slow
> (apparently accessing every row). Further, 'SELECT  max(rowid) - 1
> FROM MyTable' is slow - in fact using any constant in this expression
> (including 0) results in a slow query.
>
> Finally, 'SELECT (SELECT  max(rowid) FROM MyTable') - 10' is very 
fast.

>


Check out http://www.sqlite.org/php2004/slides-all.html Page 61

SELECT max(rowid) FROM MyTable
   and
SELECT min(rowid) FROM MyTable

are optimized to run without a full table scan. However

SELECT max(rowid) - min(rowid) FROM MyTable

is not, and will perform a full table scan. To achieve the same 
functionality with the optimizations, try:


SELECT (SELECT max(rowid) FROM MyTable) - (SELECT min(rowid) FROM 
MyTable)

SELECT (SELECT max(rowid) FROM MyTable) - 1

- Trey
Thank for that link Trey (and Puneet) - it pretty much confirms what I'd 
arrived at for myself by trial and error.


guy


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] performance question: SELECT max(rowid) - 1

[Guy Hindell]

Ah, OK, I see that doing 'SELECT 1 FROM MyTable' returns a 1 for every 
row, so I can see where the effort is probably going. However, 'SELECT  
max(rowid) - 1 FROM MyTable' still only produces one result row 
(obviously I'm experimenting with a much smaller database now). Still 
need an explanation rather than just relying on my own speculation.


Cheers
guy

Guy Hindell wrote:
I have a fairly large table (10million rows) with a simple INTEGER 
PRIMARY KEY AUTOINCREMENT field.


Executing 'SELECT  max(rowid) FROM MyTable' is very fast, as is 
'SELECT  min(rowid) FROM MyTable'.


However, 'SELECT  max(rowid) - min(rowid) FROM MyTable' is slow 
(apparently accessing every row). Further, 'SELECT  max(rowid) - 1 
FROM MyTable' is slow - in fact using any constant in this expression 
(including 0) results in a slow query.


Finally, 'SELECT (SELECT  max(rowid) FROM MyTable') - 10' is very fast.

I am curious and would be grateful if someone can explain what is 
going on here.


guy


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 









-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] performance question: SELECT max(rowid) - 1

[Guy Hindell]

I have a fairly large table (10million rows) with a simple INTEGER 
PRIMARY KEY AUTOINCREMENT field.


Executing 'SELECT  max(rowid) FROM MyTable' is very fast, as is 'SELECT  
min(rowid) FROM MyTable'.


However, 'SELECT  max(rowid) - min(rowid) FROM MyTable' is slow 
(apparently accessing every row). Further, 'SELECT  max(rowid) - 1 FROM 
MyTable' is slow - in fact using any constant in this expression 
(including 0) results in a slow query.


Finally, 'SELECT (SELECT  max(rowid) FROM MyTable') - 10' is very fast.

I am curious and would be grateful if someone can explain what is going 
on here.


guy


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] DROP TABLE slower than DELETE?

[Guy Hindell]

Hi all

In promoting SQLite 3 (still v3.2.7, so a bit behind the current 
release) for a new project I have been doing some performance tests and 
SQLite generally looks very good. However, I have seen one surprising 
result.


My schema is simple, a single table with a simple autoincrement rowid as 
the only index. Deleting all the rows from a table populated with 
~60 rows using simply DELETE FROM Foo takes twice as long as DELETE 
FROM Foo WHERE 1. This is odd - 
http://sqlite.org/capi3ref.html#sqlite3_changes - would lead me to 
expect the opposite. So, I tried DROP TABLE Foo, and sure enough, the 
time it takes is the same as the DELETE with no WHERE clause specified - 
twice as long! Odd.


Any comments?
guy


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] disk I/O error writing files mounted via samba

[Guy Hindell]

Guy Hindell wrote:

Guy Hindell wrote:

[EMAIL PROTECTED] wrote:

Guy Hindell <[EMAIL PROTECTED]> wrote:
 
I would like to use sqlite (v3.3.8) on a linux box (fedora core 5) 
to read/write a database file in a directory which is actually on a 
windows share mounted via samba/cifs. I can open the file, and read 
from it, but writing produces "disk I/O error" messages 
(SQLITE_IOERR error code). I can write ordinary files on the share 
(echo "foo" > [share]/file.txt) so it doesn't look like a 
permissions issue. Only one process is ever going to access the 
file so I wouldn't expect locking issues. If I try turning things 
around so that I build/run my sqlite program on windows and access 
a file on a samba share exported from my linux box I can read/write 
without any errors.





Please turn on extended result codes using

  sqlite3_extended_result_codes(db, 1)

Then tell me the detailed error code that results from this
error.  That will help to isolate the problem.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





  

Hi, sorry for the delay - been away from email for a couple of days.

I have tried adding a call to sqlite3_extended_result_codes() as you 
describe above (called once after the db file is opened on the handle 
returned from sqlite3_open()). Still get simply error code 10 
(SQLITE_IOERR) back from write statements (no upper bits set), but 
now get no error text from sqlite3_get_table() (which I use to front 
all my sqlite3_exec() calls).


What next?

guy

BTW, regarding the other post about file sizes greater than 2GB, no, 
the file is tiny.






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





OK, a bit more playing shows that the point at which the SQLITE_IOERR 
is produced is in os_unix.c, line ~1586 -


   lock.l_type = F_UNLCK;
   lock.l_whence = SEEK_SET;
   lock.l_start = PENDING_BYTE;
   lock.l_len = 2L;  assert( PENDING_BYTE+1==RESERVED_BYTE );
   if( fcntl(pFile->h, F_SETLK, )==0 ){
 pLock->locktype = SHARED_LOCK;
   }else{
 rc = SQLITE_IOERR_UNLOCK;  /* This should never happen <--- BUT 
IT DOES :-(

   }

Not sure why I don't see this as an extended result, but it seems like 
the cifs mounted filesystem isn't behaving in the expected manner.


guy




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





Ah! Google provides the answer - seems like the nobrl option is required 
when mounting filesystems with cifs if this sort of locking call is 
going to be made...


http://lists.samba.org/archive/linux-cifs-client/2006-November/001583.html




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] disk I/O error writing files mounted via samba

[Guy Hindell]

Guy Hindell wrote:

[EMAIL PROTECTED] wrote:

Guy Hindell <[EMAIL PROTECTED]> wrote:
 
I would like to use sqlite (v3.3.8) on a linux box (fedora core 5) 
to read/write a database file in a directory which is actually on a 
windows share mounted via samba/cifs. I can open the file, and read 
from it, but writing produces "disk I/O error" messages 
(SQLITE_IOERR error code). I can write ordinary files on the share 
(echo "foo" > [share]/file.txt) so it doesn't look like a 
permissions issue. Only one process is ever going to access the file 
so I wouldn't expect locking issues. If I try turning things around 
so that I build/run my sqlite program on windows and access a file 
on a samba share exported from my linux box I can read/write without 
any errors.





Please turn on extended result codes using

  sqlite3_extended_result_codes(db, 1)

Then tell me the detailed error code that results from this
error.  That will help to isolate the problem.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





  

Hi, sorry for the delay - been away from email for a couple of days.

I have tried adding a call to sqlite3_extended_result_codes() as you 
describe above (called once after the db file is opened on the handle 
returned from sqlite3_open()). Still get simply error code 10 
(SQLITE_IOERR) back from write statements (no upper bits set), but now 
get no error text from sqlite3_get_table() (which I use to front all 
my sqlite3_exec() calls).


What next?

guy

BTW, regarding the other post about file sizes greater than 2GB, no, 
the file is tiny.






- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





OK, a bit more playing shows that the point at which the SQLITE_IOERR is 
produced is in os_unix.c, line ~1586 -


   lock.l_type = F_UNLCK;
   lock.l_whence = SEEK_SET;
   lock.l_start = PENDING_BYTE;
   lock.l_len = 2L;  assert( PENDING_BYTE+1==RESERVED_BYTE );
   if( fcntl(pFile->h, F_SETLK, )==0 ){
 pLock->locktype = SHARED_LOCK;
   }else{
 rc = SQLITE_IOERR_UNLOCK;  /* This should never happen <--- BUT IT 
DOES :-(

   }

Not sure why I don't see this as an extended result, but it seems like 
the cifs mounted filesystem isn't behaving in the expected manner.


guy




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] disk I/O error writing files mounted via samba

[Guy Hindell]

[EMAIL PROTECTED] wrote:

Guy Hindell <[EMAIL PROTECTED]> wrote:
  
I would like to use sqlite (v3.3.8) on a linux box (fedora core 5) to 
read/write a database file in a directory which is actually on a windows 
share mounted via samba/cifs. I can open the file, and read from it, but 
writing produces "disk I/O error" messages (SQLITE_IOERR error code). I 
can write ordinary files on the share (echo "foo" > [share]/file.txt) so 
it doesn't look like a permissions issue. Only one process is ever going 
to access the file so I wouldn't expect locking issues. If I try turning 
things around so that I build/run my sqlite program on windows and 
access a file on a samba share exported from my linux box I can 
read/write without any errors.





Please turn on extended result codes using

  sqlite3_extended_result_codes(db, 1)

Then tell me the detailed error code that results from this
error.  That will help to isolate the problem.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-



  

Hi, sorry for the delay - been away from email for a couple of days.

I have tried adding a call to sqlite3_extended_result_codes() as you 
describe above (called once after the db file is opened on the handle 
returned from sqlite3_open()). Still get simply error code 10 
(SQLITE_IOERR) back from write statements (no upper bits set), but now 
get no error text from sqlite3_get_table() (which I use to front all my 
sqlite3_exec() calls).


What next?

guy

BTW, regarding the other post about file sizes greater than 2GB, no, the 
file is tiny.






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite tuning for db writes?

[Guy Hindell]

Hi
 
I'm relatively new to sqlite and this list but I have recently used it
(v3.2.7) as an alternative to the db backend of an application which
previously used SQLServer or Access as part of a proposed port to
Unix/Linux. So far so good - the app works just as it did before with
SQLServer. However, I'm interested to know how I can tune sqlite to best
advantage for the app.
 
The database is only accessed by one thread and only to store the
current state of the application, so the database is read once at start
up and then does nothing but writes (inserts, updates and deletes) after
that. The schema is simple, essentially consisting of a table where each
row represents object instances, another table which stores the values
for each object instance, and a third which stores relationships between
objects. All tables have simply a primary key which covers between 2 and
4 columns depending on the table. There are no complex queries performed
on the database (no multi table selects, joins etc.).
 
The majority of SQL commands issued are INSERTs and DELETEs, and so it
is these that I would like to optimise. Performance at the moment is
respectable, although I would prefer to see DELETEs go a bit faster. To
minimise the number of SQL commands issued I have implemented some
batching of row DELETEs (e.g. DELETE FROM Instances WHERE Id in
(1,2,3,4,5.)). All commands are issued within transactions.
 
Any suggestions? I have read the tuning hints at www.sqlite.org
  but can't tell which of these favour reading
or writing.
 
Many thanks
guy