Re: [sqlite] WAKEUP !!! SOMEBODY IS DESTROYING THE WIKI PAGES
> Looks strange, many changes now, today, in short time. But I cannot > judge what is changed, some one should take a look at it. That may have been me When I saw there was the spam warning I had a look and discovered many pages were completely replaced with http://www.porn.link//whatever1000 I went back to the history, and selected the history state of the document before the porn spam and just copied the text and edited the porn versions You should see the porn ones as the previous history, and the current version and the version previous the last one should be the same. Not that I know anything about Wiki, but I like SQlite!
Re: Re[2]: [sqlite] To whom to inform on a bug?
I tried the data you mentioned in SQLite Explorer and the SQL statemen worked correctlyt: > select * from tResult where tex like '%ra%' returned the expected record perfectly Here is the output of the command line test SQLite version 3.3.3 Enter ".help" for instructions sqlite> .schema CREATE TABLE tResult (id INTEGER,tex STRING); sqlite> select * from tResult; 3229|...Oracle... sqlite> select * from tResult where tex like '%ra%'; 3229|...Oracle... > Script: > select * from tResult where tex like '%ra%' > result = 0 > > select tex from tResult where id = 3229 > tex = "...Oracle..." > > Soft on broblem: > sqlite3explorer, > and my soft
Re: [sqlite] String to numeric conversion
I'd say leave it as it is .. ie "12.34" is a string "12.34" is a number This way you have all the options you need and it's up to the coder to decide what they want. If " 12.34" is a string it's fine If " 12.34" is *NOT* a string, then TRIM it and pass it SQLite cleaned up If you change it, what happens if " 12.34" *IS* a string? It'll get converted to a number and you'll lose all those useful " "'s which may not be want you want. In the new case, you lose a choice (actually you lose real data) > Ticket #1662 (http://www.sqlite.org/cvstrac/tktview?tn=1662) > complains that SQLite is not converting strings into numbers > if the string contains leading spaces. This happens because > SQLite just hands the string to strtod() and strtod() does not > recognize numbers with leading spaces. (Actually, strtod is > not used - our own internal implementation gets called, but > it works about the same.)
Re: [sqlite] Slow query after reboot
We have the same issue, to get around it we fire a thread when the program starts, intelligently "touching" every table that the user is likely to access (As Michael Sizaki already mentioned a select count(last_column) from big_table; will do it) Since a user is very unlikely to run a program and start doing anything complex within the first 10 to 20 seconds, by the time they DO try and run a query, it is the "second time" the query has been run and this we get the benefit of the Windows XP cache and thus the fast speed > I have created a client application that is always running on a users > desktop. The application accepts user input and then uses SQLite to > perform a few simple queries against a single db file that contains 4 > tables. The performance is fantastic after the initial install and > normal usage. When the user leaves for the night and tries a query in > the morning, the app hangs for 20 seconds and then finally comes back > with the results. If the user then duplicates the query immediately > afterward, the query is almost instantaneous. In addition, if at any > point the user reboots the machine and then retries the query, the same > delay happens. The time is spent in the SQLiteDataReader.read() > method. Does anybody have any thoughts on why this is happening? > Thanks for any help. > >
Re: [sqlite] Problem with import
I created your schema and saved the single line you provided to a file and it all imported fine: sqlite> .nullvalue NULL sqlite> .separator ; sqlite> .import text.txt Cliente sqlite> select * from cliente; 1|2005-02-13 00:00:00|FRANCISCO EDNAN SABOIA PONTES |0|R|NEWTON PARENTE|1161|PRO XIMO AO COMETA|JANGURUSSU|FORTALEZA|CE||78928958334|94006024023|327419 66 |32769280 /34724873 - PUBLICO||1977-08-17 00:00:00|NAOCONVENIADO|NORMAL|SUPERVIS OR|2005-12-29 00:00:00|1|PRE - PAGO LANCAMENTO24 HORAS|2005-04-30 00:00:00|0||0| 0|NULL|.|CELULAR-MARIAROSIMEIRE|TIO- CELSO||8861-5632|3276- 1949||CONTRATO DE LOCACAO||0|.|NULL|NULL|0|NULL|NULL|.|.|||NULL|NULL|||NULL| NULL||2005 -12-29 00:00:00|1899-12-30 17:08:25|ADMINISTRAÃ+O|ANGELA|ALTERACAO sqlite> The raw data you provided contains text. The strings are not encased in quotes or anything like that so while the example record was fine, if any of your other strings have a ; in them, I imagine that'll break your import Oen thing, your line ".import datafile.txt Cliente ";" "NULL"" didn't work for me, I used separate commands, .nullvalue and .separator Not sure why, I don't regularly use the command line importer > PLEASE, read my e-mail, i really need help ;-) > > > I am trying to import data from a file using sqlite3 command line, and > the tcl bind. But I aways get error about the Number of Columns. Sqlite > always says that I am trying to put less columns than the number of > columns defined in the table.
Re: [sqlite] Performance problem with 3.2.7
Are you wrapping the transactions in between Begin/End Transactions? BEGIN TRANSACTION; INSERT INTO table (foo) VALUES (bar); INSERT INTO table (foo) VALUES (par); INSERT INTO table (foo) VALUES (tar); INSERT INTO table (foo) VALUES (far); .. INSERT INTO table (foo) VALUES (car); INSERT INTO table (foo) VALUES (jar); INSERT INTO table (foo) VALUES (mar); COMMIT TRANSACTION; Check out this document for more info http://www.sqlite.org/lang_transaction.html > I'm sure I must be doing something wrong. This is my first attempt at > working with SQLite.
Re: [sqlite] Optimal page size
> For what it is worth I did some trials in matching page size to the > underlying virtual memory page size and was surprised to find that I did > not measure any significant performance change. My guess is that it is > the structure of the data in your application which would be sensitive > to page size. > JS I will concur with this :) Our tests were done by three different people (Who all wrote their own test programs and had their own ideas about what would be the best way to test this) We discovered it made no difference at all in one case, and a small, yet noticeable difference in the best case. Someone already replied here saying a 15% speed increase is probably the best case. I agree that's probably the best result you'll get, but add a rider that you are unlikely to get that much. You will probably get better savings fine tuning your code and algorithms
Re: [sqlite] threading and win32
I'm using threads and SQLite on a Win32 system I can tell you that things may work 100% for ages, then one day, the moons align and the threads will fall over each others feet It may happen later, rather than sooner, and may not even happen in your lifetime, but it'll happen :) > I wasn't doing a very good job paying attention to the FAQ about > threading and SQLite when I wrote a bunch of code on win32. > > I'm sharing sqlite3 database handles between threads, although I am > ensuring that no two threads are executing against the sqlite3 > database handle at the same time. > > The threads are in fact interleaving, and the sqlite3 database handle > is indeed being shared between the two threads, and everything is > working as I'd like it to work... > > Is the warning about threads in the FAQ specifically apply to Linux > systems? Or can we pretty much say that my code is going to > eventually break on win32 and I'm lucky that it hasn't already? > > Wilson
Re: [sqlite] Speed Test Done !
> You can load sqlite into memory? > I do have 2GB of RAM. You can pass ":memory:" to the sqlite_open command to open a SQLite database in memory I think it is I imagine this will speed up your retrievals but without an index it will still be slower that it should be. With an index I imagine your test should have been sub 1 second to return that select statement
Re: [sqlite] any suggestion for the database file extension?mine is xxx.drh
I recommend not using SDB (on windows machines) SQLite DataBase sounded like a good name too :) That is the default extension for something called "Appfix Package" and in certain circumstances, Windows will automatically back the DB up every time it is changed thinking that a DLL or Application has changed so the System Restore can roll it back at a later date
Re: [sqlite] count(*) slow
> Interesting. But, with the above suggestion, every INSERT or DELETE > would slow down anyway as much as it would have were SQLite to maintain > meta information itself, no? > > . > > Hence, it might be worthwhile maintaining the meta information no > matter what... most of the folks won't ever notice it, and everyone > would marvel at how quickly COUNT(*) was returning the results. I cannot say I have ever used COUNT(*) in any program I have ever created I can say however, I tend to use INSERTS fairly often IMHO, not maintining the meta info (which speeds up a process everyone does at the expense of speed in a process less people do) seems like the correct decision
[sqlite] Maximum num of tables?
I looked around, the FAQ, Wiki and history of this list but didn't see any solid reply Does anyone know what the maximum number of tables a SQLite 3.0 database can hold? We've got one that has 11k or so and it seems fine, but I am worried we may hit a limit
Re: [sqlite] beginnings with sqlite
> When I launch the program with the Terminal, if I write: > ./quickStart test.db "select * from personne", all occurs well, I > receives the data contained in the table "personne". But if I write > ./quickStart test "select * from personne", therefore I the extention in > the name of the data base does not put, I receives the same error as > for XCode: SQL error: No such table: personne My guess is you now have 2 databases One called "test.db" containing the table "personne" And one called "test" containing no tables. SQlite doesn't care much about the file extension, it doesn't really have one it defaults to if you don't supply one Make sure your XCode loads "test.db" and see if that work
Re: [sqlite] Access Violations in sqlite3_step when in DLL
On 6 Apr 2005 at 3:13, Dan Kennedy wrote: Date sent: Wed, 6 Apr 2005 03:13:58 -0700 (PDT) From: Dan Kennedy <[EMAIL PROTECTED]> Subject:Re: [sqlite] Access Violations in sqlite3_step when in DLL To: sqlite-users@sqlite.org, [EMAIL PROTECTED] > > Exactly what are you calling sqlite3_free() on? Sorry, I am probably being a bit misleading there. That Free was on any error message returned from SQlite3_ErrMsg which we call if the result from SQLite3_Open isn't SQLITE_OK In theory it's never called, we never get any errors out of SQLIte_Open, it just AV's We call SQLite3_Close and pass the pointer that is returned from the open call. I havn't seen any errors on close. We see errors on Open, Step or Get_Table depending on what we use (ie, if we just Open and close, the AV occurs eventually on one of the opens. If we try Get_Table the AV will happen sooner and happen on a Get_Table) None of the code we used (and I even tried other componants and wrappers in case the one we were using is suspect) has ever caused any issues previously and we've been using it for months. Only when called from this plugin are we seeing problems. I've tried 2 wrappers and one componant and still get the issue
Re: [sqlite] Access Violations in sqlite3_step when in DLL
> Sounds like the calling type is different. Do you know if the EXE is > calling your stuff using stdcall or cdecl, and does it match your > functions? If you don't specify, your functions are cdecl. I've seen > this kind of thing when they don't match. It may work a couple of > times, but eventually something blows up. No, I am not actually sure what they are using... though they havn't supported delphi DLL's for long... normally they supported C dlls... hurm, that's an avenue I hadn't thought of. thanks... I made sure OUR call types where correct but never thought about what they were using Someone also mentioned ram. The machine(s) we are using seem stable for other processes but won't rule that out Looks like I am going debugging :)
[sqlite] Access Violations in sqlite3_step when in DLL
We have been using SQLite3 for a while now without issues. We are running a EXE calling a DLL (Which calls a DLL which eventually uses the SQLite functions) These two DLL's are both written in Delphi - in Windows (with a delphi wrapper that runs the SQLite DLL) This code has worked fine under fairly nasty conditions for a LONG time. Recently we tried to tie in with a third party program that calls one of our DLL's (which access the SQLite3 DB and returns a data set) [Note: We eventually started a fresh project for this DLL, so there was less of a chance legacy code was the issue] We don't control the EXE that calls our DLL, basically we export a couple of functions determined by the third party. The EXE that calls our DLL is written in C++ Now the issue is, randomly, but ALWAYS within a few dozen calls, the function sqlite3_step access violates.The first 10 calls (sqlite3_prepare -> sqlite3_step -> sqlite3_finalise basically) work fine, then maybe the 11th sqlite3_step AV's It can be the first, or the 40th but is guarenteed. We tried using sqlite3_get_table and it does the same thing, same sort of AV We tore out any SQLite3 code but kept every other line of code, returning some made up garbage data. The code worked fine without any SQlite3 calls, it seems entirely located in sqlite3_step or any call that uses it... Now having used SQLite3 for a while, I'm not convinced the issue is in the SQLite3 dll, but maybe the pointer populated by sqlite3_prepare is being trashed by one of the various parties... Has anyone had issues like this before? Or any ideas as to what we can try next?
Re: [sqlite] Database Version 2 or 3, can you query for it?
> Is it possible to query the database to see if it's 2.x or 3.x, we need to > know if the rowid's are 32 or 64 bit and we don't know what version we > might be running on? You can check the DB file header. The first 16 characters are "SQLite format 3\000" if it is version 3+ Check out http://www.sqlite.org/cvstrac/getfile/sqlite/src/btree.c for more file header information This reminds me of a related point I ment to ask about Is it possible to determine what the version number of the DLL (Windows) is? The DLL doesn't seem to contain any version information
Re: [sqlite] Re: sqlite performance variationin linux and windows
On 25 Feb 2005 at 9:38, Neelamegam Appadurai wrote: > Could anyone please give me reason for variation in performance > between linux and windows. How are you testing this performance? Do you have a program written in windows and another written for linux? If so, the code/mechanism of DB access may be the issue Is it the same phisical DB? Same schema? Same machine specs? Same amount of data? All those things could contribute.
Re: [sqlite] Relative efficiency of joins, subselects, and union/intersect
On 23 Feb 2005 at 18:36, Nathan Kurz wrote: > But maybe I'm not really understanding the advantages of the in-memory > database. Is it in some way inherently faster on lookups than just > setting SQLite to use a really large cache? Well the way I think of it is the if you don't need to keep the data for longer than the existance of the program in memory, you could use SQLite In-Memory If you want to persist the data beyond a single running of the application, you store it on Disk I don't really feel that a large cache and in-memory SQLite are really ment to be interchangable uses of the program, I feel they are kinda ment to do different things (Large cache=speed up disk access, In-memory=fast DB access but we don't want to keep the data) I certainly wouldn't use an in-memory SQLite DB if I was trying to speed up access of a Disk bound DB, lots of reading and writing there for little gain IMHO That's just what I thought when I saw it in the docs though :) Sure there is lots of fun stuff you could do if you had the memory
Re: [sqlite] How to I set pragma page_size ?
> I have a Delphi SQLite wrapper. > It can Open database, run sql-s, etc. > > But I don't know, how to I set page size, because when I do open in > database, the pragma is not working, and when I try to exec sql before > open database, the wrapper is say: database not opened... > > Anybody have an example in any language in any wrapper ? I want to see > how to you do ! Page size can only be set before any tables have been added to the database as far as I can tell The pragma command is: PRAGMA page_size=(Where is the size, 4096 or something) Perhaps a better idea would to look at some of the componant sets out there, Something like the http://www.aducom.com/sqlite/ will automatically create the DB if ity doesn exist and then you can make sure you ExecSQL the PRAGMA the first chance you have (If you run PRAGMA page_size without a setting, it will tell you what your size is. Note: If you set page size then close down DB without creating a table it is RESET back to 1024)
Re: [sqlite] exact copy of an existing Table
> SQLiters: > what would be the most efficient method for creating an exact copy of an > existing table with all the columns and data of the existing table? You could just do this from commandline in SQL like so: CREATE TABLE newTable AS SELECT * FROM oldTable; That makes a new tables, same schema with the same data (in the same database) No indexes however
Re: [sqlite] Speeding up your SQLite DB (Windows mostly)
> standard VCL stuff so there's a little overhead. But surely not as dramatic as > suggested. But there's one condition. Use transactions! Without them, you'll > never get a good performance. Perhaps not dramatic no, but I was getting to the point where I am setting DB page sizes to match OS Cluster sizes to get any drop of speed out Maybe what I was saying could have been better phrased, I wasn't attacking components directly, but really pointing out that for EVERY drop in speed, you could do well looking at sending well phrased SQL directly to the DB and accessing the returned pointers yourself or with a basic wrapper Components are definitely with their uses, though I am biased for the argument that if you don't REALLY know how it works at least at a reasonable low level, you may not be getting the most you can And transactions BOY HARDY there is something, never thought to mention them though since I felt it was kinda a "given" :) And as you said, its fun :)
Re: [sqlite] Speeding up your SQLite DB (Windows mostly)
> I wouldn't sell the BDE-style components short, as they may be useful > for some, if well-written and well-documented, but I like simple. Oh quite true, but in my case (and really what I was trying to get at in that monotribe) was to get the speediest database access, you'd be better server looking at accesing the DB natively. The biggest speed increase I found was replacing some old "IF Locate() THEN Update() ELSE Insert()" with INSERT OR REPLACE INTO sql which sped things up a LOT Componants out there now needed to roll their own Locates and Seeks which are generic, and not necessarly the fastest way of doing things > I'm a big fan of small and lightweight, and have chosen that route > myself. If you're talking to a bare-bones DLL interface, be careful > of memory leaks. Much of the "Delphi wrapper plus" code I've tried > has problems I've caught even as a rank beginner. Yeah, tell me about it :) I am going through ours as we speak (or rather was, got bored and saw I had new email) fixing it up, indenting, fixing the hints and warnings (hate those) and making sure it is bug free > Thanks for mentioning the bits and pieces. I'll keep them handy. welcome :) Someone told me to make a Wiki of it so I did, its somewhere now in the Wiki area
Re: [sqlite] Speeding up your SQLite DB (Windows mostly)
> Make a wiki page. Not a bad idea :) Done
[sqlite] Speeding up your SQLite DB (Windows mostly)
I've been posting a lot on speed and SQLite, just thought I'd dump the results and findings into one message including a nice example on how dumb you can get in the hopes no one else falls this low :) After several weeks of playing with SQlite3 we have finally gotton what we want out of a DB. For single user apps (I can't talk much about multi-user, that wasn't what we were doing) SQLite is fantastic, fast, easy to use and has great functionality. (hell, it's even fun, I wv INSERT OR REPLACE INTO) Here's the various things we discovered to speed up the system the most. This relates *mostly* to Windows and Delphi enviroments but may help others: 1) Talk to the SQLite functions directly if you can, ExecSQLing stuff down to the DB is better than trying to use some faked Locate or Seek function. Even if you have to change the way you code, I recommend it. >From what I experienced, the exported functions from the DLL were simple to use and did everything you wanted, though perhaps slightly differently from the Delphi DBE way some people may be used to I found a simple wrapper class worked better than trying to use a bunch of componants pretending to be the BDE, but of course, a lot depends on what you want to do and how confident you are using exported DLL functions 2) Indexes and DB structure are important. Fairly generic DB rule one I suppose, but this is an SQL DB, its VITAL you add the indexs you need, and even MORE important you *DON'T* add the indexs you don't need. Plan your DB before hand with n eye that everything has a function, if you don't use it, don't have it. There is more about this on the SQLite docs 3) The default cluster size for a Windows NTFS system seems to be 4096 bytes. Setting the SQLite database page size to the same size will speed up your database on systems where the cluster size is the same (Note, Linux cluster I believe to 1024 which is the default for new SQLite databases) Easiest way to tell your cluster size is to defragment your drive and analyze. It tells you in there To set the SQLite page size, create a new *EMPTY* database and do a PRAGMA page_size=4096; Now create your tables immedeatly (if you close down the SQLite commandline program and reopen the DB, the page size is reset to 1024). The page size must be set before the first table is created. Once that tables made, you can't change the size Typing PRAGMA page_size; will tell you what it is currently set at 4) SQLite doesn't support clustered indexes (simply, indexes that force the data in the database to be physically layed down in the SAME order as the index needs it to be in.) This means that if your index is sequential INTEGER, the records are physically layed out in the database in that INTEGERs order, 1 then 2 then 3. You can't make a Clustered index, but you CAN sort your data in order so that any historical data is ordered nicely. Of course, as the database matures, you lose that, but it helps Someone else posted this, and it is a nice example to use, so I will. If you have a table WIBBLE whose field KEY you want to access a lot, it would be nice if everything was in order. Using the command line tool, you can create a fake cluster by doing the following: create table wibble2 as select * from wibble; delete from wibble; insert into wibble select * from wibble2 order by key; drop table wibble2; 5) Ok, as a reward for reading this far, here is the dumb thing. Be *VERY, VERY* careful what you name your database, especially the extension For example, if you give all your databases the extension .sdb (SQLite Database, nice name hey?) you discover that the SDB extension is already associated with APPFIX PACKAGES. Now, here is the cute part, APPFIX is an executable/package that Windows XP recognizes, and it will, (emphasis mine) *ADD THE DATABASE TO THE SYSTEM RESTORE FUNCTIONALITY* This means, stay with me here, every time you write ANYTHING to the database, the Windows XP system thinks a bloody executable has changed and copies your ENTIRE 800 meg database to the system restore directory I recommend something like DB or DAT. Have fun people and thanks for all the help :)
Re: [sqlite] speedtest result is obsolete
> I think you people are missing the point here, the performance increase > you're seeing is all down to OS caching and will vary across different > ports. It's nothing to do with sqlite, and will affect every package. > > Therefore the only way to fairly compare mysql/postgress/sqlite is to make > sure the machine is cleanly booted, before running any tests. > > ( well and then maybe run the tests twice in succession, so the caching > effect can be taken into account ) This was what we found, and we tested dozens of databases and home grown systems Some tools did better with initial cacheing and some better once cached All our speed tests started on a clean boot, then we tested several iterations. Even delphi wrappers for the same back end (in one case SQLite) differed in the speed they worked at. Has taken months to get to this point where we are finally happy with the results we are getting from a DB
Re: [sqlite] speedtest result is obsolete
> Another trick you can pull is to create an index that > contains every column in the table with the cluster index > columns occuring first. That will double the size of your > database. But when SQLite can get all of the information it > needs out of the index it does not bother to consult the > table itself. So the index will always stay clustered. Interesting idea. Will check that out I did the clustering trick (insert into wibble select * from wibble2 order by key1, key2;) which basically sorted the table in the order the query wants it, and there was a noticable speed increase Sure, the table will slowly, as data is added, become less and less "clustered" but frankly it's something I am willing to live with considering the user would have to use the program for 10+ years before the data they added would compare with the data that's already there Now the slowest points of the operation aren't DB access and retrieval any more, but application+parseing of the data once gathered Way to go SQLite :)
Re: [sqlite] speedtest result is obsolete
> Doing a keyed search is no guarantee that you won't touch *every* single > page in the table, if the rows are inserted in random order. Try this: ...cut... > Assuming key is the key field you want, the records will be inserted into > wibble in key order. Selecting by key will then touch the least number of > pages, speeding up the select. Ahhh excellent idea, this seems similar to a clustered index, where the data is actually stored on disk in order. That is actually what we wanted, but SQLite didn't seem to support those sorts of index > Why? Does you program require the machine to be rebooted before use? > > I'm not trying to be facetious, but your test seem very invalid without > further explanation. No perfectly understandable. This is not a test for SQLite in particular, but was an issue I discovered a long time ago when doing speed tests Speed tests on any system. SQLite, basic files whatever, are *SLOWER* on the first time you run them due mainly to HDD caching, and whatever other caching the program does with the data. We would get lovely 20k per second record update on stuff and then on fresh reboot discover we dropped to 20 records per second for first 2000 records Unfortunatly, our program has the following requirement, user turns on machine, user immedeatly does some NASTY damn data retrieval or update, closes program and turns off machine This first retrival is our issue, and is slow. What I have working now seems to be good however. We did several things: 1) Set the page size of the database to 4096 which matches most peoples NTFS partions and has sped up the initial access nicely. Wont work for everyone but most I think 2) spawn several threads as the program is opened, atrifically caching the data before the user has a chance to push any buttons and wonder why they take 2 seconds to respond. Gonna try your ordering idea, that will help IMMENSLY for the initial DB though I can see how it would slowly fall out of order as the user updates. Frankly, SQLite has been the closest I've seen to resolve this issue (well it DOES actually solve the issue). Love this DB :) Personally I think it asking a bit much (I mean 20 million rows on some guys Windows 95box? and you want it HOW fast?) Thanks
Re: [sqlite] speedtest result is obsolete
> I would be interested to know the results for very large data sets. > Indications on the list have been that performance suffers when the number > of records gets very big (> 1 million), possibly due to using an internal > sort. I must say, with a 2+ million row data set, we aren't getting anywhere near these sort of speed results. Probably in the order of 10 times slower if not much, much more. However, I havn't written any generic tools to reconfirm this, rather this is based on the results our current SQLite3 project is spitting back
Re: [sqlite] still struggling with "Database schema has changed" errors
> I am using SQLite 3.0.8 in a Win32 threaded > environment. > > I keep getting random "Database schema has changed" > errors even though I am using thread local > storage to make sure sqlite3_open() gets called > on each thread and a there is a sqlite3* per thread. > > Has anyone had any luck with resolving SQLITE_SCHEMA > errors in a threaded environment? The docs for SQLite mention that you need to specifically compile the libs (DLL) with the THREADSAFE preprocessor macro set to 1 for you to be able to use SQLite in threads Not sure what the DLL's on the web site are compiled with, perhaps that's the issue?
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> Did you try www.aducom.com/sqlite, you can create it dynamically in your > code or use it as VCL-component. > It also works in Delphi6 > > Use the Database component and the Query component, you get a > tdataset-alike resultset. That's the one we were using initially that seemed slow. I tried the version for SQLite 3 but I don't think it is completely working at this point. It seemed as if they where still converting from the old style 2.8 callbacks Eventually we discovered a wrapper that, while it doesn't have the same functionality as the Aducom stuff, it was faster so I stopped trying to work out what I was doing wrong and swopped over The wrappers don't even have a decent name :) they were from http://www.itwriting.com/sqlitesimple.php off the SQLite wrappers page. Work fine so far
RE: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
Tried to compile your wrapper this morning with no luck The code seems to be written with Delphi 7 or higher? We are still using delphi 6 which doesn't have the SysUtils data structure TFormatSettings, and the functions FormatDateTime have different declarations (only 2 paramaters) Those where the only issues I could see at a glacne, may try and hack that out at a later date when I get more time > out of curiocity, can you give a try to my delphi wrappers? > http://www.sqlite.org/contrib > (not Tdataset replacement, though, but they should be quite fast for this > reason. also try the "serverCursor" setting, which allows you to step thorugh > the result set one at a time, meaning there is no double-buffering required: > > rs:=db.createStatement; > rs.serverCursor:=true; > rs.open > while not rs.eof do.. > > let me know how they fare speed-wise, as I have not tested any other > wrappers..
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
No, the DLL wasn't the issue (heck the entire program is a DLL anyway and that's worked well for ages) We eventually discoverd through profiling the Delphi componants we had started using were the slow point, tossed them, and tried someone elses version (some stuff adapted by someone called Tim A.) and they work MUCH better It seems the chokepoints where in the wrapper code itself, perhaps in the mechanisms calling the various SQLite functions. I'd dig deeper but frankly the wrapper we have now lets me get to the bare bones easier. I am pleasently surprised about SQLites power now, for a large single user DB it works nicely. Sure, when we tossed a further 8 million rows into the DB it is slows down, but still ~1 secondish for a 3k select statement Still trying to wrench every piece of speed I can from the DB, and some functions don't seem to be working? (or I am misunderstanding them) For example: CREATE UNIQUE INDEX pk ON myTable (Field1, Field2) ON CONFLICT REPLACE; runs but doesn't create the ON CONFLICT part. When you examine the schema you see : CREATE UNIQUE INDEX pk ON myTable (Field1, Field2); Also any INSERT statement evaluate as if the default is still FAIL Still not really required, INSERT OR REPLACE works well. Was hoping setting the default CONFLICT handeler may speed things up a bit > I wouldn't think DLL calling overhead would be significant > when dealing with things as slow (relatively) as a database. > > Is it really necessary for it to be a DLL? > You might be able to statically link it and remove that overhead. > Are you using COM or ActiveX to call it? If I remember right > they had a lot more overhead than a vanilla DLL. > > Sounds like a job for the profiler!
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> H The fact that the freelist size is different > suggests either a bug in sqlite3_analyzer or a corrupt > database file. What does "PRAGMA integrity_check" say? It returned a batch of Page not used (from about 4 to 48 I think).\ That was it > What OS did you say you are using? Windows XP professional on both test machines We are currently examining the componants we are using to access SQLite3. We are accessing the DLL more directly now through exported DLL functions and getting better speeds
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> > CREATE INDEX myMyIndex ON myTable (myKey); > > CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]); > > CREATE INDEX myNumOneIndex ON myTable (NumOne); > > Why did you chose these indexes? > Updates and Inserts are faster with fewer indices. > Perhaps they can be reduces? > > You do have a rather large database, you'll have to be pretty > aggressive to get good performance from it. Yeah, originally there was (and should be) only the one UNIQUE index. I added the other two during a test and when someone here asked about the schema they were still in They have been dropped now, and the single unique index is the only one remaining. It is about the bare minimum I think I can get away with
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> Out of curiousity, have you tried doing a plain INSERT instead of INSERT > OR REPLACE INTO? > Since you have no unique fields in your table, I'm not exactly sure how > or why you might get an > INSERT failure, but perhaps Sqlite is doing some sort of data comparison > on each insert. We have one multi-field unique index, > CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]); Interestingly enough, on further tests (one developer forgot to create the index and was duplicating their data every time they ran a test) we discovered that the INSERT OR REPLACE INTO was not really much slower when it was doing it's job correctly He was getting 6 million records inserted into the DB in about 16 minutes. With the index added it dropped to about 18 but at least it was doing the right thing Thats still only about 2500 records a second, but getting much better!
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> The myMyIndex will only slow you down. myNumOneIndex > will help if you have queries that use the numOne column. Hurm, yes that makes sense. it wasn't supposed to be there, but was added just incase it helped, which it didn't > What is the output from sqlite3_analyzer? Unfortunatly didn't space well :( /** Disk-Space Utilization Report For test.sdb *** As of 2005-Feb-02 22:13:52 Page size in bytes 1024 Pages in the whole file (measured) 282059 Pages in the whole file (calculated).. 282014.0 Pages that store data. 282014.099.984% Pages on the freelist (per header) 00.0% Pages on the freelist (calculated) 45.0 0.016% Number of tables in the database.. 2 Number of indices. 3 Number of named indices... 3 Automatically generated indices... 0 Size of the file in bytes. 288828416 Bytes of user payload stored.. 135341808 46.9% *** Page counts for all tables with their indices myTable 282013.099.984% SQLITE_MASTER. 1.0 0.000% *** All tables and indices *** Percentage of total database.. 99.984% Number of entries. 10659384.0 Bytes of storage consumed. 288782336.0 Bytes of payload.. 213059020.0 73.8% Average payload per entry. 19.9879298841 Average unused bytes per entry 2.90023344689 Average fanout 94.18 Maximum payload per entry. 262 Entries that use overflow. 0.0 0.0% Index pages used.. 1763.0 Primary pages used 280251.0 Overflow pages used... 0.0 Total pages used.. 282014.0 Unused bytes on index pages... 253006.014.0% Unused bytes on primary pages. 30661696.0 10.7% Unused bytes on overflow pages 0.0 Unused bytes on all pages. 30914702.0 10.7% *** All tables *** Percentage of total database.. 59.5% Number of entries. 2664849.0 Bytes of storage consumed. 171823104.0 Bytes of payload.. 135342313.0 78.8% Average payload per entry. 50.7879857358 Average unused bytes per entry 6.38924982241 Average fanout 94.18 Maximum payload per entry. 262 Entries that use overflow. 0.0 0.0% Index pages used.. 1763.0 Primary pages used 166033.0 Overflow pages used... 0.0 Total pages used.. 167796.0 Unused bytes on index pages... 253006.014.0% Unused bytes on primary pages. 16773380.0 9.9% Unused bytes on overflow pages 0.0 Unused bytes on all pages. 17026386.0 9.9% *** All indices ** Percentage of total database.. 40.5% Number of entries. 7994535.0 Bytes of storage consumed. 116959232.0 Bytes of payload.. 77716707.0 66.4% Average payload per entry. 9.72122918969 Average unused bytes per entry 1.73722624268 Maximum payload per entry. 13 Entries that use overflow. 0.0 0.0% Primary pages used 114218.0 Overflow pages used... 0.0 Total pages used.. 114218.0 Unused bytes on primary pages. 13888316.0 11.9% Unused bytes on overflow pages 0.0 Unused bytes on all pages. 13888316.0 11.9% *** Table myTable and all its indices * Percentage of total database.. 99.984% Number of entries. 10659380.0 Bytes of storage consumed. 288781312.0 Bytes of payload.. 213058515.0 73.8% Average payload per entry. 19.9878900086 Average unused bytes per entry 2.90019757247 Average fanout 94.18 Maximum payload per entry. 61 Entries that use overflow. 0.0 0.0% Index pages used.. 1763.0 Primary pages used 280250.0 Overflow pages used... 0.0 Total pages used.. 282013.0 Unused bytes on index pages... 253006.014.0% Unused bytes on primary pages. 30661302.0 10.7% Unused bytes on overflow pages 0.0 Unused bytes on all pages. 30914308.0 10.7% *** Table myTable w/o any indices
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
Ok I hadn't toyed with those. Just tried it, the speed for queries hasn't increased much, but a little However, I started getting hard drive thrashes for 10+ seconds from time to time. Will investigate the cahce further to see what suits this app > Did you try increasing the page cache size. Your data set is very big. > > pragma page_cache = 2; > > This should at least improve the speed for queries.
Re: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?
> What speed were you expecting? > Are you comparing it to another database? If so what are the results for that > database? Anything better than what we got. The results are the worst we have gotton from any DB or any self rolled data system (Jet is better, het shouldn't be better) After more tests, it is dipping to 10 records per second update time. Based on the speed showen on the web site, I was expecting to at least get 1000 records a second updating and somewhere above 10k when selecting I am assuming we MUST have mucked something up
[sqlite] Not getting the speed I think is possoble. Basic select statment slow?
I've got a 6 million row DB in SQLite 3, but getting... odd results which don't match up with the speed tests I've seen The statement: SELECT * FROM myTable WHERE myKey=1000 takes between 1 second to 4 or 5 on spikes. The returned result set is ~2000 records. I havn't seen more than 2000 recs/second usually less Similarly, the query (A basic APPEND or INSERT) INSERT OR REPLACE INTO myTable (myKey, NumOne, NumTwo, NumThree, NumFour, NumFive, NumSix, NumSeven) VALUES (1000, 1, 2, 3, 4, 5, 6, 7) is doing at MOST about 300 records per second and at worst 100 a second. I have about 3000 inserts/updates all wrapped inside a single Transaction unless doing a complete population of the DB in which case it is batched but still all wrapped in transactions The schema is VERY basic: CREATE TABLE [myTable] ( [myKey] [bigint] NULL , [NumOne] [int] NULL , [NumTwo] [real] NULL , [NumThree] [real] NULL , [NumFour] [real] NULL , [NumFive] [real] NULL , [NumSix] [float] NULL , [NumSeven] [float] NULL ); CREATE INDEX myMyIndex ON myTable (myKey); CREATE UNIQUE INDEX [pkPrimary] ON [myTable] ([myKey], [NumOne]); CREATE INDEX myNumOneIndex ON myTable (NumOne); Now initially I didn't have anything except the UNIQUE index, though adding the second two hasn't made any difference once way or the other Personalyl I'd LOVE for that UNIQUE index to be a clustered index (it was elsewhere) but that just doesn't seem to be an option Any ideas where I am going wrong here? Or are these the numbers I am expected to see? (Note: I am using transactions in case I didn't make that clear, I am also doing this in Delphi using the open source Aducom.nl componants, but at the raw end it seems their code is mostly fairly close to the bare bones of the DLL exported functions. Doesn't seem to be an issue there but who knows)
