I have an items tables, a tokens table, and a items_tokens join table.
There
can be multiple joins for an items, tokens pair, i.e. a token appears
multiple
times in an item. I would like to increment a column in the tokens for
EACH
time it appears in in the join table. It seems that “UPDATE tokens JOIN
items_tokens ON items_tokens.token_id = tokens.id set occurrences =
occurrences + 1 WHERE items_tokens.item_id = 339;” (for item 339) would
do the
trick but it only increments each occurrences at most once, i.e.
duplicates
are removed before incrementing occurrences. Is there a way to do this
in ONE
SQL statement?
TIA,
Jeffrey
Why don’t you just use a counter_cache on Token?
ActiveRecord was great for prototyping the proof of concept. But it’s
too
slow for production. I need to keep it in all SQL.
Thanks,
Jeffrey
Quoting Rein H. [email protected]:
Sorry, I wasn’t specific enough. It is too slow for one of my
applications
for just one of the models w/ a join table. When a cronjob that is
invoked
every five minutes occasionally runs for over 20 minutes with actual
data, an
application is too slow and it is time to start profiling and
benchmarking.
The results are interesting, to me at least.
Pure Active Record: 7.2sec
SQL w/ join table: 0.77sec
SQL w/out join table: 0.20sec
The application is an adaptive RSS reader. Every article has many words
w/
statistics for each word (occurrences, click-thrus, up-votes,
down-votes,
etc.). It is faster to regenerate the word list on the fly each time
and
select on the word’s string field than use a join table with select with
joins
on the IDs.
As always, YMMV. So measure, then cut (code).
Jeffrey
Quoting Rein H. [email protected]:
LMAO. Good luck with that. And don’t tell all the people running Rails
with AR in production. I’m sure they’ll be devastated to hear that
their sites are too slow.
On Aug 17, 11:23 pm, “Jeffrey L. Taylor” [email protected]