Re: [sqlite] RESOLVED: unable to open database file/Disc I/O error
Sqlite version 3.6.15 Client running Windows Vista SP2 Server running Windows Small Business Server 2008 Written in C++ All databases are on the same share, mounted as a drive letter I'm sorry have not had time yet to write a smaller test app for you, but I'm not sure it would be reproducible for you anyway, as we have several vista machines running our application accessing the same networked share and only one of our machines seems to reliably show the problem. thanks Serena On Sat, Nov 6, 2010 at 10:53 AM, Black, Michael (IS) <michael.bla...@ngc.com > wrote: > #1 What version of Sqlite and Windows client/server are you using? > #2 What language is your application written in? > #3 Are all your databases on the same share mount point? > #4 Is your share mounted as a drive letter? > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of Serena Lien > Sent: Sat 11/6/2010 3:08 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] RESOLVED: unable to open database file/Disc > I/O error > > > > No, I didn't explain correctly. My application is not open overnight, only > the computer. > So I do not have any database connections open at all, they are not going > stale. > > In the morning, I start the application, and it opens some databases in > readwrite mode and executes statements without errors. > It opened some databases in readonly mode and executes statements, these > return SQLITE_BUSY errors, nothing else is using the databases. > > Therefore there is nothing wrong with the network connection as it has > already accessed networked databases before my errors occur. > > I can only conclude it must be a bug with locking using the readonly flag - > either sqlite or something vista OS specific going wrong. > > cheers Serena. > > > > > On Fri, Nov 5, 2010 at 10:52 PM, H. Phil Duby <phild...@phriendly.net > >wrote: > > > On Fri, Nov 5, 2010 at 3:25 AM, Serena Lien <serenal...@gmail.com> > wrote: > > > > > > I would like to inform the group that I managed to resolve the issue > with > > a > > > workaround. I am not sure if this is a bug in vista or whether there > > might > > > in fact be some slight issue with sqlite... > > > > > > The problem seems to be hardware and software related, and occurs for > me > > > when my application is running on Windows Vista and is opening a > database > > on > > > a networked drive (running Windows Small Business Server 2008) with the > > > read-only flag. Running sql queries on the database after this will > > > return SQLITE_BUSY when no one is using the database. Once the > > application > > > is closed and restarted the error does not re-occur. This is highly > > > reproducible once per day (in the morning after the machine has powered > > down > > > the screen only, it doesn't go to sleep). > > > > Given that sequence / timing, I *suspect* this is actually caused by > > the network connection 'timing out'. Your application thinks it has > > the database file open, but SBS has _decided_ that the connection is > > stale, and closed it. I do not know why your symptoms would only show > > up for read only connections. You might be able to continue after the > > error, by having the application close and reopen / reconnect to the > > database file. You might get an error doing a normal close as well, > > because SBS thinks it is already closed. > > > > When your Vista system powers down the screen, it may *also* be > > powering down the [wired or wireless] network card, which could > > trigger SBS to close the read only connection. > > > > > Removing that flag and making sure I only open databases in read-write > > mode > > > has resolved the problem. I have tested this thoroughly over a period > of > > > many days. > > > > > > As I said I'm not sure if this is an sqlite bug or not (and I'm sure > very > > > difficult to reproduce) but I'm writing this in case anyone else has > the > > > same problem in the hope it helps them. > > > > > > cheers Serena > > -- > > Phil > > ___ > > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RESOLVED: unable to open database file/Disc I/O error
No, I didn't explain correctly. My application is not open overnight, only the computer. So I do not have any database connections open at all, they are not going stale. In the morning, I start the application, and it opens some databases in readwrite mode and executes statements without errors. It opened some databases in readonly mode and executes statements, these return SQLITE_BUSY errors, nothing else is using the databases. Therefore there is nothing wrong with the network connection as it has already accessed networked databases before my errors occur. I can only conclude it must be a bug with locking using the readonly flag - either sqlite or something vista OS specific going wrong. cheers Serena. On Fri, Nov 5, 2010 at 10:52 PM, H. Phil Duby <phild...@phriendly.net>wrote: > On Fri, Nov 5, 2010 at 3:25 AM, Serena Lien <serenal...@gmail.com> wrote: > > > > I would like to inform the group that I managed to resolve the issue with > a > > workaround. I am not sure if this is a bug in vista or whether there > might > > in fact be some slight issue with sqlite... > > > > The problem seems to be hardware and software related, and occurs for me > > when my application is running on Windows Vista and is opening a database > on > > a networked drive (running Windows Small Business Server 2008) with the > > read-only flag. Running sql queries on the database after this will > > return SQLITE_BUSY when no one is using the database. Once the > application > > is closed and restarted the error does not re-occur. This is highly > > reproducible once per day (in the morning after the machine has powered > down > > the screen only, it doesn't go to sleep). > > Given that sequence / timing, I *suspect* this is actually caused by > the network connection 'timing out'. Your application thinks it has > the database file open, but SBS has _decided_ that the connection is > stale, and closed it. I do not know why your symptoms would only show > up for read only connections. You might be able to continue after the > error, by having the application close and reopen / reconnect to the > database file. You might get an error doing a normal close as well, > because SBS thinks it is already closed. > > When your Vista system powers down the screen, it may *also* be > powering down the [wired or wireless] network card, which could > trigger SBS to close the read only connection. > > > Removing that flag and making sure I only open databases in read-write > mode > > has resolved the problem. I have tested this thoroughly over a period of > > many days. > > > > As I said I'm not sure if this is an sqlite bug or not (and I'm sure very > > difficult to reproduce) but I'm writing this in case anyone else has the > > same problem in the hope it helps them. > > > > cheers Serena > -- > Phil > ___ > 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] RESOLVED: unable to open database file/Disc I/O error
I would like to inform the group that I managed to resolve the issue with a workaround. I am not sure if this is a bug in vista or whether there might in fact be some slight issue with sqlite... The problem seems to be hardware and software related, and occurs for me when my application is running on Windows Vista and is opening a database on a networked drive (running Windows Small Business Server 2008) with the read-only flag. Running sql queries on the database after this will return SQLITE_BUSY when no one is using the database. Once the application is closed and restarted the error does not re-occur. This is highly reproducible once per day (in the morning after the machine has powered down the screen only, it doesn't go to sleep). Removing that flag and making sure I only open databases in read-write mode has resolved the problem. I have tested this thoroughly over a period of many days. As I said I'm not sure if this is an sqlite bug or not (and I'm sure very difficult to reproduce) but I'm writing this in case anyone else has the same problem in the hope it helps them. cheers Serena ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unable to open database file/Disc I/O error
Hi, Just wanted to say we have the same issue, using sqlite v3.6.15 (not java) running on *some* windows vista machines accessing databases on *some* networked drives. I don't think this is an sqlite problem, because vista is obviously randomly locking files and this can be seen when using microsoft word or excel. Occasionally these programs report that the files are in use and open them as read-only, when in fact they are not. Reopening the file always makes the errors go away. With our sqlite program, we also see these errors occur - typically we get SQLITE_BUSY (the file is locked) errors compiling or executing statements, but as you said, the errors are random. Some statements accessing a database will work, and then another statement accessing the same database will fail a second later. Exiting our application and restarting it makes the problem go away for a while. If you find any vista OS patches that solve this problem, please let me know, we are looking ourselves in the hopes that microsoft has resolved this issue.. thanks, Serena. - Ferdinand wrote: Hi, we have a java application ( with sqlitejdbc-v056/windows vista) which runs only once on every PC to fill a new database. On most machines everything works perfectly, but on some machines the application fails with either "Unable to open database file" or " disk I/O error". The database is of course there . This errors occur on different locations in the code(with execute() or executeBatch()), even when previous calls to this functions were successfull.. In most cases the problems vanish when we run the apllication again (but this is not really an option we have). I searched the internet and found that this errors might be caused by locks on temporary files(we run Kaspersky Anti Virus on every machine) Are there any other explanations for this behaviour? Thanx Ferdinand Krämer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] disk IO error after windows resumes from sleep
Closing the handle before going to sleep sounds like a really sensible thing to do which I hadn't heard of before, I will definitely try that! Thanks for the quick responses from you all, Serena. On Tue, Oct 5, 2010 at 1:16 PM, Black, Michael (IS) <michael.bla...@ngc.com>wrote: > Could this also be because you never closed the database handle? So Sqlite > thinks it's still open? First time you get an errror do an sqlite3_close() > on the old handle. That may solve your problem. > > Next thing, you should register your app to receive the PBT_APMSUSPEND < > http://msdn.microsoft.com/en-us/library/aa372721(v=VS.85).aspx> event so > you can close the db handle before the system goes to sleep (you have two > seconds to do so -- you may need SetThreadExecutionState if you need > longer). > > Other option would be to only open the database when you need to and use > the SetThreadExecutionstate while you have it open. That will keep the > system from sleeping while it's busy. > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of Drake Wilson > Sent: Tue 10/5/2010 5:59 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] disk IO error after windows resumes from > sleep > > > > Quoth Serena Lien <serenal...@gmail.com>, on 2010-10-05 11:46:18 +0100: > > On a windows vista/win7 machine or a laptop which goes into sleep mode, > when > > it resumes and the application tries to open a database on a networked > > drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I > don't > > have a problem with this, if the OS has lost access to the network I can > > imagine SQLITE_IOERR is quite valid. My question is, is there any way to > > recover now from this error without forcing my application to exit and > > restart? Any number of retries using sqlite3_open_v2 always continue to > fail > > with SQLITE_IOERR. > > > > It is possible the response will be "not sqlite's problem", but I would > > appreciate any advice anyone has to give, > > I would say that unless SQLite is returning that error in unwarranted > cases, this is really an application-level error recovery problem. > What do you mean by "always continue to fail"? Is this the case even > after you have verified that the desired file is accessible? Are you > delaying retries at all? > > If the IOERR return code is truthfully signaling inability to access > the file, then if this is an interactive application, you might signal > the user to request a retry later. If it's a batch process, you might > schedule a retry for later. If there's some alternative way of > accessing the database or operating at reduced functionality without > it, you might try that. It's hard to be more specific without knowing > what kind of application is being developed. > > ---> Drake Wilson > ___ > 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
Re: [sqlite] disk IO error after windows resumes from sleep
Yes - that is indeed what I am doing (your 3rd scenario) - but I have tried to close the connection first (and ignore any errors closing it) before retrying to open. As you say, the database object I am trying to close may not be valid so I am not able to close it first, and therefore unable to open again. Terminating the application obviously allows it to close, so that restarting my application now successfully opens it. So my question is, is there any sqlite function I can call so that the connection can be successfully closed? thanks Serena. On Tue, Oct 5, 2010 at 12:57 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 5 Oct 2010, at 11:46am, Serena Lien wrote: > > > On a windows vista/win7 machine or a laptop which goes into sleep mode, > when > > it resumes and the application tries to open a database on a networked > > drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I > don't > > have a problem with this, if the OS has lost access to the network I can > > imagine SQLITE_IOERR is quite valid. My question is, is there any way to > > recover now from this error without forcing my application to exit and > > restart? Any number of retries using sqlite3_open_v2 always continue to > fail > > with SQLITE_IOERR. > > SQLite doesn't do anything if you don't have any connections open. So as > far as SQLite is concerned, there's absolutely no difference between > > start application > sleep > wakeup > sqlite3_open_v2() > > and > > start application > sleep > wakeup > quit application > restart application > sqlite3_open_v2() > > There's just no way for SQLite to tell the difference between these two > states. So if something is stopping your application from accessing a file > until it has been restarted, it's a function of the operating system rather > than something that SQLite is doing. However, are you perhaps doing > > start application > sqlite3_open_v2() > sleep > wakeup > // Oh, my open connection is now useless, I need one that works ! > sqlite3_open_v2() > > I can imagine that failing because SQLite thinks it already has a working > connection to that database. The cure would be to use sqlite3_close() > before the new _open, though I'm not sure whether you'd have an appropriate > connection you could use with it. Another problem with this is that if you > have a prepared statement for a connection, you can't close it. You have to > finalize first. > > Simon. > ___ > 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
Re: [sqlite] disk IO error after windows resumes from sleep
In response to your question - by "always continues to fail" I mean that yes, after delaying and retrying, even when the file should be accessible, I still get SQLITE_IOERR returned from sqlite3_open_v2. If my application exits and restarts, it will try to call sqlite3_open_v2 again on the same file and this time it will succeed, which is why I said the file *should* be accessible. Does that make sense and should that be the application's problem, or sqlite's? thanks, Serena. On Tue, Oct 5, 2010 at 11:59 AM, Drake Wilson <dr...@begriffli.ch> wrote: > Quoth Serena Lien <serenal...@gmail.com>, on 2010-10-05 11:46:18 +0100: > > On a windows vista/win7 machine or a laptop which goes into sleep mode, > when > > it resumes and the application tries to open a database on a networked > > drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I > don't > > have a problem with this, if the OS has lost access to the network I can > > imagine SQLITE_IOERR is quite valid. My question is, is there any way to > > recover now from this error without forcing my application to exit and > > restart? Any number of retries using sqlite3_open_v2 always continue to > fail > > with SQLITE_IOERR. > > > > It is possible the response will be "not sqlite's problem", but I would > > appreciate any advice anyone has to give, > > I would say that unless SQLite is returning that error in unwarranted > cases, this is really an application-level error recovery problem. > What do you mean by "always continue to fail"? Is this the case even > after you have verified that the desired file is accessible? Are you > delaying retries at all? > > If the IOERR return code is truthfully signaling inability to access > the file, then if this is an interactive application, you might signal > the user to request a retry later. If it's a batch process, you might > schedule a retry for later. If there's some alternative way of > accessing the database or operating at reduced functionality without > it, you might try that. It's hard to be more specific without knowing > what kind of application is being developed. > > ---> Drake Wilson > ___ > 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] disk IO error after windows resumes from sleep
I have a question about recovering from SQLITE_IOERR? We are using sqlite v3.6.15 on windows where the databases may be accessed across a network (I am aware of the caveats here). On a windows vista/win7 machine or a laptop which goes into sleep mode, when it resumes and the application tries to open a database on a networked drive, the open function returns SQLITE_CANTOPEN and SQLITE_IOERR. I don't have a problem with this, if the OS has lost access to the network I can imagine SQLITE_IOERR is quite valid. My question is, is there any way to recover now from this error without forcing my application to exit and restart? Any number of retries using sqlite3_open_v2 always continue to fail with SQLITE_IOERR. It is possible the response will be "not sqlite's problem", but I would appreciate any advice anyone has to give, thanks Serena. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] baffling performance decrease across network (specific case)
Thank you, that was immensely helpful - as well as Roger Binns many informative posts about OpLocks. Looks like there is nothing to be done about the slowdown (in fact I should be grateful there is even such a thing as opportunistic locking to make the single client situation faster), but it is a relief to have an explanation about what is happening! Serena. On Thu, May 22, 2008 at 5:59 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > This slowdown is almost certainly due to the use of opportunistic > locking in the SMB protocols. With a single client the it uses exclusive > oplocks and can cache remote file data locally. When a second client > connects it can no longer do this and subsequently slows down to the > real speed of remote file access. > > See http://en.wikipedia.org/wiki/Opportunistic_Locking for additional > info. > > HTH > Dennis Cote > ___ > 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
Re: [sqlite] baffling performance decrease across network (specific case)
Thanks for the response - no it hadn't occurred to me to try a different
network filesystem, as I don't really have access to non windows machines.
I'm just using windows xp machines set up on the same domain, where the
databases reside on shared folders.
I actually thought there might be extra work sqlite is doing (no not
sleeping!) when more than one client is accessing the same database, like
having to move between extra locking states or something like that, and that
it might be easily explainable, but you're perfectly right that it could
just be down to the OS.
thanks anyway,
Serena
On Tue, May 20, 2008 at 9:34 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
>
> That is probably a question that cannot be answered without knowing
> specifics of your network filesystem. Certainly we have NOT put code
> in SQLite that says:
>
>
> if( serena_is_accessing_multiple_times_on_network_filesystem()==TRUE ){
>sqlite3_usleep(600);
> }
>
> I really do not know why it goes slowly on a network filesystem when
> two or more clients are connected. I would guess it has something to
> do with the locking and caching protocols of your network filesystem.
> But that is only a guess. You talk as if this behavior is true of
> every network filesystem. Is that true? Have you tried on a
> different network filesystem? If you really need to know why it is
> slow, open the code, attach a protocol analyzer to your network, and
> figure it out. Let us know if you find anything interesting.
>
>
> D. Richard Hipp
> [EMAIL PROTECTED]
>
>
>
> ___
> 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
Re: [sqlite] baffling performance decrease across network (specific case)
Thanks for your response. I do realize the performance of sqlite over the network will be slower than accessing local databases - I will restate if I wasn't clear - the question I asked is why sqlite accessing a networked database is slower WHEN that particular database is attached in a different database connection/process. Please see my original message. The performance I'm getting when accessing a database over the network is perfectly acceptable to me. As soon as another client is attached to the same database, I am clearly seeing a different behaviour from sqlite, and the performance in that case is much slower, and this is what I'm trying to understand. thanks, Serena. > > I posted a query on this list very recently about a similar issue, except I > noticed a 20-fold decrease in R/W performance across the network with a > single connection (BLOB access), compared to what you get with a binary > file > R/W. Cogent explanations were given, and I conclude that SQLite (and > perhaps all non-backend-server based solutions will suffer greatly across a > network vs. local volume). > > I had to rejig my app so that remote SQLite files are first copied to a > local temp file, operated on, then copied back to remote volume. I can't > see any other way. > > Peter. > > > -- > - > Peter K. Stys, MD > Dept. of Clinical Neurosciences > Hotchkiss Brain Institute > University of Calgary > tel (403) 210-8646 > - > ___ > 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] baffling performance decrease across network (specific case)
Hello, I wonder if anyone could shed some light on this. I am using sqlite in my client programs to access databases over the network. I know sqlite isn't recommended for this usage model, but I am happy that I have implemented it such that only 1 client ever has write access, and am also happy with the performance (speed) over the network in normal situations. However, I've noticed that when 2 clients have attached to the same database (where the database is on a remote machine, *not local*) then I get a significant slowdown when querying that database - the sqlite3_step function takes longer to complete. If only 1 client is attached to the database, the query performs perfectly fast, so it's definitely not just network latency. Can anyone explain what sqlite is doing and if there is anything I can do to avoid this issue? Note I am querying a table which contains blobs of data. I think there is a slowdown on querying tables without blobs in it, but it is certainly very obviously slower on the table with blobs. You can reproduce this using the sqlite3 cmd line program (v3.5.6) and creating 2 databases where the 2nd database has a table something like: create table ImageData(ID integer primary key, nRows integer, nCols integer, imageMtx blob, palette blob, nGridRows integer, nGridCols integer, gridMtx blob); and add some data to the table First db connection open test1.db with sqlite3.exe (test1.db on remote machine) attach test2.db as currentAnalysis select nGridRows, nGridCols, gridMtx from currentAnalysis.ImageData -- data is returned instantaneously Second db connection now open test1.db with another instance of sqlite3.exe (test1.db on remote machine) attach test2.db as currentAnalysis now go back to the first db connection, repeat the query and notice the results returned after 6 or so seconds if you detach the database from the second db connection, and run the query again on the first db connection, it's still slow if you now detach and reattach the test2.db database in the first db connection and run the query again, it's fast again. Apologies for the very long description - I hope I've explained the situation clearly enough. I may legitimately have 2 clients reading the same database at the same time, and it's very frustrating for my client program to slow down in this situation. Could anyone explain if there is a reason sqlite is doing this and if I can avoid it? many thanks, Serena. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re: select round(98926650.50001, 1) ?
On 9/5/07, Doug Currie <[EMAIL PROTECTED]> wrote: > > > Clinger's) PLDI 1990 papers. What I don't know is why this bug appears > in the binary from sqlite.org but not in the version I build myself > with gcc 3.4.5 from SQLite version 3.4.2 source. > > Note it also appears when I compile SQLite from 3.4.2 source code using Microsoft Visual C++ (2003). I have not looked at using fp:precise and _controlfp: as Kees suggested because it's only a feature in VC 2005.. Serena
Re: [sqlite] Re: select round(98926650.50001, 1) ?
Thanks for pointing out the implementation issues. I was thinking only in the case where I am asking for a string result, not all the time, ie sqlite3_column_text, where I would want the string representation of the rounded number in this format, and did not realize this would require storing all the string results.. I suppose this conversion should really be done in a wrapper function instead. thanks for your help, Serena. On 9/3/07, Arjen Markus <[EMAIL PROTECTED]> wrote: > > > Hm, that is a completely different question. It would mean that more > information is > associated with the rounded result than merely the number (in internal > representation). > I do not know enough about the way SQLite organises these things to > speculate about > that, but it seems like a computational burden to me: > > Suppose you have one million records to search for numbers that are then > rounded. Not > only would you need to compute and store the rounded numbers but also > the string > representation (or something to effect that you can get a proper decimal > presentation). > > Regards, > > Arjen > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] Re: select round(98926650.50001, 1) ?
Okay, but even if the rounded result does not have a finite binary representation, shouldn't the displayed (human readable) representation be at least truncated to the number of decimal places that were requested in the round function? Not that I am confusing round with truncate, but surely it is a more acceptable result? thanks, Serena. On 9/3/07, Arjen Markus <[EMAIL PROTECTED]> wrote: > > Serena Lien wrote: > > >I have read some of the postings/faq about the difficulties with the > round > >function, when certain numbers do not have a finite representation in > >binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that > round( > >9.95, 1) rounds down. > > > >But, I have found several numbers which don't get rounded at all, and in > >fact return more decimal places! > > > >round(98926650.5, 1) -> 98926650.501 > >round(85227887.01, 1) -> 85227887.001 > > > >Even if these numbers cannot be represented properly in binary, why is it > >they aren't rounded? > > > >thanks for any assistance, > >Serena > > > > > They are in fact rounded, but the internal binary representation can not > be turned > into the appropriate decimal (and human readable) representation due to > the > finite precision. > > A number like 1.511 can be rounded to 1.5 and that is _exactly_ > representable > as binary number. But if, as in your examples, the number requires more > precision > than is available (remember: only a finite number of digits/bits in > total, no matter > what the position of the decimal point/comma), the old problem of > binary-to- > decimal conversion kicks in again. > > Regards, > > Arjen > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
[sqlite] Re: select round(98926650.50001, 1) ?
I have read some of the postings/faq about the difficulties with the round function, when certain numbers do not have a finite representation in binary, which SQLite uses. eg 9.95 is actually 9.9499...etc so that round( 9.95, 1) rounds down. But, I have found several numbers which don't get rounded at all, and in fact return more decimal places! round(98926650.5, 1) -> 98926650.501 round(85227887.01, 1) -> 85227887.001 Even if these numbers cannot be represented properly in binary, why is it they aren't rounded? thanks for any assistance, Serena On 8/31/07, Serena Lien <[EMAIL PROTECTED]> wrote: > > Hello, > > With SQLite 3.3.13, this returns 98926650.501 > Can you explain how I can get the expected rounding/truncation? > > thanks. >
[sqlite] select round(98926650.50001, 1) ?
Hello, With SQLite 3.3.13, this returns 98926650.501 Can you explain how I can get the expected rounding/truncation? thanks.
Re: [sqlite] Optimize performance - reading from multiple databas e files, processing and writing to separate results database file?
No, I am not copying anything to an in-memory database, this was just an alternative option I was suggesting, so please ignore the whole in-memory database thing from now on.. I have more databases to attach than the maximum number allowed, so no, I cannot attach my input databases to make one large DB. I believe my only option is to attach databases one at a time, and use multiple transactions around the attach/detach commands, since I cannot attach databases within a transaction. Serena. > Why7 copy to an im memory database? Why not just create a regular database as your output. The is no reason you cannot have more than one DB connection current in any process or thread. How about - Attach your input databases to make one large DB select your output data insert output data into the output DB
Re: [sqlite] Optimize performance - reading from multiple databas e files, processing and writing to separate results database file?
Thanks for the response. -- 1 Gbyte is not at all too big for Sqlite to handle in one file. True, but I think too big for an in-memory database - I was considering copying all my source data from separate databases into one table in an in-memory database so that I could access it easier later. I don't really know what the size I need to copy would be, it would depend on some sort of search to work out what input files to use. Eg Say each input database corresponds to some experimental data associated with one subject. I may have thousands of these. In the application I am writing, I may want to do a query to find all subjects with some sort of common condition - so this may return a lot of database files, and/or have a lot of data (size on disk) associated with it. -- Some portion (large??) of the performance advantages of transactions will be lost in opening/closing files, even if transactions were allowed across multiple files. The transactions are hopefully for optimizing the insertion of data into my results database file, not for reading data from my multiple source files, I don't need to write to the sources. For these reasons, might you consider using a single database rather than multiple files if you're up against performance bottlenecks? Perhaps - I thought if I needed to work with lots and lots of data in the future, rather than having everything in one database, it would be easier to manage in separate files, because I wouldn't always need to access all the experimental data all the time. Serena.
Re: [sqlite] Optimize performance - reading from multiple database files, processing and writing to separate results database file?
Yes, I could indeed determine which databases I want to attach to, but there is a limit to the number of databases you can attach (I think it's 32?) and I might have more than that. So I thought the safer route was to attach as I go.. Serena. On 10/4/06, Tom Briggs <[EMAIL PROTECTED]> wrote: Do you know all of the databases that you want to attach to at the start of processing? If so, there's no reason you can't simply attach them all beforehand, start your transaction, and complete all your processing. If you have to decide dynamically, based on the data, which databases you'll need, then you have no choice but to start and end multiple transactions in order to attach the databases as you go. -Tom
[sqlite] Optimize performance - reading from multiple database files, processing and writing to separate results database file?
Hello, I have many databases, all in separate files. I want to choose a subset of them based on some query, and read the input data one at a time, process it somehow and write the results to another database file. The problems are caused by: - May have a large number of inputs (eg > 32 databases to attach) - May be a lot of data (eg > 1GB total), too much to copy the sources into a table in an in-memory database, then processing each row in this table. - Don't particularly want to duplicate the source data, by copying sources into a temporary database on disk, unless there is no better method - Need to use transactions when inserting processed data into a table in the results database I have tried the following: - Attach results database - Begin transaction - Attach first source database and select its data - Process data and bind results for insertion into results db, call step - Detach source database and attach next source database, repeat etc - End transaction But this won't work, because trying to attach a database gives the error: Cannot attach database within transaction! But I really need transactions for performance because I am inserting a lot of data (more rows than I am reading from my inputs) I would appreciate any suggestions on how best to do this. Serena.
