Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

Re: SQLite and large number of parameters

by bart (Canon)
on Dec 10, 2012 at 11:53 UTC ( #1008091=note: print w/replies, xml ) Need Help??

in reply to SQLite and large number of parameters

Just where do you get those values from? My guess is you don't just make them up, and the user didn't enter them manually, so probably they're coming from somewhere in the database. So probably you can get the whole list using a reasonably simple query.

If at all possible, why not use a subquery, like:

SELECT * FROM my_table WHERE col_1 IN (select id from something) AND col_2 IN (select id from something_else)
where something and something_else represent the query you used to get at that list.

If it's not that simple, at worst you can first create temporary table with the values you're looking for in one column.

p.s. It's possible that using an inner join, even on a subselect, is faster. Just test it.

SELECT * FROM my_table INNER JOIN (select col_1 from something) A USING (col_1) INNER JOIN (select col_2 from something_else) B USING (col_2)

(n.b. "USING(col1,col2)" is like "ON A.col_1=B.col_1 AND A.col_2 = B.col_2" except the "*" will pick up the column name(s) only once.)

Replies are listed 'Best First'.
Re^2: SQLite and large number of parameters
by menth0l (Monk) on Dec 10, 2012 at 13:07 UTC
    I'm afraid it's more complicated than that... These values come from bk-tree (i search it for similar strings) and their number varying from few to couple of thousands values.

      I agree with bart. I don't think it's as complicated a problem as you're making it. You have a relational database. You have a problem that is trivially solved using a relational database. Just INSERT the values INTO temporary tables and either use WHERE EXISTS (if SQLite supports it) or INNER JOIN on the tables instead. Don't knock yourself out trying to work around the limitations of the WHERE … IN clause. It simply doesn't scale to your requirements.


      Then you are going to have to develop some kind of algorithm. Perhaps you could stuff those "couple thousand values" into a temporary table and then execute an INNER JOIN against it. Like it or not, you are forced to construct a different approach to your problem.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1008091]
[stevieb]: It seems as though some Strawberry Perl downloads have incorrect checksums all of a sudden. Could someone please download http://strawberryp .12.3.0/strawberry -perl- and do an sha1sum on...
[stevieb]: I'm getting 309ad7a9ba74614fcd 0c65bff7ea4400c10f a92f, but the http://strawberryp html states 0e267fc2cf5a16126d a6f9520cc7664db63d 2b57 and want to ensure it's not just me
[stevieb]: ...or the proper download link and release page even...
[pryrt]: da39a3ee5e6b4b0d32 55bfef95601890afd8 0709 *strawberry-perl-5 .12.3.0-portable. zip
[pryrt]: matches neither. :-)
[stevieb]: wtf!? lol
[stevieb]: I'm checking some other versions. This is a little frightening
[pryrt]: Sorry, my bad: it was't done downloading when I did the sha1sum... now I match your 309ad... value

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (7)
As of 2017-03-29 21:00 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (353 votes). Check out past polls.