Adding index to table - what queries will change

I don't know if this is the correct place to ask this question and maybe someone else has already asked this.
Is there a tool that can evaluates all queries in a development-environment (list of directories with sources) and report all the sources that have queries that will use the new index when you add that new index to a database-table?


Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Make one...

Depending on how large your codebase is, it shouldn't be to hard to make one: Just create an empty copy of the database (with no data, just schema) and add the index. Because the database can be empty and you don't even have to use the same AREA structure it would hardly take up space and you can keep it around for future use.
Recompile the application against that database (using XREF but not SAVE) and parse the xrefs.
Ideally you could have a tool that keeps an indexed database of all information in the xrefs of the current system. That way you only have to recompile the files that actually use the table you're adding an index to.

What we have now is a tool, triggered from AppBuilder and Subversion, that maintains the tables, after a database change we just parse the .DF file and compile all applications that use changed tables (this typically takes 30 minutes instead of 4 hours to compile everything, building .pl files and deploying to the servers now takes about 3 hours, if we had to deploy everything we would probably need an entire weekend.)


Thanks

Thanks for the Reply!
I will check that out..


tamhas's picture

I don't believe that there

I don't believe that there is an existing tool that addresses this specific question and it is possible that it is actually a difficult question to answer. You can certainly database XREF data to have a searchable record of all current index use. There is a project proposed to build a sort of SuperXREF using Proparse which could give you even more information. Analyst parses the code and will produce an XML file that has the actual WHERE clause used in the query and this is part of the information loaded into ABL2UML. So, if you had an ABL2UML model of your application you could easily look at the table in question, all of the code components which access that table and the WHERE clauses associated with those connections to make your own assessment of which would be likely to use the new index. Better yet, you could look at the pattern of actual WHERE clauses and index usage to see which ones were and were not efficient in order to decide what new index to define. But, having the tool decide for you what new index might be used is going to be difficult because that would require imitating the compiler decision about query resolution ... including the possibility of using multiple indices where possible.