[Dbix-class] Regarding Oracle's 1000 element limit in IN clause

[Jorge Gonzalez]

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 
suggested.

Thanks to all that have responded.
BR
J.