[sqlite] R: [sqlite] support for table partitioning?
> The database already knows exactly where to look for each table when all the tables are in the same file. > All it has to do is "lseek()" to the appropriate spot. How does moving tables into separate files help this or make it any faster? > "Table partitioning" is a technique used to improve performance of large databases running on large machines. With table partitioning you can configure the DB so that it stores some fields of a record in a file and the remaining fields of the same record in a different file. If each record is large, placing the two files on two different disks usually speeds things up because reading (or writing) a record requires to read, in parallel, half the record from one disk and the other half from the other disk. Performance also increases if your select happens to access only the fields stored in one of the two files: if the select requires a sequential scan of the entire (large) table, the DB manager will have to read through only half the mount of data. In my opinion however table partitioning is beyond the scope of a DB like SQLite... Bye -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato:martedì 21 marzo 2006 14.48 A: sqlite-users@sqlite.org Oggetto:Re: [sqlite] support for table partitioning? "Miha Vrhovnik" <[EMAIL PROTECTED]> wrote: > On 3/21/2006, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > > >"Miha Vrhovnik" <[EMAIL PROTECTED]> wrote: > >> Hi drh and others, > >> > >> Regarding the issues they appear on ML with very large tables and knowing > >> that sqlite now enforces constraint checks on tables, is there any > >> chances of suporting table partitoning? > >> > > > >Put each table in a separate database file then ATTACH > >the databases. > > > That's not the same. You still need to rewrite queries, where in real > table partitioning you don't need to do that. What changes to the queries do you think are necessary? > The select's and inserts > are faster because db knows where to put/search for them. > The database already knows exactly where to look for each table when all the tables are in the same file. All it has to do is "lseek()" to the appropriate spot. How does moving tables into separate files help this or make it any faster? -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Separate INTEGER and REAL affinity ?
I read in the "changes" page of the SQLite site that version 3.3.x of SQLite features "Separate INTEGER and REAL affinity". What does this exactly mean ? How is SQLite 3.3.x different from 2.8.x with respect to column affinity ? Thank you, bye
[sqlite] R: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
Most of the discussion so far was about proposed change number 2, on the contrary I'm concerned about proposed change number 1. Does this mean that a number that can be stored as an integer will be stored as an integer and, thus, I will need to read it back as an integer ? Here is what I mean: with SQLIte 3.2.x, if I run these two statements Insert into foo values(5.34); Insert into foo values(3.0); Table foo will contain two rows that both contain a real-type number, so, to read the values back from the DB, I can always use sqlite3_column_double(). With your proposed change it appears to me that for each row I will have to first test for the type of the field and then decide whether to use sqlite3_column_double() or sqlite3_column_int(). Is this correct ? If so, changes will be required to the existing code to port it to Sqlite 3.3.x. Bye -Messaggio originale- Da: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Inviato:martedì 1 novembre 2005 15.00 A: sqlite-users@sqlite.org Oggetto:[sqlite] Proposed 3.3.0 changes. Was: 5/2==2 I am proposing to make the changes outlined below in SQLite version 3.3.0 and I am wondering if these changes will cause any severe hardship. Two changes working together: (1) Floating point values are *always* converted into integers if it is possible to do so without loss of information. (2) Division of two integers returns a floating point value if necessary to preserve the fractional part of the result. The effect of change (1) is to combine the integer affinity and the numeric affinity column types into a single type. The new type is called numeric affinity, but it works like integer affinity. Change (2) resolves Ralf Junker's division paradox. The only code that I can think of that this change might break is cases where the user is depending on the division of two integers returning an integer result. Such code will need to be modified to use the "round()" function to obtain the same result. I am thinking that such code should be very uncommon and that this change will have minimal impact. Nevertheless, the impact is non-zero so I will increment the minor version number as part of this change. If you can think of any other adverse impact that this change might have, please let me know. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Optimal page size
I could not find a document explaining how to find the optimal value for the "page size" parameter. Should I set the page size to match the allocation size (cluster size) of the file system ? Should I set it so that each page contains exactly a given number of records (i.e. no record is split between two pages) ? Thank you, bye
[sqlite] How to use PRAGMA SYNCRONOUS ??
Here are a couple of questions for the most expert SQLite users. Question 1 Is #PRAGMA SYNCRONOUS a global database setting or is it possible to open two handles on the same database and set two different values for #PRAGMA SYNCRONOUS on the two handles ? Question 2 Is it possible to change the value of #PRAGMA SYNCRONOUS on the same handle during the execution of the program ? In other words can a do something like this: begin exclusive transaction #PRAGMA SYNCRONOUS 2 ... some very important update... commit ... some code unrelated to DB... begin exclusive transaction #PRAGMA SYNCRONOUS 0 ... less important update... commit ... some code unrelated to DB... begin exclusive transaction #PRAGMA SYNCRONOUS 2 ... some very important update ... commit Thank you , bye
[sqlite] Two questions about #PRAGMA SYNCRONOUS
Question 1 Is #PRAGMA SYNCRONOUS a global database setting or is it possible to open two handles on the same database and set two different values for #PRAGMA SYNCRONOUS on the two handles ? Question 2 Is it possible to change the value of #PRAGMA SYNCRONOUS on the same handle during the execution of the program ? In other words can a do something like this: #PRAGMA SYNCRONOUS 2 begin transaction ... some very important update... commit ... some code unrelated to DB... #PRAGMA SYNCRONOUS 0 begin transaction ... less important update... commit ... some code unrelated to DB... #PRAGMA SYNCRONOUS 2 begin transaction ... some very important update ... commit Thank you , bye
[sqlite] Using date fields in SQLiteExplorer
Here is another question about dates and SQLiteExplorer (v 1.7).
I'm storing dates in the native Delphi format, i.e. as floating point
numbers ("dates as text" = FALSE).
Whenever I try to update a record that contains such a date, SQLiteExplorer
displays the message "unexpected count of affected records:0" and
modifications are not written to the database.
Is it possible that SQLiteExplorer is trying to update the record on the
database using the date fields in a "where" clause but fails because of
rounding errors in the floating point rapresentation of dates ?
How can I work around this problem ?
Thank you
Bye
