topic Re: Нow to speed up loading to SAP Sybase IQ? Replication latency is more than 4 hours !!! in Qlik Replicate

Нow to speed up loading to SAP Sybase IQ? Replication latency is more than 4 hours !!!

sergsyb — Wed, 03 Aug 2022 18:58:17 GMT

The target is SAP Sybase IQ. Replication latency is more than 4 hour !!!

From SAP IQ log we can see the target is not bottleneck, because SAP Sybase IQ could apply ~ 450K rows in 17 sec. (26 500 rows per sec). After SAP Sybase IQ apllied batch then SAP IQ has to wait for 6 minutes when Qlick Replicate is preparing the next batch for loading. (approx. 1250 rows per sec)

08/03 15:06:58.222204 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 25 seconds. 451050 rows inserted.
08/03 15:14:00.629046 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds. 441281 rows inserted.
08/03 15:19:57.868585 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds. 452207 rows inserted.
08/03 15:25:20.293298 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 21 seconds. 455251 rows inserted.
08/03 15:30:19.003935 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds. 452769 rows inserted.
08/03 15:36:12.073275 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds. 455442 rows inserted.
08/03 15:41:29.112759 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds. 448012 rows inserted.
08/03 15:46:42.499121 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 13 seconds. 291810 rows inserted.
08/03 15:51:20.111017 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds. 463190 rows inserted.
08/03 15:56:33.067279 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 19 seconds. 464224 rows inserted.

According to documentation there is HIGH-PERFORMANCE DATA LOADING FOR SAP SYBASE IQ https://www.qlik.com/us/sap-analytics/data-warehouse-solutions-for-sap-sybase-iq

But in our case average loading speed (aprrox. 1000 rows per sec) it seems doesn’t meets such claims

In which way we can improve loading speed to SAP Sybase IQ?

Some screenshots are attached

Re: Нow to speed up loading to SAP Sybase IQ? Replication latency is more than 4 hours !!!

Dana_Baldwin — Wed, 03 Aug 2022 21:20:43 GMT

Hi @sergsyb

From the screen capture it looks like Replicate is waiting for the target to apply a commit.

1. What version of Replicate are you running?

2. Is this a test task, or in production?

3. On the task's Monitoring tab, Tools drop-down menu, select Log Management.
On the screen that opens, please ensure the box "Store trace/verbose logging in memory, but if an error occurs write to the logs" is NOT checked. Scroll down to the following item and set it over one position to the right, Trace. Clik OK.
The change will take effect immediately (no need to stop/resume the task).
The task log will be fairly large so limit this to only 20-30 minutes before returning these logging levels back to Info, then zip the log file and attach it.
PERFORMANCE

If there is no apparent error or software issue after checking the log, I suggest engaging our Professional Services team as implementation/configuration/performance tuning falls under their purview.

Thanks,

Dana

Re: Нow to speed up loading to SAP Sybase IQ? Replication latency is more than 4 hours !!!

sergsyb — Thu, 04 Aug 2022 06:26:30 GMT

1. Qlik 2022.5.0.291
2. production

3. We will do it and let you know

Could you describe more detailed in which way Replicate send changes to IQ. Is it use row-by-row (insert statements) or it uses bulk operations with help statement LOAD TABLE from external files.

Because Sybase IQ is not an OLTP engine. IQ performs best using set and bulk operations using LOAD TABLE from external files, or using INSERT LOCATION by OpenClient protocol from compatible servers.

If QR load the data into an IQ table row-by-row (INSERT-VALUES statements, even grouped in batch) then the processing will be very slow, no matter how much your IQ server is tuned. IQ is just not designed for row-by-row activity.

Following document mention that Qlik (Attunity) Click-2-Load solution for SAP Sybase IQ leverages Sybase LOAD TABLE statement to efficiently import data from external data sources and ensures that data is loaded into Sybase IQ as fast as possible.

Please clarify Replicate is using the same approach?

Re: Нow to speed up loading to SAP Sybase IQ? Replication latency is more than 4 hours !!!

john_wang — Thu, 04 Aug 2022 06:40:18 GMT

Hello @sergsyb , copy @Dana_Baldwin ,

You are right, Qlik Replicate uses LOAD TABLE method to get best performance. In general the options is enabled, set target_load/target_apply to Verbose will get more detailed information from the task log file.

if it's off then can be turn on by

SET OPTION PUBLIC.ALLOW_READ_CLIENT_FILE='ON';

Hope this helps.

Regards,

John.

Re: Нow to speed up loading to SAP Sybase IQ? Replication latency is more than 4 hours !!!

DesmondWOO — Thu, 04 Aug 2022 08:18:57 GMT

Hi @john_wang ,

Checked Sybase website, allow_read_client_file is OFF by default.

Regards,
Desmond

Re: Нow to speed up loading to SAP Sybase IQ? Replication latency is more than 4 hours !!!

sergsyb — Thu, 04 Aug 2022 08:39:28 GMT

Just checked- Option *allow_read_client_file* set to ON

Re: Нow to speed up loading to SAP Sybase IQ? Replication latency is more than 4 hours !!!

john_wang — Thu, 04 Aug 2022 08:47:28 GMT

Hello @sergsyb ,

Thanks for the clarification. Please follow @Dana_Baldwin suggestion to open support ticket and attach the necessary files/information, support team will investigate deeper around the 6 mins gap between two loading batches. If no error/warning/one-by-one occurs then PS engaged.

BTW, if you can set (TARGET_APPLY to Verbose , PERFORMANCE to Trace) and keep it running for 20-30 minutes then it's much better.

Best Regards,

John.

Re: Нow to speed up loading to SAP Sybase IQ? Replication latency is more than 4 hours !!!

sergsyb — Tue, 09 Aug 2022 11:23:52 GMT

Update.

We have done some researches and can claim - the delay mainly depends on Target (SybaseIQ) performance.

When delay appears we can see several ready to use files for loading prepared by REPLICATE in the folder sorter.
After REPLICATE loaded changes into temporary table attrep_changesXXX it has to wait when those changes will be applied on the target.