File types - import performance

[#1]

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

[fosuzuki]

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

[#3]

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

G

[#4]

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.

[#5]

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

[#6]

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.