<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">(original reporter: ged) Continuing my speed optimization frenzy, here is a (very) experimental patch to optimize the ORM speed by using a custom ResultProxy which only returns dicts with whatever will be used by the ORM.</p>
<p dir="auto">Once again, the results are encouraging:<br>
~26% improvement on large queries<br>
~11% improvement on very small queries</p>
<p dir="auto">Remember that I am timing <em>total</em> time, including the time for the actual DBAPI work.</p>
<p dir="auto">A few comments about the patch:</p>
<ul dir="auto">
<li>The approach needs to be able to specify a custom ResultProxy class per query (or at least from within the ORM code path). I think this can also be useful outside the ORM, if the user wants to have its results returned in a specific way (I've had a couple times where I needed that and did it on top of the standard ResultProxy but this was not very efficient, but I didn't think to this solution at the time). Within the patch, I've only made the minimum possible modification so that it would work for my test case (attached). It would of course need to be completed, but I can't do that since I am a bit lost within that part of the code (the different ways to create a connection, and the code path it goes through the dialect etc...). I am hoping for some help here.</li>
<li>I know the custom ResultProxy approach is not compatible with the specific result proxies used by the various dialects, but I am sure it is possible to refactor the code to make that work (I haven't thought about it much yet).</li>
<li>There are two parts of that patch which are ugly to my eyes:
<ul dir="auto">
<li>the way I'm guessing what exactly will serve as a key in the dictionary. I am hoping there could be an official "contract" that for example the first element of the result_map is what is used by the ORM.</li>
<li>I have duplicated the functionality of _process_row in fetchall. This is because the overhead of the function call is significant. This is ugly, I know, but worth it IMO for such a critical part.</li>
</ul>
</li>
</ul>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/1592/NamedTuple_opt.patch">NamedTuple_opt.patch</a> | <a href="../wiki/imported_issue_attachments/1592/orm_uses_col_indexes.patch">orm_uses_col_indexes.patch</a> | <a href="../wiki/imported_issue_attachments/1592/querying_columns_only_opt.patch">querying_columns_only_opt.patch</a> | <a href="../wiki/imported_issue_attachments/1592/fastdict_resultproxy.patch">fastdict_resultproxy.patch</a> | <a href="../wiki/imported_issue_attachments/1592/kwargs_are_inefficient.patch">kwargs_are_inefficient.patch</a> | <a href="../wiki/imported_issue_attachments/1592/stress_orm.py">stress_orm.py</a></p>
<p dir="auto"><strong>Migrated issue, originally created by Brad Kittenbrink (<a href="https://github.com/brad">@brad</a>)</strong></p>
<p dir="auto">WinPython distribution, Python 3.3, 64-bit, Windows 7 64-bit, SQLAlchemy 0.8, SQL Server 2012</p>
<p dir="auto">When trying to create a table on an existing SQL Server database, the <strong>do_execute</strong> method in engine\default.py receives a bytes object in the <strong>statement</strong> argument rather than a str/unicode object.</p>
<p dir="auto">Patching the method like this allows the table creation to work:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    def do_execute(self, cursor, statement, parameters, context=None):
        #begin addition
        if isinstance(statement,bytes): statement = statement.decode('utf-8')
        #end addition
        cursor.execute(statement, parameters)"><pre class="notranslate"><code class="notranslate">    def do_execute(self, cursor, statement, parameters, context=None):
        #begin addition
        if isinstance(statement,bytes): statement = statement.decode('utf-8')
        #end addition
        cursor.execute(statement, parameters)
</code></pre></div>
<h2 dir="auto">Code to reproduce error (requires and existing SQL Server database):</h2>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="# -*- coding: utf-8 -*-
from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Table1(Base):

    __tablename__ = 'tblTable1'
    MyID   = Column(Integer     , primary_key = True, autoincrement = True, nullable = False)
    Name   = Column(String(255) , nullable = False     , server_default = &quot;&quot;, default=&quot;&quot;)
       
url = 'mssql+pyodbc://{server}/{db}'.format(server='servermachine\\servername',db='testdb')
eng = create_engine(url)    
Base.metadata.create_all(eng)"><pre class="notranslate"><code class="notranslate"># -*- coding: utf-8 -*-
from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class Table1(Base):

    __tablename__ = 'tblTable1'
    MyID   = Column(Integer     , primary_key = True, autoincrement = True, nullable = False)
    Name   = Column(String(255) , nullable = False     , server_default = "", default="")
       
url = 'mssql+pyodbc://{server}/{db}'.format(server='servermachine\\servername',db='testdb')
eng = create_engine(url)    
Base.metadata.create_all(eng)
</code></pre></div>
<hr>
<p dir="auto">Text of error message:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Traceback (most recent call last):
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py&quot;, line 757, in _do_get
    return self._pool.get(wait, self._timeout)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\util\queue.py&quot;, line 166, in get
    raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File &quot;D:\src\bug_reports\sqlalchemy_bug.py&quot;, line 24, in &lt;module&gt;
    Base.metadata.create_all(eng)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\schema.py&quot;, line 2787, in create_all
    tables=tables)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1488, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\contextlib.py&quot;, line 48, in __enter__
    return next(self.gen)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1481, in _optional_conn_ctx_manager
    with self.contextual_connect() as conn:
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 1671, in contextual_connect
    self.pool.connect(),
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py&quot;, line 272, in connect
    return _ConnectionFairy(self).checkout()
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py&quot;, line 425, in __init__
    rec = self._connection_record = pool._do_get()
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py&quot;, line 777, in _do_get
    con = self._create_connection()
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py&quot;, line 225, in _create_connection
    return _ConnectionRecord(self)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py&quot;, line 322, in __init__
    exec_once(self.connection, self)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\event.py&quot;, line 390, in exec_once
    self(*args, **kw)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\event.py&quot;, line 407, in __call__
    fn(*args, **kw)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\strategies.py&quot;, line 168, in first_connect
    dialect.initialize(c)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\connectors\pyodbc.py&quot;, line 138, in initialize
    super(PyODBCConnector, self).initialize(connection)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\dialects\mssql\base.py&quot;, line 1125, in initialize
    super(MSDialect, self).initialize(connection)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\default.py&quot;, line 172, in initialize
    self._get_default_schema_name(connection)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\dialects\mssql\base.py&quot;, line 1141, in _get_default_schema_name
    user_name = connection.scalar(&quot;SELECT user_name()&quot;)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 597, in scalar
    return self.execute(object, *multiparams, **params).scalar()
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 664, in execute
    params)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 808, in _execute_text
    statement, parameters
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py&quot;, line 871, in _execute_context
    context)
  File &quot;D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\default.py&quot;, line 324, in do_execute
    cursor.execute(statement, parameters)
TypeError: The first argument to execute must be a string or unicode query."><pre class="notranslate"><code class="notranslate">Traceback (most recent call last):
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 757, in _do_get
    return self._pool.get(wait, self._timeout)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\util\queue.py", line 166, in get
    raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "D:\src\bug_reports\sqlalchemy_bug.py", line 24, in &lt;module&gt;
    Base.metadata.create_all(eng)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\schema.py", line 2787, in create_all
    tables=tables)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 1488, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\contextlib.py", line 48, in __enter__
    return next(self.gen)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _optional_conn_ctx_manager
    with self.contextual_connect() as conn:
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 1671, in contextual_connect
    self.pool.connect(),
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 272, in connect
    return _ConnectionFairy(self).checkout()
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 425, in __init__
    rec = self._connection_record = pool._do_get()
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 777, in _do_get
    con = self._create_connection()
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 225, in _create_connection
    return _ConnectionRecord(self)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\pool.py", line 322, in __init__
    exec_once(self.connection, self)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\event.py", line 390, in exec_once
    self(*args, **kw)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\event.py", line 407, in __call__
    fn(*args, **kw)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\strategies.py", line 168, in first_connect
    dialect.initialize(c)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\connectors\pyodbc.py", line 138, in initialize
    super(PyODBCConnector, self).initialize(connection)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1125, in initialize
    super(MSDialect, self).initialize(connection)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\default.py", line 172, in initialize
    self._get_default_schema_name(connection)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1141, in _get_default_schema_name
    user_name = connection.scalar("SELECT user_name()")
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 597, in scalar
    return self.execute(object, *multiparams, **params).scalar()
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 664, in execute
    params)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 808, in _execute_text
    statement, parameters
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 871, in _execute_context
    context)
  File "D:\WinPython\python33x64\python-3.3.0.amd64\lib\site-packages\sqlalchemy\engine\default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
TypeError: The first argument to execute must be a string or unicode query.
</code></pre></div>