topic File types - import performance in QlikView

File types - import performance

Fri, 01 Apr 2011 15:05:18 GMT

Hi Everyone,

I get a lot of my data out of SAS in txt form with a pipe delimiter. To load this data is very slow, but once exported to QVD is obviously much much faster. I'm trying to speed up that initial load phase. Has anyone played around with different file types for large data files and compared the speeds of importing them into QV?

The QVD concept is very good, but you still have to get data into QV so that slow process remains unfortunately...

Thanks

Gareth

File types - import performance

fosuzuki — Sat, 02 Apr 2011 11:40:54 GMT

Hey Gareth,

i'm not really answering your question, but have you considered implementing incremental loads?

1. Load new data from text file (slow but few records)

2. Load old data from QVD file (many records, but fast)

3. Create new QVD file

This could be a workaround to reduce the size of the txt file and consequently increase speed.

Regards,

Fernando

File types - import performance

Tue, 05 Apr 2011 14:45:46 GMT

Hi mate,

Thanks for your reply. Yea it is something I have considered and just trying to find some time to rewrite parts of the script.

Still I am interested in whether anyone has information about file type speeds for future projects.

Thanks

File types - import performance

Tue, 05 Apr 2011 15:08:20 GMT

This doesn't really answer your question but I've found that loading from spreadsheets in like .xlsx is INCREDIBLY slow and loading from .csv's and text files is pretty fast comparatively. I would love to know the answer to this as well as I have a daily 2 gig csv that I would like to load into QV and that is usually a pretty difficult task.

Re: File types - import performance

Mon, 05 Sep 2011 09:28:25 GMT

I made pretty simple benchmarking for different data sources by loading two separate files with exactly the same calculations.

QlikView 9.0 SR6 is in use.

* 45000 lines in two separate files, so 90000 lines totally. Here are the results:

Loading QVD files (0:07)
Loading xls files (0:13)
Loading csv files (0:25)
Loading xlsx files (3:53)
Loading xlsm files (4:01)

Extremely slow load for new excel format (xlsx and xlsm) is amazing.

Hope these helps.

- Timo

Re: File types - import performance

Mon, 05 Sep 2011 12:44:02 GMT

XSLX files keep all data internally zipped. When accessing them they undergo decompressing before data can be extracted from them.

It's interesting that plain CSV gets loaded slower than XLS -- always thought it's vice versa. Thanks.