SSAS Tabular joins on varchar columns - performance impact

Category: sql server powerpivotexcel

Question

VivDev on Thu, 14 Jul 2016 16:44:44


I started of building models using SSAS tabular and everythign worked great. But as expected, as the size and complexity of the model has grown performance is definitely taking a hit. 

Apart from looking at hardware requirements, I was wondering how much of an impact would converting all varchar/text based joins to integer joins would help. 

e.g. I have an employee ID field that is supposed to be 8 characters and is usually viewed that way e.g. instead of 12345 people prefer to see it as 00012345. 

I've therefore kept it as a char field with length 8 and used it in the joins. 

Would it help if I create a separate numeric ID field in my view and import that and use that for the joins in the model instead? 

Replies

Sebastian I. Sajaroff on Thu, 14 Jul 2016 16:56:43


Hi,

You shouldn't choose your data types according to what people "wants to see".

If you're storing integer values, then you need int fields, not varchar(8) or binary(4)

Padding with zeroes to the left is a front-end task, not a database one.

By the way, int joins will most probably run better than varchar(8) (because they take less space)

VivDev on Thu, 14 Jul 2016 18:05:30


Good point. Will keep in mind going forward.