e...@precompiled.com wrote:
> Hi Michael, > > It's a MSSQL (2012) backend. > > We have tried pyodbc and pymssql, using pyodbc or pymssql gives the same > results. Pyodbc uses an sp_prepexec procedure, which improves performance in > some cases, and pymssql doesn't, but in either case the parameters passed to > the functions are unicode. > > If I explicitly cast the parameter in my Python code to a regular string > using str(), before using it in the ORM, the query does not have the N prefix. > However, the parameter isn't something we create in our code, it also comes > from a VARCHAR database field, so SQLAlchemy converts it to unicode before > passing it to the application. > I have also read somewhere that SQLAlchemy does this, because it's best > practice to always use unicode strings in your Python code, which makes sense. > > However, in this specific case, we'd like to circumvent this because of the > performance hit in databases with the "wrong" collation. SQLAlchemy only encodes unicodes to strings going in, if necessary. On the result side, it will coerce bytestrings to unicodes again only if called for; it is likely that the DBAPI is doing that in your case, especially if the originating column is an NVARCHAR. If you want a type that will coerce unicode to bytes first, you can set “convert_unicode=‘force’” on your String or Unicode type, and that will force it to encode the value into a bytestring first before passing it in. > > > > > Op donderdag 12 februari 2015 16:18:45 UTC+1 schreef Michael Bayer: > > > er...@precompiled.com wrote: > > > Hi all, > > > > We are struggling with bad performance in a flask/sqlalchemy app on some > > databases, but not on others. > > Structurally, the databases are the same, however, we have noticed that the > > collation is different. > > > > The database that performs well uses Latin1_General_CI_AI, the bad > > performing one uses SQL_Latin1_General_CP1_CI_AI. > > We found out that running a simple select runs fine on the first server, > > badly on the second one. Here is an example query: > > > > SELECT TOP 5 * > > FROM accesscontrollog > > WHERE accesscontrollog.aclog_term_id = > > N'9041517B-AC64-411D-9E13-24B5EB49B38A' > > > > > > The thing is, accesscontrollog.aclog_term_id is a VARCHAR(36) field, and > > the parameter also, is a regular (non-unicode) string. > > If we remove the N-prefix from the parameter, the query runs fine on both > > databases. The reason is that SQL_Latin1_General_CP1_CI_AI is much more > > strict in the use of unicode, and for that reason skips some indexes we > > have because it sees the parameter is unicode. > > The Latin1_General_CI_AI collation is more forgiving, and uses the index > > regardless. > > > > Until we find a way to change the collation of the bad-performing database > > without (much) downtime, we are looking for an option to have SQLAlchemy > > pass non-unicode parameters without the N-prefix. > > > > I have looked and looked, but I haven't found an option in SQLAlchemy to do > > so. > > Is there such an option? > > > > Thanks in advance for any tips and your time. > > It’s kind of important what database backend and what DBAPI driver we’re > talking about here. > > That N is likely added by your DBAPI driver, so you might have to figure out > what kind of value can be passed to it which it will not perform this with > (e.g. a bytes perhaps). SQLAlchemy can be tailored to send any kind of > value on a per-column basis. > > > > -- > 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+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
