Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

sql server output data types

Highlighted
7 - Meteor

 What's the logic behind conversion from Alteryx field types to sql server types?
- I have found v_string usually goes to nvarchar (would have expected varchar)

and above some size (>1000, <6000) it changes to text (sometimes ntext) -> odd as sql server supports up to 8000

 

this has significant speed implications (~6x slower to text than varchar)

any ideas? 

 

Highlighted
12 - Quasar

Greg,

 

you are right V-String == Varchar . not sure why it considers as Nvarchar

are you creating the table On the Fly in Alteryx,

 

can you Design your table with Varchar if you feel that its VString? and then Map the coulumns in Alteryx

FYI : if you have your data size more than 8000 Byts, you can use Varchar (Max)

by the way, which version of SQL server are you using ?

Highlighted
7 - Meteor

Thanks - I think that is what we'll have to do... looks like [text] & [varchar](max) are similar speeds, while [varchar](xx) is much faster

(sql server 2012)

Highlighted
16 - Nebula
16 - Nebula

Alteryx assumes strings are Unicode (http://help.alteryx.com/9.5/CodePages.htm).

 

Hence nvarchar is the appropriate SQL Server type if it is creating the table.

 

 

Highlighted
12 - Quasar

@gregh

If Varchar(max) is necessary, can enable Row or Page compression on your table and test the performance?

just a thought , how we can tune outside of Alteryx

 

and Another option, did you test with Bulk Load option for loading data into SQL Server?

Highlighted
Alteryx Partner

If you make a varchar(8000) or smaller it is stored in the table dataspace. It will thus be compressed with datacompression. A varchar(MAX) is stored outside the datapage. So getting this value takes a little bit longer.

 

Look at this page: https://technet.microsoft.com/en-us/library/ms189087(v=sql.105).aspx

 

In short: varchar(MAX) is, without additional settings, sored in the same page as the record if it fits. If it doesn't fit the content will move ans only a reference remains. This will cost performance.

 

By the way: get rid of text/ntext. They will not be available soon and are already giving problems in T-SQL.

Highlighted
5 - Atom

Hi,

 

Is there a way to have the output Append option work for a pre sql statement that already created the columns as a varchar(255)?

 

thanks,

Labels