Improve performance Direct Query Mode

[luxpbi]

Hi all, 

I have a Direct Query Mode not complex at all and with a few thousand rows. 

I have two visuals, one that works only with one table and another one with this measure: 

T = 
VAR C = SELECTEDVALUE(Table1[Column1])
RETURN
CONCATENATEX(FILTER('Table2';
C = 'Table2'[Column1]);'Table2'[Column2];", ")

My first visuals is super fast but my problem is that my second visuals is very very slow, and sometimes lockes the sql server table. 

Any idea of how to improve the performance ?

I have try to do a lef join in sql native query but it is even slower... 

I appreciate any help. 

Thank you in advance!

[vanessafvg]

@luxpbi 

have you created indexes on your sql table on the columns you using to filter on?

if you do a join via sql have you tried doing a dirty read in sql ie using select * from table with (nolock)

[luxpbi]

Hi @vanessafvg, thank you a lot for your reply. 

Please consider me a novice with sql servers. 

The tables comes from Navision. 

I don't quite understand why would it help to do a dirty read as select * from table. Could you please explain? 

Thank you again!

[vanessafvg]

@luxpbi  is this directly on navision of a copy of navision?

what i am saying is what if you want ot optimise performance and using direct query, you probably need to optimise on the actual tables themselves in sql  as  sql novice i would read up on indexes read up on using sql profiler to understand your where the read is taking so long.

https://www.dummies.com/programming/sql/how-to-create-an-index-in-sql/