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
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
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
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,
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
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
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
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
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
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
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
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.
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
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
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
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
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
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:
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
>
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
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
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
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
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
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
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
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
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
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
-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
30 matches
Mail list logo
