Re: [sqlite] Performance of two queries, why such big difference ?
--- kamil <[EMAIL PROTECTED]> wrote: > I have a table with ~1 milion records. > > CREATE TABLE > ( > time INTEGER NOT NULL, > channel INTEGER NOT NULL, > path INTEGER NOT NULL, > file INTEGER NOT NULL, > flags INTEGER NOT NULL, > PRIMARY KEY(channel,time,path,file) > ) > > CREATE INDEX id_channel_time ON files(channel,time) > > > And I have a two queries: > > SELECT time,channel,path,file,flags > FROM files > WHERE channel = ? AND time >= ? > ORDER BY time ASC " > LIMIT ? > > SELECT time,channel,path,file,flags > FROM files > WHERE channel IN (-2,?) AND time >= ? > ORDER BY time ASC " > LIMIT ? > > It takes <1ms to return 16 rows using the first query, but over 200ms when > using the second one. > What is wrong ? Is there a way to speed up the second query ? I think you'll have to do this: SELECT time,channel,path,file,flags FROM files WHERE channel = -2 AND time >= ?1 UNION SELECT time,channel,path,file,flags FROM files WHERE channel = ?2 AND time >= ?2 ORDER BY time ASC " LIMIT ?3 Investigate EXPLAIN and EXPLAIN QUERY PLAN to gain more insight. Dan. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] Help In SQLIte
> >
> > Hello SQLite Users
> > I am using SQlite 2.8.17 version
> > I am trying to compile this piece of code on linux system when i type the
> > command "g++
> test.cpp"
> >
> > I get the following error which is at the bottom of this mail.
> > If anybody could which is the library file I need to include I will be
> > greatful to him.
> > Thanks
> > Ashish
> >
> >
> > #include
> > #include "sqlite.h"
> > #include
> >
> > using namespace std;
> >
> > typedef int (*sqlite_callback)(void*, int, char**, char**);
> >
> > int main() {
> > const char* DB;
> > int mode=0;
> > // sqlite_stmt** statment;
> > char** errmsg;
> > sqlite* dbHandle;
> > // int length_stat;
> > // const char* p = "create table tbl1(one varchar(10), two
> > smallint)";
> > const void** pzTail;
> > // length_stat = strlen(p);
> > dbHandle = sqlite_open(DB, mode, errmsg);
> > // sqlite_prepare(*DB, "create table tbl1(one varchar(10), two
> > smallint)", length_stat,
> > statement, pztail);
> > sqlite_exec(dbHandle, "create table tbl1(one varchar(10), two
> > smallint)", 0, 0, errmsg);
> >
> > char* name = "ashish";
> > char* numc;
> > char* fin_name;
> > int num;
> > for(int i=0;i<10;i++)
> > for(int j=0;j<1000;j++) {
> > num = i*1000+j;
> > *numc = (char)num;
> > fin_name = strcat(name, numc);
> > sqlite_exec(dbHandle, "insert into table tbl1(num, fin_name)", 0,
> > 0, errmsg);
> > }
> > // printf("hi");
> > return 0;
> > }
> >
> >
> >
> >
> > Error Message
> > --
> > g++ test.cpp
> > /tmp/ccxM7oLI.o(.text+0x24): In function `main':
> > : undefined reference to `sqlite_open'
> > /tmp/ccxM7oLI.o(.text+0x41): In function `main':
> > : undefined reference to `sqlite_exec'
> > /tmp/ccxM7oLI.o(.text+0xc4): In function `main':
> > : undefined reference to `sqlite_exec'
> > collect2: ld returned 1 exit status
> > ---
> >
> > Happy Coding!!!
> >
> > Warm regards
> > Ashish Singh
> > [EMAIL PROTECTED], [EMAIL PROTECTED]
> > Graduate Student (MS-Software Engineering)
> > University of Southern California www.cs.usc.edu
> > Los angeles California USA
> > 323 404 8621(M)
> > 213-746-4142-(R)
> >
> >
> >
> >
> >
> >
> >
> >
>
>
> Happy Coding!!!
>
> Warm regards
> Ashish Singh
> [EMAIL PROTECTED], [EMAIL PROTECTED]
> Graduate Student (MS-Software Engineering)
> University of Southern California www.cs.usc.edu
> Los angeles California USA
> 323 404 8621(M)
> 213-746-4142-(R)
>
>
>
>
>
>
>
>
Happy Coding!!!
Warm regards
Ashish Singh
[EMAIL PROTECTED], [EMAIL PROTECTED]
Graduate Student (MS-Software Engineering)
University of Southern California www.cs.usc.edu
Los angeles California USA
323 404 8621(M)
213-746-4142-(R)
Re: [sqlite] Multiple Users
[EMAIL PROTECTED] wrote: Hi to all ... any questions... ;-) How many Users can operate with one SQLite-DB at the same time? Anne, Look at the following documentation, and if you have further questions, do not hesitate to ask: http://sqlite.org/lockingv3.html Gerry
Re: [sqlite] Question on reading UTF-16 files using sqlite3.exe
Thanks. I will try again. maybe it was the BOM that dropped the bomb. I was getting illegal char error right before the C in my Create Table statement. Also, perusing the code I found what could be errors or actually more like non-compliance with unicode 4 in the trailing bytes table ( I forgot the name ). I started to put together my own Unicode functions, but I will try again. Thanks, Ché On 7/3/06, Jens Miltner <[EMAIL PROTECTED]> wrote: Am 03.07.2006 um 03:49 schrieb Ché Gonzalez: >> From my browsing through the documentation, I have observed the >> ability to > have UTF-16 column names in sqlite3. I would like to use the > command-line: > sqlite3 ae.db ".read filename.sql" where filename.sql is a UTF-16 > encoded > file. Is there any way to do this without having to code > everything in > C++? I would really appreciate the help. If you convert your .sql file to UTF-8 encoding, sqlite3 should be happy to process all unicode contents... HTH,
[sqlite] Performance query with many joins
Hi, I have been using SQLite for a while now and I have been getting very good performance. However, recently I have been stumped by the poor performance of a particular query. I wonder if anyone out there can shed any light on the matter for me. I have a database with a number of tables (N) within that simply collate data under an ID. Create Table Table1( TheID INTEGER, Column1 varchar(255), Column2 varchar(255), Column3 varchar(255), ... ColumnX varchar(255)) Create Index Table1_Index on Table1(TheID) ... Create Table TableN ( TheID INTEGER, Column1 varchar(255), Column2 varchar(255), Column3 varchar(255), ... ColumnY varchar(255)) Create Index TableN_Index on TableN(TheID) All the tables vary by the number of columns they have (some tables may have 2 columns, others 200), but essentially they are just resting places for the data that is linked on TheID. At some point the user will ask for some data to be returned. The query asked will be similar to this: SELECT T1.TheID, T1.Column1, T1.Column6, T2.Column2, T2.Column3, T2.Column6, T3.Column8, T4.Column2 FROM Table1 as T1 inner join Table2 as T2 on T1.TheID = T2.TheID inner join Table3 as T3 on T1.TheID = T3.TheID inner join Table4 as T4 on T1.TheID = T4.TheID where T1.TheID >= 11 and T1.TheID <= 15 order by T1.TheID asc The main points being: * All tables are joined on TheID - never anything else * The where clause restricts the answers back based on TheID * The data tends to be (largely) clustered on TheID order. IE each table will tend to have been inserted (largely) in TheID order. If it is not then it is in order within chucks (IE 1-50k, 100-150k, 50-100k) * Each table will generally have exactly the same amount of records in. This all works very reasonably on even quite large datasets (5 seconds for 2m). The problem occurs when I introduce a new slightly different table. The purpose of this table is to sort the data in a slightly different order. Create Table SortOrder ( SortID INTEGER PRIMARY KEY, TheID INTEGER) Create Index SortOrder_Index on SortOrder(TheID) This table gets filled in a random order with the SortID being generated on load. The Select query then changes slightly to enforce this order: SELECT T1.TheID, T1.Column1, T1.Column6, T2.Column2, T2.Column3, T2.Column6, T3.Column8, T4.Column2, S.SortID FROM Table1 as T1 inner join Table2 as T2 on T1.TheID = T2.TheID inner join Table3 as T3 on T1.TheID = T3.TheID inner join Table4 as T4 on T1.TheID = T4.TheID inner join SortOrder as S on T1.TheId = S.TheID where S.SortID >= 11 and S.SortID <= 15 order by S.SortID asc This query performs very badly. Below I have isolated two very similar queries (they only differ on the where clause and order by) and detailed the timings and explain results. My main questions are: * Why does the sorted query take so much longer than the very similar unsorted one? * Is there anything I can do to speed up the sorted query? * Can the difference be put down entirely to the extra disk cache misses? Test Details - Query1 (Unsorted) SELECT T1.TheID, T1.Column1, T1.Column6, T2.Column2, T2.Column3, T2.Column6, T3.Column8, T4.Column2, S.SortID FROM Table1 as T1 inner join Table2 as T2 on T1.TheID = T2.TheID inner join Table3 as T3 on T1.TheID = T3.TheID inner join Table4 as T4 on T1.TheID = T4.TheID inner join SortOrder as S on T1.TheId = S.TheID where T1.TheID >= 11 and T1.TheID <= 15 order by T1.TheID asc Timings: 3.2secs for 1.7m Records (average of 3 attempts once cached) Query2 (Sorted) SELECT T1.TheID, T1.Column1, T1.Column6, T2.Column2, T2.Column3, T2.Column6, T3.Column8, T4.Column2, S.SortID FROM Table1 as T1 inner join Table2 as T2 on T1.TheID = T2.TheID inner join Table3 as T3 on T1.TheID = T3.TheID inner join Table4 as T4 on T1.TheID = T4.TheID inner join SortOrder as S on T1.TheId = S.TheID where S.SortID >= 11 and S.SortID <= 15 order by S.SortID asc Timings: 174 secs for 1.7m Records (average of 3 attempts once cached) Explain Results (format in fixed format to view) Unsorted Query Sorted Query addopcodep1p2 p3 addopcodep1p2 p3 0Noop 0 0 0Noop 0 0 1Goto 0115 1Goto 0106 2Integer0 0 2Integer0 0 3OpenRead 1 2 3OpenRead 4 817448 4Set
[sqlite] Fwd: sqlite3_busy_timeout() on NetBSD
Hello List,
Got this email from this a developer of XMMS2 Sounds a bit scary,
anyone have seen this before?
-- Tobias
Begin forwarded message:
From: Alexander Botero-Lowry <[EMAIL PROTECTED]>
Date: måndag 3 jul 2006 16.13.29 GMT-04:00
To: [EMAIL PROTECTED]
Subject: Fw: sqlite3_busy_timeout() on NetBSD
Begin forwarded message:
Date: Sun, 2 Jul 2006 15:34:40 -0500
From: Alexander Botero-Lowry <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Subject: sqlite3_busy_timeout() on NetBSD
Hi,
I'm an xmms2 developer who does most of the work of getting xmms2
working on the various BSDs. I've currently run into a strange problem
with sqlite3_busy_timeout on NetBSD. It seems that it doesn't timeout
at all. Though sqlite3_busy_timeout() is set to 6, when:
sqlite3_exec (sql, "PRAGMA user_version",
xmms_sqlite_version_cb, &version, NULL)
is called, we immediately get back SQLITE_BUSY instead of having it
wait for the lock to end. I was able to very very hackishly work
around
this problem with:
if (sqlite3_exec (sql, "PRAGMA user_version",
xmms_sqlite_version_cb, &version, NULL)
== SQLITE_ BUSY) {
xmms_log_debug("busy...");
sleep(1);
sqlite3_exec(sql, "PRAGMA
user_version",
xmms_sqlite_version_cb, &version,
NULL);
}
Obviously this isn't a real solution, but it does solve the problem...
Is there something wrong with sqlite3_bus_timeout on NetBSD?
One can find the more complete source of out sqlite wrapper at:
http://git.xmms.se/?p=xmms2-
devel.git;a=blob;h=d0ee1489ad19aba2a4b72e569effd143417bcdda;hb=6732ab9
98047e684a99e558c99edb62466511df8;f=src/xmms/sqlite.c
Thanks in advance, Alex
Please CC, off list.
!DSPAM:44a97a9f190355315134984!
Re: [sqlite] Open SQLite database from VB
Thanks to Robert and Gregory! The ADO.NET 2.0 wrapper has me up and running already!
[sqlite] Multiple Users
Hi to all ... any questions... ;-) How many Users can operate with one SQLite-DB at the same time? That means (for example) max 5 users can work with a MDB (MS Access) connected via OLEDB. If more then 5 Users (writer) connect, the MDB getting often instable or corrupt. Is the a recommended limit to SQLite? In order to this, the next question ;-) Is a physcial Locking to the DB allways necessary, if more the one User (writer) connect to a DB and a Table? That means, is it insufficient, if I handle only a logical Locking in the Application instead of physcial Locking? In my App it is necessary, to lock a group of appended Records from multiple tables, if a user will edit a specific Record in one of any table. The User opens (for example) a Record from a staff member, to edit personal data. At this time all appended Records in other tables of this staff member must be also locked. I perform that with a logical locking in combination with a DB-Connect-Parameter "Optimistic Locking". Pessimistic Locking from the client program means that holding the lock for the entire duration of the view/edit/update of the record. The opposite of this is called "optimistic locking", in which hold a lock on the record for only as long as it takes for the database engine to physically update the row. To avoid a conflict to the personal data I do my logical locking. A physically conflict avoid the DB. How many Users can be reader/writer to a DB at the same time, if all User controlled by the logical Locking-System? I have searched in Mail-Archiv and in Docu, but I dont found any Infos about concurrent Users. Many thanks and best greetings from Germany Anne -- "Feel free" – 10 GB Mailbox, 100 FreeSMS/Monat ... Jetzt GMX TopMail testen: http://www.gmx.net/de/go/topmail
Re: [sqlite] endian-specific code in pure c source release?
On 3-Jul-06, at 1:34 PM, Pat Wibbeler wrote: This isn't quite what I'm looking for. I understand that resulting binaries are endian dependent, and the database file itself is endian portable after version 3. What I'm really asking (though not as clearly as I had hoped ;-)) is whether or not the sqlite source code is endian dependent. No. Configure doesn't modify the sources. Importing the .c files into xcode should "just work". You may need to set -DNDEBUG=1 -DSQLITE_PTR_SZ=4 though (not sure how you do that in Xcode). Matt (who has basically done the same for the perl DBD::SQLite module, where it works just fine). __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
RE: [sqlite] endian-specific code in pure c source release?
This isn't quite what I'm looking for. I understand that resulting binaries are endian dependent, and the database file itself is endian portable after version 3. What I'm really asking (though not as clearly as I had hoped ;-)) is whether or not the sqlite source code is endian dependent. If it is, I assume that configure/make work some "magic" to generate sources for the target endian platform. If the source is endian independent, I expect that I could use the pure c source provided for windows users and let xcode target mactel, ppc or universal during compilation. A quick search through the sqlite source release led me to believe that the source may be coded around endian issues. I guess what I'm looking for is affirmation or denial of my cursory reading. If the source is endian dependent, how does sqlite configure and/or make determine the endian nature of the platform on which it is building? What does it change? If I know these things, I suspect that I can build a project that either runs configure as part of the process, or does the same things that make/configure do to make endian-ness correct. If these aren't documented somewhere, I can reverse engineer configure/make, but I was hoping that someone here might have the answers. Thanks again! Pat -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, July 03, 2006 10:04 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] endian-specific code in pure c source release? Sqlite data is endian agnostic, but the executables are, like any executables, dependent upon the endian nature of the host processor. Just compile the Sqlite library for each platform and share the data. On platforms other than Windows use configure, otherwise use the prepared windows source. If you make any extensions to Sqlite, such as your own functions, they will be platform independent. By using the regular Sqlite source distribution you will be able to upgrade easily, and not have your application rev-locked. Pat Wibbeler wrote: > For a couple of reasons: > * I'd like to use xcode to build a universal binary. If I run > ./configure, I imagine that any endian specific code that is fixed using > configure will be set to whatever platform I run configure on (i386 or > ppc). > * I already have the packaged source for a windows build using visual > studio and I'd like to use the same sources if possible to avoid > confusion. > > Pat > > > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Monday, July 03, 2006 9:24 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] endian-specific code in pure c source release? > > Why not use the regular source and run configure? > > Pat Wibbeler wrote: > >>I'd like to build an xcode project for sqlite. One straightforward >>approach is to take the sqlite-source-3_3_6.zip "pure c" source > > release > >>and build the xcode project from that. >> >>Is there any endian specific code in that source release that might > > trip > >>me up on power pc processors? I ask this because I know that this >>release is "provided as a service to MS-Windows users who lack the > > build > >>support infrastructure of Unix." >> >>Thanks! >> >>Pat > >
Re: [sqlite] endian-specific code in pure c source release?
Sqlite data is endian agnostic, but the executables are, like any executables, dependent upon the endian nature of the host processor. Just compile the Sqlite library for each platform and share the data. On platforms other than Windows use configure, otherwise use the prepared windows source. If you make any extensions to Sqlite, such as your own functions, they will be platform independent. By using the regular Sqlite source distribution you will be able to upgrade easily, and not have your application rev-locked. Pat Wibbeler wrote: For a couple of reasons: * I'd like to use xcode to build a universal binary. If I run ./configure, I imagine that any endian specific code that is fixed using configure will be set to whatever platform I run configure on (i386 or ppc). * I already have the packaged source for a windows build using visual studio and I'd like to use the same sources if possible to avoid confusion. Pat -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, July 03, 2006 9:24 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] endian-specific code in pure c source release? Why not use the regular source and run configure? Pat Wibbeler wrote: I'd like to build an xcode project for sqlite. One straightforward approach is to take the sqlite-source-3_3_6.zip "pure c" source release and build the xcode project from that. Is there any endian specific code in that source release that might trip me up on power pc processors? I ask this because I know that this release is "provided as a service to MS-Windows users who lack the build support infrastructure of Unix." Thanks! Pat
RE: [sqlite] endian-specific code in pure c source release?
For a couple of reasons: * I'd like to use xcode to build a universal binary. If I run ./configure, I imagine that any endian specific code that is fixed using configure will be set to whatever platform I run configure on (i386 or ppc). * I already have the packaged source for a windows build using visual studio and I'd like to use the same sources if possible to avoid confusion. Pat -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, July 03, 2006 9:24 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] endian-specific code in pure c source release? Why not use the regular source and run configure? Pat Wibbeler wrote: > I'd like to build an xcode project for sqlite. One straightforward > approach is to take the sqlite-source-3_3_6.zip "pure c" source release > and build the xcode project from that. > > Is there any endian specific code in that source release that might trip > me up on power pc processors? I ask this because I know that this > release is "provided as a service to MS-Windows users who lack the build > support infrastructure of Unix." > > Thanks! > > Pat
RE: [sqlite] default value in hex
Are you performing computations with that number? For example, will you be performing addition, subtraction, or bitwise and/or? If not, you could just leave it as a string (and likely change the create to be "... text(32) default '0xFF'"). I'm not an expert on the topic, but sqlite uses "manifest typing" meaning that it doesn't enforce the type of a given column. This allows you to put whatever you want, wherever you want. SQLite will try to "coerce" the data into the type you've specified. So, when it sees xFF, it decides that you really want a string and makes it so. When it sees 0xFF it likely coerces that into the integer type specified in the create. When storing the number as an integer, it's stored as binary. The fact that you see it in base 10 when you select it is an artifact of whatever you are using to display the number. The sqlite command line utility displays integers in base 10 (for good reason, most of the time, you expect this!). I had a quick look at the sqlite built-in sql functions to see if there is a function that could convert an integer to a hex string (like TO_CHAR() in oracle, or STR() in MSSQL), but it looks like there is not. SQLite is intentinonally sparse in the included functions, so you can either write or find a custom sqlite function to do this. Alternatively, simply use whatever number->string conversion routines the language/platform you are using supplies (e.g. printf for c). Pat -Original Message- From: Wilfried Mestdagh [mailto:[EMAIL PROTECTED] Sent: Sunday, July 02, 2006 11:16 PM To: sqlite-users@sqlite.org Subject: [sqlite] default value in hex Hi, How to specify a hexadecimal value in the default by create of a table ? I try thinks like: create table Test ( [Name] char, [Color] integer default 0xFF ); But have syntax errors. When I leave out the 0 then the xFF seems to be stored as a string. it is just that 0xFF makes more sence that 16777215, specially if it is a color. -- View this message in context: http://www.nabble.com/default-value-in-hex-tf1882855.html#a5147011 Sent from the SQLite forum at Nabble.com.
Re: [sqlite] endian-specific code in pure c source release?
Why not use the regular source and run configure? Pat Wibbeler wrote: I'd like to build an xcode project for sqlite. One straightforward approach is to take the sqlite-source-3_3_6.zip "pure c" source release and build the xcode project from that. Is there any endian specific code in that source release that might trip me up on power pc processors? I ask this because I know that this release is "provided as a service to MS-Windows users who lack the build support infrastructure of Unix." Thanks! Pat
RE: [sqlite] Open SQLite database from VB
> -Original Message- > From: Gregory Letellier [mailto:[EMAIL PROTECTED] > Sent: Monday, July 03, 2006 8:54 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Open SQLite database from VB > > search finisar sqlite on google you will find a wrapper :o) > VB Express is a .NET 2.0 product, so you should be using the ADO.NET 2.0 wrapper at: http://sqlite.phxsoftware.com Robert
[sqlite] endian-specific code in pure c source release?
I'd like to build an xcode project for sqlite. One straightforward approach is to take the sqlite-source-3_3_6.zip "pure c" source release and build the xcode project from that. Is there any endian specific code in that source release that might trip me up on power pc processors? I ask this because I know that this release is "provided as a service to MS-Windows users who lack the build support infrastructure of Unix." Thanks! Pat
Re: [sqlite] Open SQLite database from VB
search finisar sqlite on google you will find a wrapper :o) cstrader232 a écrit : Hello, I'm using Visual Basic Express for the first time. I'd like to connect from VB to my sqlite database. I figured that the database would show up in the databases list as it does in Excel but it does not . I couldn't tell from the Wiki or from past threads what I should do. There seem to be a lot of .dll addins, but I don't really know what I need. How can I best do this? tia
[sqlite] Open SQLite database from VB
Hello, I'm using Visual Basic Express for the first time. I'd like to connect from VB to my sqlite database. I figured that the database would show up in the databases list as it does in Excel but it does not . I couldn't tell from the Wiki or from past threads what I should do. There seem to be a lot of .dll addins, but I don't really know what I need. How can I best do this? tia
Re: [sqlite] Problem with compiling under HP-UX
Have you checked config.log for details? Ah crap! Silly me. The flags for CFLAGS should have been "-O2 -mlp64" instead. I forgot the "m" and thus it rendered into a non-existant mode for gcc. Sorry about the confusion. -- Henrik
[sqlite] Re: Performance of two queries, why such big difference ?
kamil <[EMAIL PROTECTED]> wrote: I have a table with ~1 milion records. CREATE TABLE ( time INTEGER NOT NULL, channel INTEGER NOT NULL, path INTEGER NOT NULL, file INTEGER NOT NULL, flags INTEGER NOT NULL, PRIMARY KEY(channel,time,path,file) ) CREATE INDEX id_channel_time ON files(channel,time) This index is superfluous. Your primary key already generates an index that contains all the same information, and then some. And I have a two queries: SELECT time,channel,path,file,flags FROM files WHERE channel = ? AND time >= ? ORDER BY time ASC " LIMIT ? SELECT time,channel,path,file,flags FROM files WHERE channel IN (-2,?) AND time >= ? ORDER BY time ASC " LIMIT ? It takes <1ms to return 16 rows using the first query, but over 200ms when using the second one. What is wrong ? Is there a way to speed up the second query ? The first query can use index to satisfy ORDER BY clause, the second query cannot and has to sort on the fly. Try putting 'time' first and 'channel' second in your primary key clause - and drop that id_channel_time index. Igor Tandetnik
Re: [sqlite] Problem with compiling under HP-UX
Henrik Goldman uttered: Hi, I have a new HP-UX maching running latest official OS B.11.23 and has gcc 4.1.1. The problem is that when I try to configure I get an error: bash-3.00# ./configure CFLAGS="-O2 -lp64" --enable-threadsafe checking build system type... ia64-hp-hpux11.23 checking host system type... ia64-hp-hpux11.23 checking for gcc... /usr/local/bin/gcc checking for C compiler default output file name... configure: error: C compiler cannot create executables See `config.log' for more details. Have you checked config.log for details? bash-3.00# gcc -v Using built-in specs. Target: ia64-hp-hpux11.23 Configured with: ../gcc/configure Thread model: single gcc version 4.1.1 Does any of you have an idea how to proceed? Try compiling a simple "Hello World" application using the compiler. If the application won't run, then it's a compiler problem (I suspect this is the issue.) Thanks in advance. -- Henrik Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] Problem with compiling under HP-UX
Hi, I have a new HP-UX maching running latest official OS B.11.23 and has gcc 4.1.1. The problem is that when I try to configure I get an error: bash-3.00# ./configure CFLAGS="-O2 -lp64" --enable-threadsafe checking build system type... ia64-hp-hpux11.23 checking host system type... ia64-hp-hpux11.23 checking for gcc... /usr/local/bin/gcc checking for C compiler default output file name... configure: error: C compiler cannot create executables See `config.log' for more details. bash-3.00# gcc -v Using built-in specs. Target: ia64-hp-hpux11.23 Configured with: ../gcc/configure Thread model: single gcc version 4.1.1 Does any of you have an idea how to proceed? Thanks in advance. -- Henrik
RE: [sqlite] Performance of two queries, why such big difference ?
>It takes <1ms to return 16 rows using the first query, but over 200ms when using the second one. What is wrong ? Is there a way to speed up the second >query ? If you are using a newer version of SQLite, I suggest you run the two queries again prefixing the SQL statement with EXPLAIN QUERY PLAN Things to read: http://www.sqlite.org/cvstrac/wiki?p=QueryPlans http://www.sqlite.org/lang_explain.html The time it takes to query is related to how many records SQLite will return for your WHERE statement (in your case you have to ignore LIMIT because it has to pull *all* the records initially to do the ORDER BY statement). Personally, searching and sorting ~1 million records in a fifth of a second sounds quite quick to me. Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
[sqlite] Performance of two queries, why such big difference ?
I have a table with ~1 milion records. CREATE TABLE ( time INTEGER NOT NULL, channel INTEGER NOT NULL, path INTEGER NOT NULL, file INTEGER NOT NULL, flags INTEGER NOT NULL, PRIMARY KEY(channel,time,path,file) ) CREATE INDEX id_channel_time ON files(channel,time) And I have a two queries: SELECT time,channel,path,file,flags FROM files WHERE channel = ? AND time >= ? ORDER BY time ASC " LIMIT ? SELECT time,channel,path,file,flags FROM files WHERE channel IN (-2,?) AND time >= ? ORDER BY time ASC " LIMIT ? It takes <1ms to return 16 rows using the first query, but over 200ms when using the second one. What is wrong ? Is there a way to speed up the second query ? Thanks in advance, Kamil
Re: [sqlite] Question on reading UTF-16 files using sqlite3.exe
Am 03.07.2006 um 03:49 schrieb Ché Gonzalez: From my browsing through the documentation, I have observed the ability to have UTF-16 column names in sqlite3. I would like to use the command-line: sqlite3 ae.db ".read filename.sql" where filename.sql is a UTF-16 encoded file. Is there any way to do this without having to code everything in C++? I would really appreciate the help. If you convert your .sql file to UTF-8 encoding, sqlite3 should be happy to process all unicode contents... HTH,
