Here I am again with some additional thoughts and actuall comparison results (bear in mind this is qlikview desktop, latest version all updates):
- I have created simple load script which loads chicago parking ticket data (https://www.propublica.org/nerds/download-chicago-parking-ticket-data) which is 28mil rows csv file, when this csv is loaded I will make one more load resident from the same data (in order to remove hard disk system from equation)
- I would test other stuff as well (I did variations with some calculations while doing resident load so to move from multi threaded operation to single threaded) but I was unable to find other functions which would push computer to the limit and be measurable at the same time.
Results are as following:
- on my Intel Xeon 1650v2 system (which uses sata SSD as primary disk), I could run this script in 3 minutes 11 seconds
- on testbed AMD 2990WX system (which uses nvme SSD storage which is waaay faster), I could run same script in 2 minutes 34 seconds
- on testbed AMD 2950X system (same as above, I only changed processor), I could run same script in 2 minutes 5 seconds
Now, in theory brand new AMD 2990wx which is 32 core processor should be much faster, but in reality it is not; this is likely to its (at the moment) unique NUMA only arhitecture where not all cores have direct access to the memory. In practice, 2950x with its slightly faster cores and with UMA arhitecture ends up being faster.
What is interesting, is that processor utilization during load was about 50%, maybe slightly above that (on all cores). I wonder what I have to do to force processor to go above that because I think it should be able to import data much faster (in theory, my disk should be able to read whole csv file in 2 seconds max, so disk is not an issue here, something else is).
Does anybody else have in mind what I could test on QlikView Desktop in order to test its speed, or how can I optimize my machine to run even faster? (my work is mostly data forensics and connecting various data sets in previously unimaginable ways, so I spend most of my time loading, reloading and testing various scenarios so this is why it is important to me)
p.s. just for the record, code I used is here:
Let MyMessage = Now(1) & ' time';
Trace $(MyMessage);
Directory;
//first 1000
LOAD ticket_number,
issue_date,
violation_location,
license_plate_number,
license_plate_state,
license_plate_type,
zipcode,
violation_code,
violation_description,
unit,
unit_description,
vehicle_make,
fine_level1_amount,
fine_level2_amount,
current_amount_due,
total_payments,
ticket_queue,
ticket_queue_date,
notice_level,
hearing_disposition,
notice_number,
officer,
address
FROM
data\processed\parking_tickets.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', no eof);
Let MyMessage = Now(1) & ' time';
Trace $(MyMessage);
store parking_tickets into parking_tickets.qvd;
Let MyMessage = Now(1) & ' time';
Trace $(MyMessage);
test1:
load
hash128(license_plate_number) as H1,
AutoNumber(license_plate_number) as A1,
AutoNumberHash256(license_plate_number) as A2
resident parking_tickets;
Let MyMessage = Now(1) & ' time';
Trace $(MyMessage);
