Re: [sqlite] legacy_file_format
That's the problem! Thank you for pointing it out. /m [EMAIL PROTECTED] wrote: Version 3.3.7 creates (by default) a database file that can be read or written by any version of SQLite back to version 3.0.0. There is no need to do the "PRAGMA legacy_file_format=ON". That is now the default. But you are trying to read the database with SQLite version 2.8.17, which is earlier (and vastly different) from version 3.0.0. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is it possible to call sqlite_exec() from within the callback made inside sqlite_exec()
Kevin Stewart <[EMAIL PROTECTED]> wrote: > I am still curious. Generally speaking, can one access > the database inside the callback from select via sqlite_exec()? > Yes. SQLite is reentrant. SQLite uses this reentrancy internally. There are occasions when you are running an SQL statement that SQLite while invoke sqlite3_exec() as part of the processing for that SQL statement. This happens, for example, when you are running an ALTER TABLE or a CREATE TABLE command. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Query Optimisation Question?
Hi I have found that using an in clause with a subquery can be twice as fast as a straght join. Can enyone explain the logic of this to me? I am curious to understand it so I can optimise other queries for better performance. I have included the queries below: OT_TARGETS has 20 rows for regionid = 1 OT_PRODUCTS has 201 rows for regionid = 1 select distinct RegionID, ProductID, ProductName, ProductShortName, ProductRank from OT_PRODUCTS p Where RegionID = 1 and ProductID in (select distinct productid from ot_targets where regionid = 1) order by ProductRank, ProductName, ProductID; 2-3 seconds slower than above: select distinct t.RegionID, t.ProductID, p.ProductName, p.ProductShortName, p.ProductRank from OT_TARGETS t, OT_PRODUCTS p Where t.ProductID = p.ProductID and t.RegionID = p.RegionID and t.RegionID = 1 order by p.ProductRank, p.ProductName, p.ProductID; Thanks,
[sqlite] Index Optimisation
Hi I have a very simple database with three tables. I require joining the three tables together. I am trying to optmise a query which joins all three and sorts the results. I cannot tell if sqlite uses my indeces or not. Is there a way to tell what indexes are used in a query ? I join by the first PK, but the sort is by additonal columns. How can I go about optimising this query? Any ideas? I just cant see how to tell if changes are helping espeicially with disk caching etc... My tables are like this, and the query follows: Products: Region: PK Integer Product: PK Integer Rank: TEXT name: TEXT Customers: Region: PK Integer Customer: pK Integer; Rank: TEXT name: TEXT Targets: Region: PK Integer Customer: pK Integer; Product: PK Integer Select * from Targets t, Customers c, Products p where t.Region = c.REgion and t.Customer = c.Customer and T.REgion = p.region and t.product = p.product and t.region = 1 order by c.rank, c.name, c.customer, p.rank, p.name, p.product Thanks,
Re: [sqlite] Is it possible to call sqlite_exec() from within the callback made inside sqlite_exec()
On 9/15/06, Kevin Stewart <[EMAIL PROTECTED]> wrote: Well, its a little more complicated than that. I don't want the whole table, just the set of records that matched the initial search criteria in the select. But thanks for your response. I did a little redesigning Add a where clause to the select statement that is the source for the insert. of the database schema and the way my code handles things and I came up with a better solution anyway. Maybe I shouldn't have jumped the gun and submitted this questionhowever I am still curious. Generally speaking, can one access the database inside the callback from select via sqlite_exec()? I believe not. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Concurrency with writer and read-only processes
On Fri, 15 Sep 2006 17:29:13 -0400, you wrote: Hi Liam >I have a database that has one writer which runs once a day, and >potentially many readers running whenever someone wants some >information. I am trying to understand concurrency in sqlite3 so that I do a daily Job too in my programm. It seems to me, there are likewise rules. If you allow reading while another Process do a big writing, several reader possible get different results, while writer is not ready. Thats not a Sqlite-Problem, its the same to all other DB. The better way is, to lock the Table while it is be written and allow reading then, if this Jobs is ready. I don't mean a read-write-dialog-process in this case , but a big writer, ideally a especially and exclusive writer. Reading isn't a primary concurrency problem, much more writing is. I do my big writing while the system is generally locked to possible 1500 users. It's customized to a time, when Users are not present - at 03:00 o'clock in the morning. >I understand from lang_transaction that if the writer starts with >BEGIN IMMEDIATE >and ends with >END >then it will be possible for readers to read the database while the >writer is updating it: Is it ok to you, that user possibly get different or premature Query-Results? Best Regards Thomas www.thlu.de - To unsubscribe, send email to [EMAIL PROTECTED] -