topic Re: Indexing for query performance? in Qlik Compose

Indexing for query performance?

JacobTews — Fri, 14 Mar 2025 20:49:07 GMT

Hello, community!

Does anyone know of any issues with manually creating indexes on tables in the data warehouse to speed query performance? I didn't want to run into problems where Compose sees an index on a table and is confused.

We're on-prem, SQL Server.

Re: Indexing for query performance?

barbm — Wed, 13 Mar 2024 21:23:49 GMT

We, too, are on prem sql server. We've created a few indexes. We created a naming convention that did not conflict with the existing indexes. We created a daily SQL job that checks to see if the indexes are missing and will create them if they are. If you do a drop and recreate of the DW, like we do periodically in our test environment, the daily sql job automatically takes care of it. I can't recall if other modifications will drop all indexes, but we have this script running daily in prod and test environments just in case. I'd just be careful not to add too many so you don't impact loading.

Re: Indexing for query performance?

JacobTews — Wed, 13 Mar 2024 21:37:51 GMT

Thanks for the feedback!

I'd bet that the important part is choosing a naming convention that doesn't conflict with existing Compose indexes.

I suppose this is what a dev environment is for, to try things like creating indexes to make sure they don't break anything...just reading "drop and recreate" gives me chills, remembering when I had to do that in production (before I had a dev environment...long story).