Re: [sqlite] How to avoid the increment the id in sqlite_sequence table if the constraint occurs?
Thanks for your anwser.
On 5/19/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
PY <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I have a problem about the ROWID.
>
> I want to create a table with unique value, not only [id] but also
[name],
> and the table named [foo]
> CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT
UNIQUE);
>
> I don't want to select the table for avoiding the duplicate NAME
problem.
> So, I use the "IGNORE" when I insert into the table.
>
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('y');
>
> After that I found a phenomenon, when I select the table, the result
listed
> below.
>
> SELECT * FORM foo;
>
> id name
> -- --
> 1 x
> 5 y
>
>
>
> My expected value of y is 2 not 5.
>
>
> The squence of the id is important to us. How to resolve this problem?
> Thanks for your grest help.
>
Try using INSERT OR FAIL instead of of INSERT OR IGNORE.
--
D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] New SQLitePlus version available
Hello! Is it an open source? Kind regards, Chethana. -Original Message- From: Brett Goodman [mailto:[EMAIL PROTECTED] Sent: Friday, May 19, 2006 9:48 AM To: sqlite-users@sqlite.org Subject: [sqlite] New SQLitePlus version available Hello all. Just to let everyone know I've released a new SQLitePlus Database Manager (v5) and COM DLL wrapper (v3.5). New features include: Ability to edit/rename an existing table Improved table creation dialog Much improved Grid table editor Binary data viewer window for viewing BLOB data Data import/export facility Combined with the existing great features, such as on-the-fly encryption and compression, stored scripts and ease of use, SQLitePlus is a great choice for applications development and database management. Existing users upgrade for free. And the Grid ActiveX is also now free (source code available upon request). Best regards -Brett G. This message was sent using IMP, the Internet Messaging Program.
[sqlite] Re: New SQLitePlus version available
Sorry, forgot to put the download link: www.eztools-software.com/downloads/sqliteplus.exe cheers This message was sent using IMP, the Internet Messaging Program.
[sqlite] New SQLitePlus version available
Hello all. Just to let everyone know I've released a new SQLitePlus Database Manager (v5) and COM DLL wrapper (v3.5). New features include: Ability to edit/rename an existing table Improved table creation dialog Much improved Grid table editor Binary data viewer window for viewing BLOB data Data import/export facility Combined with the existing great features, such as on-the-fly encryption and compression, stored scripts and ease of use, SQLitePlus is a great choice for applications development and database management. Existing users upgrade for free. And the Grid ActiveX is also now free (source code available upon request). Best regards -Brett G. This message was sent using IMP, the Internet Messaging Program.
[sqlite] help with simple query, please
Suppose I have two tables, A and B. A has two columns: score, location. B has two columns leftLocation, rightLocation. I need the maximum score located outside all entries in table B. B is a small table, say less than 100 entries. I tried this: SELECT max(score),location FROM A, B GROUP BY score HAVING location < min(leftLocation) && location > max(rightLocation) It doesn't work the way I think it should, not to mention it's the wrong algorithm because left/rightLocation are made to be used in pairs. So, how is it done? Thanks, Brannon
Re: [sqlite] HELP for SQLite MEMORY test.
The latest version of SQLite from CVS should give you comparable :memory:
insert timings to that of a file-based database assuming you have enough
RAM. Well, no more than 4% slower, anyway.
Take a look at this bug ticket http://www.sqlite.org/cvstrac/tktview?tn=1790
for details and how to have :memory: DB slightly outperform a file-based
database using the 3.3.5 code release with N_PG_HASH=32768 and
SQLITE_DEFAULT_PAGE_SIZE=1024.
--- Manzoor Ilahi Tamimy <[EMAIL PROTECTED]> wrote:
> DEAR All ,
>
>I want to use SQLite for our project, the main thing is that the
>database contains millions of Records. So for the faster
> operations on the db I want to use the SQLite as in-memory database.
>
>I have compared the results of SQLite as Disk db and as Memory db
>but I am not getting much difference. I am surprised that there
> must be some difference between memory mode and disk mode.
>
>Tests were run on 2.4GHz Sempron with 1GB of RAM and running
> Windows XP + SP2 with all updates applied. Test 20 : 100
> INSERTs CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
> INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty
> three');
>
>In Memory 37.51 Sec
>DISK BASED 39.76 Sec
>Disk Space consumed 61.6 MB
>
>
>Test 21`: 300 INSERTs
>In Memory 141.79 Sec
>DISK BASED 111.906000 Sec
>Disk Space consumed 185 MB
>
>
>Test 22: 500 INSERTs
>
>In Memory 279.42 Sec
>DISK BASED 201.266000 Sec
>Disk Space consumed 308 MB
>Test 23: 1000 INSERTs
>
>
>In Memory 784.797000 Sec
>DISK BASED 399.846000 Sec
>Disk Space consumed 617 MB
>
>
>I am testing it in the following way
>
>
>dwStart = GetTickCount();
>db.execDML("begin transaction;");
>db.execDML("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))
> ;") ; for( int i=1;i<=1000;i++) db.execDML("INSERT INTO t1
> VALUES(1, 298361,'two hundred ninety eight thousand three hundred')
> "); db.execDML("commit Transaction") ; dwStop = GetTickCount();
>
>
>I think I am missing something or some necessary parameters. I
> spent a lot of time to find out the problem. Please Guide me. I
> will be really thankful.
>
>Regards,
>
>MANZOOR ILAHI
> --
> COMSATS Institute of Information Technology (http://www.ciit.edu.pk)
>
>
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: [sqlite] SQLite db file size and NFS file locking issue
Many servers create lock files on NFS instead of using POSIX locks due to the flakey NFS lock implementations. (Does any OS other than Solaris support NFS locks correctly???) The existance of the lock file indicates that another process has locked the main file. --- [EMAIL PROTECTED] wrote: > "Rajan, Vivek K" <[EMAIL PROTECTED]> wrote: > >> > > Also, is there any NFS locking issue with SQLite database? Sometimes if > > a client opens the files and die, do we have problems reading the file > > again because NFS lock for the file still exists!? Is this true? > > > > There are no locking issues within SQLite, but there are > plenty of locking issues in many implementations of NFS. > SQLite expects file locks to work as defined in the Posix > specification. If locks do not work correctly, then neither > will SQLite. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] create unique index quickly
The statement CREATE UNIQUE INDEX primkey ON 4dData (x,y,z) is too slow for me. It takes 13 sec on 700k records on my Athlon XP 2GHz. That's about four seconds longer than it took me to put the data in there. The two times together are still faster than inserting into a table with a primary key already declared. Is there some way I can create that unique index without the overhead of uniqueness checking? I know all my triples are unique when used together: I generated them that way. Or is there some other way I can create that index faster? Thanks. __ Brannon King ¯¯
[sqlite] Re: spatial sqlite anyone ?
Hello Noel, I don't plan to replace the normal indexing, I plan to have a set of function to create a (memory ?) index. But how do I retrieve the data without doing a select where rowid = xxx ? If you're going to create a memory index, than this will be no sqlite spatial index extension: I'm already doing this now by selecting records from a table and creating an in-memory spatial index. I don't know whether by coincidence or not, dr. Hipp has just published a wiki page regarding Virtual Tables which might do the trick, and although it's in very incipient stage (e.g. proposal) it sounds interesting. Go check it out at http://www.sqlite.org/cvstrac/wiki?p=VirtualTables. I must confess that I'm a little tired right now and I cannot see the Virtual Table's application in Spatial Indexes :-) Perhaps tomorrow morning my luck will change and I'll be enlightened. And another think, regarding your second wannado: 2 - to be able to load and exchange data from WKT (well know text format) and binary (shape file for instance) I don't know / think whether this extension should / must be able to read an ESRI shape. You should design your extension carefully with a pluggable way of doing readers/writers. This way, if anyone needs to work with a special format he/she could write it if it doesn't exist. I'm saying that because, for example, I've chosen to use an SVG-style notation for storing my gis elements. George. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
Re: [sqlite] opcode count for sqlite3_progress_handler
"Brannon King" <[EMAIL PROTECTED]> wrote: > Suppose I have a table with 6 INTEGER columns, and 600k entries. I need = > to > create a unique index on three of those columns, ie., CREATE UNIQUE = > INDEX > loc ON data (x,y,z). That takes a while so I thought I'd show some = > progress. > >From that information, is it possible to calculate the number of opcodes > such an operation would require for completion so that I could get an > accurate progress bar? Is there a query that would tell me how many = > opcodes > it would take? Is it possible to create the index once and count the = > number > of opcodes and do they grow linearly in the creation of indexes? > The number of opcodes to create the index will be A + Bn Where n is the number of entries in the table and A and B are constants. The A and B constants will depend on the structure of your table, and the version of SQLite you are using. A and B must be determined experimentally. If the uniqueness constraint fails, the index creation will abort immediately and so the total number of opcodes will be less than predicted. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] opcode count for sqlite3_progress_handler
Suppose I have a table with 6 INTEGER columns, and 600k entries. I need to create a unique index on three of those columns, ie., CREATE UNIQUE INDEX loc ON data (x,y,z). That takes a while so I thought I'd show some progress. >From that information, is it possible to calculate the number of opcodes such an operation would require for completion so that I could get an accurate progress bar? Is there a query that would tell me how many opcodes it would take? Is it possible to create the index once and count the number of opcodes and do they grow linearly in the creation of indexes? Thanks again, __ Brannon King ¯¯
[sqlite] what looks like a very useful debugger
FYI: I thought this was interesting enough to spam the list with it. UndoDB is a debugger like no other for compiled programs. As well as the familiar next/step type commands to move the program forwards, UndoDB has commands that step your program backwards. More accurately, UndoDB allows the programmer to view the program's state at any point in the program's execution history. SUPPORTED SYSTEMS UndoDB is supported on up-to-date installations of the following systems: Debian Sarge (2.4 and 2.6). Fedora Core 3, 4 and 5. Gentoo 2006.0. Mandriva 2006.0. Red Hat Enterprise Linux 4. Slackware 10.2. SUSE 10. Ubuntu 5.10. It is anticipated that UndoDB will run on most Linux distributions and versions. However, the nature of UndoDB means that it may not work immediately on all Linux installations. If you discover a system on which UndoDB will not run, please mail [EMAIL PROTECTED]; adding support for new configurations is usually fairly straightforward. http://undo-software.com/undodb_rationale.html
[sqlite] external indexing
Hello All, Still trying to figure out the best way to index on top of sqlite (for spatial indeex). Lets say the the external index gives me a bunch of rowid, what would be the correct way to send that to sqlite ? (My sql levelis rather low) Should I bypass the sqlparser or is there an efficent way to do that in SQL? Best regards -- Noël Frankinet Gistek Software SA http://www.gistek.net
Re: [sqlite] problem with installing SQLite3.3.5
"shibu alampatta" <[EMAIL PROTECTED]> wrote: > hello friends, > following are the last few lines on giving make to install sqlite3.3.5 I'm > using Fedora Core 1(kernel 2.4) > > any help please > > Apparently Fedora Core 1 is using an archaic version of Tcl. You can compile SQLite without Tcl. You probably do not care about Tcl or you would have already upgraded it to something recent 2002 (the release date of the last version of Tcl that might cause the problem you are seeing.) So I would suggest that you compile without Tcl support. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] SQLite db file size and NFS file locking issue
"Rajan, Vivek K" <[EMAIL PROTECTED]> wrote: >> > Also, is there any NFS locking issue with SQLite database? Sometimes if > a client opens the files and die, do we have problems reading the file > again because NFS lock for the file still exists!? Is this true? > There are no locking issues within SQLite, but there are plenty of locking issues in many implementations of NFS. SQLite expects file locks to work as defined in the Posix specification. If locks do not work correctly, then neither will SQLite. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] How to avoid the increment the id in sqlite_sequence table if the constraint occurs?
PY <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I have a problem about the ROWID.
>
> I want to create a table with unique value, not only [id] but also [name],
> and the table named [foo]
> CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE);
>
> I don't want to select the table for avoiding the duplicate NAME problem.
> So, I use the "IGNORE" when I insert into the table.
>
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('y');
>
> After that I found a phenomenon, when I select the table, the result listed
> below.
>
> SELECT * FORM foo;
>
> id name
> -- --
> 1 x
> 5 y
>
>
>
> My expected value of y is 2 not 5.
>
>
> The squence of the id is important to us. How to resolve this problem?
> Thanks for your grest help.
>
Try using INSERT OR FAIL instead of of INSERT OR IGNORE.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] sqlite3 struct etc
Micha Bieber <[EMAIL PROTECTED]> wrote: > @list > > Maybe I'm too C++ biased - but what is the state of the sqlite3 and > similar - e.g. 'Db' - structures in sqlites C-Interface ? Is this > considered 'public' and also stable or indicates the missing > documentation (at least I've found nothing apart from the sources) not to > use them in user code otherwise than as opaque pointer for the sqlite > interface ? > The sqlite3* and sqlite3_stmt* pointer are intended to be opaque. Their internal design is subject to drastic change from one point release to the next. If you search the history of the SQLite project you will find that both of these structure have undergone radical changes in the past. Additional radical changes are likely in the future. If you write code that depends on the internal layout of these structures, your code will very likely break in future releases of SQLite. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] malformed database file
Michael Knigge <[EMAIL PROTECTED]> wrote: > All, > > in a application with several threads (guess 6) we sometimes encounter a > SQLITE_CORRUPT. I'm not the author of this application and my time is > spare, but I really want to ensure that this is not an SQLite issue. > > Some hints how to debug this? Anythig I could turn on within SQLite so > we can track this down? > Is the database really corrupt, or are you getting a spurious error? You can tell if the database is truely corrupt or not by running PRAGMA integrity_check; > And yes, every thread uses its own sqlite3* ;) > Are you really sure of this? In my experience most of these kinds of problems end up being cases of threads doing things that their programmers were not aware of. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] malformed database file
All, in a application with several threads (guess 6) we sometimes encounter a SQLITE_CORRUPT. I'm not the author of this application and my time is spare, but I really want to ensure that this is not an SQLite issue. Some hints how to debug this? Anythig I could turn on within SQLite so we can track this down? And yes, every thread uses its own sqlite3* ;) bye, Michael
RE: [sqlite] add primary key after inserts?
> I'm not sure what you are getting at here. Copy memory from > where to where? The database is much more than an array of > record structures that you can copy data into. > > Dennis Cote I've been studying the profile and can see my thinking was wrong. I was thinking we would just log an insert request with the step function in memory somewhere. Now that I look at the profile more closely, I see that it actually does do a full insert on each call to the step function. I've attached a copy of the profile. You can see that near 79% of my program's execution time is in sqlite3_step or one of its children. "writeResult" is my function that calls sqlite3_step. From that you can see that the mutexing and six bind calls are less than 6% of the runtime. And it appears from the source code that the memory allcoations are all happening around or in BtreeInsert. Again, that makes more sense now that I know it does a full transaction on each step call. I wonder if I could cache my data myself for some benefit? I'll try it. "Method Name","% in","% in w/Children","Called","Average" "sqlite3VdbeExec","18.0","74.9","26,198","30.6" "OSF_SQLite::writeResult","5.7","98.7","26,165","9.6" "sqlite3WinThreadSpecificData","5.3","5.8","78,593","3.0" "RtlAllocateHeap","4.4","4.4","81,436","2.4" "sqlite3BtreeInsert","4.1","21.9","26,175","7.0" "sqlite3pager_get","3.5","3.8","196,934","0.8" "sqlite3VdbeSerialType","2.9","2.9","549,609","0.2" "sqlite3BtreeMoveto","2.8","5.6","26,183","4.8" "malloc","2.6","6.9","80,030","1.4" "sqlite3_step","2.5","79.1","26,198","4.3" "sqlite3PutVarint","2.5","2.5","262,301","0.4" "sqlite3VdbeMemRelease","2.3","2.3","628,410","0.2" "sqlite3pager_unref","2.3","3.0","291,313","0.3" "sqlite3VdbeHalt","2.3","3.4","52,388","1.9" "free","2.2","4.1","79,746","1.2" "sqlite3_bind_int64","2.2","8.1","156,996","0.6" "sqlite3VdbeSerialPut","1.9","3.1","183,203","0.5" "RtlFreeHeap","1.9","1.9","81,039","1.0" "sqlite3Error","1.7","5.4","209,417","0.4" "sqlite3MallocFailed","1.6","1.6","657,798","0.1" "sqlite3VdbeReset","1.6","4.0","26,194","2.8" "sqlite3VdbeMemSetStr","1.5","2.3","209,480","0.3" "sqlite3BtreeCursor","1.4","3.9","26,183","2.3" "sqlite3ValueSetStr","1.4","3.7","209,419","0.3" "sqlite3BtreeLast","1.3","2.5","26,171","2.2" "sqlite3BtreeGetMeta","1.2","5.5","26,176","2.0" "sqlite3VdbeMemSetInt64","1.2","1.7","156,996","0.3" "sqlite3VarintLen","1.2","1.2","235,547","0.2" "sqlite3VdbeFreeCursor","1.1","4.7","235,769","0.2" "sqlite3MallocRaw","1.1","9.2","80,030","0.6" "sqlite3GenericMalloc","1.0","8.0","80,030","0.6" "sqlite3VdbeSerialTypeLen","0.9","0.9","317,427","0.1" "sqlite3GenericFree","0.8","4.8","79,746","0.4" "sqlite3_reset","0.7","5.2","26,173","1.2" "sqlite3BtreeKeySize","0.7","0.8","26,167","1.1" "sqlite3GetVarint","0.7","0.7","134,238","0.2" "sqlite3FreeX","0.7","5.5","158,661","0.2" "sqlite3VdbeMemShallowCopy","0.6","0.6","157,040","0.2" "sqlite3_bind_int","0.6","7.4","130,830","0.2" "sqlite3ThreadDataReadOnly","0.6","6.4","78,569","0.3" "(unknown)","0.5","1.2","120,422","0.2" "sqlite3VdbeMakeReady","0.5","0.5","26,197","0.9" "TlsGetValue","0.5","0.5","79,641","0.3" "sqlite3pager_write","0.5","0.5","27,451","0.8" "sqlite3BtreeCloseCursor","0.4","2.3","26,182","0.8" "sqlite3Malloc","0.4","5.3","52,827","0.4" "sqlite3VdbeFifoClear","0.4","0.6","26,215","0.7" "sqlite3pager_ref","0.4","0.4","92,363","0.2" "Fasta::Load","0.3","0.4","2","7,532.3" "RtlLeaveCriticalSection","0.3","0.3","40,641","0.4" "sqlite3VdbeMemIntegerify","0.3","0.5","26,196","0.5" "WriteFile","0.3","0.3","1,350","9.0" "sqlite3BtreeBeginTrans","0.3","0.4","26,174","0.4" "sqlite3VdbeFifoInit","0.2","0.2","26,215","0.3" "sqlite3SafetyOff","0.2","0.2","26,228","0.3" "sqlite3ApiExit","0.2","0.2","26,261","0.3" "RtlEnterCriticalSection","0.1","0.1","40,642","0.2" "sqlite3BtreeFlags","0.1","0.1","52,353","0.1" "sqlite3BtreeCommitStmt","0.1","0.1","26,179","0.2" "sqlite3SafetyOn","0.1","0.1","26,229","0.2" "sqlite3VdbeSetChanges","0.1","0.1","26,166","0.2" "sqlite3VdbeIntValue","0.1","0.1","26,196","0.2" "CreateFileW","0.1","0.1","8","408.2" "sqlite3RunParser","0.1","0.3","29","79.2" "sqlite3pager_lookup","0.0","0.0","8,238","0.2" "DeleteFileW","0.0","0.0","6","273.7" "sqlite3Parser","0.0","0.2","689","2.1" "GetFileAttributesW","0.0","0.0","8","140.4" "SearchBases::LoadParams","0.0","0.5","1","1,122.4" "DeleteFileA","0.0","0.0","1","1,004.2" "CloseHandle","0.0","0.0","8","118.8" "FlushFileBuffers","0.0","0.0","3","264.8" "boost::lexical_cast,std::allo cator >,bool>","0.0","0.0","32","24.3" "SetFilePointer","0.0","0.0","617","1.0" "(unknown)","0.0","0.0","611","0.9" "(unknown)","0.0","0.2","608","0.8" "boost::program_options::store","0.0","0.0","2","221.5" "InitializeCriticalSection","0.0","0.0","62","7.0" "RtlDeleteCriticalSection","0.0","0.0","55","7.0" "sqlite3pager_pagecount","0.0","0.0","1,238","0.3" "GetFileAttributesA","0.0","0.0","7","53.7" "ReadFile","0.0","0.0","31","11.7" "RtlReAllocateHeap","0.0","0.0","39","9.1" "sq
Re: [sqlite] sqlite3 struct etc
Thursday, May 18, 2006, 17:36:53, Jay Sprenkle wrote: > Since you pass that in to begin with, why do you need the database > to provide information you already have? Thats by design (tm), but it might be not the best one. For performance reasons I have splitted my project in a way, requiring 2 database files. The first one holding all sort of tricky information. This database is pretty small. The second one holds somewhat bulky amounts of data. Several millions of rows distributed in 200 tables. I have to care for everything I do with this db. So also deleting the poor thing is at times the best solution todo anything in time. Of course both tables are logically intertwined and I have all sorts of different requirements - open/creating/updating the one, but not the other, etc. Attaching the big one to the small table. At different times not only during creation. For this, it helps to link the file name of one of the databases as a special table entry into the other. To do so, it would be nice to have access to the file name at random times. Micha --
Re: [sqlite] add primary key after inserts?
Brannon King wrote: So I dropped the "not null" and it gave a ~2% increase in overall speed. It's not much, but may count for something. It's a little strange because that is just the opposite of MySQL. In MySQL, it shrinks the database size and speeds up the access by declaring a column not null. Brandon, I didn't say it would save a lot of time. ;-) I am using the prepare/bind/step combination. The bind calls are negligible time-wise. It's the step function that I think could run faster. As I understand it, the step function is not actually doing an insert. That doesn't happen until the "end transaction" statement. Therefore, the not null thing should not effect the speed of the step function. Is that not true? The step function executes the VDBE opcodes until it does a Halt (for an insert). The majority of the time will be taken to execute the Insert opcode. This writes the new record into the database. The underlying pager layer will cache these writes in memory until the cache is full or the transaction is committed. At that point the cached pages are written to disk. The Insert opcode still performs all the logic of locating the next free record, allocating additional pages as necessary, updating the b-tree, etc. It seems that the step function should only occasionally allocate memory; it should allocate enough for a number of expected queries per transaction. That doesn't make sense, though, if you're not in the middle of a transaction. Hence, I wonder if we really need two different functions. The step function is overkill for just doing inserts that never return any data and that are in the middle of a transaction. What we need in that situation is a quick memcpy and nothing else. I'm not sure what you are getting at here. Copy memory from where to where? The database is much more than an array of record structures that you can copy data into. Dennis Cote
Re: [sqlite] add primary key after inserts?
On 5/18/06, Brannon King <[EMAIL PROTECTED]> wrote: >Also, do a large number of inserts within a transaction. (1000-100,000 or so, not a billion ;) ) I don't see how increasing the number of inserts per transaction changes the speed at which the sqlite3_step function executes when it is called on an insert in the middle of the transaction. I'll put some more commentary on that in my next response to Dennis. Each tiime you complete a transaction it waits for the OS to flush the data to the disk. The system has to wait for the drive platter to rotate to the correct position before writing. Optimal write speed would be to queue as many records as you can in the time it takes for one disk platter rotation. Optimal overall time would be minimizing the overhead of disk flushes. You have memory limitations though. Most systems will not let you queue a billion operations in RAM.
RE: [sqlite] add primary key after inserts?
So I dropped the "not null" and it gave a ~2% increase in overall speed. It's not much, but may count for something. It's a little strange because that is just the opposite of MySQL. In MySQL, it shrinks the database size and speeds up the access by declaring a column not null. I am using the prepare/bind/step combination. The bind calls are negligible time-wise. It's the step function that I think could run faster. As I understand it, the step function is not actually doing an insert. That doesn't happen until the "end transaction" statement. Therefore, the not null thing should not effect the speed of the step function. Is that not true? It seems that the step function should only occasionally allocate memory; it should allocate enough for a number of expected queries per transaction. That doesn't make sense, though, if you're not in the middle of a transaction. Hence, I wonder if we really need two different functions. The step function is overkill for just doing inserts that never return any data and that are in the middle of a transaction. What we need in that situation is a quick memcpy and nothing else. > Having a primary key is the same thing as adding a UNIQUE > index on the desired columns except for the case where the > key is on a single column with integer type (i.e. integer > primary key). The later case is optimized in sqlite, but must > be done before the table is populated. > > > I have a table that looks like this: > > > > CREATE TABLE IF NOT EXISTS results (qi INTEGER NOT NULL, ri INTEGER > > NOT NULL, run INTEGER NOT NULL, drl INTEGER NOT NULL, score INTEGER > > NOT NULL, qis INTEGER NOT NULL, ris INTEGER NOT NULL ); > > > > I originally had this line as well: , PRIMARY KEY (qi, ri, run) > > > > I need to do billions (no joke) of consecutive inserts into this > > table. Writing binary out with std::ofstream was using 26% > of my code > > execution time according to the profiler. I changed to sqlite3, and > > now the sqlite3_step function uses 50% of my execution time. After > > reading the news groups, I removed the primary key. That dropped it > > down to about 41%. That was significant. However, I was still > > significantly slower than binary writes with ofstream. > Then, I tried > > the PRAGMA temp_store = 2. That made absolutely no difference. I'll > > assume that's what it was to begin with, though it reports 0. Also, > > from the profiler, it seems that the step function does a lot of > > memory allocations and deallocations, yet I cannot find > them in the code. > > > > If it helps, I was testing 600k inserts in transactions of 0xFFF > > inserts and my current settings: > >PRAGMA auto_vacuum = 0; \ > >PRAGMA case_sensitive_like = 1; \ > >PRAGMA cache_size = 32768; \ > >PRAGMA default_cache_size = 32768; \ > >PRAGMA count_changes = 0; \ > >PRAGMA synchronous = 0; \ > >PRAGMA page_size = 4096; \ > >PRAGMA temp_store = 2; > > > > What else can I do to speed up my inserts? > > > > > There are a couple of things to look at. First, you don't say > exactly how you are executing the inserts. You should use > sqlite3_prepare() to compile an insert statement with a your > seven columns as parameters. > > insert into results values (?, ?, ?, ?, ?, ?, ?) > > Then inside your insert loop you should bind values to each > of these parameters. If you have known patterns in your input > data, and some of the parameter values repeat, you can > eliminate re-binding the same value to a parameter for each > loop since sqlite will retain the last bound value. Finally, > step the statement and then reset it for the next loop iteration. > > You should add your index after you have inserted all the data. > > You can also speed the execution time of the insert statement > somewhat by dropping the NOT NULL constraints on your data > columns. Each one of those is checked for every insert. For > comparison here is a dump of the code generated for a simple > two column table with and without the not null constraints. > > SQLite version 3.3.5 > Enter ".help" for instructions > sqlite> create table t1 (a integer, b integer); > sqlite> create table t2 (a integer not null, b integer not null); > sqlite> .explain on > sqlite> explain insert into t1 values (?, ?); > addr opcode p1 p2 p3 > -- -- -- > - > 0 Goto0 11 > 1 Integer 0 0 > 2 OpenWrite 0 2 > 3 SetNumColumns 0 2 > 4 NewRowid0 0 > 5 Variable1 0 > 6 Variable2
RE: [sqlite] add primary key after inserts?
>Also, do a large number of inserts within a transaction. (1000-100,000 or so, not a billion ;) ) I don't see how increasing the number of inserts per transaction changes the speed at which the sqlite3_step function executes when it is called on an insert in the middle of the transaction. I'll put some more commentary on that in my next response to Dennis.
Re: [sqlite] Open multiple Recordsets
[EMAIL PROTECTED] wrote: Hi to all ...meet another problem... ;-) Is it possible to open more than one active Recordset connect to a single-opened Database? This means, that I want to open the Database one times in a initial Function of the App. And then followed, i want to open more as one Recordset connected to this openend Database at the same time (...to read and write and manipulate the Data-Tables and after work close the Recordsets). The actual problem is, first I opened my Database, then open a Table-Recordset to fill in a View. Then I Execute a "Delete from" to the Database used by the primary Database-Connection, but create a new/another (temporary) sqlite3_stmt *pExecRecordset to perform it. I think, that the primary Recordset is not Dynamic, also it don't know this Delete. Okay, thats not the problem. It doesn't work. How is the error? If I perform the Delete separately without a "concurrent" Recordset it works well. Have I open allways a Database-Connection for each Recordset? I think, that cannot be. In a Dialog-App a User performs permanet changes to the fieldvalues of the present recordset and sends permanent a SQL-Update to the DB. Anne, I'm reading between the lines somewhat since I'm not sure I followed your question. I think you are saying you have an active select statement (i.e. a reader) in progress and then you are trying to do a delete to remove the current record. The delete would need to write to the database table. SQLite can support multiple simultaneous readers, or a single writer, but not a mixture of active readers and a writer. When a reader is active it holds a shared lock on the database. When a writer starts, it tries to get an exclusive lock on the database. If there are any active readers the writer can't get the exclusive lock. You must close the reader (i.e. finalize or reset the select statement) before the delete can be done. HTH Dennis Cote
Re: [sqlite] sqlite3 struct etc
> but seriously, it's a black box that you don't need to pry open to use Sqlite. Something of course, was driving me in posting the question - it was programmers laziness, as usual :-) In this case, it was the databases filename, available from one of the mentioned structures. There are other ways to hold them, but perhaps it is is also a candidate for the public interface. Since you pass that in to begin with, why do you need the database to provide information you already have? It's been interesting reading the mailing list. There are all kinds of unique and interesting projects being done with sqlite. You just never know if someone is doing something really dfferent with it. I wasn't sure if your application was one of those.
[sqlite] How to avoid the increment the id in sqlite_sequence table if the constraint occurs?
Hi All,
I have a problem about the ROWID.
I want to create a table with unique value, not only [id] but also [name],
and the table named [foo]
CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE);
I don't want to select the table for avoiding the duplicate NAME problem.
So, I use the "IGNORE" when I insert into the table.
INSERT OR IGNORE INTO foo (name) VALUES ('x');
INSERT OR IGNORE INTO foo (name) VALUES ('x');
INSERT OR IGNORE INTO foo (name) VALUES ('x');
INSERT OR IGNORE INTO foo (name) VALUES ('x');
INSERT OR IGNORE INTO foo (name) VALUES ('y');
After that I found a phenomenon, when I select the table, the result listed
below.
SELECT * FORM foo;
id name
-- --
1 x
5 y
My expected value of y is 2 not 5.
The squence of the id is important to us. How to resolve this problem?
Thanks for your grest help.
Thanks,
VK
Re: [sqlite] High retrieval time. Please help
Anish Enos Mathew wrote:
Hi Dennis,
I am stuck up with a problem. I want to calculate the
time taken for retrieval of 10,00,000 random records 10,00,000 times. I
am using a function getNumber for getting the random number and getTime
for getting the current time.getNumber function will return a value
between 1 and 10,00,000. When I am passing the range of random number to
be generated as 1000, the program is taking 340 seconds to complete. I
am not able to give 10,00,000 as the range, since It is going to take a
lot time.Y is it so. I am attaching the program with this. Please have a
look at it. Can any body tell me where am I going wrong. Is there any
other methods that can be used so that I can reduce the retrieval time?
int main(int argc, char *argv[])
{
char *database = "test.db";
sqlite3 *db;
sqlite3_stmt *select;
int col1,rc,i,j;
int no_of_rows=100,count;
char *col2;
char *col3;
double start_time, end_time, elapsed_time, total_elapsed_time=0;
sqlite3_open(database, &db);
sqlite3_prepare(db, "select * from data_table where seq_number = ?",
-1,&select,NULL );
for(i=0;i<100;i++)
{
j=getNumber(100);
sqlite3_bind_int(select, 1, j);
sqlite3_step(select);
start_time=getTime(); //get the starting time of fetching rows
col1 = sqlite3_column_int(select,0);
col2 = sqlite3_column_text(select,1);
col3 = sqlite3_column_text(select,2);
end_time = getTime();// getting the ending time of fetching rows
elapsed_time = end_time - start_time;
total_elapsed_time = total_elapsed_time + elapsed_time;
// printf("\n%d %s %s",col1,col2,col3);
sqlite3_reset(select);
}
sqlite3_finalize(select);
sqlite3_close(db);
printf(" \nRetrieved %d datas in %lf seconds,%lf selects/sec\n\n",
no_of_rows,total_elapsed_time,no_of_rows / total_elapsed_time);
return 0;
}
Anish,
From your code it looks like your 10,00,000 should really be 1,000,000
(i.e. one million).
Your code is summing the time it takes sqlite to return the column
values from the queries, not the time it takes sqlite to locate the
records. Is that what you want? I suspect you should be starting your
timer before the the sqlite3_step() call. Personally, I think all the
time from the sqlite3_bind() call through to the end of the
sqlite3_reset() call should be counted. That is the total execution time
it takes to retrieve one record. This is the loop time less the per loop
overhead, the time taken to measure the time, and the time to generate
your random number.
You are timing a query that does a lookup with a where clause. I suspect
that you do not have an index on the seq_number column that the where
clause is searching. If this is the case, sqlite must do a table scan
for each returned row. Even if the records were inserted in sequence,
without an index sqlite won't know that. Add an index on your seq_number
column and the lookup will go much faster.
HTH
Dennis Cote
P.S. Y don't u post ur questions in English? Those silly abbreviations
aren't cool, they're annoying. I had to get that off my chest, it's one
of my pet peeves. :-)
Re: [sqlite] sqlite3 struct etc
Thursday, May 18, 2006, 17:09:12, Jay Sprenkle wrote: > but seriously, it's a black box that you don't need to pry open to use Sqlite. Something of course, was driving me in posting the question - it was programmers laziness, as usual :-) In this case, it was the databases filename, available from one of the mentioned structures. There are other ways to hold them, but perhaps it is is also a candidate for the public interface. Micha --
[sqlite] Encoding problem ISO 8859-1 / UTF-8
Hi all, sorry for repost, but I have gathered some more info for my encoding problem. I am using latest SQLite with Java 1.4.2 and openSuSE in order to build a morphological analyzer for the German language (it's an open source project for my University). I found out that SuSE has UTF-8 activated by default, so when I compiled db and the wrapper from Christian Werner UTF-8 was "on". When I manually insert data into my db, the "umlauts" ä,ü,ß,ö and the like are properly displayed. But with Java, when I read either from an ISO 8859-1 or UTF-8 encoded textfile and then query the db, I see that the umlauts are substituted with strange letters. To be more precise, when UTF-8 was still activated, all occurences of umlaut where substituted with dotted rectangles. I then deactivated UTF-8 and now I see strange letters (like the 'i' with two dots or the question mark upside down) instead of the umlauts. What do I need to do to fix this? Must I compile SQLite again having UTF-8 deactivated? I'd prefer using ISO 8859-1 over UTF-8 if possible. _Please_ help Pasquale
Re: [sqlite] sqlite3 struct etc
On 5/18/06, Micha Bieber <[EMAIL PROTECTED]> wrote: Maybe I'm too C++ biased - but what is the state of the sqlite3 and similar - e.g. 'Db' - structures in sqlites C-Interface ? Is this considered 'public' and also stable or indicates the missing documentation (at least I've found nothing apart from the sources) not to use them in user code otherwise than as opaque pointer for the sqlite interface ? Jay's law of application programming: "Thou shalt not mess with the internal structures of the application library, even if they are public. That way lies madness." ;) but seriously, it's a black box that you don't need to pry open to use Sqlite.
[sqlite] sqlite3 struct etc
@list Maybe I'm too C++ biased - but what is the state of the sqlite3 and similar - e.g. 'Db' - structures in sqlites C-Interface ? Is this considered 'public' and also stable or indicates the missing documentation (at least I've found nothing apart from the sources) not to use them in user code otherwise than as opaque pointer for the sqlite interface ? Micha --
Re: [sqlite] add primary key after inserts?
Brannon King wrote:
Or is an Index sufficient?
Brandon,
Having a primary key is the same thing as adding a UNIQUE index on the
desired columns except for the case where the key is on a single column
with integer type (i.e. integer primary key). The later case is
optimized in sqlite, but must be done before the table is populated.
I have a table that looks like this:
CREATE TABLE IF NOT EXISTS results (qi INTEGER NOT NULL, ri INTEGER
NOT NULL, run INTEGER NOT NULL,
drl INTEGER NOT NULL, score INTEGER NOT NULL, qis INTEGER NOT NULL,
ris INTEGER NOT NULL );
I originally had this line as well: , PRIMARY KEY (qi, ri, run)
I need to do billions (no joke) of consecutive inserts into this
table. Writing binary out with std::ofstream was using 26% of my code
execution time according to the profiler. I changed to sqlite3, and
now the sqlite3_step function uses 50% of my execution time. After
reading the news groups, I removed the primary key. That dropped it
down to about 41%. That was significant. However, I was still
significantly slower than binary writes with ofstream. Then, I tried
the PRAGMA temp_store = 2. That made absolutely no difference. I'll
assume that's what it was to begin with, though it reports 0. Also,
from the profiler, it seems that the step function does a lot of
memory allocations and deallocations, yet I cannot find them in the code.
If it helps, I was testing 600k inserts in transactions of 0xFFF
inserts and my current settings:
PRAGMA auto_vacuum = 0; \
PRAGMA case_sensitive_like = 1; \
PRAGMA cache_size = 32768; \
PRAGMA default_cache_size = 32768; \
PRAGMA count_changes = 0; \
PRAGMA synchronous = 0; \
PRAGMA page_size = 4096; \
PRAGMA temp_store = 2;
What else can I do to speed up my inserts?
There are a couple of things to look at. First, you don't say exactly
how you are executing the inserts. You should use sqlite3_prepare() to
compile an insert statement with a your seven columns as parameters.
insert into results values (?, ?, ?, ?, ?, ?, ?)
Then inside your insert loop you should bind values to each of these
parameters. If you have known patterns in your input data, and some of
the parameter values repeat, you can eliminate re-binding the same
value to a parameter for each loop since sqlite will retain the last
bound value. Finally, step the statement and then reset it for the next
loop iteration.
You should add your index after you have inserted all the data.
You can also speed the execution time of the insert statement somewhat
by dropping the NOT NULL constraints on your data columns. Each one of
those is checked for every insert. For comparison here is a dump of the
code generated for a simple two column table with and without the not
null constraints.
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> create table t1 (a integer, b integer);
sqlite> create table t2 (a integer not null, b integer not null);
sqlite> .explain on
sqlite> explain insert into t1 values (?, ?);
addr opcode p1 p2 p3
-- -- --
-
0 Goto0 11
1 Integer 0 0
2 OpenWrite 0 2
3 SetNumColumns 0 2
4 NewRowid0 0
5 Variable1 0
6 Variable2 0
7 MakeRecord 2 0 dd
8 Insert 0 3 t1
9 Close 0 0
10Halt0 0
11Transaction 0 1
12VerifyCookie0 2
13Goto0 1
14Noop0 0
sqlite> explain insert into t2 values (?, ?);
addr opcode p1 p2 p3
-- -- --
-
0 Goto0 17
1 Integer 0 0
2 OpenWrite 0 3
3 SetNumColumns 0 2
4 NewRowid0 0
5 Variable1 0
6 Variable2 0
7 Dup 1 1
8 NotNull 1 10
9 Halt19 2 t2.a may not be NULL
10Dup 0 1
11NotNull 1 13
12Halt19 2 t2.b may not be NULL
13MakeRecord 2 0 dd
14Insert 0 3 t2
15Close 0 0
16Halt0 0
17Transaction
Re: [sqlite] Open multiple Recordsets
A note on prepared statements. They are actually a complete Sqlite virtual machine context, so you can have many of them current at any time. You just have to execute them serially. JS [EMAIL PROTECTED] wrote: Hi to all ...meet another problem... ;-) Is it possible to open more than one active Recordset connect to a single-opened Database? This means, that I want to open the Database one times in a initial Function of the App. And then followed, i want to open more as one Recordset connected to this openend Database at the same time (...to read and write and manipulate the Data-Tables and after work close the Recordsets). The actual problem is, first I opened my Database, then open a Table-Recordset to fill in a View. Then I Execute a "Delete from" to the Database used by the primary Database-Connection, but create a new/another (temporary) sqlite3_stmt *pExecRecordset to perform it. I think, that the primary Recordset is not Dynamic, also it don't know this Delete. Okay, thats not the problem. It doesn't work. How is the error? If I perform the Delete separately without a "concurrent" Recordset it works well. Have I open allways a Database-Connection for each Recordset? I think, that cannot be. In a Dialog-App a User performs permanet changes to the fieldvalues of the present recordset and sends permanent a SQL-Update to the DB. Many Thanks for help and Greetings Anne
Re: [sqlite] Open multiple Recordsets
An Sqlite database is a single file, and it needs to be locked whenever it is being altered. That means that only one process/thread can access the database at any one time. If you open it twice you get no benefit. You have to design your application such that multiple users have some way of synchronizing access (serializing). In certain cases you may have to create a temporary table, possibly in another database, to avoid deadlocking. I find that using a mutex to synchronize thread access within a process works efficiently. For multiple co-operating processes you could use a semaphore or similar or build lock detection into your application using the Sqlite API if you cannot be sure that the user processes will co-operate.. JS [EMAIL PROTECTED] wrote: Hi to all ...meet another problem... ;-) Is it possible to open more than one active Recordset connect to a single-opened Database? This means, that I want to open the Database one times in a initial Function of the App. And then followed, i want to open more as one Recordset connected to this openend Database at the same time (...to read and write and manipulate the Data-Tables and after work close the Recordsets). The actual problem is, first I opened my Database, then open a Table-Recordset to fill in a View. Then I Execute a "Delete from" to the Database used by the primary Database-Connection, but create a new/another (temporary) sqlite3_stmt *pExecRecordset to perform it. I think, that the primary Recordset is not Dynamic, also it don't know this Delete. Okay, thats not the problem. It doesn't work. How is the error? If I perform the Delete separately without a "concurrent" Recordset it works well. Have I open allways a Database-Connection for each Recordset? I think, that cannot be. In a Dialog-App a User performs permanet changes to the fieldvalues of the present recordset and sends permanent a SQL-Update to the DB. Many Thanks for help and Greetings Anne
[sqlite] Open multiple Recordsets
Hi to all ...meet another problem... ;-) Is it possible to open more than one active Recordset connect to a single-opened Database? This means, that I want to open the Database one times in a initial Function of the App. And then followed, i want to open more as one Recordset connected to this openend Database at the same time (...to read and write and manipulate the Data-Tables and after work close the Recordsets). The actual problem is, first I opened my Database, then open a Table-Recordset to fill in a View. Then I Execute a "Delete from" to the Database used by the primary Database-Connection, but create a new/another (temporary) sqlite3_stmt *pExecRecordset to perform it. I think, that the primary Recordset is not Dynamic, also it don't know this Delete. Okay, thats not the problem. It doesn't work. How is the error? If I perform the Delete separately without a "concurrent" Recordset it works well. Have I open allways a Database-Connection for each Recordset? I think, that cannot be. In a Dialog-App a User performs permanet changes to the fieldvalues of the present recordset and sends permanent a SQL-Update to the DB. Many Thanks for help and Greetings Anne -- Echte DSL-Flatrate dauerhaft für 0,- Euro*! "Feel free" mit GMX DSL! http://www.gmx.net/de/go/dsl
Re: [sqlite] SQLite db file size and NFS file locking issue
Is there any limit (besides the OS files system limit) on how big the db file size can be with SQLite? Mostly it's operating system limits. Also, is there any NFS locking issue with SQLite database? Sometimes if a client opens the files and die, do we have problems reading the file again because NFS lock for the file still exists!? Is this true? Yes, some NFS's don't do locking very well.
Re: [sqlite] add primary key after inserts?
On 5/18/06, Brannon King <[EMAIL PROTECTED]> wrote: As I understand SQL, "alter table blah add primary key (blah1, blah2)" should be how you do it. The sqlite documentation seems to say otherwise. Actually, I cannot figure out from the documentation how to add a primary key after the table is created and data is entered. How is it done? Or is an Index sufficient? see create index: http://sqlite.org/lang_createindex.html I need to do billions (no joke) of consecutive inserts into this table. Writing binary out with std::ofstream was using 26% of my code execution time according to the profiler. I changed to sqlite3, and now the sqlite3_step function uses 50% of my execution time. After reading the news groups, I removed the primary key. That dropped it down to about 41%. That was significant. However, I was still significantly slower than binary writes with ofstream. Then, I tried the PRAGMA temp_store = 2. That made absolutely no difference. I'll assume that's what it was to begin with, though it reports 0. Also, from the profiler, it seems that the step function does a lot of memory allocations and deallocations, yet I cannot find them in the code. Do you have any other indices on the table you're inserting into? It's faster to do all the inserts and build the index later. Also, do a large number of inserts within a transaction. (1000-100,000 or so, not a billion ;) )
[sqlite] Encoding problem
Hi all, I am inserting Strings from Java into my db. In Java those Strings have German umlauts, i.e. ä, ü, ß, ö. When I check in the db I see that there have been replaced with a dotted square. How do I fix this? BTW: When I insert data from SQLite shell, it is displayed correctly. Thanks in advance Pasquale
Re: [sqlite] HELP for SQLite MEMORY test.
On 5/18/06, Manzoor Ilahi Tamimy <[EMAIL PROTECTED]> wrote: DEAR All , I want to use SQLite for our project, the main thing is that the database contains millions of Records. So for the faster operations on the db I want to use the SQLite as in-memory database. I have compared the results of SQLite as Disk db and as Memory db but I am not getting much difference. I am surprised that there must be some difference between memory mode and disk mode. The operating system disk cache is putting the disk database in memory too. So you basically have two in memory databases.
[sqlite] High retrieval time. Please help
Hi Dennis,
I am stuck up with a problem. I want to calculate the
time taken for retrieval of 10,00,000 random records 10,00,000 times. I
am using a function getNumber for getting the random number and getTime
for getting the current time.getNumber function will return a value
between 1 and 10,00,000. When I am passing the range of random number to
be generated as 1000, the program is taking 340 seconds to complete. I
am not able to give 10,00,000 as the range, since It is going to take a
lot time.Y is it so. I am attaching the program with this. Please have a
look at it. Can any body tell me where am I going wrong. Is there any
other methods that can be used so that I can reduce the retrieval time?
int main(int argc, char *argv[])
{
char *database = "test.db";
sqlite3 *db;
sqlite3_stmt *select;
int col1,rc,i,j;
int no_of_rows=100,count;
char *col2;
char *col3;
double start_time, end_time, elapsed_time, total_elapsed_time=0;
sqlite3_open(database, &db);
sqlite3_prepare(db, "select * from data_table where seq_number = ?",
-1,&select,NULL );
for(i=0;i<100;i++)
{
j=getNumber(100);
sqlite3_bind_int(select, 1, j);
sqlite3_step(select);
start_time=getTime(); //get the starting time of fetching rows
col1 = sqlite3_column_int(select,0);
col2 = sqlite3_column_text(select,1);
col3 = sqlite3_column_text(select,2);
end_time = getTime();// getting the ending time of fetching rows
elapsed_time = end_time - start_time;
total_elapsed_time = total_elapsed_time + elapsed_time;
// printf("\n%d %s %s",col1,col2,col3);
sqlite3_reset(select);
}
sqlite3_finalize(select);
sqlite3_close(db);
printf(" \nRetrieved %d datas in %lf seconds,%lf selects/sec\n\n",
no_of_rows,total_elapsed_time,no_of_rows / total_elapsed_time);
return 0;
}
With Regards,
Anish Enos Mathew
The information contained in, or attached to, this e-mail, contains
confidential information and is intended solely for the use of the individual
or entity to whom they are addressed and is subject to legal privilege. If you
have received this e-mail in error you should notify the sender immediately by
reply e-mail, delete the message from your system and notify your system
manager. Please do not copy it for any purpose, or disclose its contents to any
other person. The views or opinions presented in this e-mail are solely those
of the author and do not necessarily represent those of the company. The
recipient should check this e-mail and any attachments for the presence of
viruses. The company accepts no liability for any damage caused, directly or
indirectly, by any virus transmitted in this email.
www.aztecsoft.com
[sqlite] problem with installing SQLite3.3.5
hello friends, following are the last few lines on giving make to install sqlite3.3.5 I'm using Fedora Core 1(kernel 2.4) any help please with regards shibu. . IDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -c ../sqlite- 3.3.5/src/tclsqlite.c gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -DHAVE_FDATASYNC=1 -I. -I../sqlite- 3.3.5/src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -c ../sqlite-3.3.5/src/tclsqlite.c -fPIC -DPIC -o .libs/tclsqlite.o ../sqlite-3.3.5/src/tclsqlite.c: In function `DbUpdateHandler': ../sqlite-3.3.5/src/tclsqlite.c:333: warning: passing arg 3 of `Tcl_ListObjAppendElement' makes pointer from integer without a cast ../sqlite-3.3.5 /src/tclsqlite.c: In function `tclSqlFunc': ../sqlite-3.3.5/src/tclsqlite.c:419: warning: passing arg 1 of `Tcl_NewByteArrayObj' discards qualifiers from pointer target type ../sqlite-3.3.5/src/tclsqlite.c:427: warning: assignment makes pointer from integer without a cast ../sqlite-3.3.5/src/tclsqlite.c:485: error: `Tcl_WideInt' undeclared (first use in this function) ../sqlite-3.3.5/src/tclsqlite.c:485: error: (Each undeclared identifier is reported only once ../sqlite-3.3.5/src/tclsqlite.c:485: error: for each function it appears in.) ../sqlite-3.3.5/src/tclsqlite.c:485: error: syntax error before "v" ../sqlite-3.3.5/src/tclsqlite.c:486: error: `v' undeclared (first use in this function) ../sqlite-3.3.5/src/tclsqlite.c: In function `DbObjCmd': ../sqlite-3.3.5/src/tclsqlite.c:685: warning: passing arg 3 of `Tcl_GetIndexFromObj' from incompatible pointer type ../sqlite-3.3.5/src/tclsqlite.c:1309: warning: passing arg 2 of `Tcl_GetVar2Ex' discards qualifiers from pointer target type ../sqlite-3.3.5/src/tclsqlite.c:1331: error: `Tcl_WideInt' undeclared (first use in this function) ../sqlite-3.3.5/src/tclsqlite.c:1331: error: syntax error before "v" ../sqlite-3.3.5/src/tclsqlite.c:1332: error: `v' undeclared (first use in this function) ../sqlite-3.3.5/src/tclsqlite.c:1382: warning: passing arg 1 of `Tcl_NewByteArrayObj' discards qualifiers from pointer target type ../sqlite-3.3.5/src/tclsqlite.c:1390: warning: assignment makes pointer from integer without a cast ../sqlite-3.3.5/src/tclsqlite.c:1838: warning: passing arg 3 of `Tcl_GetIndexFromObj' from incompatible pointer type ../sqlite-3.3.5/src/tclsqlite.c: In function `DbMain': ../sqlite-3.3.5/src/tclsqlite.c:2024: warning: passing arg 2 of `Tcl_CreateObjCommand' discards qualifiers from pointer target type make: *** [tclsqlite.lo] Error 1 [EMAIL PROTECTED] build]#
[sqlite] HELP for SQLite MEMORY test.
DEAR All ,
I want to use SQLite for our project, the main thing is that the
database contains millions of Records. So for the faster
operations on the db I want to use the SQLite as in-memory database.
I have compared the results of SQLite as Disk db and as Memory db
but I am not getting much difference. I am surprised that there
must be some difference between memory mode and disk mode.
Tests were run on 2.4GHz Sempron with 1GB of RAM and running
Windows XP + SP2 with all updates applied. Test 20 : 100
INSERTs CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));
INSERT INTO t1 VALUES(1,13153,'thirteen thousand one hundred fifty
three');
In Memory 37.51 Sec
DISK BASED 39.76 Sec
Disk Space consumed 61.6 MB
Test 21`: 300 INSERTs
In Memory 141.79 Sec
DISK BASED 111.906000 Sec
Disk Space consumed 185 MB
Test 22: 500 INSERTs
In Memory 279.42 Sec
DISK BASED 201.266000 Sec
Disk Space consumed 308 MB
Test 23: 1000 INSERTs
In Memory 784.797000 Sec
DISK BASED 399.846000 Sec
Disk Space consumed 617 MB
I am testing it in the following way
dwStart = GetTickCount();
db.execDML("begin transaction;");
db.execDML("CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100))
;") ; for( int i=1;i<=1000;i++) db.execDML("INSERT INTO t1
VALUES(1, 298361,'two hundred ninety eight thousand three hundred')
"); db.execDML("commit Transaction") ; dwStop = GetTickCount();
I think I am missing something or some necessary parameters. I
spent a lot of time to find out the problem. Please Guide me. I
will be really thankful.
Regards,
MANZOOR ILAHI
--
COMSATS Institute of Information Technology (http://www.ciit.edu.pk)
Re: AW: AW: [sqlite] Re: spatial sqlite anyone ?
Martin Pfeifle wrote: Hi, the baisc idea of the Relational R-tree is to map the hierarchical relationship between the R-tree nodes to a (father, son) relation where a b-tree is on father. You can the traverse the relation preferable using recursive SQL, if not available, you have to do it on your own. The Idea of the Relational Quadtree is to select the Quad numbers according to a space filling curve,e.g. z-values. that's what I had in mind, mapping a quadtree key to an integer key (I have seen something about morton code). However I still get a key set, what kind of sql may I use to avoid one by one select ? Do you have any pointer for that ? Then again you can use ordinary SQL. - Ursprüngliche Mail Von: Noel Frankinet <[EMAIL PROTECTED]> An: sqlite-users@sqlite.org Gesendet: Donnerstag, den 18. Mai 2006, 09:50:56 Uhr Betreff: Re: AW: [sqlite] Re: spatial sqlite anyone ? Martin Pfeifle wrote: Hi, I think the simplest solution would be to put a spatial index on top of the B-tree, that's what e.g. Oracle does in their Spatial Cartridge. Basically you store the index data in relations and index these relations by B-trees. In this case, you do not have to change the core code of SQLite. You can already do this now. Nevertheless, it would be nice, if SQlite supports somehow the "extensible indexing interface". So, you ask a spatial query and in the background the system uses the index data stored in relations. We have done a lot of research on that topic. To get acquainted to the basic idea, you might have a look at "The Paradigm of Relational Indexing: A Survey". In my opinion, a direct integration of a spatial index is of course preferable, but who is going to do that? Best Martin Not me, Thank you for the article, If I get the key from the quadtree/rtree index, what is the way to get the data while staying "on top" of sqlite ? If can see the following schema: select data according to normal sql rule (all the non spatial rules) then filter it spatailly (that's what I do now, simple bbox filtering, no indice). I would prefer the other way around, use spatial index, get a subset (a key set ?) then apply sql alphanumerical filter. Do you know a way to do that in sqlite ? Best regards - Ursprüngliche Mail Von: George Ionescu <[EMAIL PROTECTED]> An: sqlite-users@sqlite.org Gesendet: Donnerstag, den 18. Mai 2006, 08:28:39 Uhr Betreff: [sqlite] Re: spatial sqlite anyone ? Hello dear Noel, hello all sqlite users, a spatial extension for sqlite would be nice, although I think that replacing the indexing scheme (e.g. replace the current b-tree with a quad-tree or another spatial index) is alot of work. Just some questions / thoughts: 1. How would you handle indexing? Would you replace completely the btree with a spatial index (the hardest thing to do)? And if so, btree indexing would still be available or not? 2. Go for GEOS's STRTree. It's the fastest spatial index I could find (I did some research awhile ago on open-source solutions regarding spatial indexes). 3. You might want to take a look at SpatialIndex (http://u-foria.org/marioh/spatialindex/). The library is pretty clever designed and it supports RSTAR-tree, MVR-tree and TPR-tree. The problem I found with this library was that it was slower than GEOS at the time I last checked. One of the features I like is that it allows persisting the index. This might be an intermediate solution: you could store the index as a blob in the database. It would not be very efficient (as efficient as replacing indexing) but it would be a place to start. 4. Another nice library who might do the job is CGAL (http://www.cgal.org/). I didn't benchmarked it mostly because GEOS suited my needs just fine, but how knows, it might be better than the ones I mentioned. George. -- Noël Frankinet Gistek Software SA http://www.gistek.net
Re: [sqlite] Control the scheme layout
Hello Delf, Look at the PRAGMA commands: http://www.sqlite.org/pragma.html#schema HTH Philipp >> If I understand your question, the FAQ may help: >> >> http://sqlite.org/faq.html#q9 > > Oops, I did not see this point. But the FAQ does not explain how to retreive > the > field names from a table. It deals with the indices only and I think indice != > field. > > Thanks. > > -- > Delf >
Re: [sqlite] Proposal: sqlite3_column_rowid
Hello Dennis Cote, >This should probably be fixed when your proposal is implemented. I just pray and hope that it will ... Ralf
AW: AW: [sqlite] Re: spatial sqlite anyone ?
Hi, the baisc idea of the Relational R-tree is to map the hierarchical relationship between the R-tree nodes to a (father, son) relation where a b-tree is on father. You can the traverse the relation preferable using recursive SQL, if not available, you have to do it on your own. The Idea of the Relational Quadtree is to select the Quad numbers according to a space filling curve,e.g. z-values. Then again you can use ordinary SQL. - Ursprüngliche Mail Von: Noel Frankinet <[EMAIL PROTECTED]> An: sqlite-users@sqlite.org Gesendet: Donnerstag, den 18. Mai 2006, 09:50:56 Uhr Betreff: Re: AW: [sqlite] Re: spatial sqlite anyone ? Martin Pfeifle wrote: >Hi, >I think the simplest solution would be to put a spatial index on top of the >B-tree, that's what e.g. Oracle does in their Spatial Cartridge. >Basically you store the index data in relations and index these relations by >B-trees. >In this case, you do not have to change the core code of SQLite. You can >already do this now. >Nevertheless, it would be nice, if SQlite supports somehow the "extensible >indexing interface". >So, you ask a spatial query and in the background the system uses the index >data stored in relations. >We have done a lot of research on that topic. To get acquainted to the basic >idea, you might have a look at >"The Paradigm of Relational Indexing: A Survey". > >In my opinion, a direct integration of a spatial index is of course >preferable, but who is going to do that? > >Best Martin > > > Not me, Thank you for the article, If I get the key from the quadtree/rtree index, what is the way to get the data while staying "on top" of sqlite ? If can see the following schema: select data according to normal sql rule (all the non spatial rules) then filter it spatailly (that's what I do now, simple bbox filtering, no indice). I would prefer the other way around, use spatial index, get a subset (a key set ?) then apply sql alphanumerical filter. Do you know a way to do that in sqlite ? Best regards >- Ursprüngliche Mail >Von: George Ionescu <[EMAIL PROTECTED]> >An: sqlite-users@sqlite.org >Gesendet: Donnerstag, den 18. Mai 2006, 08:28:39 Uhr >Betreff: [sqlite] Re: spatial sqlite anyone ? > > >Hello dear Noel, >hello all sqlite users, > >a spatial extension for sqlite would be nice, although I think that >replacing the indexing scheme (e.g. replace the current b-tree with a >quad-tree or another spatial index) is alot of work. > >Just some questions / thoughts: > >1. How would you handle indexing? Would you replace completely the btree >with a spatial index (the hardest thing to do)? And if so, btree indexing >would still be available or not? > >2. Go for GEOS's STRTree. It's the fastest spatial index I could find (I did >some research awhile ago on open-source solutions regarding spatial >indexes). > >3. You might want to take a look at SpatialIndex >(http://u-foria.org/marioh/spatialindex/). The library is pretty clever >designed and it supports RSTAR-tree, MVR-tree and TPR-tree. The problem I >found with this library was that it was slower than GEOS at the time I last >checked. One of the features I like is that it allows persisting the index. >This might be an intermediate solution: you could store the index as a blob >in the database. It would not be very efficient (as efficient as replacing >indexing) but it would be a place to start. > >4. Another nice library who might do the job is CGAL (http://www.cgal.org/). >I didn't benchmarked it mostly because GEOS suited my needs just fine, but >how knows, it might be better than the ones I mentioned. > >George. > > > > -- Noël Frankinet Gistek Software SA http://www.gistek.net
Re: AW: [sqlite] Re: spatial sqlite anyone ?
Martin Pfeifle wrote: Hi, I think the simplest solution would be to put a spatial index on top of the B-tree, that's what e.g. Oracle does in their Spatial Cartridge. Basically you store the index data in relations and index these relations by B-trees. In this case, you do not have to change the core code of SQLite. You can already do this now. Nevertheless, it would be nice, if SQlite supports somehow the "extensible indexing interface". So, you ask a spatial query and in the background the system uses the index data stored in relations. We have done a lot of research on that topic. To get acquainted to the basic idea, you might have a look at "The Paradigm of Relational Indexing: A Survey". In my opinion, a direct integration of a spatial index is of course preferable, but who is going to do that? Best Martin Not me, Thank you for the article, If I get the key from the quadtree/rtree index, what is the way to get the data while staying "on top" of sqlite ? If can see the following schema: select data according to normal sql rule (all the non spatial rules) then filter it spatailly (that's what I do now, simple bbox filtering, no indice). I would prefer the other way around, use spatial index, get a subset (a key set ?) then apply sql alphanumerical filter. Do you know a way to do that in sqlite ? Best regards - Ursprüngliche Mail Von: George Ionescu <[EMAIL PROTECTED]> An: sqlite-users@sqlite.org Gesendet: Donnerstag, den 18. Mai 2006, 08:28:39 Uhr Betreff: [sqlite] Re: spatial sqlite anyone ? Hello dear Noel, hello all sqlite users, a spatial extension for sqlite would be nice, although I think that replacing the indexing scheme (e.g. replace the current b-tree with a quad-tree or another spatial index) is alot of work. Just some questions / thoughts: 1. How would you handle indexing? Would you replace completely the btree with a spatial index (the hardest thing to do)? And if so, btree indexing would still be available or not? 2. Go for GEOS's STRTree. It's the fastest spatial index I could find (I did some research awhile ago on open-source solutions regarding spatial indexes). 3. You might want to take a look at SpatialIndex (http://u-foria.org/marioh/spatialindex/). The library is pretty clever designed and it supports RSTAR-tree, MVR-tree and TPR-tree. The problem I found with this library was that it was slower than GEOS at the time I last checked. One of the features I like is that it allows persisting the index. This might be an intermediate solution: you could store the index as a blob in the database. It would not be very efficient (as efficient as replacing indexing) but it would be a place to start. 4. Another nice library who might do the job is CGAL (http://www.cgal.org/). I didn't benchmarked it mostly because GEOS suited my needs just fine, but how knows, it might be better than the ones I mentioned. George. -- Noël Frankinet Gistek Software SA http://www.gistek.net
Re: [sqlite] Re: spatial sqlite anyone ?
George Ionescu wrote: Hello dear Noel, hello all sqlite users, a spatial extension for sqlite would be nice, although I think that replacing the indexing scheme (e.g. replace the current b-tree with a quad-tree or another spatial index) is alot of work. Just some questions / thoughts: 1. How would you handle indexing? Would you replace completely the btree with a spatial index (the hardest thing to do)? And if so, btree indexing would still be available or not? 2. Go for GEOS's STRTree. It's the fastest spatial index I could find (I did some research awhile ago on open-source solutions regarding spatial indexes). 3. You might want to take a look at SpatialIndex (http://u-foria.org/marioh/spatialindex/). The library is pretty clever designed and it supports RSTAR-tree, MVR-tree and TPR-tree. The problem I found with this library was that it was slower than GEOS at the time I last checked. One of the features I like is that it allows persisting the index. This might be an intermediate solution: you could store the index as a blob in the database. It would not be very efficient (as efficient as replacing indexing) but it would be a place to start. 4. Another nice library who might do the job is CGAL (http://www.cgal.org/). I didn't benchmarked it mostly because GEOS suited my needs just fine, but how knows, it might be better than the ones I mentioned. George. Hello George, Thanks a lot for your input, I don't plan to replace the normal indexing, I plan to have a set of function to create a (memory ?) index. But how do I retrieve the data without doing a select where rowid = xxx ? Best regards -- Noël Frankinet Gistek Software SA http://www.gistek.net
AW: [sqlite] Re: spatial sqlite anyone ?
Hi, I think the simplest solution would be to put a spatial index on top of the B-tree, that's what e.g. Oracle does in their Spatial Cartridge. Basically you store the index data in relations and index these relations by B-trees. In this case, you do not have to change the core code of SQLite. You can already do this now. Nevertheless, it would be nice, if SQlite supports somehow the "extensible indexing interface". So, you ask a spatial query and in the background the system uses the index data stored in relations. We have done a lot of research on that topic. To get acquainted to the basic idea, you might have a look at "The Paradigm of Relational Indexing: A Survey". In my opinion, a direct integration of a spatial index is of course preferable, but who is going to do that? Best Martin - Ursprüngliche Mail Von: George Ionescu <[EMAIL PROTECTED]> An: sqlite-users@sqlite.org Gesendet: Donnerstag, den 18. Mai 2006, 08:28:39 Uhr Betreff: [sqlite] Re: spatial sqlite anyone ? Hello dear Noel, hello all sqlite users, a spatial extension for sqlite would be nice, although I think that replacing the indexing scheme (e.g. replace the current b-tree with a quad-tree or another spatial index) is alot of work. Just some questions / thoughts: 1. How would you handle indexing? Would you replace completely the btree with a spatial index (the hardest thing to do)? And if so, btree indexing would still be available or not? 2. Go for GEOS's STRTree. It's the fastest spatial index I could find (I did some research awhile ago on open-source solutions regarding spatial indexes). 3. You might want to take a look at SpatialIndex (http://u-foria.org/marioh/spatialindex/). The library is pretty clever designed and it supports RSTAR-tree, MVR-tree and TPR-tree. The problem I found with this library was that it was slower than GEOS at the time I last checked. One of the features I like is that it allows persisting the index. This might be an intermediate solution: you could store the index as a blob in the database. It would not be very efficient (as efficient as replacing indexing) but it would be a place to start. 4. Another nice library who might do the job is CGAL (http://www.cgal.org/). I didn't benchmarked it mostly because GEOS suited my needs just fine, but how knows, it might be better than the ones I mentioned. George.
