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

Compressing Tables on SQL Server

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.

Labels (2)
1 Solution

Accepted Solutions
john_wang
Support
Support

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.

john_wang_0-1673486589122.png

 

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);

 

john_wang_1-1673486992178.png

 

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!

View solution in original post

5 Replies
MartinBurgos
Support
Support

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

Dana_Baldwin
Support
Support

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

john_wang
Support
Support

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.

john_wang_0-1673486589122.png

 

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);

 

john_wang_1-1673486992178.png

 

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!
billmcevoy
Contributor II
Contributor II
Author

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.  

john_wang
Support
Support

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-i...

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!