Re: [sqlite] Can't understand out of memory error
Nemanja Čorlija wrote: On Jan 17, 2008 12:00 PM, Roger Binns <[EMAIL PROTECTED]> wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Nemanja Čorlija wrote: My problem is that I am running out of memory when trying to load 1.6GB sqlite3 db from disk to :memmory: db on a computer with 4GB of RAM. Your RAM isn't the problem. You are running out of address space. For a 32 bit process, the address space available is 4GB. The operating system typically reserves 1 or 2GB of that for its own uses (eg Linux typically reserves 1GB, Windows reserves 2GB, I don't know what OSX does). That means that all the shared libraries, your stack, each stack's thread if the process is multithreaded, memory mapped files as well as dynamically allocated memory all have to fit within that remaining space. In your case you ran out :-) Here is an example of how to look at what is in a process' address space: http://blog.mozilla.com/dolske/2007/10/16/os-x-and-virtual-bloat/ Thanks for clearing that up. That blog post was really useful in helping me understand this issue better. If you must take this :memory: approach then you will have to switch to using a 64 bit process which will also require a 64 bit kernel and processor. (Note that the amount of physical RAM only affects performance). However wanting the entire database in memory implies you are having performance issues. What were those? I have a db with 27M rows and then I have 5 other dbs with exact same schema that together have 100+M rows and for each of those 100M rows I need to check if its primary key column has a match in 27M db and if it does update that row to set a flag. There's of course a lot of ways to go about this but I just assumed that it would be fastest if I can perform all those lookups against memory db and already wrote a python script to do all the work and then hit this problem. I first thought that it is a python/APSW issue but then I got the exact same error with sqlite3 command line program. I'll try to take advantage of locality of reference on smaller presorted sets of data and see if that can get the job done quick enough. Thanks. You are unlikely to find a memory DB running faster than a disk based one (because of caching). - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Date arithmetic question
Guys, I guess I'm the newest SQLite person on this email list and I know I'm definitely the dumbest. It seems like a lot of you are trying to justify why two dates that are one minute apart can have a function say they're one month apart. Don't look at it that way. Back when mainframes and dinosaurs ruled the world I used to be a fairly decent programmer, now I run a small company, so today I'm coming from a business standpoint not a programmer one. To give some background, we're in the process of embedding SQLite into our application, and yes it's an awesome product. Our software sits on top of huge data warehouses (hundreds of millions of rows) which are typically either Oracle or SQL Server. We run queries against these databases and store the result sets in SQLite, where we run subsequent queries to filter & format the output. A huge number of businesses are date driven. Publishers want to renew subscriptions before they expire. Insurance companies need to renew policies. Our largest client sells service contracts which have a start & end date. So it's an everyday occurrence for a manager to want to know how many customers will expire within the next three months, or what an average contract length is in months. My request was for a new date function that returns the difference, or "calendar interval" if you prefer, between two dates. Without such a function we must say: WHERE (strftime('%Y', LaborEndDate)*12+strftime('%m', LaborEndDate)) - (strftime('%Y', LaborStartDate)*12+strftime('%m', LaborStartDate)) < 3 Wow. This is quite a mouthfull for something that's so commonly needed, and it's harder to generate SQL automatically when a user clicks a checkbox. Clearly it's far simpler (and easier to program) if we could say: WHERE datediff('month', LaborEndDate, LaborStartDate) < 3 Datediff also supports years, weeks, days, hours, seconds, and milliseconds. It's just a quick & easy way to make life easier. But.. of course I know the SQLite team can't slap in every enhancement that somebody suggests; so I just wanted to explain why this would be useful for some of us. We do have a vested interest in the product! - Jeff From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thu 1/17/2008 9:21 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date arithmetic question Gerry Snyder <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should > > return 1 even though the difference is really only 1 second? Seems > > goofy to me > > > > > > I have been staring at this until I'm getting goofy. > > Written as it is, isn't the time interval 1 second short of two days? > > If you want an interval of 1 second shouldn't it be > > datediff('month', '2008-02-01 00:00:00','2008-01-31 23:59:59') > > ? > > > Gerry, more confused than usual (as usual) > Yeah. I got it backwards. Sorry. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Variable substitution (TCL & SQLite)
On Jan 17, 2008, at 10:23 PM, Zbigniew Baniewski wrote: I'm choosing desired column names dynamically, then store all the names in one variable, something like this... set columns "column1, column2, column3" The names are chosen in much more complicated way, but the above is just a variable contents example. I'm trying then to fetch the data like this: set data [dbcomm eval {SELECT $columns FROM some_table}] ...but it doesn't work. The rules of TCL parsing are that text within {...} gets passed into its command exactly as written with the outermost {...} removed. So the command that is running is: command-name: dbcomm 1st-argument: eval 2nd-argument: SELECT $columns FROM some_table In other words, the $columns was *not* expanded by TCL. It got passed down into SQLite. SQLite sees the $columns and thinks you are dealing with an application variable. Just like a "?" or a ":abc" or "@xyz". Sqlite3_prepare() runs and treats the $columns token as it would any other SQL variable. After the statement is prepared. TCL asks the statement: "What application variables do you have, and what are their names?" The statement tells TCL that it has a variable named "$columns". TCL says "I have a variable by that name", and so then TCL then calls sqlite3_bind_text() to stick the value of the $columns TCL variable into the SQLite variable. TCL then calls sqlite3_step() to run the statement. So, even though $columns looks something like a TCL variable, it is really an SQLite variable. You can change the value of an SQLite variable by binding all you want and it is not going to cause the statement to be reparsed. This is a feature, not a bug - it prevents SQL injection attacks. Notice that the $columns token is an SQLite variable because the {...} prevented TCL from expanding the text within the {...} and thus caused the original $columns text, not the expansion of the value of $columns, to be passed down into SQLite. This is very important. This is the essence of TCL. This is the part of TCL that people who have difficulty with TCL don't understand. The rules of TCL are very, very simple, but they are also different from the rules of Algol-derived languages like C++ or Python and that difference confuses many people. TCL is much closer to Lisp. Make sure you understand this before going on. Now, suppose you use "..." instead of {...} in the original statement: dbcomm eval "SELECT $columns FROM some_table" The rules of TCL are that text within "..." is treated as a single token, but unlike {...} the text within "..." undergoes variable expansion and [...] substatement evaluation before being passed into the command. So the command that gets run is this: command-name: dbcomm 1st-argument: eval 2nd-argument: SELECT column1, column2, column3 FROM some_table The second argument gets passed to sqlite3_prepare(). This causes the statement to be prepared as you want it to be. There are no SQLite variables in this case. The $columns has been interpreted and expanded by TCL before the statement is ever sent into SQLite. You should be very careful using "..." instead of {...} in this context. If a user can control the content of $columns, then the user might be able to do something equivalent to: set columns {null; DELETE FROM critical_table; SELECT null} The result would be a classic SQL injection attach. The use of {...} is preferred for this reason. But sometimes, when you want the text of your SQL statement to be under program control, you want to use "..." instead. Just be very sure you know exactly what you are doing whenever you use "..." D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can't understand out of memory error
On Jan 17, 2008 12:00 PM, Roger Binns <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Nemanja Čorlija wrote: > > My problem is that I am running out of memory when trying to load > > 1.6GB sqlite3 db from disk to :memmory: db on a computer with 4GB of > > RAM. > > Your RAM isn't the problem. You are running out of address space. For > a 32 bit process, the address space available is 4GB. The operating > system typically reserves 1 or 2GB of that for its own uses (eg Linux > typically reserves 1GB, Windows reserves 2GB, I don't know what OSX does). > > That means that all the shared libraries, your stack, each stack's > thread if the process is multithreaded, memory mapped files as well as > dynamically allocated memory all have to fit within that remaining > space. In your case you ran out :-) > > Here is an example of how to look at what is in a process' address space: > > http://blog.mozilla.com/dolske/2007/10/16/os-x-and-virtual-bloat/ > Thanks for clearing that up. That blog post was really useful in helping me understand this issue better. > If you must take this :memory: approach then you will have to switch to > using a 64 bit process which will also require a 64 bit kernel and > processor. (Note that the amount of physical RAM only affects performance). > > However wanting the entire database in memory implies you are having > performance issues. What were those? > I have a db with 27M rows and then I have 5 other dbs with exact same schema that together have 100+M rows and for each of those 100M rows I need to check if its primary key column has a match in 27M db and if it does update that row to set a flag. There's of course a lot of ways to go about this but I just assumed that it would be fastest if I can perform all those lookups against memory db and already wrote a python script to do all the work and then hit this problem. I first thought that it is a python/APSW issue but then I got the exact same error with sqlite3 command line program. I'll try to take advantage of locality of reference on smaller presorted sets of data and see if that can get the job done quick enough. Thanks. -- Nemanja Čorlija <[EMAIL PROTECTED]>
[sqlite] Variable substitution (TCL & SQLite)
I'm choosing desired column names dynamically, then store all the names in one variable, something like this... set columns "column1, column2, column3" The names are chosen in much more complicated way, but the above is just a variable contents example. I'm trying then to fetch the data like this: set data [dbcomm eval {SELECT $columns FROM some_table}] ...but it doesn't work. It returns that column names, not the data from the table. When I replace $columns with just the column names separated by colons - I mean: directly with $columns contents - there's no problem anymore. Not sure: the variable substitution won't work the way presented above? What should I change? Currently I made a temporary fix, fetching just all (*), then selecting the data I need - but I don't like it: I'm fetching more, than I needed, and there's an additional "cleaning" loop, which is slowing down the entire procedure. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date arithmetic question
Gerry Snyder <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should > > return 1 even though the difference is really only 1 second? Seems > > goofy to me > > > > > > I have been staring at this until I'm getting goofy. > > Written as it is, isn't the time interval 1 second short of two days? > > If you want an interval of 1 second shouldn't it be > > datediff('month', '2008-02-01 00:00:00','2008-01-31 23:59:59') > > ? > > > Gerry, more confused than usual (as usual) > Yeah. I got it backwards. Sorry. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date arithmetic question
[EMAIL PROTECTED] wrote: So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should return 1 even though the difference is really only 1 second? Seems goofy to me I have been staring at this until I'm getting goofy. Written as it is, isn't the time interval 1 second short of two days? If you want an interval of 1 second shouldn't it be datediff('month', '2008-02-01 00:00:00','2008-01-31 23:59:59') ? Gerry, more confused than usual (as usual) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite character comparisons
On Thu, Jan 17, 2008 at 05:25:30PM -0500, Fowler, Jeff wrote: > By the way.. I found this snippet. If I read it right, it seems that > IGNORING trailing spaces during string comparisons is ANSI standard. I'm not sure. I was always avoiding such problem by "trim"-ming everything to be inserted; either before, or during insertion (directly in SQL query). There is remark in Postgres docs, that it does follow SQL-92 - so probably the above is a recommendation rather than a rule. In the Postgres docs I've found: #v+ The notations varchar(n) and char(n) are aliases for character varying(n) and character(n) [..] Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type character, and they will be removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values. #v- So, you can just use "character" type, to have what you need. But I'm still talking about Postgres ;) - and you were asking about SQLite. I'm trimming it all anyway... ;) -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3 performace
Areyou doing an sqlite3_finalize and checking to see that you actually close the DB? Philip Nick wrote: Thanks for replying, I have tried moving the Open/Close outside the mutex no change. As for using our own mutex, we started with early versions of sqlite and had to come up with our own solution. I was planning on looking into using the built in mutex's, but first I need to solve the performance issues. The only difference I can see between our code and the CLI is it uses sqlite3_exec and a call back, while we do sqlite3_prepare Then we call sqlite3_column_count. We loop through 0-num_col and call: sqlite3_column_name sqlite3_column_decltype Then we do while(sqlite3_step() == SQLITE_ROW) From my understanding the sqlite3_exec() is doing the same thing and sending the info too the callback. Any ideas? Thanks On Jan 17, 2008 5:09 PM, <[EMAIL PROTECTED]> wrote: "Philip Nick" <[EMAIL PROTECTED]> wrote: Greetings, Currently I am using sqlite3 in a multi-process/multi-threaded server setting. I use a Mutex to ensure only one process/thread can access the database at one time. The current flow of events: Get Mutex Open Database connection Run Query Close Database connection Release Mutex SQLite does the mutexing automatically (assuming you are using version 3.5.0 or later). -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3 performace
Thanks for replying, I have tried moving the Open/Close outside the mutex no change. As for using our own mutex, we started with early versions of sqlite and had to come up with our own solution. I was planning on looking into using the built in mutex's, but first I need to solve the performance issues. The only difference I can see between our code and the CLI is it uses sqlite3_exec and a call back, while we do sqlite3_prepare Then we call sqlite3_column_count. We loop through 0-num_col and call: sqlite3_column_name sqlite3_column_decltype Then we do while(sqlite3_step() == SQLITE_ROW) >From my understanding the sqlite3_exec() is doing the same thing and sending the info too the callback. Any ideas? Thanks On Jan 17, 2008 5:09 PM, <[EMAIL PROTECTED]> wrote: > "Philip Nick" <[EMAIL PROTECTED]> wrote: > > Greetings, > > > > Currently I am using sqlite3 in a multi-process/multi-threaded server > > setting. > > I use a Mutex to ensure only one process/thread can access the database > at > > one time. > > > > The current flow of events: > > Get Mutex > > Open Database connection > > Run Query > > Close Database connection > > Release Mutex > > > > SQLite does the mutexing automatically (assuming you are using > version 3.5.0 or later). > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > -- Philip Nick E-Mail: [EMAIL PROTECTED] Cell: 352-262-9067 Web: http://www.ruffasdagut.com
RE: [sqlite] SQLite character comparisons
By the way.. I found this snippet. If I read it right, it seems that IGNORING trailing spaces during string comparisons is ANSI standard. SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations. -Original Message- From: Zbigniew Baniewski [mailto:[EMAIL PROTECTED] Sent: Thursday, January 17, 2008 5:20 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite character comparisons On Thu, Jan 17, 2008 at 10:43:20AM -0500, Fowler, Jeff wrote: > I've used SQL Server for over 15 years, Oracle off & on when I have no > choice, but SQLite for a couple weeks. I've just learned (today) that > SQLite respects trailing spaces when comparing two character fields. > I.e. 'SQLITE' <> 'SQLITE ' > > Is this behavior intentional? Neither SQL Server nor Oracle do this. > Just curious as to why it works this way. PostgreSQL sees the strings that way too: mydbase=> select 'str' = 'str'; ?column? -- t (1 row) mydbase=> select 'str' = 'str '; ?column? -- f (1 row) Those are different strings, anyway. Can't recall now, does there any setting to change this - but you can easily find out at their website. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite character comparisons
On Thu, Jan 17, 2008 at 10:43:20AM -0500, Fowler, Jeff wrote: > I've used SQL Server for over 15 years, Oracle off & on when I have no > choice, but SQLite for a couple weeks. I've just learned (today) that > SQLite respects trailing spaces when comparing two character fields. > I.e. 'SQLITE' <> 'SQLITE ' > > Is this behavior intentional? Neither SQL Server nor Oracle do this. > Just curious as to why it works this way. PostgreSQL sees the strings that way too: mydbase=> select 'str' = 'str'; ?column? -- t (1 row) mydbase=> select 'str' = 'str '; ?column? -- f (1 row) Those are different strings, anyway. Can't recall now, does there any setting to change this - but you can easily find out at their website. -- pozdrawiam / regards Zbigniew Baniewski - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite3 performace
"Philip Nick" <[EMAIL PROTECTED]> wrote: > Greetings, > > Currently I am using sqlite3 in a multi-process/multi-threaded server > setting. > I use a Mutex to ensure only one process/thread can access the database at > one time. > > The current flow of events: > Get Mutex > Open Database connection > Run Query > Close Database connection > Release Mutex > SQLite does the mutexing automatically (assuming you are using version 3.5.0 or later). -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite3 performace
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of > Philip Nick > Sent: Thursday, January 17, 2008 1:48 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] sqlite3 performace > > Greetings, > > Currently I am using sqlite3 in a multi-process/multi-threaded server > setting. > I use a Mutex to ensure only one process/thread can access the database at > one time. > > The current flow of events: > Get Mutex > Open Database connection > Run Query > Close Database connection > Release Mutex > > This seems to work well except I have noticed some performance issue when > the database grows beyond a MB. Why not move the Open/Close outside of the mutex, hold a connection open, and re-use it for all queries? Otherwise you're making SQLite reload the schema definition every time you perform a query, as I understand it. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3 performace
Greetings, Currently I am using sqlite3 in a multi-process/multi-threaded server setting. I use a Mutex to ensure only one process/thread can access the database at one time. The current flow of events: Get Mutex Open Database connection Run Query Close Database connection Release Mutex This seems to work well except I have noticed some performance issue when the database grows beyond a MB. I have noticed my program basically reads the whole database every time the query is run. The IO Read bytes increases by 900k for every query we run. We also chew a good chunch of the cpu I have tried keeping the database connection open and open/closing for every query. But there was no change. if I launch the CLI and run the query it runs instantly and monitoring the IO read bytes is see only ~20 bytes of read to execute the query, when my code is using over 900k for every call. I have been looking into the CLI source to see what is done differently, but was hoping someone on here might have some insight. Thanks Phil --
Re: [sqlite] Date arithmetic question
On 1/17/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > "Fowler, Jeff" <[EMAIL PROTECTED]> wrote: > > Remember gang, if you want to know how many seconds are between two > > timestamps, you wouldn't ask for a difference in months. You'd say > > something like DATEDIFF(seconds, t1, t2). > > > > So DATEDIFF doesn't really compute the difference between > two dates. right, because to compute the "real" difference is tricky as you have already pointed out... until someone mandates a 100 day, 10 months year, a 10 hour day, and so on. > It computes the number of date measurement > interval transitions that occur between the two dates. in other words, the function provides the rope along with mandatory warnings, and if the user/developer still wishes to hang him/herself, well, so be it. > > > > > -Original Message- > > From: Markus Hoenicka [mailto:[EMAIL PROTECTED] > > Sent: Thursday, January 17, 2008 3:47 PM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] Date arithmetic question > > > > [EMAIL PROTECTED] writes: > > > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') > > should > return 1 even though the difference is really only 1 second? > > Seems > goofy to me > > > > > > > well, this is one second rounded up to the next full month...If that is > > the kind of information you want to compute, it's probably not that > > goofy after all. > > > > regards, > > Markus > > > > -- > > Markus Hoenicka > > [EMAIL PROTECTED] > > (Spam-protected email: replace the quadrupeds with "mhoenicka") > > http://www.mhoenicka.de > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > > > - > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > > . > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date arithmetic question
"Fowler, Jeff" <[EMAIL PROTECTED]> wrote: > Remember gang, if you want to know how many seconds are between two > timestamps, you wouldn't ask for a difference in months. You'd say > something like DATEDIFF(seconds, t1, t2). > So DATEDIFF doesn't really compute the difference between two dates. It computes the number of date measurement interval transitions that occur between the two dates. > > -Original Message- > From: Markus Hoenicka [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 17, 2008 3:47 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Date arithmetic question > > [EMAIL PROTECTED] writes: > > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') > should > return 1 even though the difference is really only 1 second? > Seems > goofy to me > > > > well, this is one second rounded up to the next full month...If that is > the kind of information you want to compute, it's probably not that > goofy after all. > > regards, > Markus > > -- > Markus Hoenicka > [EMAIL PROTECTED] > (Spam-protected email: replace the quadrupeds with "mhoenicka") > http://www.mhoenicka.de > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > . - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Date arithmetic question
Remember gang, if you want to know how many seconds are between two timestamps, you wouldn't ask for a difference in months. You'd say something like DATEDIFF(seconds, t1, t2). - Jeff -Original Message- From: Markus Hoenicka [mailto:[EMAIL PROTECTED] Sent: Thursday, January 17, 2008 3:47 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date arithmetic question [EMAIL PROTECTED] writes: > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should > return 1 even though the difference is really only 1 second? Seems > goofy to me > well, this is one second rounded up to the next full month...If that is the kind of information you want to compute, it's probably not that goofy after all. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Date arithmetic question
Not really that goofy, just very specific. The SQL Server manual describes it this way: "Returns the number of date and time boundaries crossed between two specified dates." Regards, Noah Hart -Original Message- So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should return 1 even though the difference is really only 1 second? Seems goofy to me -- D. Richard Hipp <[EMAIL PROTECTED]> CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date arithmetic question
[EMAIL PROTECTED] writes: > So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should > return 1 even though the difference is really only 1 second? Seems > goofy to me > well, this is one second rounded up to the next full month...If that is the kind of information you want to compute, it's probably not that goofy after all. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date arithmetic question
"Virgilio Fornazin" <[EMAIL PROTECTED]> wrote: > DATEDIFF should compute the difference by arithmetic subtracting M/Y in > month case, if I'm not wrong > > ex: > > DateDiff (month, 1-1-2007, 3-30-2007) will return 2 > > Its that right ? So datediff('month', '2008-02-01 23:59:59','2008-01-31 00:00:00') should return 1 even though the difference is really only 1 second? Seems goofy to me -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Date arithmetic question
You're right of course - I just noticed the question pertains to the algorithm, not the function itself. Sorry! I just ran a simple test using "popular RDBMS product A" on one of our internal databases, as follows: select 'year difference:', datediff(yy,'12/31/2007','1/1/2008') --> year difference: 1 select 'month difference:', datediff(mm,'1/31/2007','2/1/2007') --> month difference: 1 select 'week difference:', datediff(wk,'1/5/2008','1/6/2008') --> week difference: 1 So for months, the function basically does the arithmetic I did myself using the (strftime( '%Y', date1)*12+strftime('%m',date1)) - (strftime('%Y',date2)*12+strftime('%m',date2)) Regardless of actual days, it simply decides that if two dates occur in two consecutive calendar months, they are a month apart. Same w/ year and week. Of course, for precise accuracy it's better to use days.. - Jeff -Original Message- From: Scott Baker [mailto:[EMAIL PROTECTED] Sent: Thursday, January 17, 2008 3:13 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date arithmetic question Fowler, Jeff wrote: > Yes - I've looked over the current date functions. I would propose a > single function addition that's hugely valuable in the business world. > SQL Server has a function called "datediff" for date arithmetic. It > accepts three parameters. The first indicates the unit of scale > (years, months, weeks, days, etc. - I think it even goes into milliseconds!). > The next two parameters are the dates to compute the difference between. > It returns an integer. I don't think you answered Richard's original question. What constitutes a month? Since month lengths vary, there is no exactly science to "how many months between these two dates." Otherwise your best bet is what he already recommended. SELECT (julianday(date2) - julianday(date1)) / 30.43666 AS Months; -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date arithmetic question
DATEDIFF should compute the difference by arithmetic subtracting M/Y in month case, if I'm not wrong ex: DateDiff (month, 1-1-2007, 3-30-2007) will return 2 Its that right ? A good reference for trying implementing it should be: http://www.sqlteam.com/article/datediff-function-demystified On Jan 17, 2008 6:12 PM, <[EMAIL PROTECTED]> wrote: > "Fowler, Jeff" <[EMAIL PROTECTED]> wrote: > > Yes - I've looked over the current date functions. I would propose a > > single function addition that's hugely valuable in the business world. > > SQL Server has a function called "datediff" for date arithmetic. It > > accepts three parameters. The first indicates the unit of scale (years, > > months, weeks, days, etc. - I think it even goes into milliseconds!). > > The next two parameters are the dates to compute the difference between. > > It returns an integer. > > > > Fair enough, and simple enough to do for weeks, days, and smaller > intervals. But what algorithm do I use to compute the number of > months difference? Or years? Is there a standard for such a > thing? Anybody know? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Date arithmetic question
Fowler, Jeff wrote: Yes - I've looked over the current date functions. I would propose a single function addition that's hugely valuable in the business world. SQL Server has a function called "datediff" for date arithmetic. It accepts three parameters. The first indicates the unit of scale (years, months, weeks, days, etc. - I think it even goes into milliseconds!). The next two parameters are the dates to compute the difference between. It returns an integer. I don't think you answered Richard's original question. What constitutes a month? Since month lengths vary, there is no exactly science to "how many months between these two dates." Otherwise your best bet is what he already recommended. SELECT (julianday(date2) - julianday(date1)) / 30.43666 AS Months; -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date arithmetic question
"Fowler, Jeff" <[EMAIL PROTECTED]> wrote: > Yes - I've looked over the current date functions. I would propose a > single function addition that's hugely valuable in the business world. > SQL Server has a function called "datediff" for date arithmetic. It > accepts three parameters. The first indicates the unit of scale (years, > months, weeks, days, etc. - I think it even goes into milliseconds!). > The next two parameters are the dates to compute the difference between. > It returns an integer. > Fair enough, and simple enough to do for weeks, days, and smaller intervals. But what algorithm do I use to compute the number of months difference? Or years? Is there a standard for such a thing? Anybody know? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Date arithmetic question
Yes - I've looked over the current date functions. I would propose a single function addition that's hugely valuable in the business world. SQL Server has a function called "datediff" for date arithmetic. It accepts three parameters. The first indicates the unit of scale (years, months, weeks, days, etc. - I think it even goes into milliseconds!). The next two parameters are the dates to compute the difference between. It returns an integer. Regards, - Jeff -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 17, 2008 2:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Date arithmetic question "Fowler, Jeff" <[EMAIL PROTECTED]> wrote: > Hello All, > > SQLite newbie here. I've looked through the email archives and website > trying to find out how to compute the difference in months between two > given dates. Each date is in -MM-DD HH:MM:SS format. > > The best I've been able to come up with seems rather ugly: > > SELECT (strftime( '%Y', date1)*12+strftime('%m',date1)) - > (strftime('%Y',date1)*12+strftime('%m',date1)) > > Am I on the right track, or is there something obvious I'm missing? > Computing the number of months between two dates is problematic since the length of a month varies. Is 2007-01-31 to 2007-02-28 a whole month even thought it is only 28 days? What about 2007-04-10 to 2007-05-08? That is also 28 days. Does it count as a whole month too, or is it two days shy of a whole month? You can compute the number of days between two days very easily: SELECT julianday(date2) - julianday(date1); And I suppose you could divide that value by 30.4366 (which is the average number of days in a month) to get the number of months. So the answer to your question is that there is no easy answer to your question because it depends on how you define the "difference in months" and there does not appear to be a single intuitive definition for that question. Do you have a particular algorithm for "difference in months" in mind? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Date arithmetic question
"Fowler, Jeff" <[EMAIL PROTECTED]> wrote: > Hello All, > > SQLite newbie here. I've looked through the email archives and website > trying to find out how to compute the difference in months between two > given dates. Each date is in -MM-DD HH:MM:SS format. > > The best I've been able to come up with seems rather ugly: > > SELECT (strftime( '%Y', date1)*12+strftime('%m',date1)) - > (strftime('%Y',date1)*12+strftime('%m',date1)) > > Am I on the right track, or is there something obvious I'm missing? > Computing the number of months between two dates is problematic since the length of a month varies. Is 2007-01-31 to 2007-02-28 a whole month even thought it is only 28 days? What about 2007-04-10 to 2007-05-08? That is also 28 days. Does it count as a whole month too, or is it two days shy of a whole month? You can compute the number of days between two days very easily: SELECT julianday(date2) - julianday(date1); And I suppose you could divide that value by 30.4366 (which is the average number of days in a month) to get the number of months. So the answer to your question is that there is no easy answer to your question because it depends on how you define the "difference in months" and there does not appear to be a single intuitive definition for that question. Do you have a particular algorithm for "difference in months" in mind? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite character comparisons
Hello All, I've used SQL Server for over 15 years, Oracle off & on when I have no choice, but SQLite for a couple weeks. I've just learned (today) that SQLite respects trailing spaces when comparing two character fields. I.e. 'SQLITE' <> 'SQLITE ' Is this behavior intentional? Neither SQL Server nor Oracle do this. Just curious as to why it works this way. Thanks, - Jeff
Re: [sqlite] Can't understand out of memory error
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Nemanja Čorlija wrote: > My problem is that I am running out of memory when trying to load > 1.6GB sqlite3 db from disk to :memmory: db on a computer with 4GB of > RAM. Your RAM isn't the problem. You are running out of address space. For a 32 bit process, the address space available is 4GB. The operating system typically reserves 1 or 2GB of that for its own uses (eg Linux typically reserves 1GB, Windows reserves 2GB, I don't know what OSX does). That means that all the shared libraries, your stack, each stack's thread if the process is multithreaded, memory mapped files as well as dynamically allocated memory all have to fit within that remaining space. In your case you ran out :-) Here is an example of how to look at what is in a process' address space: http://blog.mozilla.com/dolske/2007/10/16/os-x-and-virtual-bloat/ If you must take this :memory: approach then you will have to switch to using a 64 bit process which will also require a 64 bit kernel and processor. (Note that the amount of physical RAM only affects performance). However wanting the entire database in memory implies you are having performance issues. What were those? The archives of this list contain many performance questions and solutions. The wiki also contains some, although nothing specific to OSX. This is an example for Windows: http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHjzVKmOOfHg372QQRAol6AJ4vs+SzvMqWr3Dp6oe+C62zshAV3wCgvVAA 4sPfraHPcTH3/BSbL17kkaw= =/M2H -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Database growing while updating rows
Hello, I have an issue with my database (sqlite 3.4.2), runing some updates makes it use more filespace than needed. I am using this kind of schema : CREATE TABLE mytable (myKey TEXT PRIMARY KEY, myInt INTEGER, myBlob TEXT) 1st : Inserting a row Let's say that myBlob data is 256K. After the insert, everything is fine, the database is slighly more than 256K. 2nd : Updating myInt in the freshly inserted row UPDATE myTable SET myInt=1 WHERE myKey=1 After the update, the database size will double. I can now repeat as many updates as I want on that row, the database won't grow anymore. (Note that vacuuming the database makes it regain its normal size, but I want to avoid running some vacuum since the database can be huge.) Is it a normal behavior ? Something I am doing wrong ? Any help would be really very appreciated. Thanks for your attention -- _ _ __ __ ___ __ _ _ Jean Collonville web : http://www.hungmaow.org jabber : [EMAIL PROTECTED] PGP Key : http://www.hungmaow.org/cjean.asc signature.asc Description: This is a digitally signed message part