[sqlite] Multiple constraints per table?
This is the way I normally create my shopping database, but this leads to thousands of duplicates. CREATE TABLE shopping(item TEXT,units TEXT,quantity TEXT,category TEXT,shop TEXT,aisle TEXT,price TEXT,total TEXT,date TEXT,note TEXT,record INTEGER PRIMARY KEY AUTOINCREMENT) 'item' is the full retailer's description for this shopping item. If I make this unique, then i eliminate all duplicates and any further entries of this item, so that's no good. item TEXT CONSTRAINT item UNIQUE is it legal sql syntax to allow more than one constraint field in table creation? I need at least these four below to guarantee that duplicate items do make it into the database but not on the same day. item TEXT CONSTRAINT item UNIQUE units TEXT CONSTRAINT units UNIQUE shop TEXT CONSTRAINT shop UNIQUE date TEXT CONSTRAINT date UNIQUE So the below three records would be allowed as the unit weight is different and also there are two different dates and as everyone knows, peppers come in all shapes and sizes (grin) (This is okay) pepper, 120gm, coles, 02/02/2006 pepper, 50gm, coles, 02/02/2006 pepper, 50gm, coles, 04/11/2007 Or would the multiple constraints work globally (if legal syntax) Would it end up with only the single record below because the all constraints operated globally? pepper, 120gm, coles, 02/02/2006 -- View this message in context: http://www.nabble.com/Multiple-constraints-per-table--tp18209309p18209309.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Rigging up SQLite over LINQ (Entity Framework)
Just when I thought I was hitting the home stretch, I came across an aggregate query test that I'm not sure how to solve. LINQ generated the following query programmatically: -- Begin auto-generated query SELECT 1 AS [C1], [Project2].[ProductID] AS [ProductID], [Project2].[ProductName] AS [ProductName], [Project2].[QuantityPerUnit] AS [QuantityPerUnit], [Project2].[UnitPrice] AS [UnitPrice], [Project2].[UnitsInStock] AS [UnitsInStock], [Project2].[UnitsOnOrder] AS [UnitsOnOrder], [Project2].[ReorderLevel] AS [ReorderLevel], [Project2].[Discontinued] AS [Discontinued], [Project2].[CategoryID] AS [CategoryID], [Project2].[SupplierID] AS [SupplierID] FROM ( SELECT [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[SupplierID] AS [SupplierID], [Extent1].[CategoryID] AS [CategoryID], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[Discontinued] AS [Discontinued], (SELECT Count([Project1].[C1]) AS [A1] FROM ( SELECT 1 AS [C1] FROM[Order Details] AS [Extent2] LEFT OUTER JOIN [Orders] AS [Extent3] ON [Extent2].[OrderID] = [Extent3].[OrderID] LEFT OUTER JOIN [Customers] AS [Extent4] ON [Extent3].[CustomerID] LIKE [Extent4].[CustomerID] INNER JOIN [Suppliers] AS [Extent5] ON [Extent4].[Country] LIKE [Extent5].[Country] WHERE ([Extent1].[SupplierID] = [Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID]) ) AS [Project1]) AS [C1] FROM [Products] AS [Extent1] ) AS [Project2] WHERE [Project2].[C1] > 2 -- End autogenerated query The error SQLite throws is "no such column: Extent1.ProductID" and I believe it's in the inner WHERE clause: WHERE ([Extent1].[SupplierID] = [Extent5].[SupplierID]) AND ([Extent1].[ProductID] = [Extent2].[ProductID]) I can of course supply a schema, but I think it may be irrelevant and have more to do with the fact that it's got some inner subqueries referencing an outer object? Since the SQL is autogenerated, I have limited control over it. The actual LINQ query was: var query = from p in db.Products where p.Order_Details.Count(od => od.Orders.Customers.Country == p.Suppliers.Country) > 2 select p; Aside from this little issue involving aggregates, SQLite is working nicely with Microsoft's new Entity Framework. Robert Simpson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite assert failure in sqlite3FaultEndBenign()
> > > It is not a bug because sqlite3FaultEndBenign() is used during testing > only. And the tests in which this function are used only run a single > thread. > Recompile with -DSQLITE_OMIT_BUILTIN_TEST=1 to completely disable the EndBenign() function and its friends. Or omit the -DSQLITE_DEBUG=1 that you are currently using and the assertion will not fail. Either way, it should start working for you. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite assert failure in sqlite3FaultEndBenign()
On Jun 30, 2008, at 5:41 PM, Andrew de los Reyes wrote: > > I added some logging to investigate how this happens, and found > that sqlite3FaultBeginBenign() and sqlite3FaultEndBenign() are being > called > with -1 as the only argument by many threads simultaneously. > > I added code to log entrances and exits to these functions with a > negative > argument is passed, and to occasionally sleep() in > sqlite3FaultEndBenign() > and to dump the last 20 log messages to console occasionally. I > found this > contiguous sequence of events: > > — thread id 12803 enters sqlite3FaultEndBenign() > — thread id 12803 exits sqlite3FaultEndBenign() > — thread id 13059 exits sqlite3FaultEndBenign() > > This is a bug, because one thread (id 13059) is > inside sqlite3FaultEndBenign() while another thread (id 12803) calls > the > same function. Inside sqlite3FaultEndBenign(), a static variable, > aFault, is > accessed (operator--, which i believe is read,modify,write) without > the > protection of a mutex. It is not a bug because sqlite3FaultEndBenign() is used during testing only. And the tests in which this function are used only run a single thread. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting where AUTOINCREMENT starts?
Shawn Wilsher <[EMAIL PROTECTED]> wrote: > I was wondering if we could set the value that an AUTOINCREMENT starts > at for temporary tables. Right now we are looking at having to manage > it ourselves, but if we could use sqlite to handle it, as long as it > starts at the right value, that would be ideal. http://sqlite.org/autoinc.html Read about SQLITE_SEQUENCE table. Simply update the starting count there before inserting any rows. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting where AUTOINCREMENT starts?
Looks like you can insert and delete a row to set whatever you want as the
starting number:
sqlite> create table t1 (oid integer primary key autoincrement, a);
sqlite> insert into t1 values (100, 'foo');
sqlite> delete from t1;
sqlite> insert into t1 (a) values ('bar');
sqlite> select * from t1;
101|bar
-Clark
- Original Message
From: Shawn Wilsher <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database
Sent: Monday, June 30, 2008 2:55:34 PM
Subject: [sqlite] Setting where AUTOINCREMENT starts?
Hey all,
I was wondering if we could set the value that an AUTOINCREMENT starts
at for temporary tables. Right now we are looking at having to manage
it ourselves, but if we could use sqlite to handle it, as long as it
starts at the right value, that would be ideal.
Cheers,
Shawn
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA temp_store confusion
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 D. Richard Hipp wrote: > http://www.sqlite.org/draft/doc/35to36.html Where is the appropriate place to discuss 3.6 issues? For example in my case I have very strong feelings about error codes vs return values in the VFS api. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIaWhcmOOfHg372QQRAtZ5AKCo3ID3zgnVc6sQSB+7GAQdazjLmgCgyqLl kk4qSeGps4GLqRBPtN1MwA4= =mezu -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA temp_store confusion
On Mon, Jun 30, 2008 at 6:41 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > The default TEMP_STORE is 1, not 0. The default "PRAGMA temp_store" > is 0. TEMP_STORE=1 means that temporary storage defaults to a file > but can be overridden by the temp_store pragma. Alright, cool. > Temporary storage is never fsync-ed. For that matter, temporary files > are always delete-on-close. So in an OS with a good disk cache, > little or no real disk I/O ever actually occurs on temp files. > Instead, all the data just gets moved in and out of cache blocks in > the kernel. This normally works better than storing the temp data in > memory since after the temp data is deleted, the disk cache block can > be reused by other processes. But writes to the disk can still hurt us with ext3 (and similar file systems) once we call fsync on anything else. We are trying to minimize the number of writes to alleviate this problem. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA temp_store confusion
On Jun 30, 2008, at 4:48 PM, Shawn Wilsher wrote: > Hey all, > > Over at mozilla we are looking into using more temporary tables, and > likely want them all to be in memory as opposed to files. I was > looking at http://sqlite.org/pragma.html#pragma_temp_store, and > noticed the table, which seems to imply that if TEMP_STORE is either > zero or not defined, temporary tables are always written to a file. > Is this correct, or is the documentation a bit misleading? > The default TEMP_STORE is 1, not 0. The default "PRAGMA temp_store" is 0. TEMP_STORE=1 means that temporary storage defaults to a file but can be overridden by the temp_store pragma. Temporary storage is never fsync-ed. For that matter, temporary files are always delete-on-close. So in an OS with a good disk cache, little or no real disk I/O ever actually occurs on temp files. Instead, all the data just gets moved in and out of cache blocks in the kernel. This normally works better than storing the temp data in memory since after the temp data is deleted, the disk cache block can be reused by other processes. A draft of the upcoming version 3.6.0 documentation is available online at http://www.sqlite.org/draft/doc/pragma.html#pragma_temp_store http://www.sqlite.org/draft/doc/compile.html#temp_store Note that the name of the compile-time parameter is adding an "SQLITE_" prefix for consistency. Additional information about proposed changes that might impact projects can be found at: http://www.sqlite.org/draft/doc/35to36.html D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index and ORDER BY
When I try a similar query (i.e, no type comparison), I get the same
results as you:
sqlite> SELECT eid,type FROM EVENTS WHERE eid<=3261976 ORDER BY eid DESC
LIMIT 1;
3261976|21
CPU Time: user 0.00 sys 0.027996
sqlite> EXPLAIN QUERY PLAN SELECT eid,type FROM EVENTS WHERE
eid<=3261976 ORDER BY eid DESC LIMIT 1;
0|0|TABLE EVENTS USING PRIMARY KEY ORDER BY
As soon as I add type criterion to the where clause, though, the
performance falls way off (the index is on events(type)):
sqlite> SELECT eid,type FROM EVENTS WHERE eid<=3261976 AND type=22 ORDER
BY eid DESC LIMIT 1;
3261891|22
CPU Time: user 3.069533 sys 0.485927
sqlite> EXPLAIN QUERY PLAN SELECT eid,type FROM EVENTS WHERE
eid<=3261976 AND type=22 ORDER BY eid DESC LIMIT 1;
0|0|TABLE EVENTS WITH INDEX ev4_idx ORDER BY
The fact that it seems to be able to do a descending sort very quickly
if it's only using the primary key, let me to try the following, which
turned out to be more convoluted and was much slower.
sqlite> SELECT e.eid,e.type FROM events e, (SELECT eid FROM events WHERE
eid<=3261976 ORDER BY eid DESC) l WHERE e.eid=l.eid AND type=22 LIMIT 1;
CPU Time: user 29.111574 sys 3.276502
sqlite> EXPLAIN QUERY PLAN SELECT e.eid,e.type FROM events e, (SELECT
eid FROM events WHERE eid<=3261976 ORDER BY eid DESC) l WHERE
e.eid=l.eid AND type=22 LIMIT 1;
0|0|TABLE events AS e WITH INDEX ev4_idx
1|1|TABLE events USING PRIMARY KEY
It seems to me that sqlite can very efficiently do a descending sort on
a primary key by itself, but not when it's used with an index. Does
that sound correct? Also, I'm using sqlite 3.5.6, not 3.5.9. Does that
make a difference?
Thanks,
Jeff
Alexey Pechnikov wrote:
> I try with this script on my laptop with 1 Gb RAM
>
> #!/usr/bin/tclsh
> package require sqlite3
>
> sqlite3 db index_order.db
> db eval {DROP TABLE IF EXISTS events}
> db eval {CREATE TABLE events (eid INTEGER PRIMARY KEY)}
> db eval {CREATE INDEX ev_desc_idx ON events(eid desc)}
> db transaction {
> for {set i 0} {$i<1} {incr i} {
> db eval {insert into events values ($i)}
> }
> }
> db close
>
> SQLite version 3.5.9 is used.
>
> I'm increasing ~ x10 rows count (and search for 32619760 row against 3261976
> in your query) and my database size is similar to your database:
> $ ls -lh .|grep db
> -rw-r--r-- 1 veter veter 2,4G Июн 29 12:08 index_order.db
>
> There are my results:
>
> sqlite> SELECT events.* FROM events WHERE eid<=32619760 ORDER BY eid DESC
> LIMIT 1;
> 32619760
> CPU Time: user 0.00 sys 0.00
>
> sqlite> explain query plan SELECT events.* FROM events WHERE eid<=32619760
> ORDER BY eid DESC LIMIT 1;
> 0|0|TABLE events USING PRIMARY KEY ORDER BY
>
> Index ev_desc_idx is not used.
>
> Check your SQLite version and try again with my script.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Setting where AUTOINCREMENT starts?
Hey all, I was wondering if we could set the value that an AUTOINCREMENT starts at for temporary tables. Right now we are looking at having to manage it ourselves, but if we could use sqlite to handle it, as long as it starts at the right value, that would be ideal. Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite assert failure in sqlite3FaultEndBenign()
Hi, I think I may have discovered a bug in SQLite3. It is a timing bug that
is easily reproduced with my (unfortunately proprietary) code. I'll try to
describe the situation as best as possible. This is using SQLite source
from http://www.sqlite.org/sqlite-source-3_5_9.zip running on Mac OS X 10.5
Leopard Intel. We compile and link in our own version of SQLite directly
with our executable. We compile with the following SQLite flags:
-DSQLITE_DEBUG=1 \
-DSQLITE_THREADSAFE=1 \
I have many threads each calling sqlite3_prepare() at the same time for the
same table in the same database. If the timing works out, I get a check
failure.
Here is a (sanitized) backtrace of the failing thread from gdb:
#0 0x9658b2ee in __semwait_signal_nocancel ()
#1 0x9658aed2 in nanosleep$NOCANCEL$UNIX2003 ()
#2 0x9657d1b7 in usleep$NOCANCEL$UNIX2003 ()
#3 0x9659e9bb in abort ()
#4 0x0035fa9d in __eprintf (string=0x3c ,
expression=0x3c , line=60, filename=0x3c
) at
/var/tmp/gcc/gcc-5465~16/src/gcc/libgcc2.c:1838
#5 0x0030a780 in sqlite3FaultEndBenign (id=0) at
.../path/to/sqlite/v3_5_9/files/fault.c:120 (see NOTE below)
#6 0x0033f7d5 in sqlite3VdbeExec (p=0xa4d938) at
.../path/to/sqlite/v3_5_9/files/vdbe.c:576
#7 0x0034ccaa in sqlite3Step (p=0xa4d938) at
.../path/to/sqlite/v3_5_9/files/vdbeapi.c:477
#8 0x0034cfe8 in sqlite3_step (pStmt=0xa4d938) at
.../path/to/sqlite/v3_5_9/files/vdbeapi.c:539
#9 0x00312aa0 in sqlite3_exec (db=0xa25188, zSql=0x1052c08 "CREATE TABLE
(id VARCHAR(128) primary key, "..., xCallback=0,
pArg=0x0, pzErrMsg=0xb0101a58) at
.../path/to/sqlite/v3_5_9/files/legacy.c:72
#10 0x0032a5d8 in sqlite3InitCallback (pInit=0xb0101b40, argc=3,
argv=0xa47d74, azColName=0xa47d68) at
.../path/to/sqlite/v3_5_9/files/prepare.c:81
#11 0x00312bf8 in sqlite3_exec (db=0xa25188, zSql=0xa44808 "SELECT name,
rootpage, sql FROM 'main'.sqlite_master", xCallback=0x32a375
, pArg=0xb0101b40, pzErrMsg=0x0) at
.../path/to/sqlite/v3_5_9/files/legacy.c:103
#12 0x0032adcb in sqlite3InitOne (db=0xa25188, iDb=0, pzErrMsg=0xb0102174)
at .../path/to/sqlite/v3_5_9/files/prepare.c:326
#13 0x0032afc1 in sqlite3Init (db=0xa25188, pzErrMsg=0xb0102174) at
.../path/to/sqlite/v3_5_9/files/prepare.c:394
#14 0x0032b106 in sqlite3ReadSchema (pParse=0xb010216c) at
.../path/to/sqlite/v3_5_9/files/prepare.c:430
#15 0x002f606f in sqlite3LocateTable (pParse=0xb010216c, isView=0,
zName=0xa382b8 "", zDbase=0x0) at
.../path/to/sqlite/v3_5_9/files/build.c:307
#16 0x0033102e in prepSelectStmt (pParse=0xb010216c, p=0xa428c8) at
.../path/to/sqlite/v3_5_9/files/select.c:1334
#17 0x0033465b in sqlite3SelectResolve (pParse=0xb010216c, p=0xa428c8,
pOuterNC=0x0) at .../path/to/sqlite/v3_5_9/files/select.c:2736
#18 0x00334fcc in sqlite3Select (pParse=0xb010216c, p=0xa428c8,
pDest=0xb0101fe0, pParent=0x0, parentTab=0, pParentAgg=0x0, aff=0x0) at
.../path/to/sqlite/v3_5_9/files/select.c:3079
#19 0x00323ef9 in yy_reduce (yypParser=0x1042c08, yyruleno=104) at
parse.y:364
#20 0x003267d5 in sqlite3Parser (yyp=0x1042c08, yymajor=1, yyminor={z =
0x10409c3 " ", dyn = 0, n = 1}, pParse=0xb010216c) at parse.c:3606
#21 0x00337d82 in sqlite3RunParser (pParse=0xb010216c, zSql=0x1040608
"SELECT "..., pzErrMsg=0xb0102168) at
.../path/to/sqlite/v3_5_9/files/tokenize.c:454
#22 0x0032b63e in sqlite3Prepare (db=0xa25188, zSql=0x103d60c "SELECT "..., nBytes=956, saveSqlFlag=0, ppStmt=0xb0102410,
pzTail=0xb010240c) at .../path/to/sqlite/v3_5_9/files/prepare.c:565
#23 0x0032bb68 in sqlite3LockAndPrepare (db=0xa25188, zSql=0x103d60c "SELECT
"..., nBytes=956, saveSqlFlag=0, ppStmt=0xb0102410,
pzTail=0xb010240c) at .../path/to/sqlite/v3_5_9/files/prepare.c:653
#24 0x0032bdca in sqlite3_prepare (db=0xa25188, zSql=0x103d60c "SELECT "..., nBytes=956, ppStmt=0xb0102410, pzTail=0xb010240c) at
.../path/to/sqlite/v3_5_9/files/prepare.c:709
#25.. (functions in our software)
NOTE: in frame #5 gdb says id is 0. it was passed in as -1, but the function
modified it to 0 before the backtrace was printed
I added some logging to investigate how this happens, and found
that sqlite3FaultBeginBenign() and sqlite3FaultEndBenign() are being called
with -1 as the only argument by many threads simultaneously.
I added code to log entrances and exits to these functions with a negative
argument is passed, and to occasionally sleep() in sqlite3FaultEndBenign()
and to dump the last 20 log messages to console occasionally. I found this
contiguous sequence of events:
— thread id 12803 enters sqlite3FaultEndBenign()
— thread id 12803 exits sqlite3FaultEndBenign()
— thread id 13059 exits sqlite3FaultEndBenign()
This is a bug, because one thread (id 13059) is
inside sqlite3FaultEndBenign() while another thread (id 12803) calls the
same function. Inside sqlite3FaultEndBenign(), a static variable, aFault, is
accessed (operator--, which i believe is read,modify,write) without the
protection of a mutex.
I would like to help those with more sqlite smarts than I fix this bug
quickly, so I would be happy to help out
[sqlite] PRAGMA temp_store confusion
Hey all, Over at mozilla we are looking into using more temporary tables, and likely want them all to be in memory as opposed to files. I was looking at http://sqlite.org/pragma.html#pragma_temp_store, and noticed the table, which seems to imply that if TEMP_STORE is either zero or not defined, temporary tables are always written to a file. Is this correct, or is the documentation a bit misleading? Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] unresolved external symbol
Hi list.
I'm currently making modifications to a project, and want to add SQLite3 to
it.
I'm using VS6, have the sqlite3.h and sqlite3.c in the same directory, and
this is the code:
#include "sqlite3.h"
bool TestSQLite()
{
sqlite3 *db;
int rc;
rc = sqlite3_open("test.sqlite",);
return true;
}
but when I build, get the following error
Configuration: mfc01 - Win32 Release
Compiling...
mfc01Dlg.cpp
Linking...
mfc01Dlg.obj : error LNK2001: unresolved external symbol _sqlite3_open
However, if I just compile, everything goes ok.
Any help?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
On Jun 30, 2008, at 2:37 PM, Andrea Connell wrote: > Any ideas? It's driving me crazy why SQLite is this much slower on > UNIX > boxes, while other applications maintain their speed. What filesystem are you using on the unix boxes? Are you *sure* you are not using NFS? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance on HP
I'm still struggling with this issue. I've tried compiling on Solaris
and get similar results. On Windows SQLite is twice as fast as our
in-house database system, but on HP and now Solaris it is over 6 times
slower than ours. There must be something I am missing here.
I've tried increasing the cache size and the page size but saw no real
difference. It was still taking over 30 seconds to find 30,000 rows
while our system does it in 4 seconds.
The databases have the same schema and the queries are all using the
proper index (I checked the explain output).
CREATE TABLE LEVEL1 ( ADDRESS_TYPE text, COUNTRY_ID text, DATA_SOURCE
text, DIR_SEARCH_AREA1 integer, ... PHONETIC_KEY text, ... , RECORDKEY
integer);
CREATE TABLE LEVEL2 ( ... PRIM_NBR_HIGH text, PRIM_NBR_LOW text, ...
RECORDKEY integer, PARENT_KEY integer );
CREATE TABLE LEVEL3 ( ... RECORDKEY integer, ... PARENT_KEY integer );
CREATE INDEX L1_INDEX ON LEVEL1 (COUNTRY_ID, DIR_SEARCH_AREA1,
ADDRESS_TYPE, PHONETIC_KEY);
CREATE INDEX L2_RANGE_INDEX ON LEVEL2 (PARENT_KEY, PRIM_NBR_HIGH,
PRIM_NBR_LOW);
CREATE INDEX L3_INDEX ON LEVEL3 (PARENT_KEY);
SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ? AND DIR_SEARCH_AREA1 = ? AND
ADDRESS_TYPE = ? AND PHONETIC_KEY LIKE ? ;
SELECT * FROM LEVEL2 WHERE PARENT_KEY = ? AND PRIM_NBR_LOW <= ? AND
PRIM_NBR_HIGH >= ?;
SELECT * FROM LEVEL3 WHERE PARENT_KEY = ? ;
Not sure if it will help at all, but once the database is created it
will literally never be updated or have another row inserted. Is there
any special optimization if we ONLY care about fast selects, and nothing
else?
Any ideas? It's driving me crazy why SQLite is this much slower on UNIX
boxes, while other applications maintain their speed.
Thanks from a desperate coder
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Andrea Connell
Sent: Monday, June 23, 2008 11:48 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Performance on HP
>> The program took 47 seconds to run, but the results only account for
>> .39 seconds
>
> Most likely all the time is being spent in IO related system calls
> - read(), write() and fsync().
>
> Dan.
Thanks for the idea Dan. How can I confirm this or try reducing the time
spent? I use the same method for reading my input file when I run both
SQLite and our in house system, and the other way only takes 4 seconds
total so I don't think it could be from that. Also, when I run our in
house system and use the profiler the time spent adds up to 100%
So this must be something within SQLite. I am using a transaction for my
queries. How can I find all of that missing time?
If anybody is interested, here is my main chunk of code. ReadLine()
parses the input file and fills the required variables. This method is
shared for both database systems (SQLite and ours). ReadSQLiteComponent
just calls one of the sqlite3_column functions based on the type of the
field, and a similar method is used for our system.
std::ifstream inf(argv[1]);
sqlite3 *db;
sqlite3_stmt *stmt;
sqlite3_stmt *stmt2;
sqlite3_stmt *stmt3;
int rc = sqlite3_open(argv[3], );
if( rc )
{
printf("Can't open database: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
return -1;
}
sqlite3_prepare(db,"BEGIN TRANSACTION;", 100, ,0);
sqlite3_step(stmt);
char * qry = "SELECT * FROM LEVEL1 WHERE COUNTRY_ID = ?
AND DIR_SEARCH_AREA1 = ? AND ADDRESS_TYPE = ? AND PHONETIC_KEY LIKE ?
;";
int p = sqlite3_prepare_v2(db,qry,1000,,0);
char * qry2 = "SELECT * FROM LEVEL2 WHERE PARENT_KEY = ?
AND PRIM_NBR_LOW <= ? AND PRIM_NBR_HIGH >= ?;";
int p2 = sqlite3_prepare_v2(db,qry2,1000,,0);
char* qry3 = "SELECT * FROM LEVEL3 WHERE PARENT_KEY = ?
;";
int p3 = sqlite3_prepare_v2(db,qry3,1000,,0);
if ( p || p2 || p3 )
{
printf("Can't create prepared statement: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
return -1;
}
while (ReadLine(inf))
{
sqlite3_bind_text(stmt, 1, cntryid, -1,
SQLITE_TRANSIENT);
sqlite3_bind_int(stmt, 2, searcharea);
sqlite3_bind_text(stmt, 3, addrtype, -1,
SQLITE_TRANSIENT);
int len = strlen(phnkey);
phnkey[len] = '%';
phnkey[len+1] = '\0';
sqlite3_bind_text(stmt, 4, phnkey, -1,
SQLITE_TRANSIENT);
while(sqlite3_step(stmt)==SQLITE_ROW)
{
for(int i=0; i
Re: [sqlite] How to connect the SQLite with DBDesigner4?
On Sun, 29 Jun 2008 23:32:47 -0700 (PDT), you wrote: > >I tried to export the the SQL command exported from DBDesigner4 is not going >to run on SQLite. But working on MYSQL. However I saw that the DBDesigner4 >can connect to SQLite server. > >I tried to download the source code from SQLite CVS. However I have no clue >how to build the SQLite server from the source code. I also tried the binary >on the SQLite webpage. I can't find the server option as well. So is there a >way that I can export my table from DBDesigner4 to SQLite. As some other people already mentioned, SQLite is not a server. If DBDesigner can handle ODBC, you could try one of the SQLite_ODBC drivers. But that will not solve everything, as DBDesigner probably doesn't know the SQLite syntax. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update static database with records from memory database.
That is fantastic Igor. Thankyou. All I need to do now is work out how to get an update query to use a select query to update the records from stats_memory to stats_static after doing the link. I think google may be able to help me with that. Thankyou for your time. =) Igor Tandetnik wrote: > > "Pejayuk" <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] >> Is it possible to have a single SQL query execute on the stats_static >> stats database and have it update from the records in stats_memory >> stats database?. > > http://www.sqlite.org/lang_attach.html > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Update-static-database-with-records-from-memory-database.-tp18187288p18197572.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to connect the SQLite with DBDesigner4?
There is no Sqlite server. It is embedded. winstonma wrote: > I tried to export the the SQL command exported from DBDesigner4 is not going > to run on SQLite. But working on MYSQL. However I saw that the DBDesigner4 > can connect to SQLite server. > > I tried to download the source code from SQLite CVS. However I have no clue > how to build the SQLite server from the source code. I also tried the binary > on the SQLite webpage. I can't find the server option as well. So is there a > way that I can export my table from DBDesigner4 to SQLite. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to connect the SQLite with DBDesigner4?
Dom Dom wrote: Hi, I already asked some weeks ago fabforce.net about the ability to connect sqlite and dbdesigner. The answer was as follows Bye Dominique Sorry, we never got the SQLite support finished. We now have the successor application out, MySQL Workbench, and we might add SQLite support there in the future. But for now there is no support, sorry. Best regards, Mike I think that's pretty clear then, and we can consider the issue closed... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to connect the SQLite with DBDesigner4?
Hi, I already asked some weeks ago fabforce.net about the ability to connect sqlite and dbdesigner. The answer was as follows Bye Dominique > Sorry, we never got the SQLite support finished. We now have the successor > application out, MySQL Workbench, and we might add SQLite support there in > the future. But for now there is no support, sorry. > > Best regards, > Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to connect the SQLite with DBDesigner4?
winstonma wrote: Thanks for the reply. However I downloaded the CVS and tried to compile from source, I can't find out anything related to server or client. Oh, I must have missed the part about client/server. You misunderstand the nature of SQLite. There is no server and no client. SQLite is an engine that gets embedded in your application and operates on databases which are contained in single files (although you can attach additional databases from other files into your main database.) For more information, please check http://www.sqlite.org/whentouse.html . I have never used the software you're trying - you might ask the authors for how to use it with SQLite. Mihai Limbasan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to connect the SQLite with DBDesigner4?
Thanks for the reply. However I downloaded the CVS and tried to compile from source, I can't find out anything related to server or client. Mihai Limbasan wrote: > > winstonma wrote: >> I tried to export the the SQL command exported from DBDesigner4 is not >> going >> to run on SQLite. But working on MYSQL. However I saw that the >> DBDesigner4 >> can connect to SQLite server. >> >> I tried to download the source code from SQLite CVS. However I have no >> clue >> how to build the SQLite server from the source code. I also tried the >> binary >> on the SQLite webpage. I can't find the server option as well. So is >> there a >> way that I can export my table from DBDesigner4 to SQLite. >> > Instructions on how to build SQLite from source in various environments > can be found at this Wiki page: > http://www.sqlite.org/cvstrac/wiki?p=HowToCompile > > Hope this helps. > > Mihai Limbasan > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/How-to-connect-the-SQLite-with-DBDesigner4--tp18189489p18191433.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to connect the SQLite with DBDesigner4?
winstonma wrote: I tried to export the the SQL command exported from DBDesigner4 is not going to run on SQLite. But working on MYSQL. However I saw that the DBDesigner4 can connect to SQLite server. I tried to download the source code from SQLite CVS. However I have no clue how to build the SQLite server from the source code. I also tried the binary on the SQLite webpage. I can't find the server option as well. So is there a way that I can export my table from DBDesigner4 to SQLite. Instructions on how to build SQLite from source in various environments can be found at this Wiki page: http://www.sqlite.org/cvstrac/wiki?p=HowToCompile Hope this helps. Mihai Limbasan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] getting ROWID
c.panel wrote: Hello, I execute a statement and want to have rowid(s) of each row like a : "select rowid,* from mytable;" how can I do this with C API ? thanks ! There's nothing special about selecting ROWIDs in the C API - you just use the query you posted above like you would use any other query without the rowid field. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to connect the SQLite with DBDesigner4?
I tried to export the the SQL command exported from DBDesigner4 is not going to run on SQLite. But working on MYSQL. However I saw that the DBDesigner4 can connect to SQLite server. I tried to download the source code from SQLite CVS. However I have no clue how to build the SQLite server from the source code. I also tried the binary on the SQLite webpage. I can't find the server option as well. So is there a way that I can export my table from DBDesigner4 to SQLite. -- View this message in context: http://www.nabble.com/How-to-connect-the-SQLite-with-DBDesigner4--tp18189489p18189489.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
