On Mon, Oct 8, 2018 at 9:56 AM Mike Bayer <mike...@zzzcomputing.com> wrote:
> > > On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski <szybal...@gmail.com> > wrote: > >> >> I have a query in sqlalchemy like below where I lookup contract# in >>>>>> mssql. How do I enforce the varchar instead of nvarchar? I tried >>>>>> converting >>>>>> my field to "str(mycurrent)" but that didn't do anything. Is there some >>>>>> other spot to force VARCHAR to be sent? >>>>>> >>>>> >>>>> >>>>> this just came up in >>>>> https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case, >>>>> in that case they are using typed parameters: >>>>> >>>>> String(50, convert_unicode='force') >>>>> >>>>> if you're dealing w/ the raw string like that try this: >>>>> >>>>> execute(text("select ... where foo = >>>>> :mycurrent").bindparams(bindparam("mycurrent", >>>>> type_=String(convert_unicode='force'))) >>>>> >>>>> let me know if that works b.c. this has to be in the docs >>>>> >>>> > > you need to use the text() construct as I illustrated earlier: > > session.execute( > text("select ... where foo = > :mycurrent").bindparams(bindparam("mycurrent", > type_=String(convert_unicode='force')), > params={"mycurrent": "value"} > ) > > > > > > >> >> >>> You can find that the query that uses NVARCHAR does an index scan has >>> 30,909 logical reads on the dbo.P table. It also uses 890 ms of CPU and >>> has a total elapsed time of 938 ms. >>> >>> The query that uses VARCHAR does an index seek and has 7 logical reads >>> on the dbo.P table. It uses 0 ms of CPU and has a total elapsed time of 11 >>> ms. >>> >>> >>> p=*session.execute*("select PZ.p_id,PZ.pimage_num from dbo.P >>> with(nolock) inner join dbo.PZ PZ with(nolock) on PZ.p_id = p.p_id inner >>> join dbo.D D with(nolock) on D.p_id = p.p_id AND D.pimage_num=PZ.pimage_num >>> where p.current=:mycurrent and D.srtype_id >>> =5",params={'mycurrent':str(mycurrent)}).fetchall() >>> >>> >>> >>> >>> >>> [image: Inline image 1] >>> >>> >>> >>> SUPER FAST..now.!!! (I wonder what would be a side effect if this was done by default? aka user varchar) For Reference: I'll I split into two lines because for some reason I was making some mistake and had to google my way out of it. Version1 (easier to understand and test) *from sqlalchemy import text, bindparam,String* # ":mycontract" and "mycontract" parts bound by the bindparams to create a statemetn query. *# item in bold is what is required to get it to convert from nvarchar to varchar* stmt= text("select P.contract_id from dbo.P P with(NOLOCK) where currentstatus_id in (1,2) and current_cuntract=:mycontract"). *bindparams(bindparam("mycontract",type_=String(50,convert_unicode='force')))* #We now execute the statement from above and bind it to a variable I'm passing row.mycontract from our forloop. p=session.execute(stmt,params={'mycontract':row.mycontract}).fetchall() This causes the value to be varchar instead of nvarchar which fixes mssql : "NVARCHAR cause performance downgrade because NVARCHAR literals do not use VARCHAR indexes. When we add convert_unicode='force' all our N'something' turns into 'something' and gains performance from indexes." https://bitbucket.org/zzzeek/sqlalchemy/issues/4161/sql-server-string-use-case **Can you post above as comment to bitbucket ticket. I can't register it says "are you sure you are not a robot" with no other option ;) lol . I guess they don't get many new accounts lol Thank you sqlalchemy! Lucas -- http://lucasmanual.com/ <http://lucasmanual.com/blog/> > >> >> >> -- >> SQLAlchemy - >> The Python SQL Toolkit and Object Relational Mapper >> >> http://www.sqlalchemy.org/ >> > -- http://lucasmanual.com/ <http://lucasmanual.com/blog/> -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
