#29 Possibly, missing index JAILER_ENTITY(r_entitygraph, type)

[Alexander Petrossian (PAF)]

Ralf,
I've also noticed a hint from MSSQL that one more index would help:

(@1 int,@2 tinyint)DELETE [LOAN_MT].[dbo].[JAILER_ENTITY]  WHERE [r_entitygraph]=@1 AND [type]=@2


/*
Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 94.8288%.
*/

CREATE NONCLUSTERED INDEX <TODO>
ON [dbo].[JAILER_ENTITY] ([r_entitygraph],[type])

Currently execution plan uses index on @1, but it claims having index on @1+@2 would help dramatically.

[Alexander Petrossian (PAF)]

I'm in the middle of 3 databases, about 2K tables, each process.
And in a huff, so don't know if it helped (created).

Leaving it in your hands, Ralf.

[Ralf Wisser]

Hi,
Thank you very much for your efforts.

There is an index
CREATE INDEX jlr_enty_brthdy ON JAILER_ENTITY (r_entitygraph, birthday, type);

but it cannot be used for the DELETE statement because 'birthday' is not give.
Presumably this index fits better:

CREATE INDEX jlr_enty_brthdy ON JAILER_ENTITY (r_entitygraph, type, birthday);

But I am not sure if this does not affect other statements. Maybe both indices are necessary?
If you like, you can test the change by replacing the file "script/ddl-template.sql" with the one from the attachment.

But then please first execute a
DELTE FROM JAILER_CONFIG
This forces the tables to be recreated.

[Alexander Petrossian (PAF)]

Ralf, I didn't try suggested template, because I've chopped off deletion for a while.
Now I've installed version, compiled from master (with new delete mechanism for MSSQL that goes by chunks (delete top ...)
I don't see any problems with delete whatsoever.
So I'm not sure about this index.
Leaving it in your hands.
Maybe there was some sense in that warning from MSSQL, maybe there was not.
I would suggest to concervatively do nothing until somebody else would complain. If ever.