menth0l has asked for the wisdom of the Perl Monks concerning the following question:
I have a sqlite table that has two columns: COL_A and COL_B (both integer). Now i want to select some records where col_a has values from one large (hundreds, maybe few thousands) set and col_b has values from another large set:
This will result in error:
My attempt to go around this problem resulted in this code.
This actually works but i wonder if there is a more convenient (and faster!) way to do selects with large IN statements. I can't use BETWEEN operator since those values aren't continuous.
Any ideas?
SELECT * FROM my_table WHERE col_1 IN (<large set of integers>) AND col_2 IN (<another large set of integers>)
This will result in error:
DBD::SQLite::db prepare_cached failed: too many SQL variables
My attempt to go around this problem resulted in this code.
use List::MoreUtils qw/natatime/; sub select_in_chunks { my ($self, $set_1, $set_2) = @_; my @ret; my $chunk_size = 450; my $it1 = natatime $chunk_size, @$set_1; while (my @a = $it1->()) { my $it2 = natatime $chunk_size, @$set_2; while (my @b = $it2->()) { # previous query with subsets my $records = $self->query(\@a, \@b) push @ret, @$records; } } return \@ret; }
This actually works but i wonder if there is a more convenient (and faster!) way to do selects with large IN statements. I can't use BETWEEN operator since those values aren't continuous.
Any ideas?
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: SQLite and large number of parameters
by bart (Canon) on Dec 10, 2012 at 11:53 UTC | |
by menth0l (Monk) on Dec 10, 2012 at 13:07 UTC | |
by Jim (Curate) on Dec 10, 2012 at 19:26 UTC | |
by Anonymous Monk on Dec 10, 2012 at 16:40 UTC | |
Re: SQLite and large number of parameters
by moritz (Cardinal) on Dec 10, 2012 at 11:53 UTC | |
by menth0l (Monk) on Dec 10, 2012 at 13:40 UTC |
Back to
Seekers of Perl Wisdom