[sqlite] How can i see if an query uses an index ?
Hi all I have the following table + index CREATE TABLE O_YDLRK_CK91_HIST ( ISIN TEXT NULL, BOERS_DATODATE NOT NULL, TERM_DATO DATE NOT NULL, AFDRAG_BELOEB REAL NULL, RENTE_BELOEB REAL NULL, CONSTRAINT XPKO_YDLRK_CK91_HIST PRIMARY KEY (ISIN,BOERS_DATO,TERM_DATO) ); CREATE INDEX XIE1O_YDLRK_CK91_HIST ON O_YDLRK_CK91_HIST(BOERS_DATO); with aprx. 5.000.000 rows. I do the following query against the table Select a.Boers_Dato,a.Isin,a.Term_dato,a.Afdrag_Beloeb,a.Rente_Beloeb from O_YDLRK_CK91_HIST a,( select Isin as FK,Term_dato as tm, max(Boers_Dato) as p from O_YDLRK_CK91_HIST where Isin='DK0009253064' and Boers_Dato<='2004-10-01' group by Isin,Term_dato) b where a.Isin=b.FK and a.Term_dato=b.tm and a.Boers_Dato=b.p and a.Term_dato>='2004-10-01' Order by a.Isin, a.Term_dato, a.Boers_Dato And have some problems with execution times. This takes aprx. 5 minutes to get an answer - which is longer than i have expected (compared to other databases) So how can i see if it really uses the index or not ??? Any other performance enhancement / efficient rewriting tips are appreciated - since i am a newbie to SQLite. Thanx in advance Jon Friis
Re: [sqlite] Importing Tab delimited data
Nemanja, I never knew this could happen. In one of my latest project I had to use sed to convert data into csv. Later this data was imported by import command. But the example stated here would reduce atleast one step in my project. Moreover I am only concerned that the data may not be tab limited by offcource seperated by space. Thanks On 1/30/06, Nemanja Corlija <[EMAIL PROTECTED]> wrote: > On 1/30/06, Clint Bailey <[EMAIL PROTECTED]> wrote: > > Is it possible to import tab delimited data into sqlite in order to > > start testing? > You can use sqlite3 command for that. Something like this: > > E:\SQLite>sqlite3.exe test.db3 > SQLite version 3.2.6 > Enter ".help" for instructions > sqlite> CREATE TABLE test(a, b, c, d); > sqlite> .mode tabs > sqlite> .import "tab_delimited_data.txt" test > sqlite> .mode column > sqlite> SELECT * FROM test LIMIT 10; > > Of course, replace "test" table above with something that matches > number of columns in your input file. > There's a patch attached to > http://www.sqlite.org/cvstrac/tktview?tn=1506 that adds 2 flags to > .import. If you are on windows I can send you sqlite3.exe compiled > with that patch if you need it. > > -- > Nemanja Corlija <[EMAIL PROTECTED]> > -- With Best Regards, Vishal Kashyap. http://www.vishal.net.in
Re: [sqlite] Transforming BLOB results in SELECT using sqlite?
Thanks very much, the quote() helps a lot. On Mon, 30 Jan 2006 [EMAIL PROTECTED] wrote: > Shane Baker <[EMAIL PROTECTED]> wrote: > > Are there any mechanisms that will display the [BLOB] > > data in a human readable format? > > Assuming the table is: CREATE TABLE t1(x BLOB) > You can do this: > > SELECT quote(x) FROM t1; > > > For that matter, can I view an INTEGER > > column as hex in the output window? > > Not without modifying the shell to implement some kind > of custom function to do so. At least no way that I can > think of right off hand. > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
Re: [sqlite] Auto Increment?
Clint Bailey wrote: Can you set up a field to auto-increment, and if so how? Details are in the fourth paragraph of: http://sqlite.org/lang_createtable.html Summary: create table tbl(fieldname integer primary key autoincrement, ...) HTH, Gerry
Re: [sqlite] Auto Increment?
http://www.sqlite.org/lang_createtable.html Regards. rayB |-+> | | Clint Bailey | | | <[EMAIL PROTECTED]| | | h.net> | | || | | 31/01/2006 14:24 | | | Please respond to| | | sqlite-users | | || |-+> >--| | | | To: sqlite-users@sqlite.org | | cc: | | Subject: [sqlite] Auto Increment? | >--| Can you set up a field to auto-increment, and if so how? ** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING * *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com
[sqlite] Auto Increment?
Can you set up a field to auto-increment, and if so how?
Re: [sqlite] sqlite in a shell
The MSYS "start" command just circumvents the problem by starting a new clunky Windows cmd window. The Cygwin, Emacs and remote users logging into a Windows box via telnet/ssh who prefer to work in their native terminal program either have to hack the sqlite3 shell.c code to force an interactive session, or use a cygwin-compiled sqlite3.exe. --- Dennis Cote <[EMAIL PROTECTED]> wrote: > You are correct. This is a common problem for Unix code under windows > (at least with MSYS), since isatty() doesn't work correctly. MSYS comes > with a start command to work around this issue. Instead of: > > sqlite3 ... > > you can use > > start sqlite3 ... > > at the MSYS prompt. It will start a windows command shell running > sqlite3 where the isatty returns the correct results, and hence the I/O > is flushed to the screen before any prompts for input. > > HTH > Dennis Cote > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] querying hierarchy
On Sun, Jan 29, 2006 at 08:30:57AM +0100, Marten Feldtmann wrote: > Databases like DB2, MaxDB or Oracle offer you these recursive > SQL statements. The main winning is, that you execute ONE query > and retrieves all rows belonging to a sub tree, which is MUCH > faster, that to execute multiple queries ... Is this true in the sqlite context? For client/server systems, getting the server to execute the recursive queries is surely a win, because you cut out all the back-and-forth communication latencies when specifying each new query. In sqlite, though, you can run a second query from your own code just as efficiently as sqlite can from its code... -- Nathaniel -- When the flush of a new-born sun fell first on Eden's green and gold, Our father Adam sat under the Tree and scratched with a stick in the mould; And the first rude sketch that the world had seen was joy to his mighty heart, Till the Devil whispered behind the leaves, "It's pretty, but is it Art?" -- The Conundrum of the Workshops, Rudyard Kipling
Re: [sqlite] Transforming BLOB results in SELECT using sqlite?
Shane Baker <[EMAIL PROTECTED]> wrote: > Are there any mechanisms that will display the [BLOB] > data in a human readable format? Assuming the table is: CREATE TABLE t1(x BLOB) You can do this: SELECT quote(x) FROM t1; > For that matter, can I view an INTEGER > column as hex in the output window? Not without modifying the shell to implement some kind of custom function to do so. At least no way that I can think of right off hand. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Transforming BLOB results in SELECT using sqlite?
I don't know if what I want to do is possible or not. I have a schema that has a couple of BLOB columns where the data is either: 1. Really random, or 2. May contain leading 0's Either way, the results aren't really readable with SELECT using the sqlite command line tool. Are there any mechanisms that will display the data in a human readable format? For that matter, can I view an INTEGER column as hex in the output window? I could transform the data myself on the way into and out of the database, but that would generally be wasteful since it only helps when reading the data directly from the DB and it would make the program more complex because I'd need to switch to either keeping track of the transformed data myself or switching to SQLITE_TRANSIENT when I bind the BLOB for insertion. I hope I have asked this question clearly enough. Thanks for any suggestions, Shane
Re: [sqlite] Compile SQLite
Merijn sqlite.dll and the console utility were the first two programs that I ever compiled on VS2005. VS is a bit of an "experience" compared to what I'm used to - Delphi - so I just followed the wiki in very dumb step-by-step manner. You don't need to download any files, as the sqlite.def file is already included with the source. The DLL and console apps work, but to be honest I only built them for the purposes of trying to find a problem I had - my preference is to use the official releases. Regards, Carl.
Re: [sqlite] access to SQLite with OO
Hi, a look through openoffice howto docs reveals: http://documentation.openoffice.org/HOW_TO/data_source/SQLite.pdf Regards, Eugene Wee Jay Sprenkle wrote: Yes, I saw a driver in the programmers documentation section On 1/28/06, Tomàs Eroles i Forner <[EMAIL PROTECTED]> wrote: Hello all! Is it possible to access and work with SQLite databases with Open Office Base?
[sqlite] Re: Help with IF NOT EXiSTS
nbiggs wrote: I get the following error if I try to use the IF NOT EXISTS function. If I take out the "IF NOT EXISTS", the statement creates the index. The same happens when I try using IF NOT EXISTS in a CREATE table statement. CREATE UNIQUE INDEX IF NOT EXISTS idx_table ON table (field1, field2); SQL error: near "NOT": syntax error I've never seen such syntax. SQLite does not support it. What is it supposed to mean anyway? If you want to create an index unless one exists already, just try to create it and check for failure. Igor Tandetnik
[sqlite] Help with IF NOT EXiSTS
I get the following error if I try to use the IF NOT EXISTS function. If I take out the "IF NOT EXISTS", the statement creates the index. The same happens when I try using IF NOT EXISTS in a CREATE table statement. CREATE UNIQUE INDEX IF NOT EXISTS idx_table ON table (field1, field2); SQL error: near "NOT": syntax error Any ideas? Thanks for your help. Nathan Biggs
Re: [sqlite] access to SQLite with OO
Yes, I saw a driver in the programmers documentation section On 1/28/06, Tomàs Eroles i Forner <[EMAIL PROTECTED]> wrote: > Hello all! > Is it possible to access and work with SQLite databases with Open Office > Base?
Re: [sqlite] READ UNCOMMITTED isolation?
[EMAIL PROTECTED] wrote: "Dan Petitt" <[EMAIL PROTECTED]> wrote: I think (looking at the source) that it's a pragma, but I don't know when you set it, once when DB is opened, on each write or on each read. You are the third to ask (including me), maybe Richard or someone else can through some light on it for us. READ UNCOMMITTED only works if you enable the shared cache feature and have two or more database connections sharing the same page and schema cache (meaning that they are both running in the same thread). Documentation is forthcoming. I don't understand the point of this feature. In fact I don't know why I would want more than one database connection per thread at all. When would I need that? -- Gerhard
Re: [sqlite] ATTACH DATABASE how-to
[EMAIL PROTECTED] wrote: Hi all, The situation is: i have two DBs - one in memory and one in the filesystem. I need to fill some tables in the second DB from the fist DB. So, how can I ATTACH DATABASE from memory? (unfortunately ATTACH DATABASE ":memory:" AS mem; fails ...) It works for me. How do you know it fails? Do you get an error message? If so, what??? Do you have one process creating the memory DB and another trying to access it? More details are needed. Gerry
Re: [sqlite] ATTACH DATABASE how-to
[EMAIL PROTECTED] writes: > The situation is: > i have two DBs - one in memory and one in the filesystem. I > need to fill some tables in the second DB from the fist DB. > So, how can I ATTACH DATABASE from memory? > (unfortunately ATTACH DATABASE ":memory:" AS mem; fails ...) You need to ATTACH the disk-resident database to the memory one, rather than vice versa as you're trying. Since you already have the database handle for the memory-resident database, issue an "ATTACH 'diskdatabase.db' AS disk_db;" command on the memory-resident database handle. Derrell
Re: [sqlite] Compile SQLite
Merijn Vandenabeele wrote: Hi, I'd like to compile SQLite v3.3.2 using Visual Studio .NET 2003. I found a reference to a solution in the wike but I couldn't find a link to download the file. How should I compile SQLite from source? Best regards, Merijn Vandenabeele http://www.sqlite.org/sqlite-source-3_3_2.zip -- Craig Morrison =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= http://pse.2cah.com Controlling pseudoephedrine purchases. http://www.mtsprofessional.com/ A Win32 email server that works for You.
[sqlite] ATTACH DATABASE how-to
Hi all, The situation is: i have two DBs - one in memory and one in the filesystem. I need to fill some tables in the second DB from the fist DB. So, how can I ATTACH DATABASE from memory? (unfortunately ATTACH DATABASE ":memory:" AS mem; fails ...) Regards E. - Slon.bg ™ Симпатичният магазин за книги, DVD, игри и музика http://www.slon.bg
Re: [sqlite] Database Locked Error
Ritesh Kapoor <[EMAIL PROTECTED]> wrote: > Yes. > My machine has NFS and the machines I log onto also have NFS. But if > this is the problem then why dosen't it appear on my machine when I run > the app. Perhaps you are using a local filesystem when you run on your machine. Or perhaps NFS is configured properly on your machine but not on the other machines. > Is there a workaround for this? without having to change the file system > from NFS. > Yes. Configure your NFS so that file locking works correctly. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Compile SQLite
Hi, I'd like to compile SQLite v3.3.2 using Visual Studio .NET 2003. I found a reference to a solution in the wike but I couldn't find a link to download the file. How should I compile SQLite from source? Best regards, Merijn Vandenabeele
Re: [sqlite] Database Locked Error
Yes. My machine has NFS and the machines I log onto also have NFS. But if this is the problem then why dosen't it appear on my machine when I run the app. Is there a workaround for this? without having to change the file system from NFS. Thanks, Ritesh On Mon, 2006-01-30 at 18:12, [EMAIL PROTECTED] wrote: > Ritesh Kapoor <[EMAIL PROTECTED]> wrote: > > > > Can anyone suggest what the problem is with sqlite when running apps on > > different machines. > > This happens sometimes when NFS us misconfigured so that it > does not support fcntl() file locks. The lock requests always > fail, hence SQLite always returns "database is locked". > > Are you using NFS? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> >
Re: [sqlite] READ UNCOMMITTED isolation?
"Dan Petitt" <[EMAIL PROTECTED]> wrote: > I think (looking at the source) that it's a pragma, but I don't know when > you set it, once when DB is opened, on each write or on each read. > > You are the third to ask (including me), maybe Richard or someone else can > through some light on it for us. > READ UNCOMMITTED only works if you enable the shared cache feature and have two or more database connections sharing the same page and schema cache (meaning that they are both running in the same thread). Documentation is forthcoming. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Database Locked Error
Ritesh Kapoor <[EMAIL PROTECTED]> wrote: > > Can anyone suggest what the problem is with sqlite when running apps on > different machines. This happens sometimes when NFS us misconfigured so that it does not support fcntl() file locks. The lock requests always fail, hence SQLite always returns "database is locked". Are you using NFS? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Bug in insert into ... select * ....
"shum [Ming Yik]" <[EMAIL PROTECTED]> wrote:
>
> create table test01( FLD01 char(3) default '');
> create table test02( FLD01 char(3) default '');
>
> insert into test01(FLD01) values('001');
> insert into test01(FLD01) values('002');
>
> insert into test02 select * from test01 ;
>
>
>
> then you will find that in test01:
> 001
> 002
>
> then you will find that in test02:
> 001
> 002
> 001
> 002
>
That's not what I get:
create table test01( FLD01 char(3) default '');
create table test02( FLD01 char(3) default '');
insert into test01(FLD01) values('001');
insert into test01(FLD01) values('002');
insert into test02 select * from test01 ;
select 111, * from test01;
111|001
111|002
select 222, * from test02;
222|001
222|002
I'm not sure what it is you are doing wrong. Are you
typing this in at the command-line shell? Or are you
using some kind of language binding?
--
D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] READ UNCOMMITTED isolation?
I think (looking at the source) that it's a pragma, but I don't know when you set it, once when DB is opened, on each write or on each read. You are the third to ask (including me), maybe Richard or someone else can through some light on it for us. Dan Petitt DigiGuide TV Guide First Floor Office Suite 17 The Strand Exmouth Devon. EX8 1AF Tel / Fax: 01395 272555 -Original Message- From: Jack Pan [mailto:[EMAIL PROTECTED] On Behalf Of Cecilia Chen Sent: 29 January 2006 15:33 To: sqlite-users@sqlite.org Subject: [sqlite] READ UNCOMMITTED isolation? Does anyone know how to use the new READ UNCOMMITTED isolation? It would be great to have this isolation level when one thread reads and another writes. My program doesn't worry too much about read consistency. Thanks, Jack Pan
