[Dbix-class] Regarding Oracle's 1000 element limit in IN clause
jorge.gonzalez at daikon.es
Mon Oct 24 14:06:39 GMT 2011
El 24/10/11 13:46, Matija Grabnar escribió:
>> Question is, my server has enough RAM to slurp the resultset and then
>> search (which is what I'm doing now). If enough RAM is available, no
>> disk-based SQL server can beat that, provided that efficient search
>> algorithms are used (I'm using the resultset to create several RAM
>> based indexes - perl hashes - before doing any searching).
> You would be surprised at what disk-based SQL servers can do,
> particularly when compared to programs written in an interpreted
> language. If your RAM based searches ran fast enough for your needs, I
> don't think you'd be writing here. If they don't run fast enough for
> you, you can implement a quick benchmark to see how a DISK-based SQL
> server can do.
I would not classify Perl as an interpreted language - at least not in
the traditional sense. My search function is the perl hash search
function which I suspect is highly optimized. And I maintain my words:
no disk-based SQL server is going to beat an in-memory search, given
that the data is correctly indexed in both systems. All other factors
equal (or similar), RAM access times and disk access times are different
by several orders of magnitude.
The queries run fast enough (for now), but they will be a problem
sometime in the future: instead of doing a fixed number of queries
regardless of the number of elements to filter, I have to do one for
each 1000 elements: instead of constant cost, I have linear cost. For
now it's ok, but soon there will be not 40.000 elements but 200.000 to
filter, and then I'll have to do 200 queries instead of 40. And what
about when I have to filter by 1000000 values?
In the long term, I suppose I'll end mirroring parts of the data
warehouse locally, syncing it by the night, as someone has already
Thanks to all that have responded.
More information about the DBIx-Class