New post
Question
Florian Hansmann · Sep 13, 2021

SQL - use specific index

#CSP #Databases #Performance #SQL #Caché

Hello Community,

we want to use a specific index on our sql-class.

The index we want to use is called "iFilter".
Currently we use the following technique of ignoring all other indices because the automatically chosen index is always too slow. 

SELECT TOP 100 d0.ID FROM %IGNOREINDEX Belegindex %IGNOREINDEX KundenNrGlobalindex %IGNOREINDEX Rechnungsnummerindex %IGNOREINDEX Erfassungsartindex %IGNOREINDEX Belegsuche %IGNOREINDEX DatumAuftragindex %IGNOREINDEX OnlineBestellnummerindex %IGNOREINDEX Belegnummerindex %IGNOREINDEX KundenNrGlobalindex %IGNOREINDEX VAMindex %IGNOREINDEX BelegnummerbeiLagerLagerBestellungIndex %IGNOREINDEX iStatusSolr db.BelegeKopf AS d0 WHERE (((d0.KundenNr = 'BUKR01')) AND d0.ErfassungsartBez IN ('AB', 'AN') AND d0.DatumAuftrag >= '2020')

Do you know of a better/shorter way to resolve this?

 

Best regards.

Florian Hansmann

Product version: Caché 2018.1
0
1 154
Discussion (3)1
Log in or sign up to continue
Danny Wijnschenk · Sep 13, 2021

Can you show the class definition ? Maybe it is better to try to find out why your iFilter is not taken as the best option for this query.

0 0
Robert Cemper · Sep 13, 2021

For this query, I would suggest to define this index:

Index iFilter on (KundenNr,ErfassungsartBez,DatumAuftrag) ;

The sequence of KundenNr,ErfassungsartBez,DatumAuftrag should by increasing selectivity
As found in Tune Table output (SMP > SQL > Actions > TuneTable)

1 0
Florian Hansmann · Sep 13, 2021

Hey Robert,

on Point. Thats helps me a lot increasing the selectivity.

Thanks!

0 0