Im working on getting off bitbucket but maybe it's an ad blocker? try a private window or something
On Mon, Oct 8, 2018 at 6:36 PM Lukasz Szybalski <szybal...@gmail.com> wrote: > > > 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. > -- 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.
