> On 15 Nov 2013, at 17:00, John Kida <[email protected]> wrote: > > Hi guys, i dont really think this is sqlalchemy issue, but thought someone > might be able to tell me why. > > I have a large list of id values, over 100,000 that are given to me from a > text file.. Now I need to pull all rows that match these ids out of my > database. When it has to create the WHERE IN statement with raw id values it > takes forever to run... BUT.. when i prototype a test by doing SELECT * FROM > test where id IN (SELECT test.id FROM test LIMIT 100000) then it returns > instantly... WHY? > > > ie. SELECT * FROM test where id IN (SELECT test.id FROM test LIMIT 100000) > is much much much much faster then. > > SELECT * FROM test where id IN (id1,id2,id3,id4,id5 ... id100000) > > Howcome? The only other option i think of is to take the 100000 id values > from the text file insert them into a TEMP TABLE then select them out in the > WHERE IN clause. > > > Any other suggestions?
I had this problem a while ago (not quite as many values as you've got, but perhaps a few hundred). When I profiled it, it turned out that sqlalchemy was spending a lot of time building the bind parameters for the query. Because of the way bind parameters work, each id needs a separate parameter. Is it important that you get the results all in one go? Could you try processing the ids in smaller batches, perhaps 100 or so at a time? Simon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
