Re: [Python-sybase-misc] How to set Sybase sets/tuples from Python?

[Deron M. <der...@gm...>]

> On Tue, Aug 18, 2009 at 10:37 AM, Nick Edds <ne...@gm...> wrote:
>> I was under the impression that repeat use of a query was for performance
>> given this in the documentation of execute:
>>
>> The prepared dynamic SQL will be reused by the cursor if the same SQL is
>> passed in the sql argument. This is most effective for algorithms where the
>> same operation is used, but different parameters are bound to it (many
>> times).

Well, this is technically a property of the specific database you're using.

I don't know that much really about Sybase or SQL Server.
In Oracle this is true, although the newest releases have made this
performance penalty much less.  In MySQL there's no practical
difference.

However, still, from my examples we have:

  SQL = "select foo from bar where baz in (@item0, @item1, @item2)"
  ARGS = {'@item0': 'A', '@item1': 'B', '@item2': 'C'}

That's still using substitution, which for those databases which
matter, will allow the prepared statement to be reused (for any
set of values with the same number of items).

True, if you had another query that had four items rather than three
that may require another prepare.  But it's still much better than
using embedded literals in which case you could never reuse the
same statement even for ('A','B','C') and ('X','Y','Z').

Though I'm not sure that any database supports substitutions on more
than individual (literal) values anyway; e.g., you can't substitute an
entire set for an IN clause.  (Any Sybase experts which to chime in?)


>> If the parameters are not being used for performance though, how is your
>> solution superior to the naive:
>> query = 'select foo from bar where baz in %s'
>> types = ('A', 'B', 'C')
>> cursor.execute(query % str(types))

For this example either way is fine.  However what you're doing
is building the SQL with embedded string literals.  What I
showed still used the substitution mechanism.

True, the string formatting is way is clearer, but it's also
quite fragile.  What if you have a one-tuple:
   types = ('A',).
Then you get the invalid SQL:
   .... IN ('A',)
or if types was say something other than a tuple, say
   types = ['A','B','C']
or
   types = set(['A','B','C'])


You also don't get the advantage of correct escaping.  What if
you had types = ('A\'s', 'B') -- then it would break (a possible
SQL injection attack vector).

You also don't get the type conversions; e.g.,
  types = ('A', None, u'Z\u2012', datetime.date(2009,9,18))
etc....


>> If the performance gains are of significance from repeat use of a query,
>> your solution has the advantage that the cursor will reuse the prepared
>> dynamic SQL for the 2nd+ time using any particular number of items.

Well, we really need a Sybase expert to weigh in here, which is
not me.  I can tell you that on Oracle it may make some difference,
but probably not much, especially on newer versions.  On MySQL its
a wash.
-- 
Deron Meranda