Re: [sqlite] Issue with Session while using SQLLITE database
Am 10.04.2012 11:17, schrieb Dorababu Meka: > Hi I am using SQLLITE database in my .net application. Unfortunately if I > perform any operation like Insert and performing other operations like > getting data from database my Session which was assigned before is getting * > NULL.* Is this a bug or what, have you fixed this in later versions, if so > please let me know. > > I am using Visual Studio 2010.. > We have been using System.Data.SQLite for many years, without major bugs. So its unlikely you found one. Instead the way you are using the Framework might be somehow erroneous. If you ask these kind of questions, please post some lines of relevant code. With such an unspecific information, nobody is able to help you. Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Profiler
Hello, is anyone aware of a profiler for SQLite? I know the "Explain Query Plan", but this is too simple for what I'm looking for. We 've been using profilers for Sybase and MySQL, where you can see a lot more: index usage, number of page reads, returning rows and time - for the whole query and for every subquery. Is such a tool available or in development? I really would pay money for that. Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backup API handling of corrupt source DB
Am 07.03.2012 13:21, schrieb Marcus Grimm: > > On 07.03.2012 13:13, Eduardo Morras wrote: >> At 12:22 07/03/2012, you wrote: >>> Dear list, >>> >>> I'm using the backup api to frequently backup >>> a running sqlite database. >>> >>> I'm wondering if the backup API is able to detect a corrupt >>> database or will it simply also backup a corrupt DB ? >>> >>> I evaluating the need to issue a (timeconsuming) "pragma >>> integrity_check" prior >>> running the backup to avoid that a backup will be overwritten >>> with an invalid database. >> >> You can do the backup and after that do an integrity check on the >> backup. Surely you're backing up on a different >> server, don't you? If the back up pass the integrity check it a real >> backup, if not, launch a warning. > > Yeah, that's a good idea. > Ohh boy, why I didn't think about that my self ? :-) In this process, you have to prevent that you overwrite your last "working" backup! Which database will you use, if you realize, that the backup is a corrupt database? On a webserver you should have enough space to make additional copies. In an embedded environment, this could be difficult. Thomas > > Thanks > > Marcus > >> >>> Sure I could try to simulate that, but probably somebody here >>> knows the answer. >>> >>> Thank you. >>> >>> Marcus >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Slower access to RowId than primary key
Hello folks, I have a database of roads in a SpatiaLite database. It consist of a road-table with ~30 columns, including a BLOB-column for the geometry content. The performance critical operation is a select from a table with ~2 Mio. records, where I try to read a subset of roads from a DVD and insert it into a database on the harddrive. The select looks like this: insert into main.roads select * from external.roads where rowid in (select pkid from external.roads_way where MBRWithin()); Originally the road-table was defined as: create table roads (WayID UNSIGNED INTEGER NOT NULL PRIMARY KEY, , WAY LINESTRING); The page size is 32K, the cache size is 2 pages. On my search for a speedup I came across the SQLite-documentation about "ROWIDs and the INTEGER PRIMARY KEY" on http://www.sqlite.org/lang_createtable.html#rowid There it says: "Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value." Applied to my database, I realized, that the data type of WayID results in a separate index for the primary key. I changed that as suggested to get a performance increase to the following creation statement: create table roads (WayID INTEGER PRIMARY KEY, , WAY LINESTRING); Suddenly, the database is noticeably smaller, most likely because WayID is the RowID now and needs no further index. Surprisingly, the speed for the select statement above did not increase! The time for the same select-operation doubled! How can this happen? During the test, I had the feeling that in scenario 2 the DVD drive was working like crazy. It sounded like the reading head was repositioning for every single byte. In scenario1 the DVD was read smoothly, like it was reading huge chunks. And dont worry: I tried to eliminate the influence of the drive cache by removing und reinserting the DVD before every test. I did the test several times, so the figures should be real. Anyone who can explain this or has an idea? TeDe ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Drop Foreign Key
Hello, due serious changes in the table layout of an existing database, I need to remove a foreign key. I could not find any SQL command in the docs, how to do that. But this must be possibe, since SQLiteExpert supports this. Could someone give me a hint? Thanks, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.2
In one of the previous post someone said, that WindowsCE is not supported yet. Especially one file function of the WAL mode seems to be not compliant. When will a compatible version be available? Thanks, TeDe Am 24.08.2010 03:01, schrieb Richard Hipp: > SQLite version 3.7.2 is now available on the SQLite website: > http://www.sqlite.org/ > > SQLite version 3.7.2 fixes a single bug that was discovered just hours after > the release of 3.7.1. The bug can result in corruption of the database > free-list after an incremental vacuum. The bug had nothing whatsoever to do > with SQLite version 3.7.1 or any other recent release. The problem had been > in the code for over a year, since version 3.6.16. The discovery of the > problem so soon after the release of version 3.7.1 was purely coincidental. > > The bug fixed in 3.7.2 can result in database corruption. However, the > corruption caused by this bug can almost always be fixed simply by running > VACUUM on the database. And the corruption will only occur in an > incrementally vacuumed database which at some point in time contains > hundreds of unused pages which are slowly released back to the operating > system by multiple calls to the incremental_vacuum PRAGMA. Even then, one > must be particularly unlucky to hit the right combination of freed pages in > order to trigger the bug. Hence the problem is quite obscure and was not > noticed for over a year. > > Hundreds of lines of code where changed for version 3.7.2, but most of those > changes were to test procedures. As is the custom with SQLite, not only was > the specific bug fixed, but new tests where put in place to detect and > prevent similar kinds of bugs elsewhere in the code. We believe that one > should not just fix the bug, but also fix the process that generated the > bug. The only 4 working lines of code were changed for version 3.7.2: > > http://www.sqlite.org/src/fdiff?v1=2dff4076d3c994dc&v2=5047fb303cdf6806 > > Special thanks to Filip Navara for finding and reporting the problem with > incremental vacuum. > > Please report any other problems to the sqlite-users@sqlite.org mailing > list, or directly to me. Thanks. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reserve database pages
Am 12.08.2010 13:16, schrieb Martin.Engelschalk: > Am 12.08.2010 13:04, schrieb TeDe: >> Am 12.08.2010 12:16, schrieb Martin.Engelschalk: >>> Am 12.08.2010 12:08, schrieb TeDe: >>>>Hello, >>>> >>>> I want to import a big subset of data from one database to a new one. I >>>> attach the two databases together and use >>>> >>>> insert into customers select * from source.customers where name LIKE 'x%' >>>> >>>> I can approximately calculate, how big the new database will grow. Is >>>> there a way to tell SQLite to reserve an inital space or numer of pages >>>> instead of letting the database file grow again and again? I'm looking >>>> for a way to speed up the import. >>>> >>> Hello Thomas, >>> >>> I create a dummy table with a blob field and fill it with a very large >>> empty blob. Then I drop the table. The empty pages remain behind an can >>> the be used by the followimng inserts. >>> >> Hello Martin, >> >> that sounds like a good idea. Do you use it to have enough space for >> later operations or because you want to speed up the inserts? >> How big is the space you reserve by this and how much faster is it? I >> presume, you have to allocate quite big BLOBs. >> >> Best regards, >> >> Thomas > Hello Thomas, > > My primary goal was not a speedy insert but to avoid fragmentation of > the resulting database file, which slows down later access to the file. > So, this is not exactly on topic of your post. > I did not measure the changes in speed of the insert. However, later > selects, which in my case use practically all the data in the database, > speed up on the order of 20%. > I have to admit that this does not seem like much. However, my customer > for some reason did not like the fragmentation and insisted on a solution. Is that because of the lower fragmentation? Anyway, I consider 20% very good for the little effort you have to make, especially when you can re-use the code. If you have a well designed database and a well written application, it could become a hard job to squeeze out another 20% if you need them. Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reserve database pages
Hello Pawel, you made some good points. I'm still in the stage of evaluation, I don't claim to know, its faster. But I saw that behavior on a filemanger: when you copy a large file, it immediately reseveres the whole space. The same with STL vectors: initializing it with a size is faster than growing it element by element. Therefor my question, if there is such a possibility. > I wouldn't be so sure about that. Did anybody make any measurements? > 1) I don't know where do you think CPU cycles are saved but you > definitely get some more CPU cycles in maintaining free-list of pages > which will never be there if database grows page-by-page. Here I (or we) think of the cycles the system needs when the small niche of the initial database is exhausted and it has to look for another free block on the filesystem. If you can tell the system in advance, how big the niche has to be, it saves some time. > 2) Even if you use Martin's technique by creating some big blob why do > you think that SQLite will grow database file by necessary amount of > pages at once instead of page-by-page? And is there something in > SQLite's code that writes several sequential pages in one OS call > instead of writing them page-by-page? Thats indeed very uncertain. Here we have to look, how SQLite handles this. If you have a transaction, SQLite could look, how much more space is needed and preallocate this. > I can agree that making file grow in one big piece instead of many > small ones seems to compact most IO into one call instead of many. But > will it be somehow faster? I doubt it. And bear in mind that all your > efforts can be immediately trashed away by another process reading > some big file(s) which will consume all OS file cache, so OS will have > to re-read your database file later when you actually need it. This > way I guess overall number of IO operations on the system will only > increase... That might be. We just can prove it one way: measure, measure, measure. I also don't expect huge performance increases, but if it is some percent with little effort.. It could be worth it. Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reserve database pages
Am 12.08.2010 12:16, schrieb Martin.Engelschalk: > > Am 12.08.2010 12:08, schrieb TeDe: >> Hello, >> >> I want to import a big subset of data from one database to a new one. I >> attach the two databases together and use >> >> insert into customers select * from source.customers where name LIKE 'x%' >> >> I can approximately calculate, how big the new database will grow. Is >> there a way to tell SQLite to reserve an inital space or numer of pages >> instead of letting the database file grow again and again? I'm looking >> for a way to speed up the import. >> > Hello Thomas, > > I create a dummy table with a blob field and fill it with a very large > empty blob. Then I drop the table. The empty pages remain behind an can > the be used by the followimng inserts. > Hello Martin, that sounds like a good idea. Do you use it to have enough space for later operations or because you want to speed up the inserts? How big is the space you reserve by this and how much faster is it? I presume, you have to allocate quite big BLOBs. Best regards, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reserve database pages
Hello, I want to import a big subset of data from one database to a new one. I attach the two databases together and use insert into customers select * from source.customers where name LIKE 'x%' I can approximately calculate, how big the new database will grow. Is there a way to tell SQLite to reserve an inital space or numer of pages instead of letting the database file grow again and again? I'm looking for a way to speed up the import. Thanks in advance, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Way to close statement without finalize
Hello Folks,
I'm looking for a way to close a statement, but do not want to finalize
it. I have 2 statement I would like to execute in a loop. Since I want
to reuse them, I do not want to finalize them. Unfortunately
sqlite3_reset() does not release the lock on the database. So I need a
function that does something between finalize() and reset().
Here is some pseudo code.
prepare(stmt1);
prepare(stmt2);
for(int i = 0; i < anzahl; i++){
bind_value(stmt1);
step(stmt1);
close(stmt1);
bind_value(stmt2);
step(stmt2);
close(stmt2);
}
finalize(stmt1);
finalize(stmt2);
Can somebody help me with this? Is there such a function? Or do I have
to open 2 separate connections (in the same thread)?
Best regards,
Thomas
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite profiler
mcnamaragio schrieb: > Hello, > > Would anyone be interested in sqlite profiler? If yes what features would > you expect from it? > > Thank you. > If you mean, that every sub-query and its execution-time is displayed: yes, definitely! I would even pay money for it. Features I would expect: - execution-time of every sub-query - records affected by every where-clause - indexes used - maybe a small graphical interface? - tabbed interface to compare different versions of a query during optimization What are your plans? Best Regards, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
