Solved

Adding index to existing table that isn't mine.

  • 25 August 2023
  • 2 replies
  • 77 views

Userlevel 7
Badge +5

What’s the best way to add an index to an existing table that’s a third-party table?

It’s a table with an auto-increment field as it’s primary key (along with CompanyID). But when I look at the underlying views, the code is querying the table using non-indexed fields. I don’t have time, at the moment, to reach out to the third-party to suggest that they add a key to make the lookups faster mostly because I won’t have time to integrate any patch from them into my code base at this point even if they agree.

So I was thinking of adding a non-unique key on the fields that I see the view using.

Should I just add a SQL script to my customization project to add the index if it isn’t already in place?

Or is there a more official way that I should do this?

icon

Best answer by Tony Lanzer 26 August 2023, 23:18

View original

2 replies

Userlevel 4
Badge +2

@Django, I would suggest a SQL script to add the index, yes, especially if this is a SaaS instance.  Is a unique index not possible for what it’s searching on?  In addition, I always write my customization SQL scripts to work when run multiple times.  As you mention, check if the index already exists before creating it.

Userlevel 7
Badge +5

The table is kind of a register table that also has the current state of a piece of equipment that I can reliably count on. So we’ll have multiple records with the same equipment ID but only a handful per sales order so I can take the ‘hit’ on having to look at a half-dozen records knowing the sales order number.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved