[sqlite] Bogdan Ureche -- SQLite Expert
Your www.sqliteexpert.com domain has expired. Email is underliverable to both [EMAIL PROTECTED] and the domain's registered contact address listed in the whois data. Is this an oversite or are you abandoning SQLiteExpert? I hope not, it's handy program. Don
RE: [sqlite] PRAGMA cache_size = 0
>> What exactly happens when I change the cache_size (both increase and >> decrease size)? > A variable is set. It seems this term is a misnomer. What are we achieving by setting this variable? This is what is mentioned in the documentation of SQLITE: PRAGMA cache_size; PRAGMA cache_size = Number-of-pages; Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses about 1.5K of memory. The default cache size is 2000. If you are doing UPDATEs or DELETEs that change many rows of a database and you do not mind if SQLite uses more memory, you can increase the cache size for a possible speed improvement. So without allocation extra memory how can I increase the size of cache_size? >> What happens to the data that's there in the result cache at the time >> when the instruction PRAGMA cache_size = 0 is executed? > Nothing. The aforementioned variable is set to 10 instead of the > specified value. >> Will there be any memory that will be freed up when I reduce the size of >> result cache? > No. So whats there in this cache? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] MMap On Solaris
The behaviour depends on whether you map shared or not. If for map shared multiple users can read and write to the file simultaneously. If you have a situation where you access he same bytes you need to use some form of synchronization, just as you do with read and write. You can map for exclusive access and also for private. In the provate case other users do not see your changes to the file. If the file has been extended by another user past the area you have mapped you will not access it unless you mmap to the new length. If the file is growing fast that could make using read and write more appropriate. Ken wrote: John, You seem pretty knowledgable regarding MMAP. I was wondering if you could help me with this MMAP scenario: I'm curious as to how the OS and multple processes interact regarding file i/o and mmap. Process A --- Writes to a file sequentially using either pwrite or kaio. I would like to write a process B. That performs a read against what was written by A. I'm able to coordinate where to stop the read in other words I don't want to read more than what has been written by A. Currently I'm just using os calls to "read" but I thought that maybe MMAP might give better performance especially if the OS would just provide the written buffers performed by Process A to Process B's address space that is MMAPed. Thanks for any guidance. Ken John Stanton <[EMAIL PROTECTED]> wrote: MMAP just lets you avoid one or two layers of buffering and APIs. If you were to use fopen/fread you go to function calls then open/read plus buffering and function calls then to to the VM to actually access the data. Going direct to the VM and getting a pointer to the VM pages is more efficient. I got about 30% better speed out of one of my compilers just by removing the reads and local buffering and using mmap. A b-tree index almost doubled in speed by removing local reads and buffering and using mmap. Mitchell Vincent wrote: Hi John! Thanks for the reply! I think that makes a good point that the vm page fault is probably faster than the overhead of copying the data to a local buffer. So, page fault or not, I think that's the way I'm going to do it. Again, thanks very much for your input! On 6/12/07, John Stanton wrote: Mitchell Vincent wrote: Working with some data conversion here (that will eventually go into an SQLite database). I'm hoping you IO wizards can offer some help on a question that I've been trying to get answered. I'm using Solaris 10 for this. If I mmap a large file and use madvise with MADV_SEQUENTIAL and MADV_WILLNEED, then start processing the file, when will the system discard pages that have been referenced? I guess what I'm wondering is if there is any retention of "back" pages? Say for example I start reading the file, and after consuming 24,576 bytes, will the first or second pages still be in memory (assuming 8192 byte pages)? Thanks! In general it means that the file is mapped into virtual memory. How much of it remains in actual memory depends upon the memory demands on the OS at the time. If the sequential and random advice is used by the OS it is most likely to implement a look ahead for requential access. Not all OS's pay attention to those advisory settings. What you are doing is to access the file as if it were an executing program image. Similar rules apply. The answer is that you cannot assume that pages you have read are in actual memory and you cannot assume that they are not. When you access a page not currently in memory the OS will read it in and find space for it somehow, maybe by discarding some other page. This is an excellent way to read files because you avoid one level of buffer shadowing and get cacheing adjusted to currently available memory. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multiple *processes* accessing one database
> From: Andrew Finkenstadt <[EMAIL PROTECTED]> > To: sqlite-users@sqlite.org > Sent: Thursday, June 14, 2007 12:39:46 AM > Subject: Re: [sqlite] Multiple *processes* accessing one database > > On 6/13/07, Andrew Roark <[EMAIL PROTECTED]> wrote: > > > > My question: how well does sqlite handle one database file being accessed > > by multiple processes? > > > Very well, so far. You'll want to make sure you use an appropriate locking > (transaction) strategy, and accomodate SQLITE_BUSY even when you think it > can't happen. :) > superb -- thank you very much :) Again, sqlite is brilliant! Andrew Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online. http://smallbusiness.yahoo.com/webhosting - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How can I convert from Julian time to a tm structure?
We just lifted the routines out of Sqlite to do that. They are in date.c. By making an Sqlite-style date type and a small library of date manipulation routines we move date conversion to the application. It is handy when handling ISO8601 and HTTP date formats plus integrating with file agees. Rob Richardson wrote: Greetings! I am trying to speed up an application that queries a table with three columns and around a million records, and one of the fields is a timestamp. One thing I want to do is to move the conversion of the timestamp from a Julian time to a human-readable time from the query to my application. (By the way, this is a C++ app written in MS Visual Studio 6.) I could build a query and have SQLite execute it, something like "SELECT datetime(123456.789) AS timestring", but that has the overhead of preparing the query, executing it and finalizing it, plus the overhead of converting from a string representation into the tm structure once I get the result of the query. I didn't see any little utility function in the SQLite library that just exposes whatever routine SQLite uses to do the conversion. Does one exist? There must be plenty of algorithms out there to do this conversion. A quick search revealed a few, but they were obviously faulty (assuming every year has 365.25 days, for instance) or not precise enough (returning only the day). I need an algorithm that is accurate to the nearest second. What should I use? Or is the SQLite query the best I'm going to do? Thank you very much. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite 3.X Database File Format ?
Is there an SQLite 3.x equivalent document for this? SQLite 2.X Database File Format http://sqlite.org/fileformat.html If not, is this 2.x document worth reading as a background to the general structure of the sqlite 3.x file and page format? Or has it changed so much that it's not useful? Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list=396545469 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is it a bug?
--- Scott Hess <[EMAIL PROTECTED]> wrote: > On 6/14/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > You can't infer a function's return type from its arguments. > > Take the hypothetical function FOO(x). If I pass it a number, it will > > return the number spelled out as TEXT, but if I pass it a BLOB it will > > return its length*PI as a FLOAT. > > Would it be reasonable to have: > > SELECT myfunc(x, y, z); > > have an undefined return type, while: > > SELECT CAST(myfunc(x, y, z) AS INTEGER); > > would return a well-defined return type? > > After all, in SQLite you can't infer a column's type, either, you can > only infer how the column is declared. What it actually contains > could be anything at all. True enough, but in spite of my contrived FOO function, I'd think that 99% of functions would return one specific type or a NULL. In the situations where this would not be the case, the CAST operator, as you point out, would be a good way to override the returned declared type. I don't see this proposed type mechanism as being at odds with SQLite's typeless nature - it does not detract from it. It's not mandating policy - it's just suggesting a standard type reporting convention which you may elect to use or ignore. It also would provide a facility to return column types for ad-hoc SELECT expressions that return no rows (i.e., you can't infer the column types from the first row because there are no rows returned from the query). Get the Yahoo! toolbar and be alerted to new email wherever you're surfing. http://new.toolbar.yahoo.com/toolbar/features/mail/index.php - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is it a bug?
On 6/14/07, Joe Wilson <[EMAIL PROTECTED]> wrote: You can't infer a function's return type from its arguments. Take the hypothetical function FOO(x). If I pass it a number, it will return the number spelled out as TEXT, but if I pass it a BLOB it will return its length*PI as a FLOAT. Would it be reasonable to have: SELECT myfunc(x, y, z); have an undefined return type, while: SELECT CAST(myfunc(x, y, z) AS INTEGER); would return a well-defined return type? After all, in SQLite you can't infer a column's type, either, you can only infer how the column is declared. What it actually contains could be anything at all. -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA cache_size = 0
> What exactly happens when I change the cache_size (both increase and > decrease size)? A variable is set. > What happens to the data that's there in the result cache at the time > when the instruction PRAGMA cache_size = 0 is executed? Nothing. The aforementioned variable is set to 10 instead of the specified value. > Will there be any memory that will be freed up when I reduce the size of > result cache? No. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is it a bug?
> The code changes are not that complex. The hard part is getting > me to agree to such a change. Surely by now you have come to > better understand my views toward static typing Isn't this decision already made? Strict affinity mode is mentioned on official SQLite site suggesting that it will happen. -- Cywilizacje powstaja z gliny i zelaza. Travian >>> http://link.interia.pl/f1a7e - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] performance question: SELECT max(rowid) - 1
Trey Mack wrote: > I have a fairly large table (10million rows) with a simple INTEGER > PRIMARY KEY AUTOINCREMENT field. > > Executing 'SELECT max(rowid) FROM MyTable' is very fast, as is > 'SELECT min(rowid) FROM MyTable'. > > However, 'SELECT max(rowid) - min(rowid) FROM MyTable' is slow > (apparently accessing every row). Further, 'SELECT max(rowid) - 1 > FROM MyTable' is slow - in fact using any constant in this expression > (including 0) results in a slow query. > > Finally, 'SELECT (SELECT max(rowid) FROM MyTable') - 10' is very fast. > Check out http://www.sqlite.org/php2004/slides-all.html Page 61 SELECT max(rowid) FROM MyTable and SELECT min(rowid) FROM MyTable are optimized to run without a full table scan. However SELECT max(rowid) - min(rowid) FROM MyTable is not, and will perform a full table scan. To achieve the same functionality with the optimizations, try: SELECT (SELECT max(rowid) FROM MyTable) - (SELECT min(rowid) FROM MyTable) SELECT (SELECT max(rowid) FROM MyTable) - 1 - Trey Thank for that link Trey (and Puneet) - it pretty much confirms what I'd arrived at for myself by trial and error. guy - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is it a bug?
--- [EMAIL PROTECTED] wrote: > Joe Wilson <[EMAIL PROTECTED]> wrote: > > > > As a possible extension one could see sqlite3_create_function taking an > > optional argument with a hint as to its return type that sqlite may use > > for sqlite3_column_decltype. But SQLite does not currently return any > > column types for any ad-hoc expression. So this would not be a trivial > > change. > > > > The code changes are not that complex. The hard part is getting > me to agree to such a change. Surely by now you have come to > better understand my views toward static typing No doubt. This issue comes up frequently when making database drivers for JDBC, ODBC, R, (you name the language) as they often expect static column types in result sets. Yes, I've hacked my way around it, as has every other driver writer, but it would be nice if everyone did not have to reinvent the wheel each time. In a hypothetical implementation, one could preserve backwards API compatiblity by overloading the zFunctionName argument by tacking on a TYPE suffix (i.e., "myfunction:TYPE") to convey the type being returned by the function. int sqlite3_create_function( sqlite3 *, const char *zFunctionName, int nArg, int eTextRep, void *pUserData, void (*xFunc)(sqlite3_context*,int,sqlite3_value**), void (*xStep)(sqlite3_context*,int,sqlite3_value**), void (*xFinal)(sqlite3_context*) ); But it would probably be easier to convince all the other popular relational databases that they should change to exclusively use dynamic typing. ;-) Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games. http://sims.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is it a bug?
Joe Wilson <[EMAIL PROTECTED]> wrote: > > As a possible extension one could see sqlite3_create_function taking an > optional argument with a hint as to its return type that sqlite may use > for sqlite3_column_decltype. But SQLite does not currently return any > column types for any ad-hoc expression. So this would not be a trivial > change. > The code changes are not that complex. The hard part is getting me to agree to such a change. Surely by now you have come to better understand my views toward static typing -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is it a bug?
--- [EMAIL PROTECTED] wrote: > Thank you Joe. At least sqlite3_column_type gives me a column type I > can use as > a hint. > > The issue is that I have a well-defined column type naming convention for my > wrapper classes to look for (INT, TEXT, FLOAT, BLOB, etc.). For binary data > fields, its BLOB. For other field types, when encrypted they should be > prefixed with BLOB (eg. BLOB_INT, BLOB_TEXT, BLOB_FLOAT), so that my low-level > execute function can provide hints to the downstream result-set parsing to use > for converting types. > > Wouldn't it be entirely possible to provide the column's type if the function > used only a single column in its parameter list? For example, consider the You can't infer a function's return type from its arguments. Take the hypothetical function FOO(x). If I pass it a number, it will return the number spelled out as TEXT, but if I pass it a BLOB it will return its length*PI as a FLOAT. As a possible extension one could see sqlite3_create_function taking an optional argument with a hint as to its return type that sqlite may use for sqlite3_column_decltype. But SQLite does not currently return any column types for any ad-hoc expression. So this would not be a trivial change. > UPPER function. In cases where its just used to convert a single column to > upper-case, couldn't Sqlite just use that column's data type? E.g., > > UPPER(FirstName) > > It should be an easy thing to provide this info. Even if there were multiple > fields involved, if they were all the same data type, Sqlite could know this > and provide the common type. E.g., > > UPPER(FirstName + LastName) Even a simple operator like '+' you cannot easily determine the return type of its expression without evaluating it - and even then it may return a FLOAT in one invocation and TEXT in a different invocation. With some static expression analysis you could infer the return type for a number of input combinations, but this would require a fair bit of new code. > If both field types were BLOB_TEXT, then that should be provided. Only > in cases > where there were multiple columns and the data types were different > would a NULL > be returned. > > This would certainly be an improvement for Sqlite, would it not? > > cheers > -brett > > Quoting Joe Wilson <[EMAIL PROTECTED]>: > > > --- [EMAIL PROTECTED] wrote: > >> I have a user-defined function named DECRYPT, which decrypts column data > >> encrypted by my other UDF named ENCRYPT. > >> > >> The UDF callback function (which does the decrypting) calls > >> sqlite3_result_blob > >> after decrypting the data. Sqlite does return the data OK...BUT! > >> It doesn't > >> provide the data type for the column when I call sqlite3_column_decltype > >> for > >> the column. The return value is NULL. I must have the column data > >> type, else > >> my code can't properly interpret the value. This must be a bug, right? > > > > As far as I know, SQLite has to way of knowing what the column type > > is in the case of a function: > > > > "If the Nth column of the result set is not a table column, > > then a NULL pointer is returned." > > > > http://www.sqlite.org/capi3ref.html#sqlite3_column_decltype > > > > You can interrogate the first row's column values with > > sqlite3_column_type as a workaround (assuming the column in subsequent > > rows are of the same type): > > > > http://www.sqlite.org/capi3ref.html#sqlite3_column_type Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] performance question: SELECT max(rowid) - 1
someone else might give a more technical and scientific explanation, but my take is that "SELECT n FROM table" is just that -- a row returned for every row in the table because there is no WHERE clause constraining the results. "SELECT max() - 1 FROM table" on the other hand GROUPs the result before returning it, hence GROUPing acts as a constraint. By that logic, "SELECT (SELECT max(rowid) FROM MyTable') - 10" is very fast because it is SELECTing one record from a returned set of one record... basically, the external SELECT is just as superfluous as "SELECT SELECT (SELECT max(rowid) FROM MyTable') - 10" would be just as fast as well, and so on. On 6/14/07, Guy Hindell <[EMAIL PROTECTED]> wrote: Ah, OK, I see that doing 'SELECT 1 FROM MyTable' returns a 1 for every row, so I can see where the effort is probably going. However, 'SELECT max(rowid) - 1 FROM MyTable' still only produces one result row (obviously I'm experimenting with a much smaller database now). Still need an explanation rather than just relying on my own speculation. Cheers guy Guy Hindell wrote: > I have a fairly large table (10million rows) with a simple INTEGER > PRIMARY KEY AUTOINCREMENT field. > > Executing 'SELECT max(rowid) FROM MyTable' is very fast, as is > 'SELECT min(rowid) FROM MyTable'. > > However, 'SELECT max(rowid) - min(rowid) FROM MyTable' is slow > (apparently accessing every row). Further, 'SELECT max(rowid) - 1 > FROM MyTable' is slow - in fact using any constant in this expression > (including 0) results in a slow query. > > Finally, 'SELECT (SELECT max(rowid) FROM MyTable') - 10' is very fast. > > I am curious and would be grateful if someone can explain what is > going on here. > > guy > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > > - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_release_memory
sqlite3_release_memory(int n) internally calls sqlite3pager_release_memory(int n) A negative value of input n implies free as much as you can. Suppose if the no of pages in cache = x. Will all the pages be freed when I call sqlite3_release_memory with a negative argument? If not, then what makes it prevent from freeing up all the pages? What is the difference between calling sqlite3_release_memory with negative value as argument and executing the PRAGMA cache_size = 0? Regards, Phani
Re: [sqlite] performance question: SELECT max(rowid) - 1
Ah, OK, I see that doing 'SELECT 1 FROM MyTable' returns a 1 for every row, so I can see where the effort is probably going. However, 'SELECT max(rowid) - 1 FROM MyTable' still only produces one result row (obviously I'm experimenting with a much smaller database now). Still need an explanation rather than just relying on my own speculation. Cheers guy Guy Hindell wrote: I have a fairly large table (10million rows) with a simple INTEGER PRIMARY KEY AUTOINCREMENT field. Executing 'SELECT max(rowid) FROM MyTable' is very fast, as is 'SELECT min(rowid) FROM MyTable'. However, 'SELECT max(rowid) - min(rowid) FROM MyTable' is slow (apparently accessing every row). Further, 'SELECT max(rowid) - 1 FROM MyTable' is slow - in fact using any constant in this expression (including 0) results in a slow query. Finally, 'SELECT (SELECT max(rowid) FROM MyTable') - 10' is very fast. I am curious and would be grateful if someone can explain what is going on here. guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] PRAGMA cache_size = 0
What exactly happens when I change the cache_size (both increase and decrease size)? What happens to the data that's there in the result cache at the time when the instruction PRAGMA cache_size = 0 is executed? Will there be any memory that will be freed up when I reduce the size of result cache? These are some of the questions for which I am yet to receive the answers. Regards, Phani -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 12, 2007 3:40 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] PRAGMA cache_size = 0 "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote: > I am yet to get answers for the following questions. > Weiyang Wang correctly answered your question at http://www.mail-archive.com/sqlite-users%40sqlite.org/msg25290.html -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] performance question: SELECT max(rowid) - 1
I have a fairly large table (10million rows) with a simple INTEGER PRIMARY KEY AUTOINCREMENT field. Executing 'SELECT max(rowid) FROM MyTable' is very fast, as is 'SELECT min(rowid) FROM MyTable'. However, 'SELECT max(rowid) - min(rowid) FROM MyTable' is slow (apparently accessing every row). Further, 'SELECT max(rowid) - 1 FROM MyTable' is slow - in fact using any constant in this expression (including 0) results in a slow query. Finally, 'SELECT (SELECT max(rowid) FROM MyTable') - 10' is very fast. I am curious and would be grateful if someone can explain what is going on here. guy - To unsubscribe, send email to [EMAIL PROTECTED] -
