RE: [sqlite] Question about speed of CASE WHEN
I use SQLite as a data manipulator, not as a database. I get data from a server database, dump to SQLite, manipulate the data and finally dump to Excel. As this is reporting software speed is important, so I will go with the fastest method. RBS -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 23:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN RB Smissaert wrote: > Ok, now done some better testing and the method with CASE WHEN is indeed, as > expected a bit faster To me the lookup table method seems like exactly what a relational database is used for. The CASE WHEN would have to be dramatically faster, and in an area where timing was critical, for me to choose that way. If there were thousands of items, and changes were frequent, you wouldn't even consider CASE WHEN, would you? Remember: timing isn't important, except when it is. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
In the sense that the legacy code produces files ~100MB. The collection is
not legacy, that's what I am trying to setup. Unless I don't understand what
you mean
2007/3/19, guenther <[EMAIL PROTECTED]>:
On Sun, 2007-03-18 at 23:51 +0200, Dimitris Servis wrote:
> in my wildest dreams... if you read carefully, *each* file is about
> 100-200MB. I now end up wit ha collection of 100-200 of them and need to
> bundle in one file
Yes, I did read carefully. 100 (source) files, each 100 MByte, stuffed
into a single (target, database) file results into that database file
being 100*100 MByte. Considering "possibly 200 or more", this easily
could result in a single 64+ GByte file.
So, in what way was this meant to be a response regarding my
concerns? ;)
guenther
--
char
*t="[EMAIL PROTECTED]";
main(){ char h,m=h=*t++,*x=t+2*h,c,i,l=*x,s=0; for (i=0;i>=1)||!t[s+h]){ putchar(t[s]);h=m;s=0;
}}}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Question about speed of CASE WHEN
RB Smissaert wrote: Ok, now done some better testing and the method with CASE WHEN is indeed, as expected a bit faster To me the lookup table method seems like exactly what a relational database is used for. The CASE WHEN would have to be dramatically faster, and in an area where timing was critical, for me to choose that way. If there were thousands of items, and changes were frequent, you wouldn't even consider CASE WHEN, would you? Remember: timing isn't important, except when it is. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
On Sun, 2007-03-18 at 23:51 +0200, Dimitris Servis wrote:
> in my wildest dreams... if you read carefully, *each* file is about
> 100-200MB. I now end up wit ha collection of 100-200 of them and need to
> bundle in one file
Yes, I did read carefully. 100 (source) files, each 100 MByte, stuffed
into a single (target, database) file results into that database file
being 100*100 MByte. Considering "possibly 200 or more", this easily
could result in a single 64+ GByte file.
So, in what way was this meant to be a response regarding my
concerns? ;)
guenther
--
char *t="[EMAIL PROTECTED]";
main(){ char h,m=h=*t++,*x=t+2*h,c,i,l=*x,s=0; for (i=0;i>=1)||!t[s+h]){ putchar(t[s]);h=m;s=0; }}}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Question about speed of CASE WHEN
Ok, now done some better testing and the method with CASE WHEN is indeed, as expected a bit faster, I would say about a third. I have only tested this with some 8 different convert values, so maybe it will be different if there are much more different values to convert. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 17:17 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Question about speed of CASE WHEN Done some testing now and surprisingly, it seems the lookup method with a join to a lookup table is very slightly faster than the CASE WHEN construction. There isn't much in it though. Also tried the shorter CASE WHEN construction, like this: > case ENTRY_TYPE > when 9 then 'Issue > when 2 then 'Note' Etc But didn't get that to work as it always produced the first WHEN option. RBS -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 15:29 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN RB Smissaert uttered: > Looking at the fastest way to convert a field in a table and wonder if in > general an update with a CASE WHEN construction or an update with a join to > a lookup table is faster. My guess is that the CASE WHEN form will be faster for small number of possibilities. It compiles into a straight list of compare instructions. > > These conversions are of this type: > > UPDATE A3SQL77D_J > SET ENTRY_TYPE = (case > when ENTRY_TYPE = 9 then 'Issue > when ENTRY_TYPE = 2 then 'Note' > when ENTRY_TYPE = 1 then 'Encounter' > when ENTRY_TYPE = 8 then 'Authorisation' > when ENTRY_TYPE = 11 then 'Prescription' > when ENTRY_TYPE = 5 then 'Treatment' > when ENTRY_TYPE = 3 then 'Problem' > when ENTRY_TYPE = 13 then 'Discontinuation' > when ENTRY_TYPE = 6 then 'Reminder' > when ENTRY_TYPE = 14 then 'Adverse reaction' > when ENTRY_TYPE = -1 then 'Unknown' > when ENTRY_TYPE = 4 then 'Sub-problem' > when ENTRY_TYPE = 7 then 'Battery' > when ENTRY_TYPE = 10 then 'Return-Script' > else ENTRY_TYPE end) > > So, an integer value to be converted to a limited number of strings. > I could figure this out with some testing, but maybe there are some general > rules that apply to this. Testing is probably the best way. However, the difference in speed may not be significant given the increased maintainability of the table lookup based solution. > > Also, would the order of the CASE WHEN options make a difference, > speed-wise, so would it be faster to have the WHEN options in decreasing > order of frequency? The sequence of code generated compares the cases in the order written. So the common cases should go first. > > RBS > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
Hello Guenther,
in my wildest dreams... if you read carefully, *each* file is about
100-200MB. I now end up wit ha collection of 100-200 of them and need to
bundle in one file
BR
dimitris
2007/3/18, guenther <[EMAIL PROTECTED]>:
Well, actually I did not mean to post at this stage but resort to
lurking and learning, since I am still doing some rather basic
experimenting with SQLite. Anyway, I followed this thread and it strikes
me as a crack idea. But aren't these the most fun to hack on? ;)
On Sun, 2007-03-18 at 01:06 +0200, Dimitris P. Servis wrote:
> I want to do the following; save a set of 100-200 (or even more) binary
> files into a single DB file. The binary files are of 100-200 (or a bit
> more :-) ) MB each. My requirements are:
One thing that just popped up in my mind when reading this thread...
The above calculates to 10-40 (or more) GByte.
On the other hand, you recently mentioned "good old untouchable legacy
software". It may be just me, but "legacy" and "10 GByte files" just
don't mix. Did you think about this yet? Does your legacy system use a
file storage backend that easily can handle files of this size?
Just a thought...
guenther
--
char
*t="[EMAIL PROTECTED]";
main(){ char h,m=h=*t++,*x=t+2*h,c,i,l=*x,s=0; for (i=0;i>=1)||!t[s+h]){ putchar(t[s]);h=m;s=0;
}}}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Saving binary files
Well, actually I did not mean to post at this stage but resort to
lurking and learning, since I am still doing some rather basic
experimenting with SQLite. Anyway, I followed this thread and it strikes
me as a crack idea. But aren't these the most fun to hack on? ;)
On Sun, 2007-03-18 at 01:06 +0200, Dimitris P. Servis wrote:
> I want to do the following; save a set of 100-200 (or even more) binary
> files into a single DB file. The binary files are of 100-200 (or a bit
> more :-) ) MB each. My requirements are:
One thing that just popped up in my mind when reading this thread...
The above calculates to 10-40 (or more) GByte.
On the other hand, you recently mentioned "good old untouchable legacy
software". It may be just me, but "legacy" and "10 GByte files" just
don't mix. Did you think about this yet? Does your legacy system use a
file storage backend that easily can handle files of this size?
Just a thought...
guenther
--
char *t="[EMAIL PROTECTED]";
main(){ char h,m=h=*t++,*x=t+2*h,c,i,l=*x,s=0; for (i=0;i>=1)||!t[s+h]){ putchar(t[s]);h=m;s=0; }}}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Saving binary files
Dimitris Servis wrote: Hello John, You do not have to load the entire file into memory. The best way is to memory map it and use the returned pointer to copy it into the RDBMS. You can retrieve it to a file in a similar way. It helps if you store the file size in the DB so that you can create a file the correct size to act as a destination for your memcopy. It is only a few lines of code to wrap such logic along with the current Sqlite API. That's just a great idea. Is there an API in SQLite or should I wrap the native OS APIs? THANKS!!! dimitris Build your own function to go into your application or into your own Sqlite interface wrapper set. If you are using Linux just - open the file, ...optional read some details of the file to put in your Sqlite table as a key get the file size mmap it and get a pointer to the data close the file bind file ptr to the prepared SQL statement ...optional bind file size to the prepared SQL statement ...optional bind file description to prepared SQL statement sqlite step sqlite reset unmap the file The file will now be a blob in the DB and you have no memory to free. If you add the optional events you will be able to use SQL to retrieve the file based on a description or key and will have the size stored for when you want to retrieve the file and do something with it. You have totally avoided the dreaded malloc and free and their pathological side effects and can be unconcerned about the file size since the VM system takes care of it. In some circumstances it might be appropriate to register the logic as an Sqlite function so that you could pop a file in and out of the DB using SQL alone. You can do that by having an application level custom function or compiling it into the Sqlite library by taking advantage of the open source nature of Sqlite. Adding functions to the Sqlite library is very simple. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
Hello John, You do not have to load the entire file into memory. The best way is to memory map it and use the returned pointer to copy it into the RDBMS. You can retrieve it to a file in a similar way. It helps if you store the file size in the DB so that you can create a file the correct size to act as a destination for your memcopy. It is only a few lines of code to wrap such logic along with the current Sqlite API. That's just a great idea. Is there an API in SQLite or should I wrap the native OS APIs? THANKS!!! dimitris
Re: [sqlite] Saving binary files
I agree! My statement was meant "in general", hence my proposal of the sqlite3_bind_file-functions that I think would be a nice feature in SQLite. Dimitris Servis wrote: Hello Daniel, Personally I think that files should be save like files on the filesystem. Personally I think that each tool should be used for the purpose it has been created, just to generalize what you said above. Nevertheless, there are situations like mine, where you need the good old untouchable legacy software that was once run on a stanfalone platform, to work over a network in a parallel computing scheme. So you either develop a full transaction/communication/locking etc system yourself, or you try to use what's there and robust to do it... BR dimitris - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
You do not have to load the entire file into memory. The best way is to memory map it and use the returned pointer to copy it into the RDBMS. You can retrieve it to a file in a similar way. It helps if you store the file size in the DB so that you can create a file the correct size to act as a destination for your memcopy. It is only a few lines of code to wrap such logic along with the current Sqlite API. Daniel Önnerby wrote: The questions about saving files in a database is a reoccurring subject of this mailing list. How about adding a feature request for something like a sqlite3_bind_file() to load a file into a statement and maybe a sqlite3_column_savefile(). I guess this could solve some things like not loading the whole file into memory and instead stream the file on commit. Oracle has something similar like this in the PHP-implementation ( http://se2.php.net/manual/en/function.oci-lob-import.php ) Personally I think that files should be save like files on the filesystem. Best regards Daniel Eduardo Morras wrote: At 19:00 18/03/2007, you wrote: Hello John, thanks for the valuable piece of advice. The idea is that either 1) I store data in tabular form and work with them 2) I create a table of blobs and each blob is the binary content of a file (2) is my method in question, for (1) we all know it works. So I turned to SQLite just because it seems that it is a lighweight single file database. So, even if i don't like (2), I can setup an implementation where I have a file system inside a fully portable file. BR dimitris You can use zlib to dwhat you want. It has functions for add and delete files, it's flat file and provides medium/good compression. You can store your file metadata on SQLite as zip filename, name of the binary file, an abstract or even a password for zip file. HTH - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
Your BLOBs are big for rapid access as linked stored pages in an RDBMS. Individual files give processing advantages but have the downside of not being just one file like Sqlite. Design an experiment. You will find the balance between simplicity and speed which suits your application. Dimitris Servis wrote: Hello John, thanks for the valuable piece of advice. The idea is that either 1) I store data in tabular form and work with them 2) I create a table of blobs and each blob is the binary content of a file (2) is my method in question, for (1) we all know it works. So I turned to SQLite just because it seems that it is a lighweight single file database. So, even if i don't like (2), I can setup an implementation where I have a file system inside a fully portable file. BR dimitris 2007/3/18, John Stanton <[EMAIL PROTECTED]>: A word of warning if you use the traditional method, an RDBMS table with descriptive data and a reference to the name of the file storing the binary data. If you store a lot of files in a directory you can get into trouble. A robust design uses some form of tree structure of directories to limit the size of individual directories to a value which the system utilities can handle. It is very tedious to discover that "ls" does not work on your directory! Martin Jenkins wrote: > Dimitris P. Servis wrote: > >> I have to provide evidence that such an anorthodox solution is also >> feasible > > > If it was me I'd "investigate" the problem by doing the "right" thing in > the first place, by which time I'd know enough to knock up the "wrong" > solution for the doubters before presenting the "proper" solution as a > fait accompli. > >> I have to compare access performance with flat binary files > > > If I remember correctly, there's no random access to BLOBs so all you'd > be doing is storing a chunk of data and reading the whole lot back. I > don't think that's a realistic test - the time it takes SQLite to find > the pages/data will be a tiny fraction of the time it will take to read > that data off the disk. You can't compare performance against reading > "records" out of the flat file because "they" won't let you do that. In > all it doesn't sound very scientific. ;) > > Martin > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
Hello Daniel, Personally I think that files should be save like files on the filesystem. Personally I think that each tool should be used for the purpose it has been created, just to generalize what you said above. Nevertheless, there are situations like mine, where you need the good old untouchable legacy software that was once run on a stanfalone platform, to work over a network in a parallel computing scheme. So you either develop a full transaction/communication/locking etc system yourself, or you try to use what's there and robust to do it... BR dimitris
Re: [sqlite] Saving binary files
Hello Eduardo, this is one of the alternatives, for sure. It would bundle many files into one very effectively, and even without compression, you have a filesystem. However, my real problem is that I don't want to develop software for handling file access, locking, concurrency etc myself. What interests me though is your suggestion to combine the zipped (tared or whatever) file with SQLite. Thanks a lot!!! BR dimitris 2007/3/18, Eduardo Morras <[EMAIL PROTECTED]>: At 19:00 18/03/2007, you wrote: >Hello John, > >thanks for the valuable piece of advice. The idea is that either > >1) I store data in tabular form and work with them >2) I create a table of blobs and each blob is the binary content of a file > >(2) is my method in question, for (1) we all know it works. So I turned to >SQLite just because it seems that it is a lighweight single file database. >So, even if i don't like (2), I can setup an implementation where I have a >file system inside a fully portable file. > >BR > >dimitris You can use zlib to dwhat you want. It has functions for add and delete files, it's flat file and provides medium/good compression. You can store your file metadata on SQLite as zip filename, name of the binary file, an abstract or even a password for zip file. HTH - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
The questions about saving files in a database is a reoccurring subject of this mailing list. How about adding a feature request for something like a sqlite3_bind_file() to load a file into a statement and maybe a sqlite3_column_savefile(). I guess this could solve some things like not loading the whole file into memory and instead stream the file on commit. Oracle has something similar like this in the PHP-implementation ( http://se2.php.net/manual/en/function.oci-lob-import.php ) Personally I think that files should be save like files on the filesystem. Best regards Daniel Eduardo Morras wrote: At 19:00 18/03/2007, you wrote: Hello John, thanks for the valuable piece of advice. The idea is that either 1) I store data in tabular form and work with them 2) I create a table of blobs and each blob is the binary content of a file (2) is my method in question, for (1) we all know it works. So I turned to SQLite just because it seems that it is a lighweight single file database. So, even if i don't like (2), I can setup an implementation where I have a file system inside a fully portable file. BR dimitris You can use zlib to dwhat you want. It has functions for add and delete files, it's flat file and provides medium/good compression. You can store your file metadata on SQLite as zip filename, name of the binary file, an abstract or even a password for zip file. HTH - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
At 19:00 18/03/2007, you wrote: Hello John, thanks for the valuable piece of advice. The idea is that either 1) I store data in tabular form and work with them 2) I create a table of blobs and each blob is the binary content of a file (2) is my method in question, for (1) we all know it works. So I turned to SQLite just because it seems that it is a lighweight single file database. So, even if i don't like (2), I can setup an implementation where I have a file system inside a fully portable file. BR dimitris You can use zlib to dwhat you want. It has functions for add and delete files, it's flat file and provides medium/good compression. You can store your file metadata on SQLite as zip filename, name of the binary file, an abstract or even a password for zip file. HTH - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
Hello John, thanks for the valuable piece of advice. The idea is that either 1) I store data in tabular form and work with them 2) I create a table of blobs and each blob is the binary content of a file (2) is my method in question, for (1) we all know it works. So I turned to SQLite just because it seems that it is a lighweight single file database. So, even if i don't like (2), I can setup an implementation where I have a file system inside a fully portable file. BR dimitris 2007/3/18, John Stanton <[EMAIL PROTECTED]>: A word of warning if you use the traditional method, an RDBMS table with descriptive data and a reference to the name of the file storing the binary data. If you store a lot of files in a directory you can get into trouble. A robust design uses some form of tree structure of directories to limit the size of individual directories to a value which the system utilities can handle. It is very tedious to discover that "ls" does not work on your directory! Martin Jenkins wrote: > Dimitris P. Servis wrote: > >> I have to provide evidence that such an anorthodox solution is also >> feasible > > > If it was me I'd "investigate" the problem by doing the "right" thing in > the first place, by which time I'd know enough to knock up the "wrong" > solution for the doubters before presenting the "proper" solution as a > fait accompli. > >> I have to compare access performance with flat binary files > > > If I remember correctly, there's no random access to BLOBs so all you'd > be doing is storing a chunk of data and reading the whole lot back. I > don't think that's a realistic test - the time it takes SQLite to find > the pages/data will be a tiny fraction of the time it will take to read > that data off the disk. You can't compare performance against reading > "records" out of the flat file because "they" won't let you do that. In > all it doesn't sound very scientific. ;) > > Martin > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Search engines and the Sqlite.Org website
I noticed that one of the sqlite contrib files had 1500 downloads in a single day this past week, which is around 100X normal. I'm guessing it was a robot. I see that Google and many other websites' robots.txt prefer this form: Disallow: /contrib/download/ over this form: Disallow: /contrib/download for directories. Is the trailing slash required to disallow directories for (some) robots? Also, what does the trailing question mark mean in robots.txt? Disallow: /foo/bar? Is it a wildcard or does it literally mean the '?' character? The fish are biting. Get more visitors on your site using Yahoo! Search Marketing. http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
Done some testing now and surprisingly, it seems the lookup method with a join to a lookup table is very slightly faster than the CASE WHEN construction. There isn't much in it though. Also tried the shorter CASE WHEN construction, like this: > case ENTRY_TYPE > when 9 then 'Issue > when 2 then 'Note' Etc But didn't get that to work as it always produced the first WHEN option. RBS -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 15:29 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN RB Smissaert uttered: > Looking at the fastest way to convert a field in a table and wonder if in > general an update with a CASE WHEN construction or an update with a join to > a lookup table is faster. My guess is that the CASE WHEN form will be faster for small number of possibilities. It compiles into a straight list of compare instructions. > > These conversions are of this type: > > UPDATE A3SQL77D_J > SET ENTRY_TYPE = (case > when ENTRY_TYPE = 9 then 'Issue > when ENTRY_TYPE = 2 then 'Note' > when ENTRY_TYPE = 1 then 'Encounter' > when ENTRY_TYPE = 8 then 'Authorisation' > when ENTRY_TYPE = 11 then 'Prescription' > when ENTRY_TYPE = 5 then 'Treatment' > when ENTRY_TYPE = 3 then 'Problem' > when ENTRY_TYPE = 13 then 'Discontinuation' > when ENTRY_TYPE = 6 then 'Reminder' > when ENTRY_TYPE = 14 then 'Adverse reaction' > when ENTRY_TYPE = -1 then 'Unknown' > when ENTRY_TYPE = 4 then 'Sub-problem' > when ENTRY_TYPE = 7 then 'Battery' > when ENTRY_TYPE = 10 then 'Return-Script' > else ENTRY_TYPE end) > > So, an integer value to be converted to a limited number of strings. > I could figure this out with some testing, but maybe there are some general > rules that apply to this. Testing is probably the best way. However, the difference in speed may not be significant given the increased maintainability of the table lookup based solution. > > Also, would the order of the CASE WHEN options make a difference, > speed-wise, so would it be faster to have the WHEN options in decreasing > order of frequency? The sequence of code generated compares the cases in the order written. So the common cases should go first. > > RBS > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
A word of warning if you use the traditional method, an RDBMS table with descriptive data and a reference to the name of the file storing the binary data. If you store a lot of files in a directory you can get into trouble. A robust design uses some form of tree structure of directories to limit the size of individual directories to a value which the system utilities can handle. It is very tedious to discover that "ls" does not work on your directory! Martin Jenkins wrote: Dimitris P. Servis wrote: I have to provide evidence that such an anorthodox solution is also feasible If it was me I'd "investigate" the problem by doing the "right" thing in the first place, by which time I'd know enough to knock up the "wrong" solution for the doubters before presenting the "proper" solution as a fait accompli. I have to compare access performance with flat binary files If I remember correctly, there's no random access to BLOBs so all you'd be doing is storing a chunk of data and reading the whole lot back. I don't think that's a realistic test - the time it takes SQLite to find the pages/data will be a tiny fraction of the time it will take to read that data off the disk. You can't compare performance against reading "records" out of the flat file because "they" won't let you do that. In all it doesn't sound very scientific. ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Question about speed of CASE WHEN
Sorry, had to rush off and missed your alternative. Will do some testing now. RBS -Original Message- From: T [mailto:[EMAIL PROTECTED] Sent: 18 March 2007 14:55 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Question about speed of CASE WHEN Hi RBS, >> Perhaps the alternative form of the CASE statement would be faster > > What form is that? The form that I showed in my previous email. That is, using: > case ENTRY_TYPE > when 9 then 'Issue > when 2 then 'Note' etc instead of: >> case >> when ENTRY_TYPE = 9 then 'Issue >> when ENTRY_TYPE = 2 then 'Note' etc Tom - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Question about speed of CASE WHEN
RB Smissaert uttered: Looking at the fastest way to convert a field in a table and wonder if in general an update with a CASE WHEN construction or an update with a join to a lookup table is faster. My guess is that the CASE WHEN form will be faster for small number of possibilities. It compiles into a straight list of compare instructions. These conversions are of this type: UPDATE A3SQL77D_J SET ENTRY_TYPE = (case when ENTRY_TYPE = 9 then 'Issue when ENTRY_TYPE = 2 then 'Note' when ENTRY_TYPE = 1 then 'Encounter' when ENTRY_TYPE = 8 then 'Authorisation' when ENTRY_TYPE = 11 then 'Prescription' when ENTRY_TYPE = 5 then 'Treatment' when ENTRY_TYPE = 3 then 'Problem' when ENTRY_TYPE = 13 then 'Discontinuation' when ENTRY_TYPE = 6 then 'Reminder' when ENTRY_TYPE = 14 then 'Adverse reaction' when ENTRY_TYPE = -1 then 'Unknown' when ENTRY_TYPE = 4 then 'Sub-problem' when ENTRY_TYPE = 7 then 'Battery' when ENTRY_TYPE = 10 then 'Return-Script' else ENTRY_TYPE end) So, an integer value to be converted to a limited number of strings. I could figure this out with some testing, but maybe there are some general rules that apply to this. Testing is probably the best way. However, the difference in speed may not be significant given the increased maintainability of the table lookup based solution. Also, would the order of the CASE WHEN options make a difference, speed-wise, so would it be faster to have the WHEN options in decreasing order of frequency? The sequence of code generated compares the cases in the order written. So the common cases should go first. RBS Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Question about speed of CASE WHEN
Hi RBS, Perhaps the alternative form of the CASE statement would be faster What form is that? The form that I showed in my previous email. That is, using: case ENTRY_TYPE when 9 then 'Issue when 2 then 'Note' etc instead of: case when ENTRY_TYPE = 9 then 'Issue when ENTRY_TYPE = 2 then 'Note' etc Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Question about speed of CASE WHEN
Hi RBS, Looking at the fastest way to convert a field in a table and wonder if in general an update with a CASE WHEN construction or an update with a join to a lookup table is faster. Perhaps the alternative form of the CASE statement would be faster, but I don't know: case ENTRY_TYPE when 9 then 'Issue when 2 then 'Note' when 1 then 'Encounter' when 8 then 'Authorisation' when 11 then 'Prescription' when 5 then 'Treatment' when 3 then 'Problem' when 13 then 'Discontinuation' when 6 then 'Reminder' when 14 then 'Adverse reaction' when -1 then 'Unknown' when 4 then 'Sub-problem' when 7 then 'Battery' when 10 then 'Return-Script' else ENTRY_TYPE end Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
Hello Martin, If it was me I'd "investigate" the problem by doing the "right" thing in the first place, by which time I'd know enough to knock up the "wrong" solution for the doubters before presenting the "proper" solution as a fait accompli. That's already been done. It is more or less that I now harvest the misinterpretation of my own words and have to implement pretty nonsense stuff It seems like you're right inside my mind ;-) If I remember correctly, there's no random access to BLOBs so all you'd be doing is storing a chunk of data and reading the whole lot back. I don't think that's a realistic test - the time it takes SQLite to find the pages/data will be a tiny fraction of the time it will take to read that data off the disk. You can't compare performance against reading "records" out of the flat file because "they" won't let you do that. In all it doesn't sound very scientific. ;) That's absolutely correct, that's why I am so relaxed considering that I have to prove that elephants rarely fly. I'll be using SQLite as a file system as already pointed out, so the only overhead compared to reading the flat binary file is that tiny little time needed to access the record. Unless I miss something, there'll be no penalty there. However, accessing the ij-th element of each array stored in a rational database and collect all these in a vector, will be much faster if I use a nice schema rather than reading digging int o the binary files. This is my major usecase. Thanks a lot for the help dimitris
[sqlite] Database is locked - again
Hello, I'm getting this error sometimes when calling a single SELECT statement via sqlite3.so library while (I guess) cron daemon is updating the same table with the sqlite3 command line tool like: buildsqlcmd | sqlite3 /path/database.db where buildsqlcmd is a simple utility that outputs something like "INSERT INTO table ... VALUES ...;" but, as far as I understand, sqlite must handle the database access correctly delaying the SELECT query for a few seconds until INSERT is completed. is it true? how can I fix the "database is locked" behavior? my system is fedora core 1 with some updates and kernel 2.6.16. sqlite built from 3.3.13 sources. no one has access to the database except the cron and a web application written on Parser which only calls a single SELECT via sqlite3.so library. sqlite access code can be seen here: http://cvsview.parser.ru/cgi/viewcvs.cgi/sql/sqlite/parser3sqlite.C?rev=1.1=text/vnd.viewcvs-markup -- Best regards, Dmitry Bobrik sqlite on bcsoft dot org - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
That's not a bad idea at all and I'll check it out. However, since the data is written from a client, I can only do arbitrary chopping without separating them in a sensible manner. Maybe I don't need it though, as I could use it for setting up a pageing system in memory. Thanks!!! 2007/3/18, Teg <[EMAIL PROTECTED]>: Hello Dimitris, If I was going to do this, I'd chop up the binary file in some manageable length like, say 1 meg, insert each chunk as a blob,including an index record for each chunk so, you can select them in order and re-assemble them piece by piece as you enumerate through the records. In that way you never have to hold the entire 200 meg file in memory and you get some kind of random access. Basically you're using SQlite as a file system and each record becomes a "cluster". I think that's very doable as far as storage is concerned. Don't know about the locking part. Since the bottleneck is the disk drive, I'd probably use a single worker and a queue to serialize access to the DB. C Saturday, March 17, 2007, 7:06:46 PM, you wrote: DPS> Hi all, DPS> I want to do the following; save a set of 100-200 (or even more) binary DPS> files into a single DB file. The binary files are of 100-200 (or a bit DPS> more :-) ) MB each. My requirements are: DPS> 1) Many clients should be able to connect to the database to save their DPS> file. Clients are actually client programs that calculate the binary DPS> file. So the db server must be able to handle the concurrency of requests. DPS> 2) The file would be portable and movable (i.e. copy-paste will do, no DPS> special arrangement to move around) DPS> Ideally I would like to provide client programs with a stream to read DPS> and write files. I guess the files should be stored as blob records in a DPS> single table within the database. So my question is whether all this is DPS> possible, since I am not very familiar with SQLite (I have been DPS> redirected here). Just to straighten things, I know this is not the DPS> orthodox use of a DBMS, i.e. I should store my nice scientific data in DPS> tables and define good relations and suff. I really do believe in this DPS> scheme. However at this point, I have to provide evidence that such an DPS> anorthodox solution is also feasible (not to mention that I have to DPS> compare access performance with flat binary files :-/ ). DPS> TIA DPS> -- ds DPS> - DPS> To unsubscribe, send email to [EMAIL PROTECTED] DPS> - -- Best regards, Tegmailto:[EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Question about speed of CASE WHEN
Looking at the fastest way to convert a field in a table and wonder if in general an update with a CASE WHEN construction or an update with a join to a lookup table is faster. These conversions are of this type: UPDATE A3SQL77D_J SET ENTRY_TYPE = (case when ENTRY_TYPE = 9 then 'Issue when ENTRY_TYPE = 2 then 'Note' when ENTRY_TYPE = 1 then 'Encounter' when ENTRY_TYPE = 8 then 'Authorisation' when ENTRY_TYPE = 11 then 'Prescription' when ENTRY_TYPE = 5 then 'Treatment' when ENTRY_TYPE = 3 then 'Problem' when ENTRY_TYPE = 13 then 'Discontinuation' when ENTRY_TYPE = 6 then 'Reminder' when ENTRY_TYPE = 14 then 'Adverse reaction' when ENTRY_TYPE = -1 then 'Unknown' when ENTRY_TYPE = 4 then 'Sub-problem' when ENTRY_TYPE = 7 then 'Battery' when ENTRY_TYPE = 10 then 'Return-Script' else ENTRY_TYPE end) So, an integer value to be converted to a limited number of strings. I could figure this out with some testing, but maybe there are some general rules that apply to this. Also, would the order of the CASE WHEN options make a difference, speed-wise, so would it be faster to have the WHEN options in decreasing order of frequency? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
Dimitris P. Servis wrote: I have to provide evidence that such an anorthodox solution is also feasible If it was me I'd "investigate" the problem by doing the "right" thing in the first place, by which time I'd know enough to knock up the "wrong" solution for the doubters before presenting the "proper" solution as a fait accompli. I have to compare access performance with flat binary files If I remember correctly, there's no random access to BLOBs so all you'd be doing is storing a chunk of data and reading the whole lot back. I don't think that's a realistic test - the time it takes SQLite to find the pages/data will be a tiny fraction of the time it will take to read that data off the disk. You can't compare performance against reading "records" out of the flat file because "they" won't let you do that. In all it doesn't sound very scientific. ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving binary files
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dimitris P. Servis wrote: > 2) The file would be portable and movable (i.e. copy-paste will do, no > special arrangement to move around) You can do it if you drop that requirement and make it a single directory. The easiest way of dealing with large blobs is to save them as files and record the filename in the database, do you could put the files and the SQLite db in the same directory. Depending on the content you can also do things like break the blobs into pieces (eg each 10MB in size) and then have the filename be the md5 of the data. If there is duplication between blob contents then this will help save space, and is also a race free to create the blobs. > Ideally I would like to provide client programs with a stream to read > and write files. If you have to provide it at the SQLite level then look into virtual tables. You can have a backend provider that aggregates the chunks back into blobs. You can even make tables where each chunk is a row of an overall blob as the SQLite api only gives you the entire blob. > I should store my nice scientific data in > tables and define good relations and suff. I can't believe each multi hundred meg file is a single indivisible piece of data. You could store the real data as proper fields in SQLite. And if SQL semantics aren't quite what you want, you can certainly use virtual tables to make the data appear in any form you want on demand. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF/N1rmOOfHg372QQRAqaKAKDBT4fBFTzsCPuuHOMXdL1E9Y9heACfYST5 kyqWlQUCWwc7J6pYCLeuIDI= =YqhO -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
