On 11/13/07, Benilton Carvalho <[EMAIL PROTECTED]> wrote:
> I have "fixed" my problem, working late night and not sleeping usually
> causes that: I missed a WHERE on the UPDATE statement, so I could
> modify multiple rows at a time. Basically what I did was create a
> primary key (integer), which is the "row number of my matrix" and run
> the UPDATE like:
>
> UPDATE table SET column = <values> WHERE id = <ids>;
>
> shame on me... and I apologize for missing such basic thing...
No worries, we all miss that one at one time or another.
> But, anyways, the first INSERT takes 4secs for all 1M rows.. the
> updates take 9secs, each.
I'm impressed with your storage bandwidth. If you don't need the
database to be persistent (you restart the whole process in case of
failure), turning the synchronous pragma off may help your I/O times a
bit. SQLite won't try so hard to keep the data safe from
interruptions that way.
> I'm in the process of creating the whole db now and will give it a
> try... maybe creation is the worse part, as I'm going to be accessing
> contiguous sets of rows (all columns) at a time... something like:
>
> SELECT * FROM table LIMIT 10000 OFFSET 20000;
If possible, I'd recommend a simple "SELECT * FROM table;" and only
fetch 10000 at a time. I don't know what the RSQLite interface is
like though. (The reasoning is that OFFSET must look at all the rows
prior, so each query will take longer than the previous.) But yes, I
would expect this to work much faster than the updates, as it should
be very nearly contiguous on disk.
My only other suggestion at the moment would be to buffer and batch
updates if possible. Process a few columns at a time, and update them
all at once like:
UPDATE table SET column1 = <value>, column2 = <value> ... WHERE id = <id>;
Each update should take about the same amount of time (9s), but you'll
get more done each time.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
