Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?
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 wrote:
>
>
> On Mon, Oct 8, 2018 at 9:56 AM Mike Bayer
> wrote:
>
>>
>>
>> On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski
>> 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/
Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?
On Mon, Oct 8, 2018 at 9:56 AM Mike Bayer wrote:
>
>
> On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski
> 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/
Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?
On Mon, Oct 8, 2018 at 10:28 AM Lukasz Szybalski
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
>>>
>>>
>>>
>>>
> Hello Mike.
>
> Do you have a minute to guide me on the syntax. I can't figure this out,
> and I need to fix this performance problem. If I can't figure it out I'll
> just create stored procedures to replace the query.
>
>
> Here is the code again:
>
>
>
> # 'm struggling with the syntax here for the bind param. How do I convert
> from session.execut(stmt,params) to your bindparams? I can't just cast the
> variable I'm passing to correct format?
>
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"}
)
# Keep in mind that the value is either some sqlalchemy object.columname or
> some 'other system' variable from a loop aka (for row in First_query:
> mycontract=row.contract_no
>
> #I need to go from to :
>
>>
>> From
>> 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.contract=:mycontract and D.srtype_id
>> =5",params={'mycontract':str(mycontract)}).fetchall()
>>
>> To:
>> from sqlalchmy import bindparams
>> ##mycurrent=someobject.contract_no
>> mycurrent='ABC123'
>>
>> I tried these syntax but it doesn;t work:
>
> #Incorrect Syntax#1?
>
>>
>> (bad) p=session.execute(text("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.contract=:mycontract and D.srtype_id
>> =5").bindparam('mycontract'),params={'mycontract':str(mycontract)}).fetchall()
>> AttributeError: 'TextClause' object has no attribute 'bindparam'
>>
>> #Incorrect Syntax#2?
>
>> (bad) 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.contract=:mycontract and D.srtype_id
>> =5").bindparam('mycontract').fetchall()
>> A value is required for bind parameter 'mycontract'
>>
>>
>> #Incorrect Syntax #3?
> This in the link
>
> indicated.
> Page says that a "String(50).with_variant(String(50,
> convert_unicode='force'), 'mssql')" has worked for Jan K. but it doesn't
> really say how does somebody converts their variable into correct type
> string:
>
>
>> like:
>> mycontract=cast(mycontract, String(convert_unicode='force'))
>>
>>
>> #Incorrect Syntax #4?
>
>> Since I'm passing params right after my statement should the bindparam be
>> in params section?
>> something along:
>> params={'mycontract':sqlalchemy.string(mycontract).convert_unicode='force')})
>>
>> The easiest way would be to convert mycontract into proper
>> String(convert_unicode='force') and pass mycontract as in my original query?
>>
>>
>>
>
>
>> 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]
>>
>>
>>
>>
>
>
> --
> 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
Re: [sqlalchemy] NVARCHAR vs VARCHAR in mssql + convert_unicode='force' Syntax?
> 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
>>>
>>
>>
>>
>>
Hello Mike.
Do you have a minute to guide me on the syntax. I can't figure this out,
and I need to fix this performance problem. If I can't figure it out I'll
just create stored procedures to replace the query.
Here is the code again:
# 'm struggling with the syntax here for the bind param. How do I convert
from session.execut(stmt,params) to your bindparams? I can't just cast the
variable I'm passing to correct format?
# Keep in mind that the value is either some sqlalchemy object.columname or
some 'other system' variable from a loop aka (for row in First_query:
mycontract=row.contract_no
#I need to go from to :
>
> From
> 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.contract=:mycontract and D.srtype_id
> =5",params={'mycontract':str(mycontract)}).fetchall()
>
> To:
> from sqlalchmy import bindparams
> ##mycurrent=someobject.contract_no
> mycurrent='ABC123'
>
> I tried these syntax but it doesn;t work:
#Incorrect Syntax#1?
>
> (bad) p=session.execute(text("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.contract=:mycontract and D.srtype_id
> =5").bindparam('mycontract'),params={'mycontract':str(mycontract)}).fetchall()
> AttributeError: 'TextClause' object has no attribute 'bindparam'
>
> #Incorrect Syntax#2?
> (bad) 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.contract=:mycontract and D.srtype_id
> =5").bindparam('mycontract').fetchall()
> A value is required for bind parameter 'mycontract'
>
>
> #Incorrect Syntax #3?
This in the link
indicated.
Page says that a "String(50).with_variant(String(50,
convert_unicode='force'), 'mssql')" has worked for Jan K. but it doesn't
really say how does somebody converts their variable into correct type
string:
> like:
> mycontract=cast(mycontract, String(convert_unicode='force'))
>
>
> #Incorrect Syntax #4?
> Since I'm passing params right after my statement should the bindparam be
> in params section?
> something along:
> params={'mycontract':sqlalchemy.string(mycontract).convert_unicode='force')})
>
> The easiest way would be to convert mycontract into proper
> String(convert_unicode='force') and pass mycontract as in my original query?
>
>
>
> 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]
>
>
>
>
--
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.
