Very Slow Data Load - 13 CSV files (folder) sizes 41-134mb

[Anonymous]

Hi all,

I have a folder query that references (currently) thirteen CSV files with sizes of 41 to 134mb. When refreshing, the query reaches the final step (the 131mb file), and just sits - literally hours. Total size of files combined is 812mb.

Waiting long enough allows the refresh to complete, but hours is not a sustainable approach. I've tried using DAX Studio to work out what is going on, but I cannot interpret the results very accurately.

I have turned off "Allow data preview to download in the background"

If anybody can help me either with solutions or potential diagnostics using DAX Studio I'd be appreciative. 

The query is not complex:

[amitchandak]

@Anonymous , Try these settings. Enable parallel load and increase memory management cache to 8-10 GB (should be supported by RAM for virtual memory)

[Anonymous]

Morning @amitchandak ,

Tried as you suggested. No luck.

System is: Intel(R) Core(TM) i5-8265U CPU @ 1.60GHz with 16gb RAM, but the 32gb RAM machine next to me doesn't do it much faster.

I wondered if there was some other issue? I can load significantly larger data sets from other sources e.g. Google Analytics, SQL Analysis Services, much faster. 

Is there any way with DAX Studio to diagnose it? As mentioned, I've run it simultaneously, but I received no updates between query refresh start and finish, so perhaps I'm not using the tool properly/

[Anonymous · Accepted solution]

Just recreated the query in a brand new Excel sheet. No problems. So went back over the initial query and noted the "sort rows" step. That was the problem!!

Apologies for wasting your time @amitchandak  shows you sometimes the solution is right in front of you

[waeltken]

Zhivana,

How did you identify the "sort rows" step as the culprit? And how did you have to alter it in order for the file ballooning to stop? I have a similar issue with my model.

Regards,

- H

[Anonymous]

I can't recall exactly, but it was largely a "eureka" moment.

I was looking through each step in the query (as part of a general diagnostic) and wondered why I had sort rows in at all. 

So, I got rid of it, and it sped up considerably.

I simply deleted it - it didn't add any value anyway (and I'm pretty sure a new index column would achieve the same result in less time).