none
Recent Super Slow Performance on XLS/XLSX Files RRS feed

  • Question

  • Hi guys,

    I run a query every 30 days. I assume I got an PWQR update since the last time I ran the query. I am pulling data from multiple XLS/XLSX files. They are not big. Max few hundred rows each. No extremes.

    I dont why but the entire query now takes literally 10x more time to run. It is just superslow.

    Anyone experiencing the same issue with XLS/XLSX files?

    DZ

    Wednesday, October 26, 2016 7:45 AM

Answers

All replies

  • No, I haven't observed such a behaviour.

    If you're importing from sheet instead of from a table object or named range, it is possible that due to some formatting or other event, many blank cells are actually being imported. Are you able to check that?

    This would normally also effect the file size of the xlsx-files.


    Imke Feldmann TheBIccountant.com

    Wednesday, October 26, 2016 12:48 PM
    Moderator
  • Hi Imke,

    yes, it is from sheets. There are no tables in the docs. The files are small though.

    The main issue is that is worked fine last month but starting today, it is super slow.

    DZ

    Wednesday, October 26, 2016 1:55 PM
  • Well, that was just a possibility. Sorry, no further ideas.

    I just learned that there is a big performance difference between accessing xlsx-files and csv-files: Xlsx-files are going to be called 1x per row!!!. So if you have the chance to convert your source data to csv instead, import should be done in a breeze.


    Imke Feldmann TheBIccountant.com

    Wednesday, October 26, 2016 7:02 PM
    Moderator
  • hmmm, unfortunately not an option for me to convert to CSVs... :(

    What amazes me it used to work fine for 1.5 years, now BOOM and it is still working but superslowly.

    DZ

    Thursday, October 27, 2016 7:35 AM
  • Yes, of course it is possible that some change in the algorithm has caused that. Are you able to run the file on the input of the previous month? 

    If that's also superslow, it might be a good idea to create a bug-report via send-a-frown.


    Imke Feldmann TheBIccountant.com

    Thursday, October 27, 2016 8:08 AM
    Moderator
  • Yea, I am. It is slow as well.... Basically, every month I just a new files containing data for the current month...

    Send Frown also stopped working few months ago for me (outlook would not start).

    Seems like my pwqr is sentenced to death :).

    Thursday, October 27, 2016 8:11 AM
  • Hi Daniel,

    Are you able to share a sample xlsx file with us (one where loading is slow)?

    Ehren

    Thursday, October 27, 2016 6:04 PM
    Owner
  • Sure, but it is like 50 small files to be loaded. You can contact me at mail@danzrust.cz and I will send/share you entire setup...
    Monday, October 31, 2016 8:44 AM