Reload time: SQL Load vs. CSV load

[#1]

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

[Anonymous]

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.

[#3]

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

[Anonymous]

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

[#5]

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

[Anonymous]

Sweet!