Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
sergsyb
Contributor III
Contributor III

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

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)

 

  1. 08/03 15:06:58.222204 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 25 seconds.  451050 rows inserted.
  2. 08/03 15:14:00.629046 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds.  441281 rows inserted.
  3. 08/03 15:19:57.868585 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds.  452207 rows inserted.
  4. 08/03 15:25:20.293298 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 21 seconds.  455251 rows inserted.
  5. 08/03 15:30:19.003935 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds.  452769 rows inserted.
  6. 08/03 15:36:12.073275 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds.  455442 rows inserted.
  7. 08/03 15:41:29.112759 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds.  448012 rows inserted.
  8. 08/03 15:46:42.499121 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 13 seconds.  291810 rows inserted.
  9. 08/03 15:51:20.111017 Insert for 'attrep_changes9CF96B7E39EADDA6' completed in 17 seconds.  463190 rows inserted.
  10. 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

1 Solution

Accepted Solutions
sergsyb
Contributor III
Contributor III
Author

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.

View solution in original post

7 Replies
Dana_Baldwin
Support
Support

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

sergsyb
Contributor III
Contributor III
Author

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?

john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
DesmondWOO
Support
Support

Hi @john_wang ,

Checked Sybase website, allow_read_client_file is OFF by default.

Regards,
Desmond

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
sergsyb
Contributor III
Contributor III
Author

Just checked- Option *allow_read_client_file* set to ON
john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
sergsyb
Contributor III
Contributor III
Author

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.