Is there any way to see which objects use an index in Genexus?

Question

Is there any way to see which objects use an index in Genexus?

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (0 votes)

1

I have a pretty old KB, which has several indices that were defined to improve the performance of reports. The reports have changed over time and surely there are many indexes that no longer make sense and I would like to eliminate them.

Try to do a xRef of the object Index to see what programs (procedures, webpanels, etc) use the index in your navigation but the list of "Is Referenced By" is always blank - as much as I know that the index is used:

Does anyone have a trick to solve this?

Update the Question after Mark's response:

The question does not focus on the use of the indices in the database - that as the Marcos explains, the DBMS will use or not the indices that we define in GeneXus - the intention of the question is more for cleaning / order of the KB ...

That is to say, if I have defined indexes defined in the KB that are not being referenced (that is, in the KB there is no object that establishes that Order) it is extremely high probability that the index is not used by the DBMS (a unless you have been very lucky!;). However, the existence of the index (not referenced) does negatively affect the writing operations in the database. I'm sure many old KBs suffer from this problem.

I could go deleting indices and throw a specification - to see which objects report a warning for lack of index - to verify that I do not have any object that references it, but ... it seemed a bit too much work.

Another case of use of the x-ref for indexes happens to me when there are defined indexes that are similar - Index 1: A, B, C and index 2: B, C, A, D - and are candidates to analyze if not It is convenient to unify them into one and for that it is important to understand which navigations use them and analyze if that separation makes sense or not.

genexus

asked by Lali 10.02.2016 в 00:30

source

3 answers

Display data with ListView with Django 1.8 preventDefault href does not work

score 3 · Answer 1

It is very difficult to know from GeneXus if the database manager will end up using a certain index or not. As much as GeneXus says it will use an index, it is possible that the DBMS may end up using another one due to some internal optimization that it can perform.

For this reason, the best way to know if an index is used or not is to see the activity in the database. You can do a tour of the entire application and record the sentences that are executed, and then see what indexes are used.

score 1 · Answer 2

The DBMS will use the "best index" you can in each query that is made. In the single-key indexes, undoubtedly the lowest cost of the query for the DBMS will be to use the index. In the case of duplicate key indexes, it can not even be affirmed that a TODAY analysis of how the DBMS performs the query coincides with an analysis of the same query 6 months later. The index you will use will depend on the values obtained by the DBMS of the statistics of the data distribution in the tables that you use. The most "optimal" from the point of view of the DBA, is to monitor the queries, and perform the same analyzing the cost of each one (arm a script with the most common queries, and the most "heavy" and execute it periodically), looking add indices or modify existing ones if the query triggers its "cost" beyond the values it deems appropriate. The analysis of what the DBMS does TODAY is not permanent and should be monitored every so often.

score 0 · Answer 3

Depending on the DBMS that the application uses, there are ways to perform querys for Get the statistics of use of your indexes. As an example we can cite the sys.dm_db_index_usage_stats of MSSQL Server.

In this way, it would not be necessary to perform any x-ref in the Genexus metadata, as Mark Crispino quotes, even making an x-ref in the aforementioned Genexus KB there would be no guarantee that said index was being used. for the DBMS in question .-

This type of statistics that I mention are usually used by the DBA when they precisely maintain the databases.

Here we can see an application example of the aforementioned statistics. In particular, note the part that describes, for example, the number of times an index was used in a search query or a table update.