On a few occasions we use the history table to calculatie timings and would like to add an index to the history table for improved performance.
Page 1 / 1
New→Open
Open→Working on it!
Working on it!→Next release
Next release→Completed
If you want to use an index on the PK of the original table, you can use the thinkstore model “Add index temporal table” for this:
I tried using the Thinkstore model ‘Add index temporal table’, but it did not seem to work correctly. I added the ‘add_history_index’ tag to table with system versioning, but it looked like the model tried adding indexes to all tables in stead of only the table with that tag, which resulted in a MERGE conflict.
I was also wondering if this model would allow me to add indexes to specific columns of the history table? And if not, what is the recommended way to do something like this?
I tried using the Thinkstore model ‘Add index temporal table’, but it did not seem to work correctly. I added the ‘add_history_index’ tag to table with system versioning, but it looked like the model tried adding indexes to all tables in stead of only the table with that tag, which resulted in a MERGE conflict.
I was also wondering if this model would allow me to add indexes to specific columns of the history table? And if not, what is the recommended way to do something like this?
Hi Freek,
The solution should no try to add the index to every table, only for tables with system_version enabled and the tag attached, I just checked the code and I cannot see where it could go wrong.
Can you show me the code of the dynamic model of the solution?
This is the code from sql_create_indexes:
-- Create indexes
------------------------------
-- Create foreign key indexes
------------------------------
declare @storage_id storage_id
-- Get index storage location from branch
select @storage_id = default_indx_storage_id
from branch
where model_id = @model_id
and branch_id = @branch_id
insert into #indx
(
indx_id,
tab_id,
primary_key,
foreign_key,
type_of_indx,
unique_indx,
ignore_null,
storage_id
)
select
'fk_' + iif(len(r.ref_id) > 125, -- The length of the indx domain is varchar(128). 'fk_' + 125 characters makes 128 characters.
left(r.ref_id, 120) + right('00000' + cast(row_number() over(partition by left(r.ref_id, 120) order by r.ref_id asc) as varchar), 5),
r.ref_id) as indx_id,
r.target_tab_id,
0 as primary_key,
1 as foreign_key,
1 as type_of_indx,
0 as unique_indx,
0 as ignore_null,
@storage_id as storage_id
from ref r
join tab t -- target tab
on t.model_id = r.model_id
and t.branch_id = r.branch_id
and t.tab_id = r.target_tab_id
join tab s -- source tab
on s.model_id = r.model_id
and s.branch_id = r.branch_id
and s.tab_id = r.source_tab_id
where r.model_id = @model_id
and r.branch_id = @branch_id
and r.no_of_col > 0 -- Must have columns
and t.type_of_table in (0, 4) -- Tables, MQT (Snapshot)
and ( s.type_of_table in (0, 4)
or s.type_of_table in (1, 2) and r.is_detail = 1) -- Apply indexes to tables that are details of a view
and not exists (select 1 -- Do not create indexes for fk's with calculated fields
from ref_col rc
join col c
on c.model_id = rc.model_id
and c.branch_id = rc.branch_id
and c.tab_id = rc.target_tab_id
and c.col_id = rc.target_col_id
where rc.model_id = r.model_id
and rc.branch_id = r.branch_id
and rc.ref_id = r.ref_id
and c.calculated_field_type <> 0)
and not exists (select 1 -- Do not create indexes for fk's to blobs and lobs
from col c
join dom d
on d.model_id = c.model_id
and d.branch_id = c.branch_id
and d.dom_id = c.dom_id
where c.model_id = r.model_id
and c.branch_id = r.branch_id
and c.tab_id = r.target_tab_id
and d.dttp_id in ('NVARCHAR_MAX', 'VARCHAR_MAX', 'VARBINARY_MAX')
and exists(select 1
from ref_col rc
where rc.model_id = c.model_id
and rc.branch_id = c.branch_id
and rc.target_tab_id = c.tab_id
and rc.target_col_id = c.col_id
and rc.ref_id = r.ref_id))
and not exists (select 1 -- Option to exclude indexes for performance
from ref_tag rt
where rt.model_id = r.model_id
and rt.branch_id = r.branch_id
and rt.ref_id = r.ref_id
and rt.tag_id = 'EXCLUDE_FROM_FK_INDEXES')
insert into #indx_col
(
indx_id,
tab_id,
col_id,
sort_order,
included,
order_no
)
select
'fk_' + iif(len(r.ref_id) > 125, -- The length of the indx domain is varchar(128). 'fk_' + 125 characters makes 128 characters.
left(r.ref_id, 120) + right('00000' + cast(row_number() over(partition by left(r.ref_id, 120) order by r.ref_id asc) as varchar), 5),
r.ref_id) as indx_id,
r.target_tab_id,
rc.target_col_id,
0,
0,
row_number() over (partition by t.model_id, t.branch_id, t.tab_id, r.ref_id order by rc.order_no, rc.target_col_id) as order_no
from ref r
join ref_col rc
on rc.model_id = r.model_id
and rc.branch_id = r.branch_id
and rc.ref_id = r.ref_id
join tab t -- target tab
on t.model_id = r.model_id
and t.branch_id = r.branch_id
and t.tab_id = r.target_tab_id
join tab s -- source tab
on s.model_id = r.model_id
and s.branch_id = r.branch_id
and s.tab_id = r.source_tab_id
where r.model_id = @model_id
and r.branch_id = @branch_id
and r.no_of_col > 0 -- Must have columns
and t.type_of_table in (0, 4) -- Tables, MQT (Snapshot)
and ( s.type_of_table in (0, 4)
or s.type_of_table in (1, 2) and r.is_detail = 1) -- Apply indexes to tables that are details of a view
and not exists (select 1 -- Do not create indexes for fk's with calculated fields
from ref_col rc
join col c
on c.model_id = rc.model_id
and c.branch_id = rc.branch_id
and c.tab_id = rc.target_tab_id
and c.col_id = rc.target_col_id
where rc.model_id = r.model_id
and rc.branch_id = r.branch_id
and rc.ref_id = r.ref_id
and c.calculated_field_type <> 0)
and not exists (select 1 -- Do not create indexes for fk's to blobs and lobs
from col c
join dom d
on d.model_id = c.model_id
and d.branch_id = c.branch_id
and d.dom_id = c.dom_id
where c.model_id = r.model_id
and c.branch_id = r.branch_id
and c.tab_id = r.target_tab_id
and d.dttp_id in ('NVARCHAR_MAX', 'VARCHAR_MAX', 'VARBINARY_MAX')
and exists(select 1
from ref_col rc
where rc.model_id = c.model_id
and rc.branch_id = c.branch_id
and rc.target_tab_id = c.tab_id
and rc.target_col_id = c.col_id
and rc.ref_id = r.ref_id))
and not exists (select 1 -- Option to exclude indexes for performance
from ref_tag rt
where rt.model_id = r.model_id
and rt.branch_id = r.branch_id
and rt.ref_id = r.ref_id
and rt.tag_id = 'EXCLUDE_FROM_FK_INDEXES')
------------------------------
-- Create clustered index
------------------------------
insert into #indx
(
indx_id,
tab_id,
primary_key,
foreign_key,
type_of_indx,
unique_indx,
ignore_null
)
select
'indx_' + t.tab_id + '_partition',
t.tab_id,
0 as primary_key,
0 as foreign_key,
0 as type_of_indx,
0 as unique_indx,
0 as ignore_null
from tab t
join col c
on c.model_id = t.model_id
and c.branch_id = t.branch_id
and c.tab_id = t.tab_id
and c.col_id = t.partition_col_id
where t.model_id = @model_id
and t.branch_id = @branch_id
and t.partition_scheme_id is not null
and t.partition_col_id is not null
and c.primary_key = 0
-- Check if there are no other clustered indexes
and not exists (select 1
from indx i1
where i1.model_id = t.model_id
and i1.branch_id = t.branch_id
and i1.tab_id = t.tab_id
and i1.type_of_indx in (0, 4)) -- Clustered or columnstore clustered
-- Check if the index we're about to create doesn't already exist
and not exists (select 1
from indx i2
where i2.model_id = t.model_id
and i2.branch_id = t.branch_id
and i2.tab_id = t.tab_id
and i2.type_of_indx = 0
and i2.indx_id = 'indx_' + t.tab_id + '_partition')
-- Add the partition col to the clustered index
insert into #indx_col
(
indx_id,
tab_id,
col_id,
sort_order,
included,
order_no
)
select
'indx_' + t.tab_id + '_partition',
c.tab_id,
c.col_id,
0,
0,
1
from tab t
join col c
on c.model_id = t.model_id
and c.branch_id = t.branch_id
and c.tab_id = t.tab_id
and c.col_id = t.partition_col_id
where t.model_id = @model_id
and t.branch_id = @branch_id
and t.partition_scheme_id is not null
and t.partition_col_id is not null
and c.primary_key = 0
and not exists (select 1
from indx i
where i.model_id = t.model_id
and i.branch_id = t.branch_id
and i.tab_id = t.tab_id
and i.type_of_indx = 0
and i.indx_id <> 'indx_' + t.tab_id + '_partition')
-- Check if the index col we're about to create doesn't already exist
and not exists (select 1
from indx_col ic
where ic.model_id = t.model_id
and ic.branch_id = t.branch_id
and ic.tab_id = t.tab_id
and ic.indx_id = 'indx_' + t.tab_id + '_partition'
and ic.col_id = c.col_id)
-- Add the primary key cols to the clustered index
insert into #indx_col
(
indx_id,
tab_id,
col_id,
sort_order,
included,
order_no
)
select
'indx_' + t.tab_id + '_partition',
c1.tab_id,
c1.col_id,
0,
0,
1 + c1.abs_order_no
from tab t
join col c1
on c1.model_id = t.model_id
and c1.branch_id = t.branch_id
and c1.tab_id = t.tab_id
join col c2
on c2.model_id = t.model_id
and c2.branch_id = t.branch_id
and c2.tab_id = t.tab_id
and c2.col_id = t.partition_col_id
where t.model_id = @model_id
and t.branch_id = @branch_id
and t.partition_scheme_id is not null
and t.partition_col_id is not null
and c1.primary_key = 1
and c2.primary_key = 0
and not exists (select 1
from indx i
where i.model_id = t.model_id
and i.branch_id = t.branch_id
and i.tab_id = t.tab_id
and i.type_of_indx = 0
and i.indx_id <> 'indx_' + t.tab_id + '_partition')
-- Check if the index col we're about to create doesn't already exist
and not exists (select 1
from indx_col ic
where ic.model_id = t.model_id
and ic.branch_id = t.branch_id
and ic.tab_id = t.tab_id
and ic.indx_id = 'indx_' + t.tab_id + '_partition'
and ic.col_id = c1.col_id)
------------------------------
-- Create sort order indexes
------------------------------
insert into #indx
(
indx_id,
tab_id,
primary_key,
foreign_key,
type_of_indx,
unique_indx,
ignore_null
)
-- Create temp table
select
'so_' + t.tab_id as indx_id,
t.tab_id as tab_id,
0 as primary_key,
0 as foreign_key,
1 as type_of_indx,
0 as unique_indx,
0 as ignore_null
from tab t
where t.model_id = @model_id
and t.branch_id = @branch_id
and t.type_of_table = 0
and exists (select 1 -- There must be a default sorting
from col c
where c.model_id = t.model_id
and c.branch_id = t.branch_id
and c.tab_id = t.tab_id
and c.default_sort = 1)
and not exists(select 1 -- Add check for not already existing, since manually aliased indexes / generated by base model
from indx i -- are no longer marked as generated, thus not removed
where i.model_id = t.model_id
and i.branch_id = t.branch_id
and i.indx_id = 'so_' + t.tab_id
and (i.generated_by_control_proc_id <> @control_proc_id
or i.generated_by_control_proc_id is null
or i.generated_by_control_proc_id = 'Copied from base model'))
and not exists (select 1 -- Do not create indexes for sorting on calculated fields, lobs and blobs
from col c
join dom d
on d.model_id = c.model_id
and d.branch_id = c.branch_id
and d.dom_id = c.dom_id
where c.model_id = t.model_id
and c.branch_id = t.branch_id
and c.tab_id = t.tab_id
and c.default_sort = 1
and (c.calculated_field_type <> 0
or d.dttp_id in ('NVARCHAR_MAX', 'VARCHAR_MAX', 'VARBINARY_MAX')))
insert into #indx_col
(
indx_id,
tab_id,
col_id,
sort_order,
included,
order_no
)
-- Merge index columns for the columns sorted on by default for generated sort indexes
select
'so_' + t.tab_id as indx_id,
t.tab_id as tab_id,
c.col_id as col_id,
c.sort_order as sort_order,
0 as included,
row_number() over (partition by t.model_id, t.branch_id, t.tab_id order by c.sort_no, c.col_id) as order_no
from tab t
join col c
on c.model_id = t.model_id
and c.branch_id = t.branch_id
and c.tab_id = t.tab_id
and c.default_sort = 1
where t.model_id = @model_id
and t.branch_id = @branch_id
and t.type_of_table = 0
and exists (select 1 -- There must be a default sorting
from col c
where c.model_id = t.model_id
and c.branch_id = t.branch_id
and c.tab_id = t.tab_id
and c.default_sort = 1)
and not exists(select 1 -- Add check for not already existing, since manually aliased indexes / generated by base model
from indx i -- are no longer marked as generated, thus not removed
where i.model_id = t.model_id
and i.branch_id = t.branch_id
and i.indx_id = 'so_' + t.tab_id
and (i.generated_by_control_proc_id <> @control_proc_id
or i.generated_by_control_proc_id is null
or i.generated_by_control_proc_id = 'Copied from base model'))
and not exists (select 1 -- Do not create indexes for sorting on calculated fields, lobs and blobs
from col c
join dom d
on d.model_id = c.model_id
and d.branch_id = c.branch_id
and d.dom_id = c.dom_id
where c.model_id = t.model_id
and c.branch_id = t.branch_id
and c.tab_id = t.tab_id
and c.default_sort = 1
and (c.calculated_field_type <> 0
or d.dttp_id in ('NVARCHAR_MAX', 'VARCHAR_MAX', 'VARBINARY_MAX')))
------------------------------
-- Clean up redundant indexes
------------------------------
declare @redundant_indx table (indx_id indx_id primary key)
insert into @redundant_indx
(
indx_id
)
-- Get the indexes which only have the same columns as the primary key (or less)
select i.indx_id
from #indx i
outer apply (select count(*) as no_of_col
from #indx_col ic
where ic.indx_id = i.indx_id) count
cross apply (select 1 as overlapped_by_pk
from col c
join #indx_col ic -- index column identical to primary key column
on ic.indx_id = i.indx_id
and ic.tab_id = c.tab_id
and ic.col_id = c.col_id
and ic.sort_order = 0 -- asc
and ic.included = 0 -- no
and ic.order_no = c.abs_order_no
where c.model_id = @model_id
and c.branch_id = @branch_id
and c.tab_id = i.tab_id
and c.primary_key = 1
and not exists(select 1 -- Only applies when there is no clustered index defined
from indx i
where i.model_id = c.model_id
and i.branch_id = c.branch_id
and i.tab_id = c.tab_id
and i.type_of_indx = 0)
group by c.tab_id
having count(*) = count.no_of_col) t3
where i.type_of_indx = 1 -- Only check for generated, nonclustered indexes.
group by i.indx_id
union
-- Get the indexes which already exists with antother name
select i.indx_id
from #indx i
outer apply (select count(*) no_of_col
from #indx_col ic2
where ic2.indx_id = i.indx_id
and ic2.tab_id = i.tab_id
group by ic2.indx_id ) as cnt_indx_col
cross apply (select i2.indx_id
from indx i2
join indx_col ic2
on ic2.model_id = i2.model_id
and ic2.branch_id = i2.branch_id
and ic2.indx_id = i2.indx_id
join #indx_col ic -- index column identical to other index column
on ic.indx_id = i.indx_id
and ic.tab_id = ic2.tab_id
and ic.col_id = ic2.col_id
and ic.sort_order = ic2.sort_order
and ic.included = ic2.included
and ic.order_no = ic2.abs_order_no
where i2.model_id = @model_id
and i2.branch_id = @branch_id
and i2.tab_id = i.tab_id
and i2.type_of_indx in (0, 1) -- Clustered and nonclustered are allowed to overlap
and i2.indx_id <> i.indx_id
and (i2.generated = 0 and (i2.generated_by_control_proc_id is null or i2.generated_by_control_proc_id <> @control_proc_id)
or not (i2.type_of_indx = i.type_of_indx -- When truly identical, keep the one with the highest alphabetical id.
and i2.no_of_col = cnt_indx_col.no_of_col
and i2.indx_id < i.indx_id))
group by i2.indx_id
having count(*) = cnt_indx_col.no_of_col) t2
group by i.indx_id
union
-- Get the obsolete indexes which within the temp table
select i.indx_id
from #indx i
outer apply (select count(*) no_of_col
from #indx_col ic2
where ic2.indx_id = i.indx_id
and ic2.tab_id = i.tab_id
group by ic2.indx_id ) as cnt_indx_col
cross apply (select i2.indx_id
from #indx i2
join #indx_col ic2
on ic2.indx_id = i2.indx_id
join #indx_col ic -- index column identical to other index column
on ic.indx_id = i.indx_id
and ic.tab_id = ic2.tab_id
and ic.col_id = ic2.col_id
and ic.sort_order = ic2.sort_order
and ic.included = ic2.included
and ic.order_no = ic2.order_no
where i2.tab_id = i.tab_id
and i2.type_of_indx in (0, 1) -- Clustered and nonclustered are allowed to overlap
and i2.indx_id <> i.indx_id
and not (i2.type_of_indx = i.type_of_indx -- When truly identical, keep the one with the highest alphabetical id.
and i2.indx_id < i.indx_id)
group by i2.indx_id
having count(*) = cnt_indx_col.no_of_col) t2
group by i.indx_id
-- Delete the redundant indexes from the temp table to prevent them from being added
delete i
from #indx_col i
join @redundant_indx ri
on ri.indx_id = i.indx_id
delete i
from #indx i
join @redundant_indx ri
on ri.indx_id = i.indx_id
-- Remove indexes that are already present but unmanaged
delete i
from #indx_col i
where exists(
select 1
from indx i2
where i2.model_id = @model_id
and i2.branch_id = @branch_id
and i2.tab_id = i.tab_id
and i2.indx_id = i.indx_id
and (i2.generated_by_control_proc_id is null
or i2.generated_by_control_proc_id <> @control_proc_id)
)
delete i
from #indx i
where exists(
select 1
from indx i2
where i2.model_id = @model_id
and i2.branch_id = @branch_id
and i2.tab_id = i.tab_id
and i2.indx_id = i.indx_id
and (i2.generated_by_control_proc_id is null
or i2.generated_by_control_proc_id <> @control_proc_id)
)
This is the code from sql_create_indexes:
I see, that is the old “wrong” code.
Since the 2025.1 we support history index from within the model (https://docs.thinkwisesoftware.com/blog/2025_1#data---create-indexes-on-history-tables), with that release we also released an updated the thinkstore model “Add index temporal table”
So I would recommend that if you are on SF 2025.1 or above to re-download the thinkstore solution, this should work seamless.
What would you recommend if I am not on SF 2025.1 or above (besides upgrading)?
What would you recommend if I am not on SF 2025.1 or above (besides upgrading)?
Upgrading is the best recomendation.
But otherwise for now:
Add the most important indexes in a after upgrade script and write them yourself.
But keep in mind that those indexes need a drop and a create at every creation, which means that upgrading will take longer the more records are in the history table(s).
Why do they need a drop and a create at every creation?
It could be that something has changed in the table, which makes you need to recreate. That is what is makes tricky.
Adding the index in an after upgrade script does not work because at the ‘indexes’ step of creation the indexes get reset again.
Adding the index in an after upgrade script does not work because at the ‘indexes’ step of creation the indexes get reset again.
You can use the code group “manual” for this, then the code will be added in the end of all the code.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.