topic Re: Compressing Tables on SQL Server in Qlik Replicate

Compressing Tables on SQL Server

billmcevoy — Tue, 10 Jan 2023 18:45:48 GMT

Is there a way to specify in QLIK Replication that when it creates target tables in SQL Server that it also enable compression? It would be preferred to do it on a table by table basis.

What happens now is that we enabled compression in the target after QLIK created the tables, but when the task was restarted, the old tables were dropped and new tables added without compression.

We needed to restart the task to bring over new columns from the Oracle source, otherwise we would normally just truncate and import.

Re: Compressing Tables on SQL Server

MartinBurgos — Wed, 11 Jan 2023 08:15:59 GMT

Hi Billmcevoy,

Hope all is good on your end.

Unfortunately, that won’t be possible because it’d slower the task RW process, resulting in bad performance.

Hope this helps!

Martin

Re: Compressing Tables on SQL Server

Dana_Baldwin — Wed, 11 Jan 2023 15:28:32 GMT

Hi @billmcevoy

If you would like to pursue this further, you can submit this as a feature request here for our Product Management team to evaluate:

https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

Thanks,

Dana

Re: Compressing Tables on SQL Server

john_wang — Thu, 12 Jan 2023 01:33:28 GMT

Hi @billmcevoy ,

Totally agree with @Dana_Baldwin . In general we may custom the target endpoint CREATE TABLE SQL statements to add some special settings eg COMPRESSION, NLS language, CODEPAGE, Storage setting etc. However there are a few of exceptions, SQL Server target is one of the exception unfortunately . That's why we need a FR.

In current Replicate version the available options:

1. We need to create the table with COMPRESSION properties manually prior to the task FULl Load by setting "If target table already exists" to "TRUNCATE before loading" in Replicate. With this setting Replicate will keep the COMPRESSION properties of the table.

2. Or, you may let Replicate create the target table automatically without COMPRESSION (so far we need not to create the table manually), after the Full Load done then compress the table manually, then resume the task.

alter table table_name
rebuild with (datacompression = page | row | none);

Hope this helps.

Regards,

John.

Re: Compressing Tables on SQL Server

billmcevoy — Thu, 12 Jan 2023 04:15:45 GMT

Thank you for your answers everyone. Even with compression on (I added it after the tables were created) performance is not an issue. In fact, we are all very impressed with the performance.

Re: Compressing Tables on SQL Server

john_wang — Fri, 13 Jan 2023 09:20:27 GMT

Hello @billmcevoy ,

Thank you for you opening the FR:

https://community.qlik.com/t5/Suggest-an-Idea/Add-option-to-run-a-SQL-script-when-a-Replicate-task-is/idi-p/2025067

Regards,

John.