Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Reload time: SQL Load vs. CSV load

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

Tags (3)
5 Replies
jsn
Honored Contributor

Re: Reload time: SQL Load vs. CSV load

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.

Not applicable

Re: Reload time: SQL Load vs. CSV load

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

jsn
Honored Contributor

Re: Reload time: SQL Load vs. CSV load

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

Not applicable

Re: Reload time: SQL Load vs. CSV load

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

jsn
Honored Contributor

Re: Reload time: SQL Load vs. CSV load

Sweet!

Community Browser