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. Erik -- 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.
