topic Reload time: SQL Load vs. CSV load in QlikView

Reload time: SQL Load vs. CSV load

Thu, 05 Jul 2012 08:29:53 GMT

Hi,

I'm investigating a new way to load data into our Qlik templates.

At the moment, we do everything via a LOAD SQL but I was investigating a method to get it done faster.

This is what I have in mind:

Prod server: Run the query on the local server and export the table as CSV
Zip the CSV file
From Qlikview server: sftp get the file
Qlikview server: unzip the file
Load from CSV file

Did anybody ever tested such a method? Would loading data from a CSV be faster?

Any comment/suggestion welcome

Re: Reload time: SQL Load vs. CSV load

Anonymous — Thu, 05 Jul 2012 08:33:40 GMT

Hm, don't think you'll see a massive increase there unless the connection is very slow between QVS and SQL boxes.

Would probably recommend using QVD's (if you're not already) on the QVS side and do incremental loads only from the SQL source.

Re: Reload time: SQL Load vs. CSV load

Thu, 05 Jul 2012 08:41:52 GMT

Yes,

I'm already using QVDs and incremental load... This is much faster alreday but I'm just wondering if loading from a local CSV is faster than loading from a distant SQL Server in which case I could use CSVs.

Guess what? I'm going to make a comparison mysel

Re: Reload time: SQL Load vs. CSV load

Anonymous — Thu, 05 Jul 2012 08:57:45 GMT

Sounds good man. Please post the results. Always interesting to see new takes on processes.

Re: Reload time: SQL Load vs. CSV load

Thu, 05 Jul 2012 10:25:14 GMT

NOTE: I'm in the UK, Prod server is in Australia.

====

OS: CentOS 5.6

Table targeted content: 1.446.810 rows

SQL Query on local server into CSV: 2 min 6.96 sec - CSV file size: 631 MB

Zip CSV file: 40 secs - zipped file size: 16 MB

File transfer: 40 secs

File extraction: 10 secs

==========

RELOAD SPEED TEST:

Reload via SQL query: More than 20 min

Reload using the CSV file: 1min 30secs

CONCLUSION:

CSV transfer method: TOTAL=4m 7secs !!!

SQL LOAD : more than 20 min

WINNER: CSV transfer

Re: Reload time: SQL Load vs. CSV load

Anonymous — Thu, 05 Jul 2012 10:31:43 GMT

Sweet!