Schedule Index Creation Analysis on slower query which is taking a bit time to complete Transaction

sivachandu paka · ‎05-31-2024

I am trying to debug the transaction which is taking some time around 5 minutes approximately to complete. It is functioning as expected, but only doubt is why it is taking some time to complete?

Functionality I am performing is : Click on UI Action(Delete) from a form view of a record, A script is given in UI Button Client script which actually executes after click--It will delete that corresponding record from its table.(Refer Below Script)

So, I started Investigation on this and it took me to an article (https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0782916) which guided me to debug steps. I followed as per the KB article mentioned here.
It is about Schedule the creation of index. I followed the sequence of steps as per article. At one point where I clicked on create Index Button, It is running an operation as shown in screenshot. But this operation literally takes hours of time may be around 8 hours or so.

My Question --> Will this Create Index Operation effect the Instance which I am running by any means ?? Does it effect the performance/back end issues on any tables in Servicenow in that Instance ??

Can anyone Help me provide knowledge or info on this

Thanks
Siva

Maik Skoddow · ‎05-31-2024

With such a high number of records (over 46 million) everything works as expected. Deleting a record is the most expensive database operation and thus will take several minutes and also creating the index will take that observed time. But just creating an index just because any automatic suggestion tells you this is not a good idea. With that high amount of records, you need a holistic approach and more analysis. And an index also consumes database space, which is not available anymore for the real data. Therefore, be careful and engage experts.

sivachandu paka · ‎06-02-2024

Hi Maik,
Thanks for replying....
So, As per your thought. New Index consumes database space and is not recommended to proceed with this approach, Is that what you meant ?
Observation : After Creating this new Index for that particular query(Slower Transaction query) on the table where the transaction is happening. Despite it took long running operation of nearly 7 hours. Now, the transaction which I am functioning through UI Acton is really quick. Looks like this resolved the slower transaction process. So Can I promote it to next level Instances and move it to our Main PRODUCTION Instance by capturing the newly created Index ?? Can you refer screenshot attached shows after long running operation. There is a record created under related item "Related Indexes" tab and it is somehow shows relation between my current table where I am working on and to Task table.

Can you please share your thoughts. Thanks