[sqlite] Query Optimizer and INTEGER PRIMARY KEY

[Jesús López]

Hi,

 

Given the table:

CREATE TABLE Posts (
PostID INTEGER PRIMARY KEY,
CategoryID INT NOT NULL
)

Filled up with 500,000 rows

And the index:

CREATE UNIQUE INDEX IX_Posts (CategoryID, PostID)

The query:

SELECT CategoryID, PostID
FROM Posts
ORDER BY CategoryID
LIMIT 10

Takes about 15 ms to complete.

However the query:

SELECT CategoryID, PostID
FROM Posts
ORDER BY CategoryID, PostID
LIMIT 10

Takes about 1200 ms to complete. Almost one hundred times slower.

This seems to me odd behavior, because I have specified both CategoryID and
PostID in the index IX_Posts. SQLite is sorting the rows, and it is not
taking advantage of the index.

If I define the table like this:

CREATE TABLE Posts (
PostID LONG PRIMARY KEY,
CategoryID INT NOT NULL
)

both queries takes about 15 ms, which is even more amazing.

What is hapening here? Why SQLite behaves like if the INTEGER PRIMARY KEY
field was not in IX_Posts index?

  _  

Regards

Jesús López

Re: [sqlite] SQL error: near "READ_ONLY": syntax error

[John Stanton]

Look here -
http://www.sqlite.org/lang_transaction.html

LuYanJun wrote:
> Thanks, But I step the instruction fellow the hint by offcial document by 
> which said that's correct(BTW forgive me my poor english ):
> http://www.sqlite.org/concurrency.html
> 4.1 Read-only transactions
> BEGIN READ_ONLY;
> SELECT * FROM t1;
> SELECT * FROM t2;
> COMMIT;
> 
> 4.2 Defer write locks
> BEGIN READ_INITIALLY;
> SELECT * FROM t1;
> SELECT * FROM t2;
> COMMIT;
> 4.4 Defer writes
>  BEGIN DEFER_WRITES;
> SELECT * FROM t1;
> -- Processing delay
> UPDATE t1 SET ... WHERE ...
> SELECT * FROM t2;
> -- Processing delay
> UPDATE t2 SET ... WHERE ...
> SELECT * FROM t3;
> -- Processing delay
> UPDATE t3 SET ... WHERE ...
> COMMIT;
> ...and so forth.
> 
> I wonder that does do something with version? 
> - Original Message - 
> From: "Marten Feldtmann" <[EMAIL PROTECTED]>
> To: 
> Sent: Monday, December 18, 2006 3:00 PM
> Subject: Re: [sqlite] SQL error: near "READ_ONLY": syntax error
> 
> 
> 
>>LuYanJun schrieb:
>>
>>>Hi guy:
>>>what does the follow meaning?
>>>sqlite> BEGIN READ_ONLY;
>>>SQL error: near "READ_ONLY": syntax error
>>
>>READ_ONLY is not a valid option for this command.
>>
>>BEGIN [DEFERRED | EXCLUSIVE | IMMEDIATE]
>>
>>Marten
>>
>>-
>>To unsubscribe, send email to [EMAIL PROTECTED]
>>-
>>
> 
>>


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

[sqlite] Re: SQL error: near "READ_ONLY": syntax error

[Igor Tandetnik]

LuYanJun <[EMAIL PROTECTED]> wrote:

Thanks, But I step the instruction fellow the hint by offcial
document by which said that's correct(BTW forgive me my poor english
): http://www.sqlite.org/concurrency.html
4.1 Read-only transactions
BEGIN READ_ONLY;
SELECT * FROM t1;
SELECT * FROM t2;
COMMIT;


If you actually read the text accompanying the example, you would see 
the example is shown as a hypothetical: " Perhaps a keyword after the 
BEGIN statement *could* be used" (emphasis mine).


Note that the article is quite obsolete. Many of the problems it 
discusses are solved long ago, without introducing those extra keywords.


Igor Tandetnik 



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

Re: [sqlite] SQL error: near "READ_ONLY": syntax error

[LuYanJun]

Thanks, But I step the instruction fellow the hint by offcial document by which 
said that's correct(BTW forgive me my poor english ):
http://www.sqlite.org/concurrency.html
4.1 Read-only transactions
BEGIN READ_ONLY;
SELECT * FROM t1;
SELECT * FROM t2;
COMMIT;

4.2 Defer write locks
BEGIN READ_INITIALLY;
SELECT * FROM t1;
SELECT * FROM t2;
COMMIT;
4.4 Defer writes
 BEGIN DEFER_WRITES;
SELECT * FROM t1;
-- Processing delay
UPDATE t1 SET ... WHERE ...
SELECT * FROM t2;
-- Processing delay
UPDATE t2 SET ... WHERE ...
SELECT * FROM t3;
-- Processing delay
UPDATE t3 SET ... WHERE ...
COMMIT;
...and so forth.

I wonder that does do something with version? 
- Original Message - 
From: "Marten Feldtmann" <[EMAIL PROTECTED]>
To: 
Sent: Monday, December 18, 2006 3:00 PM
Subject: Re: [sqlite] SQL error: near "READ_ONLY": syntax error


> LuYanJun schrieb:
>> Hi guy:
>> what does the follow meaning?
>> sqlite> BEGIN READ_ONLY;
>> SQL error: near "READ_ONLY": syntax error
> READ_ONLY is not a valid option for this command.
> 
> BEGIN [DEFERRED | EXCLUSIVE | IMMEDIATE]
> 
> Marten
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
>

Re: [sqlite] Is there a method for doing bulk insertion?

[Jay Sprenkle]

On 12/18/06, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:

I was hoping there was the equivalent of Sybase's BCP program. I was
also hoping something programmatic was available, i.e., not something
from the command shell. Maybe a little background would help.

I'm planning on using the perl package DBD::SQLite. My department is a
big sybase user but because of the nature of our workload, we experience
a lot of contention in both the transaction log and tempdb (the database
that houses temporary tables). I'm investigating the feasibility of
transferring data into SQLite, doing all the data manipulations there,
and then transferring it back to the appropriate sybase tables. I
suspect this could be a big win for a number of our applications.

But if it can be avoided, I don't want to do a CSV conversion, nor do I
want to shell out of the code to invoke this.


I created a c++ version for my own use. The source code is downloadable
if that's of any help to you. See my sig line for the address.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

Re: [sqlite] Is Column UNIQUE? How To

[Dennis Cote]

Firman Wandayandi wrote:

Hi,

Is any possible way to know if a column is UNIQUE without "PRAGMA
index_info('')"? Seems "PRAGMA table_info('')"
doesn't returns the unique flag of column.

Thanks for advice.

Firman,

You should try pragma index_list(''). It returns a list of 
all the indexes (or indices) on the specified table. It provides a flag 
for each unique index as well as the index names. You can then get the 
details about the number, and order of the columns in the index using 
pragma index_info.


Here is a short sample session with the sqlite shell.

   SQLite version 3.3.8
   Enter ".help" for instructions
   sqlite> create table t (a integer primary key, b text unique);
   sqlite> .header on
   sqlite> .mode column
   sqlite> pragma table_info('t');
   cid nametypenotnull dflt_value  pk
   --  --  --  --  --  --
   0   a   integer 0   1
   1   b   text0   0
   sqlite> pragma index_list('t');
   seq name  unique
   --    --
   0   sqlite_autoindex_t_1  1
   sqlite> pragma index_info('sqlite_autoindex_t_1');
   seqno   cid name
   --  --  --
   0   1   b

HTH
Dennis Cote

-
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]
-

RE: [sqlite] Is there a method for doing bulk insertion?

[Anderson, James H \(IT\)]

I was hoping there was the equivalent of Sybase's BCP program. I was
also hoping something programmatic was available, i.e., not something
from the command shell. Maybe a little background would help.

I'm planning on using the perl package DBD::SQLite. My department is a
big sybase user but because of the nature of our workload, we experience
a lot of contention in both the transaction log and tempdb (the database
that houses temporary tables). I'm investigating the feasibility of
transferring data into SQLite, doing all the data manipulations there,
and then transferring it back to the appropriate sybase tables. I
suspect this could be a big win for a number of our applications.

But if it can be avoided, I don't want to do a CSV conversion, nor do I
want to shell out of the code to invoke this.

jim

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Monday, December 18, 2006 9:12 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a method for doing bulk insertion?

"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> or do I have to creation a gazillion insert statements?
> 

The sqlite3 command-line shell has a ".import" command which
can be used to read CSV data.  But the way this works internally
is that the command-line shell constructs an INSERT statement,
parses each line of the CSV file and binds the values to that
INSERT statement, then runs the INSERT statement for each line.
So at the end of the day, a bunch of INSERT statements are still
getting evaluated - you just don't see them.

On my workstation, an INSERT statement can be parsed, compiled,
and evaluated in 25-40 microseconds.  That's about 3 rows
per second.  How much performance do you need?

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

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

Re: [sqlite] SQLite 3 and MS SQL CE Compatibility Issues?

[Gregory Letellier]

Many differents on create table statements...

[EMAIL PROTECTED] a écrit :

My client has an application which uses MS-SQL CE.  My app uses SQLite
3.
 
Does anyone know of any compatibility issues?
 
Thanks,

Richard

  



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

[sqlite] SQLite 3 and MS SQL CE Compatibility Issues?

[Richard.Murphy]

My client has an application which uses MS-SQL CE.  My app uses SQLite
3.
 
Does anyone know of any compatibility issues?
 
Thanks,
Richard

Re: [sqlite] Is there a method for doing bulk insertion?

[drh]

"Anderson, James H \(IT\)" <[EMAIL PROTECTED]> wrote:
> or do I have to creation a gazillion insert statements?
> 

The sqlite3 command-line shell has a ".import" command which
can be used to read CSV data.  But the way this works internally
is that the command-line shell constructs an INSERT statement,
parses each line of the CSV file and binds the values to that
INSERT statement, then runs the INSERT statement for each line.
So at the end of the day, a bunch of INSERT statements are still
getting evaluated - you just don't see them.

On my workstation, an INSERT statement can be parsed, compiled,
and evaluated in 25-40 microseconds.  That's about 3 rows
per second.  How much performance do you need?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

[sqlite] Is there a method for doing bulk insertion?

[Anderson, James H \(IT\)]

...or do I have to creation a gazillion insert statements?

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

Re: [sqlite] Calculate years and months difference with Julian Date?

[bartsmissaert]

Thanks for clarifying that. I think this should settle it now.
In fact when that format is used Excel will pick it up correctly as
well, so that avoids the trouble of my previous method to convert
my Interbase mmdd dates to Excel integer dates via the Julianday
function.

RBS

> [EMAIL PROTECTED] wrote:
>> I think it was the date format in the SQLite table that wasn't right.
>> I had this as dd-mm-
>>
>> Doing SELECT date('2006-02-16','+1 month')
>> gives me indeed correctly 16 Feb 2006
>>
>> So does the format have to be -mm-dd  ?
>>
>
> Yes.  That format is called ISO-8601.  Everybody in the world
> is moving to it (some faster than others).  You would do well
> to get on board.
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




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

Re: [sqlite] Calculate years and months difference with Julian Date?

[bartsmissaert]

Yes, so that may settle it then.

RBS

> [EMAIL PROTECTED] wrote:
>> I think it was the date format in the SQLite table that wasn't right.
>> I had this as dd-mm-
>>
>> Doing SELECT date('2006-02-16','+1 month')
>> gives me indeed correctly 16 Feb 2006
>>
>> So does the format have to be -mm-dd  ?
>>
> Only if you want it to work... ;)
>
> Martin
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




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

Re: [sqlite] Calculate years and months difference with Julian Date?

[drh]

[EMAIL PROTECTED] wrote:
> I think it was the date format in the SQLite table that wasn't right.
> I had this as dd-mm-
> 
> Doing SELECT date('2006-02-16','+1 month')
> gives me indeed correctly 16 Feb 2006
> 
> So does the format have to be -mm-dd  ?
> 

Yes.  That format is called ISO-8601.  Everybody in the world
is moving to it (some faster than others).  You would do well
to get on board.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Calculate years and months difference with Julian Date?

[Martin Jenkins]

[EMAIL PROTECTED] wrote:

I think it was the date format in the SQLite table that wasn't right.
I had this as dd-mm-

Doing SELECT date('2006-02-16','+1 month')
gives me indeed correctly 16 Feb 2006

So does the format have to be -mm-dd  ?
  

Only if you want it to work... ;)

Martin

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

Re: [sqlite] Calculate years and months difference with Julian Date?

[bartsmissaert]

I think it was the date format in the SQLite table that wasn't right.
I had this as dd-mm-

Doing SELECT date('2006-02-16','+1 month')
gives me indeed correctly 16 Feb 2006

So does the format have to be -mm-dd  ?

RBS


> [EMAIL PROTECTED] wrote:
>> Unfortunately, it looks that just adds 30 days, so 16 Feb 06 will give
>> 18
>> March 06 etc.
>>
>
> SELECT date('2006-01-16','+1 month');
>
> yields 2006-02-16.
>
> You must have typed something wrong.
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




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

Re: [sqlite] Calculate years and months difference with Julian Date?

[drh]

[EMAIL PROTECTED] wrote:
> Unfortunately, it looks that just adds 30 days, so 16 Feb 06 will give 18
> March 06 etc.
> 

SELECT date('2006-01-16','+1 month');

yields 2006-02-16. 

You must have typed something wrong.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

RE: [sqlite] Calculate years and months difference with Julian Date?

[bartsmissaert]

Unfortunately, it looks that just adds 30 days, so 16 Feb 06 will give 18
March 06 etc.

RBS

>> If you have two dates A and B, you can ask the question:
>
>>   Is A more than one month after B like this:
>
>> SELECT julianday(A,'+1 month')>B ...
>
>
> Thanks; that looks exactly what I need.
>
> RBS
>
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: 18 December 2006 01:52
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Calculate years and months difference with Julian
> Date?
>
> "RB Smissaert" <[EMAIL PROTECTED]> wrote:
>> Is it possible with Julian dates in 2 fields to calculate the months
> and/or
>> years difference between those 2 dates?
>>
>> I know I can subtract the 2 dates and divide by 30 (or 30.42) or 365 and
>> take it from there, but I would like it a bit more precise, so for
> example:
>> Is 2 March 2006 more than one month after 1 February 2006?
>> If calculated with a simple division the answer would be no, but I would
>> like it to be yes.
>>
>> Had a look at the date/time functions on the WIKI site, but couldn't see
> it.
>>
>
> If you have two dates A and B, you can ask the question:
>
> Is A more than one month after B like this:
>
> SELECT julianday(A,'+1 month')>B ...
>
> If you really need to know the number of months difference between
> A and B, then perhaps something like this:
>
> SELECT (strftime('%m',A)+12*strftime('%Y',A)) -
>(strftime('%m',B)+12*strftime('%Y',B)) ...
>
> Caution:  Both of the above are off the top of my head and are
> untested.  But perhaps they will give you some ideas.
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> 
> -
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>




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

RE: [sqlite] Is sqlite the right db for me?

[Denis Povshedny]

Hi Steve,
 
as a possible alternative for Python you may take a look at Lua
language. www.lua.org Here is also a few webserver platform such as
Xavante. Web-pages looks as standard html pages with Lua code fragment
(similar to ASP but simpler)
 
Lua supports SQLite with nice library luasqlite.
 
In current project we had thinking which language shall we use - Python
or Lua - and still satisfied with our choice. 
 
WBR, Denis

-Original Message-
From: Steve Davis [mailto:[EMAIL PROTECTED] 
Sent: Sunday, December 17, 2006 2:02 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Is sqlite the right db for me?


I am starting an ambitious project that I fear I may regret :P
 
I've done a bit of research and it looks like Python is the way to go -
unless anyone has any other suggestions.  It means learning Python from
scratch, but Python looks like it might be worth the effort.