CODEWITHSUNDEEP
databaseindexing
Lock
Lock

Reputation: 5522

Vendor will not create index because of data integrity

I am trying to get a vendor to create an index on a progress 10.2b database to aid in migrating data from said database, however the vendor is reluctant to create an index, saying it could impact data integrity. There response is below. Is their any truth/merit in what is being said?

There are a number of reasons we will not add indices but the main reason is, as you have outlined, Progress selects the index it uses based on the parameters in the query. So for example if we had code that does the following:

Find first record where a= 1 and b = 2

As the existing index stands this would find the record using index ‘M’ and it would find record ‘X’

If we add a new index to the table there is a chance that this code could decide to use the new index to find the record and return record ‘Y’ instead.

Sure creating indices is a core part of any database, but proper development practices would require heaps of testing before applying an index change to a product system. Without testing, the integrity of the system cannot be guaranteed.

So my thoughts on this are:

Progress selects the index it uses based on the parameters in the query

Isn't this how any database usually selects the index? Based on the required columns/where clause, it can decide the appropriate index (if any) available.

If we add a new index to the table there is a chance that this code could decide to use the new index to find the record and return record ‘Y’ instead.

To me, it almost sounds like they have programmed their program to rely on "grabbing the first record out of the database". If it was to use an index, then sure, it might order the results differently if no order by has been specified. If this is the case, then that is just poor programming.

Upvotes: 1

Views: 46

Answers (1)

Markus Winand
Markus Winand

Reputation: 8746

I pretty much agree with you:

To me, it almost sounds like they have programmed their program to rely on "grabbing the first record out of the database". If it was to use an index, then sure, it might order the results differently if no order by has been specified. If this is the case, then that is just poor programming.

If they wrote their query correctly, an index doesn't change the result — just the speed. If they left the order by and just rely that the index has the right order anyway, another index could cause problems.

However, to emphasis this: The bug is the query then.

Upvotes: 1

Related Questions