Is Textual SQL DB neutral

[Ken MacKenzie]

I have a query I have constructed and I had to deal with a composite primary key to select items;

            q = s.query(cls)

            or_cond = []

            for x in id_batch:

                pkf = [text(f + "=='" + v + "'") for f,v in zip(cls.SQL_PK, x)]

                and_cond = (and_(*pkf))

                or_cond.append(and_cond)

            q = q.filter(or_(*or_cond)).delete(synchronize_session=False)

cls.SQL_PK is a tuple of the primary key fields for the model described by class.  This is a class method that is part of a inherited class to the model

The current target is SQL Server.  My concern is using text('field = 'value'), is that going to work for other DB targets like say postgres?

The first round of doing this I tried using a tuple_(*cls.SQL_PK).in_(id_batch), but that did not work and the resulting SQL id not work in SSMS leading me to believe that SQL Server (or at least the version we are using) does not support tuples.

Ken

[Simon King]

Textual SQL is not DB-neutral in general. Luckily, in this case you
shouldn't even need it. Try something like this:

for x in id_batch:
cols = [getattr(cls, colname) for colname in cls.SQL_PK]
pkf = [(col == v) for (col, v) in zip(cols, x)]
and_cond = and_(*pkf)
or_cond.append(and_cond)

ie. use "getattr" to retrieve the actual column property from the class.

Hope that helps,

Simon

[Ken MacKenzie]

So I implemented this version with one change.  I moved this line:

cols = [getattr(cls, colname) for colname in cls.SQL_PK]

To the beginning before the loop, actually before the outer loop that parses the batch.

However, the new version is MUCH slower than the original, so I profiled it and this is what I get:

 %prun import db_api_lib.del_test

         1421296 function calls (1277536 primitive calls) in 620.434 seconds

   Ordered by: internal time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)

       12  615.512   51.293  615.512   51.293 {method 'execute' of 'pyodbc.Cursor' objects}

208876/208271    0.316    0.000    0.332    0.000 {built-in method isinstance}

96876/96874    0.184    0.000    0.185    0.000 {built-in method getattr}

    11123    0.137    0.000    0.242    0.000 elements.py:2927(__init__)

 38970/10    0.136    0.000    1.502    0.150 visitors.py:75(_compiler_dispatch)

    11123    0.128    0.000    0.397    0.000 elements.py:861(__init__)

44492/11123    0.127    0.000    1.504    0.000 operators.py:297(__eq__)

     5568    0.116    0.000    0.617    0.000 elements.py:1860(_construct)

    11132    0.113    0.000    0.246    0.000 compiler.py:676(visit_column)

    11123    0.111    0.000    1.039    0.000 compiler.py:1040(_generate_generic_binary)

     5567    0.108    0.000    0.108    0.000 {method 'fetchone' of 'pyodbc.Cursor' objects}

    11123    0.099    0.000    0.646    0.000 default_comparator.py:290(_check_literal)

    16684    0.095    0.000    0.195    0.000 operators.py:1007(is_precedent)

    11123    0.094    0.000    1.010    0.000 default_comparator.py:22(_boolean_compare)

33375/11129    0.090    0.000    1.445    0.000 {built-in method eq}

    11123    0.075    0.000    0.465    0.000 compiler.py:1164(visit_bindparam)

        1    0.075    0.075  619.467  619.467 db_base.py:138(dict_delete)

    11139    0.073    0.000    0.331    0.000 base.py:1128(decorate)

    68831    0.070    0.000    0.070    0.000 {method 'get' of 'dict' objects}

        1    0.068    0.068  620.471  620.471 del_test.py:1(<module>)

22250/5567    0.067    0.000    1.482    0.000 compiler.py:806(<genexpr>)

    11125    0.066    0.000    0.102    0.000 compiler.py:1265(_process_anon)

    11123    0.065    0.000    1.179    0.000 compiler.py:1006(visit_binary)

    11123    0.063    0.000    1.263    0.000 base.py:1306(visit_binary)

    11123    0.061    0.000    0.458    0.000 elements.py:3818(_bind_param)

    22269    0.058    0.000    0.097    0.000 compiler.py:2901(quote)

    11125    0.058    0.000    0.261    0.000 compiler.py:1246(_truncated_identifier)

    11123    0.058    0.000    1.299    0.000 annotation.py:100(__eq__)

    11123    0.056    0.000    1.066    0.000 type_api.py:60(operate)

    56386    0.055    0.000    0.055    0.000 {method 'append' of 'list' objects}

    11126    0.055    0.000    0.168    0.000 elements.py:3962(__new__)

    11123    0.055    0.000    0.334    0.000 compiler.py:1233(_truncate_bindparam)

    11125    0.054    0.000    0.192    0.000 elements.py:4073(apply_map)

    11206    0.051    0.000    0.089    0.000 elements.py:3918(__new__)

    22246    0.051    0.000    0.051    0.000 elements.py:640(self_group)

22250/5567    0.049    0.000    1.493    0.000 compiler.py:804(<genexpr>)

        2    0.048    0.024    0.049    0.024 {built-in method connect}

    11123    0.047    0.000    1.386    0.000 properties.py:269(operate)

8689/3128    0.045    0.000    1.508    0.000 {method 'join' of 'str' objects}

    11123    0.045    0.000    0.076    0.000 compiler.py:959(_get_operator_dispatch)

    11123    0.045    0.000    0.056    0.000 compiler.py:1271(bindparam_string)

    11123    0.040    0.000    0.411    0.000 annotation.py:78(_compiler_dispatch)

15610/15322    0.038    0.000    0.043    0.000 {built-in method hasattr}

     5567    0.038    0.000    0.294    0.000 elements.py:1883(<listcomp>)

    11125    0.036    0.000    0.138    0.000 _collections.py:728(__missing__)

    16691    0.035    0.000    0.090    0.000 elements.py:4216(_expression_literal_as_text)

     5561    0.035    0.000    1.539    0.000 db_base.py:174(<listcomp>)

    16704    0.033    0.000    0.055    0.000 elements.py:4220(_literal_as_text)

    11123    0.033    0.000    0.073    0.000 type_api.py:504(coerce_compared_value)

31584/31469    0.031    0.000    0.032    0.000 {built-in method len}

    11123    0.030    0.000    0.165    0.000 elements.py:2986(self_group)

     5562    0.030    0.000    0.187    0.000 result.py:1156(fetchone)

    11131    0.030    0.000    0.077    0.000 compiler.py:494(<genexpr>)

    11123    0.029    0.000    0.047    0.000 type_api.py:452(_cached_bind_processor)

Any guidance to how I can improve the performance of this solution and remain DB neutral?

[Ken MacKenzie]

rerunning with -s "time" for the %prun call.  Also with a full run as that was the partial.

In total full run should delete 9000+ records.

[Simon King]

It would be interesting to see the profile of the textual SQL version.
It looks like most of the time is being spent inside pyodbc, rather
than SQLAlchemy, so I guess it must be something to do with the
processing of bind parameters. How many parameters are being sent in
per query? ie. what is len(id_batch) * len(cls.SQL_PK)?

You could try playing with your batch sizes to see what sort of effect that has.

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Ken MacKenzie]

After the current sorted profile finishes I will revert to the textual version and run a profile on that.  I expect another 10-15 minutes for this to finish right now.

At present the batch size is set to 1000, total record count is just over 9000 in these tests.

The reason for 1000 was at first I was looking at doing this as a tuple_(fld, fld).in_((val, val),(val,val)) format.  The 1000 should keep me under most DB restrictions on the in statement.

However since SQL Server does not seem to support the tuple_ usage I reverted to this method.

I technically have one more method and that is a concat_ in_ where I concat the fields.

Other specifics, the table in question has 2 fields for the PK, both are varchar, one length 3, the other length 10.  There are 5 non key fields, 3 short varchars, one decimal at 14,2 precision and one varchar(800) which contains description text.

Total record count of the table before any deletion is about 1.05 million.

Python version is 3.4.5, running on a modest CentOS desktop and to be fair the SQL Server instance is sub optimal for development.

[Ken MacKenzie]

Sorted:

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)

       16 1053.794   65.862 1053.794   65.862 {method 'execute' of 'pyodbc.Cursor' objects}

348916/348311    0.539    0.000    0.556    0.000 {built-in method isinstance}

160920/160918    0.313    0.000    0.313    0.000 {built-in method getattr}

    19123    0.282    0.000    0.471    0.000 elements.py:2927(__init__)

 66982/14    0.234    0.000    2.561    0.183 visitors.py:75(_compiler_dispatch)

    19123    0.231    0.000    0.702    0.000 elements.py:861(__init__)

76492/19123    0.228    0.000    2.740    0.000 operators.py:297(__eq__)

     9572    0.213    0.000    1.092    0.000 elements.py:1860(_construct)

    19123    0.201    0.000    1.878    0.000 default_comparator.py:22(_boolean_compare)

    19132    0.194    0.000    0.424    0.000 compiler.py:676(visit_column)

     9567    0.190    0.000    0.190    0.000 {method 'fetchone' of 'pyodbc.Cursor' objects}

    19123    0.183    0.000    1.761    0.000 compiler.py:1040(_generate_generic_binary)

    19123    0.180    0.000    1.155    0.000 default_comparator.py:290(_check_literal)

    28684    0.171    0.000    0.347    0.000 operators.py:1007(is_precedent)

57379/19133    0.157    0.000    2.634    0.000 {built-in method eq}

    19123    0.133    0.000    0.786    0.000 compiler.py:1164(visit_bindparam)

    19143    0.123    0.000    0.566    0.000 base.py:1128(decorate)

   116835    0.120    0.000    0.120    0.000 {method 'get' of 'dict' objects}

        1    0.117    0.117 1061.906 1061.906 del_test.py:1(<module>)

    19123    0.116    0.000    2.005    0.000 compiler.py:1006(visit_binary)

    19123    0.114    0.000    0.816    0.000 elements.py:3818(_bind_param)

    19123    0.113    0.000    2.154    0.000 base.py:1306(visit_binary)

    19125    0.111    0.000    0.173    0.000 compiler.py:1265(_process_anon)

38254/9571    0.111    0.000    2.528    0.000 compiler.py:806(<genexpr>)

    38273    0.102    0.000    0.167    0.000 compiler.py:2901(quote)

    19123    0.099    0.000    1.976    0.000 type_api.py:60(operate)

    19123    0.098    0.000    0.565    0.000 compiler.py:1233(_truncate_bindparam)

    19125    0.097    0.000    0.438    0.000 compiler.py:1246(_truncated_identifier)

    19126    0.097    0.000    0.295    0.000 elements.py:3962(__new__)

    38246    0.093    0.000    0.093    0.000 elements.py:640(self_group)

    19123    0.092    0.000    2.376    0.000 annotation.py:100(__eq__)

    19125    0.092    0.000    0.321    0.000 elements.py:4073(apply_map)

    19206    0.089    0.000    0.155    0.000 elements.py:3918(__new__)

    88398    0.087    0.000    0.087    0.000 {method 'append' of 'list' objects}

38254/9571    0.084    0.000    2.547    0.000 compiler.py:804(<genexpr>)

    19123    0.082    0.000    2.526    0.000 properties.py:269(operate)

    19123    0.075    0.000    0.128    0.000 compiler.py:959(_get_operator_dispatch)

12693/3132    0.073    0.000    2.566    0.001 {method 'join' of 'str' objects}

    19123    0.069    0.000    0.704    0.000 annotation.py:78(_compiler_dispatch)

    19123    0.068    0.000    0.087    0.000 compiler.py:1271(bindparam_string)

        1    0.068    0.068 1060.665 1060.665 db_base.py:138(dict_delete)

     9571    0.065    0.000    0.520    0.000 elements.py:1883(<listcomp>)

    19123    0.065    0.000    2.041    0.000 <string>:1(<lambda>)

     9561    0.062    0.000    2.802    0.000 db_base.py:174(<listcomp>)

23610/23322    0.062    0.000    0.068    0.000 {built-in method hasattr}

    28695    0.061    0.000    0.156    0.000 elements.py:4216(_expression_literal_as_text)

    19123    0.059    0.000    0.129    0.000 type_api.py:504(coerce_compared_value)

    28716    0.057    0.000    0.095    0.000 elements.py:4220(_literal_as_text)

    19125    0.057    0.000    0.229    0.000 _collections.py:728(__missing__)

    19123    0.053    0.000    0.294    0.000 elements.py:2986(self_group)

     9562    0.053    0.000    0.325    0.000 result.py:1156(fetchone)

    19135    0.053    0.000    0.134    0.000 compiler.py:494(<genexpr>)

    19123    0.050    0.000    0.070    0.000 sqltypes.py:2608(_resolve_value_to_type)

    19123    0.050    0.000    0.082    0.000 type_api.py:452(_cached_bind_processor)

[Ken MacKenzie]

Sorted Textual SQL profile

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)

       16    1.840    0.115    1.840    0.115 {method 'execute' of 'pyodbc.Cursor' objects}

     9572    0.196    0.000    0.788    0.000 elements.py:1860(_construct)

    57700    0.189    0.000    0.189    0.000 {method 'sub' of '_sre.SRE_Pattern' objects}

     9567    0.185    0.000    0.185    0.000 {method 'fetchone' of 'pyodbc.Cursor' objects}

100329/99724    0.125    0.000    0.142    0.000 {built-in method isinstance}

    19123    0.120    0.000    0.200    0.000 elements.py:1225(__init__)

        1    0.117    0.117    5.042    5.042 del_test.py:1(<module>)

    19123    0.100    0.000    0.222    0.000 compiler.py:737(visit_textclause)

38254/9571    0.099    0.000    0.566    0.000 compiler.py:806(<genexpr>)

 28738/14    0.096    0.000    0.598    0.043 visitors.py:75(_compiler_dispatch)

    19123    0.086    0.000    0.286    0.000 elements.py:1240(_create_text)

38254/9571    0.079    0.000    0.585    0.000 compiler.py:804(<genexpr>)

12693/3132    0.068    0.000    0.603    0.000 {method 'join' of 'str' objects}

        1    0.065    0.065    3.811    3.811 db_base.py:138(dict_delete)

     9561    0.062    0.000    0.398    0.000 db_base.py:178(<listcomp>)

     9571    0.060    0.000    0.240    0.000 elements.py:1883(<listcomp>)

    28695    0.060    0.000    0.156    0.000 elements.py:4216(_expression_literal_as_text)

    28716    0.058    0.000    0.096    0.000 elements.py:4220(_literal_as_text)

     9562    0.052    0.000    0.317    0.000 result.py:1156(fetchone)

     9562    0.051    0.000    0.105    0.000 operators.py:1007(is_precedent)

    50154    0.050    0.000    0.050    0.000 {method 'append' of 'list' objects}

    19123    0.050    0.000    0.336    0.000 <string>:1(text)

     9572    0.048    0.000    0.203    0.000 elements.py:1865(<listcomp>)

27063/27061    0.047    0.000    0.048    0.000 {built-in method getattr}

        2    0.044    0.022    0.045    0.022 {built-in method connect}

  9571/10    0.037    0.000    0.595    0.059 compiler.py:797(visit_clauselist)

     9562    0.037    0.000    0.534    0.000 elements.py:1894(and_)

    28683    0.036    0.000    0.036    0.000 del_test.py:14(<genexpr>)

     9562    0.036    0.000    0.057    0.000 result.py:1085(process_rows)

     9561    0.029    0.000    0.159    0.000 elements.py:1971(self_group)

28504/28389    0.028    0.000    0.028    0.000 {built-in method len}

      125    0.026    0.000    0.026    0.000 {built-in method loads}

  750/724    0.026    0.000    0.340    0.000 {built-in method __build_class__}

     9561    0.025    0.000    0.560    0.000 <string>:1(and_)

     9561    0.024    0.000    0.130    0.000 elements.py:1818(self_group)

     9562    0.024    0.000    0.341    0.000 result.py:868(__iter__)

     9564    0.022    0.000    0.207    0.000 result.py:1053(_fetchone_impl)

     9572    0.022    0.000    0.032    0.000 _collections.py:788(coerce_generator_arg)

     9562    0.021    0.000    0.021    0.000 result.py:1098(<listcomp>)

    19122    0.021    0.000    0.021    0.000 elements.py:1601(self_group)

    21232    0.021    0.000    0.021    0.000 {method 'get' of 'dict' objects}

       10    0.019    0.002    0.019    0.002 {method 'commit' of 'pyodbc.Connection' objects}

    19123    0.019    0.000    0.019    0.000 compiler.py:734(post_process_text)

    290/1    0.016    0.000    5.044    5.044 {built-in method exec}

       68    0.015    0.000    0.042    0.001 inspect.py:264(getmembers)

      361    0.014    0.000    0.103    0.000 inspect.py:943(getfullargspec)

     2884    0.014    0.000    0.020    0.000 sre_parse.py:197(__next)

    10420    0.013    0.000    0.013    0.000 {built-in method __new__ of type object at 0x7f9abdca4e40}

     9561    0.012    0.000    0.012    0.000 result.py:168(keys)

      361    0.012    0.000    0.065    0.000 inspect.py:2383(from_function)

      714    0.010    0.000    0.018    0.000 inspect.py:1056(formatargspec)

      296    0.010    0.000    0.041    0.000 <frozen importlib._bootstrap>:2016(find_spec)

4486/4198    0.010    0.000    0.015    0.000 {built-in method hasattr}

   130/38    0.010    0.000    0.044    0.001 sre_parse.py:448(_parse)

[Simon King]

You could also try using executemany:

http://docs.sqlalchemy.org/en/latest/core/tutorial.html#executing-multiple-statements

I think it would look something like this:

table = cls.__table__
condition = sa.and_(
table.c.pk1 == sa.bindparam('pk1'),
table.c.pk2 == sa.bindparam('pk2'),
)
statement = sa.delete(table, whereclause=condition)
batchparams = [{'pk1': v[0], 'pk2': v[1]} for v in id_batch]
session.execute(statement, batchparams)

Simon

[Ken MacKenzie]

Not sure how I would iterate through a non predetermined number of primary keys.

I guess part of me is wondering that although textual sql is not inherently db neutral how different between the db targets is the where field = 'value' syntax?

[Mike Bayer]

it looks like you have 16 SQL statements that take a very different
amount of time depending on format. Can you please paste the output
with echo=True on create_engine() so that the difference in SQL
statements can be seen?

[Ken MacKenzie]

I tried an alternate version:

    @classmethod

    def dict_delete2(cls, dict_in):

        """

        Delete records from the database

        based on a dictionary keyed by

        PK tuple

        """

        s = dbc.get_session()

        keys_in = ["-".join(k) for k in dict_in.keys()]

        batch_size = 1000

        cols = [getattr(cls, colname) for colname in cls.SQL_PK]

        cols_dash = []

        for col in cols:

            cols_dash.append(col)

            cols_dash.append('-')

        cols_dash = cols_dash[:-1]

        print(cols_dash)

        while len(keys_in):

            id_batch = keys_in[:batch_size]

            # print(id_batch)

            del keys_in[:batch_size]

            q = s.query(cls)

            q = q.filter(func.concat(*cols_dash).in_(id_batch)).delete(synchronize_session=False)

            s.commit()

        s.close()

Performance was even worse?

         673960 function calls (659542 primitive calls) in 2315.534 seconds

   Ordered by: internal time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)

       16 2312.810  144.551 2312.810  144.551 {method 'execute' of 'pyodbc.Cursor' objects}

     9567    0.186    0.000    0.186    0.000 {method 'fetchone' of 'pyodbc.Cursor' objects}

     9572    0.166    0.000    0.433    0.000 elements.py:861(__init__)

        1    0.119    0.119 2315.536 2315.536 del_test.py:1(<module>)

62442/61837    0.101    0.000    0.118    0.000 {built-in method isinstance}

     9572    0.068    0.000    0.407    0.000 compiler.py:1164(visit_bindparam)

     9575    0.062    0.000    0.171    0.000 elements.py:3962(__new__)

     9561    0.060    0.000    0.492    0.000 functions.py:436(_bind_param)

     9574    0.059    0.000    0.090    0.000 compiler.py:1265(_process_anon)

     9574    0.054    0.000    0.227    0.000 compiler.py:1246(_truncated_identifier)

       10    0.052    0.005    0.738    0.074 default_comparator.py:110(_in_impl)

     9562    0.051    0.000    0.319    0.000 result.py:1156(fetchone)

     9655    0.049    0.000    0.084    0.000 elements.py:3918(__new__)

     9572    0.047    0.000    0.291    0.000 compiler.py:1233(_truncate_bindparam)

        2    0.047    0.023    0.048    0.024 {built-in method connect}

     9574    0.047    0.000    0.163    0.000 elements.py:4073(apply_map)

       10    0.042    0.004    0.042    0.004 {method 'commit' of 'pyodbc.Connection' objects}

    40649    0.040    0.000    0.040    0.000 {method 'append' of 'list' objects}

     9572    0.039    0.000    0.049    0.000 compiler.py:1271(bindparam_string)

  9696/14    0.039    0.000    0.521    0.037 visitors.py:75(_compiler_dispatch)

     9611    0.039    0.000    0.485    0.000 compiler.py:806(<genexpr>)

14090/13802    0.037    0.000    0.042    0.000 {built-in method hasattr}

17697/17695    0.037    0.000    0.038    0.000 {built-in method getattr}

     9562    0.037    0.000    0.058    0.000 result.py:1085(process_rows)

     9561    0.036    0.000    0.080    0.000 elements.py:4254(_is_literal)

    28683    0.036    0.000    0.036    0.000 del_test.py:14(<genexpr>)

     9562    0.030    0.000    0.068    0.000 type_api.py:504(coerce_compared_value)

       22    0.029    0.001    0.103    0.005 elements.py:1784(<listcomp>)

    12713    0.027    0.000    0.536    0.000 {method 'join' of 'str' objects}

     9572    0.026    0.000    0.038    0.000 sqltypes.py:2608(_resolve_value_to_type)

       16    0.026    0.002    0.026    0.002 {method 'close' of 'pyodbc.Cursor' objects}

     9574    0.026    0.000    0.116    0.000 _collections.py:728(__missing__)

     9584    0.026    0.000    0.067    0.000 compiler.py:494(<genexpr>)

    21325    0.025    0.000    0.025    0.000 {method 'get' of 'dict' objects}

      125    0.025    0.000    0.025    0.000 {built-in method loads}

  750/724    0.025    0.000    0.338    0.000 {built-in method __build_class__}

     9572    0.025    0.000    0.041    0.000 type_api.py:452(_cached_bind_processor)

     9562    0.024    0.000    0.343    0.000 result.py:868(__iter__)

     9564    0.022    0.000    0.209    0.000 result.py:1053(_fetchone_impl)

     9603    0.022    0.000    0.022    0.000 elements.py:640(self_group)

    10206    0.021    0.000    0.021    0.000 {method 'split' of 'str' objects}

     9562    0.021    0.000    0.021    0.000 result.py:1098(<listcomp>)

     9611    0.021    0.000    0.506    0.000 compiler.py:804(<genexpr>)

    10421    0.020    0.000    0.020    0.000 {built-in method __new__ of type object at 0x7fc6ee607e40}

     9603    0.020    0.000    0.053    0.000 elements.py:4216(_expression_literal_as_text)

     9624    0.019    0.000    0.033    0.000 elements.py:4220(_literal_as_text)

18953/18838    0.019    0.000    0.019    0.000 {built-in method len}

    10209    0.016    0.000    0.016    0.000 weakref.py:364(__getitem__)

    290/1    0.016    0.000 2315.538 2315.538 {built-in method exec}

       68    0.016    0.000    0.042    0.001 inspect.py:264(getmembers)

      361    0.014    0.000    0.102    0.000 inspect.py:943(getfullargspec)

     2884    0.014    0.000    0.019    0.000 sre_parse.py:197(__next)

     9561    0.014    0.000    0.014    0.000 result.py:168(keys)

      361    0.012    0.000    0.064    0.000 inspect.py:2383(from_function)

[Ken MacKenzie]

I am noticing the 16 execute calls.  There should only be 10 with a batch size of 1000 and a record size of 9,000 and some change.

Are you just wanting to see the sql via a print(q)?

[Ken MacKenzie]

With a batch size of 10000 based on the original selection, no improvement.

I am going to see what sql this is creating.

         2328928 function calls (2085124 primitive calls) in 1084.925 seconds

   Ordered by: internal time

   ncalls  tottime  percall  cumtime  percall filename:lineno(function)

       26 1074.733   41.336 1074.733   41.336 {method 'execute' of 'pyodbc.Cursor' objects}

        2    0.871    0.436    0.872    0.436 {built-in method connect}

349128/348523    0.556    0.000    0.573    0.000 {built-in method isinstance}

       26    0.502    0.019    0.502    0.019 {method 'close' of 'pyodbc.Cursor' objects}

       20    0.485    0.024    0.485    0.024 {method 'commit' of 'pyodbc.Connection' objects}

161062/161060    0.317    0.000    0.318    0.000 {built-in method getattr}

    19123    0.259    0.000    0.789    0.000 elements.py:861(__init__)

     9567    0.258    0.000    0.258    0.000 {method 'fetchone' of 'pyodbc.Cursor' objects}

76492/19123    0.248    0.000    2.842    0.000 operators.py:297(__eq__)

 67012/24    0.236    0.000    2.601    0.108 visitors.py:75(_compiler_dispatch)

    19123    0.234    0.000    0.424    0.000 elements.py:2927(__init__)

     9582    0.213    0.000    1.104    0.000 elements.py:1860(_construct)

    19132    0.205    0.000    0.435    0.000 compiler.py:676(visit_column)

    19123    0.189    0.000    1.806    0.000 compiler.py:1040(_generate_generic_binary)

    19123    0.183    0.000    1.263    0.000 default_comparator.py:290(_check_literal)

57389/19143    0.173    0.000    2.732    0.000 {built-in method eq}

    19123    0.171    0.000    1.912    0.000 default_comparator.py:22(_boolean_compare)

    28684    0.169    0.000    0.346    0.000 operators.py:1007(is_precedent)

    19126    0.143    0.000    0.352    0.000 elements.py:3962(__new__)

    19123    0.129    0.000    0.795    0.000 compiler.py:1164(visit_bindparam)

    19123    0.128    0.000    0.918    0.000 elements.py:3818(_bind_param)

    19153    0.126    0.000    0.581    0.000 base.py:1128(decorate)

        1    0.125    0.125 1084.925 1084.925 del_test.py:1(<module>)

   116845    0.121    0.000    0.121    0.000 {method 'get' of 'dict' objects}

38264/9581    0.115    0.000    2.567    0.000 compiler.py:806(<genexpr>)

    19123    0.113    0.000    2.048    0.000 compiler.py:1006(visit_binary)

    19125    0.113    0.000    0.174    0.000 compiler.py:1265(_process_anon)

    19125    0.109    0.000    0.451    0.000 compiler.py:1246(_truncated_identifier)

    19123    0.108    0.000    2.457    0.000 annotation.py:100(__eq__)

    19123    0.107    0.000    2.190    0.000 base.py:1306(visit_binary)

    19123    0.104    0.000    2.015    0.000 type_api.py:60(operate)

    38283    0.101    0.000    0.167    0.000 compiler.py:2901(quote)

    19123    0.095    0.000    0.577    0.000 compiler.py:1233(_truncate_bindparam)

    19206    0.094    0.000    0.163    0.000 elements.py:3918(__new__)

    19125    0.093    0.000    0.323    0.000 elements.py:4073(apply_map)

    38246    0.091    0.000    0.091    0.000 elements.py:640(self_group)

    88460    0.088    0.000    0.088    0.000 {method 'append' of 'list' objects}

    19123    0.084    0.000    2.621    0.000 properties.py:269(operate)

38264/9581    0.084    0.000    2.586    0.000 compiler.py:804(<genexpr>)

    19123    0.082    0.000    0.732    0.000 annotation.py:78(_compiler_dispatch)

12703/3142    0.076    0.000    2.605    0.001 {method 'join' of 'str' objects}

    19123    0.075    0.000    0.129    0.000 compiler.py:959(_get_operator_dispatch)

        1    0.071    0.071 1082.250 1082.250 db_base.py:172(dict_delete)

    19123    0.070    0.000    0.089    0.000 compiler.py:1271(bindparam_string)

    19123    0.070    0.000    2.085    0.000 <string>:1(<lambda>)

     9581    0.067    0.000    0.520    0.000 elements.py:1883(<listcomp>)

     9561    0.064    0.000    2.905    0.000 db_base.py:208(<listcomp>)

23611/23323    0.062    0.000    0.067    0.000 {built-in method hasattr}

    28705    0.061    0.000    0.159    0.000 elements.py:4216(_expression_literal_as_text)

    28746    0.058    0.000    0.098    0.000 elements.py:4220(_literal_as_text)

    19123    0.058    0.000    0.130    0.000 type_api.py:504(coerce_compared_value)

     9562    0.056    0.000    0.416    0.000 result.py:1156(fetchone)

    19125    0.056    0.000    0.230    0.000 _collections.py:728(__missing__)

    19123    0.054    0.000    0.296    0.000 elements.py:2986(self_group)

[Ken MacKenzie]

So the big difference between the methods when seen with print(q)

Textual SQL is building a statement with the values inline

such as where college=888 and check=1234567

The methods using the column object are building with parameters

where college = (?) and check = (?)

That is all I can see different.

[Mike Bayer]

When you call create_engine:


e = create_engine(url, echo=True)


include the "echo=True" part.

Then look at the SQL being emitted.

That's the important part here.

[Jonathan Vanasco]

Looking at your code and considering the size of your database, I've run into performance issues under PostgreSQL a few times that may be related... they're all usually because very slight text changes to a query can drastically alter the server's query planner. 

The cause/fixes were:

1. Indexes often got ignored, ending up with lots of row scans.  To fix, I would wrap the "deletion" work with some sql to setup & tear down a new index with all the columns present in the query.  That usually tricked it into using the new index.
2. Too many fkey checks onto the table for deleted items.  Deferring constraint checks often helped.
3. The position of the 'commit' ended up clearing the db's internal cache each loop, so a reread of rows/indexes would happen each iteration.  Expanding the memory allocation for caching on the server helped handle that.
4. Sometimes there was a mix of all the above and/or an issue with the size of the query (so many values).  What often helped was replacing the DELETE with INSERTS of ids to delete into a new temporary table, then deleting from that table.

[Ken M]

When I get in tomorrow I will take a look at some of these options. I also intend to spin up a quick MySQL or postgresql db to test if my textual solution can work against an alternate target.

Sent from my iPad

[Simon King]

The generic version would look something like this:

table = cls.__table__
pkconditions = []
for pk in cls.SQL_PK:
pkconditions.append(table.c[pk] == sa.bindparam(pk)
condition = sa.and_(*pkconditions)
statement = sa.delete(table, whereclause=condition)
batchparams = [dict(zip(cls.SQL_PK, v)) for v in id_batch]
session.execute(statement, batchparams)

As for the portability of the textual version: your original code
produced fragments like this:

field == 'value'

a) Is the double-equals a typo, or is it syntax that SQL Server
expects? I've never used double-equals in MySQL.
b) Are the primary key fields always strings? You later said that they
were, but if you ever had an integer primary key column, comparing it
against a quoted value would be an error on postgres (I believe).
c) Can the values that you are comparing against ever contain single
quotes? That would break your query and potentially leave you open to
SQL injection.
d) Are your values unicode strings or byte strings? If unicode, does
the encoding match what the database expects?

If none of those are issues for you, the textual version is probably
pretty safe.

Simon

[Simon King]

I'm pretty sure the bad performance is due to pyodbc (or maybe SQL
Server) not handling *huge* numbers of bind parameters gracefully. You
are generating a query with (batch_size * n_primary_key_columns) bind
parameters, which even in your original version meant 2000 bind
parameters.

Try with a batch sizes of 10, 50 and 100. You'll run more queries but
I suspect each query will perform better.

Simon

[Ken M]

Yes the double quote was a typo, sorry about that.

Your point about non varchar pk, that is why I will double check other dbs. SQL server lets it fly but I think you are right about Postgres.



Sent from my iPad

> You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/lAT3Epi7Fkw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Ken M]

I tried a batch size as small as ten and each step took about 2 seconds. I set it up to show me the time between each batch for that run. Lowering the batch size actually seems to worsen performance. Which is not what I expected.

Sent from my iPad

> You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/lAT3Epi7Fkw/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Ken MacKenzie]

Ending output from batch size of 10:

501 - 0:24:35.558103

491 - 0:24:37.197976

481 - 0:24:38.909318

471 - 0:24:40.645801

461 - 0:24:41.954807

451 - 0:24:43.628955

441 - 0:24:45.246469

431 - 0:24:46.662228

421 - 0:24:48.192343

411 - 0:24:49.839395

401 - 0:24:51.513340

391 - 0:24:53.153668

381 - 0:24:54.836501

371 - 0:24:56.372331

361 - 0:24:58.034510

351 - 0:24:59.714655

341 - 0:25:01.401695

331 - 0:25:03.026082

321 - 0:25:04.657724

311 - 0:25:06.324783

301 - 0:25:07.946133

291 - 0:25:09.347907

281 - 0:25:11.000936

271 - 0:25:12.640046

261 - 0:25:14.281258

251 - 0:25:15.939942

241 - 0:25:17.651838

231 - 0:25:19.307522

221 - 0:25:20.931354

211 - 0:25:22.589152

201 - 0:25:24.223949

191 - 0:25:25.868513

181 - 0:25:27.540003

171 - 0:25:29.198259

161 - 0:25:30.492486

151 - 0:25:31.778539

141 - 0:25:33.292928

131 - 0:25:34.966223

121 - 0:25:36.623493

111 - 0:25:38.276205

101 - 0:25:39.996527

91 - 0:25:41.374338

81 - 0:25:43.011192

71 - 0:25:44.639656

61 - 0:25:46.293672

51 - 0:25:47.954129

41 - 0:25:49.663463

31 - 0:25:51.333620

21 - 0:25:52.842479

11 - 0:25:54.489094

1 - 0:25:56.109371

0 - 0:25:56.431443

Basically the only conclusion I can draw is that it is a case of the bind params.

So I know this is not recommended but to retain DB neutrality can I use the output from query.statement.compile(dialect=s.bind.dialect, compile_kwargs={"literal_binds": True}) and feed that output back in direct to execute?

[Ken MacKenzie]

So here is my solution as of now.  As far as I can tell hits the marks of:

1. DB Neutral

2. Performance (9k records deleted in 5 seconds)

3. Generic

(excuse the print statements still there for testing)

Would welcome comments on this proposed solution.  I have learned a lot from the advice in this thread and can always stand to learn more for the sake of my code.

    @classmethod

    def dict_delete(cls, dict_in):

        """

        Delete records from the database

        based on a dictionary keyed by

        PK tuple

        """

        s = dbc.get_session()

        d_start = datetime.datetime.now()

        keys_in = [k for k in dict_in.keys()]

        batch_size = 1000

        cols = [getattr(cls, colname) for colname in cls.SQL_PK]

        while len(keys_in):

            id_batch = keys_in[:batch_size]

            del keys_in[:batch_size]

            or_cond = []

            for x in id_batch:

                pkf = [(col == v) for (col, v) in zip(cols, x)]

                and_cond = and_(*pkf)

                or_cond.append(and_cond)

            stmt = cls.__table__.delete(or_(*or_cond))

            stmt_comp = str(stmt.compile(dialect=s.bind.dialect, compile_kwargs={"literal_binds": True}))

            print(stmt_comp)

            print(len(keys_in), "-", datetime.datetime.now() - d_start)

            s.execute(stmt_comp)

            s.commit()

        s.close()

[Mike Bayer]

I'd not bother with the literal_binds and just use a literal value:

pkf = [(col == literal_column("'%s'" % v)) for (col, v) in zip(cols, x)]

but also I'd look to see what the nature of "v" is, if it's like a
Unicode object or something, you might be getting bogged down on the
decode/encode or something like that. Sending as bytes() perhaps
might change that.

>
> print(stmt_comp)
> print(len(keys_in), "-", datetime.datetime.now() - d_start)
>
> s.execute(stmt_comp)
>
> s.commit()
>
> s.close()
>
>

[Ken MacKenzie]

So inspecting the elements of the tuple, they are both str, so hence unicode.

Are you saying that if I convert those values to bytes it could improve performance?

[Ken MacKenzie]

Tried doing various conversions on the pk values as they enter the statement:

1.  to bytes

2.  to ascii

3. to latin1 (technically the same encoding as the extract source before entering the db)

None of which yielded a performance improvement for the non-compiled version.

I have read that this can be an issue with pyodbc and that there are engine settings related to it.  Also perhaps I should try using the pymssql driver to see if that changes anything.

[Ken MacKenzie]

On a side note the literal_column change worked fine performance wise allowing me to remove the literal_bind/compile step.

I guess I am 50/50 on which is cleaner.  Is either one more advantageous from a SQL injection perspective?

SQL Injection is not a primary concern at the moment for this process as what we are dealing with is a data replication service pulling from a u2 data store to sql server.  The delete is part of batch cleanup testing.