Indexing Entities

Indexing Entities

  

Hello

We are experiencing timeouts in a query and it's impacting the daily usage of our application.

The client's DBA has analyzed the query, and determined that an index similar to this should be created:

CREATE NONCLUSTERED INDEX IdxName on Entity(Column1, Column2) Include (Column3)


We wish to create the index in the entity itself so as to keep these database elements within the outsystems plaform, however, Outsystems doesn't seem to support indexes with included columns.

Is there a way to create this within outsystems? What would be the impact of either having Column3 as an index key along Column1 and Column2, or omitting it alltogether?

Hi Ricardo,

OutSystems supports Indexes on multiple Attributes just fine. In the Entity pop-up, select the Indexes tab, then press the New button. In the right panel, you can select (Add Attribute) to select an Attribute. You can select multiple Attributes there for a single Index.

Hello Kilian, thanks for the quick response.


I understand that you define an index with multiple attributes. My question stems from my lack of index knowledge, as i don't really understand the impact between these two statements:

CREATE NONCLUSTERED INDEX IdxName on Entity(Column1, Column2) Include (Column3)

CREATE NONCLUSTERED INDEX IdxName on Entity(Column1, Column2, Column3)

Hi Ricardo,

I don't know either, I'm no DBA :). It seems that "Include" specifies columns not included for the index itself, but I don't know what that means. Nonetheless, you can't do that with OutSystems, so if it's really necessary for performance, the DBAs will have to manually add such an index to the physical table in the database.

Ricardo Mata wrote:

Hello Kilian, thanks for the quick response.


I understand that you define an index with multiple attributes. My question stems from my lack of index knowledge, as i don't really understand the impact between these two statements:

CREATE NONCLUSTERED INDEX IdxName on Entity(Column1, Column2) Include (Column3)

CREATE NONCLUSTERED INDEX IdxName on Entity(Column1, Column2, Column3)


Ricardo,

From this link I can read:

"This is because the nonkey columns have the following benefits:

  • They can be data types not allowed as index key columns.

  • They are not considered by the Database Engine when calculating the number of index key columns or index key size."


So, if you use a nonkey column, they don't count towards the number of index key columns or index key size, and they allow to use columns that cannot be used as index key columns.


Abílio Matos

If that's the only difference, I don't think it matters much for OutSystems Entities.

From what I understand from that article, nonkey columns aren't actually included in the physical index. Doesn't that mean that nonkey columns are fetched from the table after the index is determined? If that's the case, isn't that the same as not even declaring the column on the index at all?

Ricardo,

They aren't included in physical index but they are part of the index itself. So when you have those columns in the query it gains performance because all index is searched. 

So, they are in index, but not used to identify the index as keys.


Abílio Matos

Thank you for the clarification Abilio.

So basically this tells the SQL Engine to fetch those columns from the database whenever the index is searched? Correct me if i'm wrong, but i'm guessing this is a means to compromise between the space used by an index, VS the time it takes to seek the values I need? Meaning that an all-column-keys index would need more space allocated but would be faster, but a mix of key and non key index would take up less space, but would be a bit slower due to higher seek time?