Thanks for the info, Niphlod. I will look into the efficiency of the IN clause for my needs, as it appears to offer a solution which may mollify the IT DBA, and his demands for bind vars (they are concerned that a looped select will bring the db to it's knees).
Martin Barnard. On 6 April 2013 00:07, Niphlod <[email protected]> wrote: > I really don't understand. > The query "shipped" to your oracle instance will be exactly the same using > DAL as the one using "bind variables". > > The DAL just does binding for you (in the 90% of the cases, better) > > If you want to retrieve from a table all records in one query, and you > have a "large" list of "key values" to filter it with, you can do it only > with two syntaxes.... > either > select * from table where column in (1,2,3,4,5,6,....) > > or > select * from table > where > column = 1 > or > column = 2 > or > column = 3 > or > column = 4 > > > > Now, every database has its own query planning, but as far as I know > Oracle responds better to the first one than the latter. > For the sake of completeness, MSSQL is more fun .... MSSQL 2000 to 2005 > without any SP performed better on the first, while 2005 sp2 to 2008 R2 > performs best with the latter ... Postgresql instead has similar times but > from 8.4 to 9.1 there has been a steep improvement on it, etc etc etc > > Binding with "in" won't get you where you want to go.... you have to > prepare the "in" list as a "pre-quoted" string and pass that as a > parameter, however, as I was saying, there will not be any difference on > the query shipped to your db instance. > > Same thing goes for the "or" method ..... > > For the sake of completeness #2, in web2py should resemble similarly to > mems = [1,2,3,4,5, ...] > q = [] # list of conditions > for c in mems: > q.append[db.table1.field == c] > all_queries_in_or = reduce(lambda a,b: (a | b ), q) > result = db(all_queries_in_or).select() > > > > -- > > --- > You received this message because you are subscribed to a topic in the > Google Groups "web2py-users" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/web2py/E2pVWl_71t4/unsubscribe?hl=en. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
