Wrapping up this thread.
What I am going to go with is the following. I'm using the initial WITH to
reduce as much as possible the number of records to be repeatedly searched:
maybe 25/30%. The addresses table currently has 4.4mm rows; it will go up
to 11.5mm when this goes to prod. I'm testing performance right now.
Might not be too good.
begin transaction;
with ver_addresses AS (
SELECT rowid as id,zip,crrt
from addresses
where version_id = '0060'
)
update addresses
set segment = '60'
where
rowid in (
select id
from ver_addresses [a]
where [a].zip = [addresses].zip and [a].crrt = [addresses].crrt
limit (
SELECT net_non_pieces
FROM crrt_net_non [b]
WHERE [b].zip = zip AND [b].crrt = crrt
)
);
commit;
On Fri, Oct 16, 2015 at 9:24 AM, Don V Nielsen <donvnielsen at gmail.com>
wrote:
> The final solution at this point is Ruby. I really want to push
> everything I can into Sqlite because *it is so freakin fast!*
> Unfortunately. I I am just not getting it. Also unfortunately, iterating,
> getting data, and updating data in scripting languages is not efficient.
> I've implemented a transaction for each crrt_net_non iteration, but that is
> still painfully slow. I will have to be more creative in my data handling
> (move to dbi versus activerecord) and partitioning of transactions
> (committing after every 100,000 updates, for example.)
>
> NetNon.all.each {|nn|
> ActiveRecord::Base.transaction {
> addrs = Address.
> where(zip:nn.zip,crrt:nn.crrt,version_id:non_sel_ver.ver_id).
> select(:id,:segment).
> take(nn.net_non_pieces)
> addrs.each {|addr| addr.update(segment:'xx') }
> }
> pb.inc
> }
>
> In English..get all rows from crrt_net_non: it has the control quantity
> net_non_pieces. Select from addresses all rows with the same zip and crrt
> values, and matching the version to be updated. I only need columns id and
> segment, and take only the number of records as calculated in
> net_non_pieces. Update the segment code. Loop.
>
> On Thu, Oct 15, 2015 at 9:47 AM, Don V Nielsen <donvnielsen at gmail.com>
> wrote:
>
>> I'm surprised that and extension for this type of functionality has not
>> been been developed by someone with the c/c++. It's seems like a natural
>> fit. I wish I had the kind of ability & smarts to do it.
>>
>> On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik <igor at tandetnik.org>
>> wrote:
>>
>>> On 10/15/2015 9:36 AM, Don V Nielsen wrote:
>>>
>>>> limit ifnull( (
>>>> select net_non_pieces from crrt_net_non net
>>>> where net.zip=zip and net.crrt=crrt
>>>>
>>>
>>> I suspect this WHERE clause is equivalent to "where net.zip=net.zip and
>>> net.crrt=net.crrt" - that is, always true. The actual limit value comes
>>> from whichever row accidentally happens to be first.
>>>
>>> --
>>> Igor Tandetnik
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>
