Changing GP table structures

(0) Share

Report

Posted on by Richard Wheeler

75,850 Moderator

I am hoping someone from Microsoft will chime in on this one. One of our clients has been noticing a sizable performance degradation throughtout all of GP. When I inquired as to whether any changes were being made to their infrastructure they finally confessed that they had hired a web devleopment company to completely rewrite their website. Now this company knows nothing about GP. It was pretty obvious what was going on once I ran SQL profiler, expecially at night, when I see all this activity with no one in GP. Well this company is now demanding that we add indexes to the customer master and sales order transactions tables. Now I have already told them that in no way should any table changes be made to any GP tables and my client would like an official statement from someone at Microsoft stating the same. Has anyone ever run into this before and, if so, how did you handle it?

*This post is locked for comments

I have the same question (0)

All responses (11)

Answers (1)

Olaf Laos 1,890 on at

Like (0)

Report

Is the website a "commerce" type website that is interacting with GP adding new customers and sales orders etc?

Was this reply helpful? Yes No
Ian Richardson 4,150 on at

Like (0)

Report

Hi Richard

I've been a user and a reseller. My answer likely won't be one you like. You can change the GP tables. You just have to really really know what you are doing.

As a reseller I once made changes to POP tables. My boss the owner of the VAR company missed a table and check links saw that one table and changed the other 6 tables I had changed back to their original state.

Programmers routinely by pass the GP business logic and think they know enough to hit tables directly. I am presently a user and our VAR has programmed an add on that is attempting just that. They are trying to hit the JE work tables and overlooked that some of our gl accounts are / could be allocation accounts. If they would simply use the GP business logic that would be no issue.

Both these examples show the importance of knowing what you are doing. I do however feel that as long as you have lots of back ups and lots of testing you can make that claim you are OK to make table changes.

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

The issue here is one of speed. They have come along just as programmers and have created a series of joins. These joins would definately go quicker if the tables had indexes that corresponded to the fields in the joins. So for an outsider it makes sense. However, I am the one who does all the GP maintenance and this includes the upgrades. My biggest concern is at upgrade time. I have seen upgrades fail when a change has been made to a table. So I would have to know all the indexes they have created, drop them just prior to the upgrade, peform the upgrade and then recreate the indexes. Not to mention, let's say they make these changes and then something else starts going wrong, where do I point the finger? Once I tell Microsoft that there have been table changes, they will rightfully wash their hands of this.

My suggestion is we either create a replicated database or for these dozen or so tables we create mirror images of them and have hourly update jobs run that constantly to update these tables.These new tables will have the indexes they want. It will all come down to how often these tables need to be updated.

Was this reply helpful? Yes No
Ian Richardson 4,150 on at

Like (0)

Report

HI Richard

Yes I have seen upgrades fail due to changed tables. I have seen new features in GP change tables or create brand new tables, that then break the mod's people have written such as you describe.

I would definitely agree that a sql query that populates a temp table with any index desired would be the way to go. As long as the web page is not trying to push data back to GP (in which case there is an integration module available -e-connect).

I do have a couple of sql views that make temp tables that I write smart lists and crystal reports off.

Ian

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

Ian, I am going to review the seven tables to which they want to add indexes and compare to what indexes are already there. In theory adding indexes should not be a problem but past experiences tell me not to mess with the structure of GP tables.

Was this reply helpful? Yes No
Ian Richardson 4,150 on at

Like (0)

Report

i would be comfortable changing the contents of the table (if I knew what it did and was not breaking links to other tables). I don't know that I would be comfortable changing the structure of the table itself.

good luck!

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

I just read a long explanation of why the web programmer wants to add these indexes. Apparently they are using eConnect to sense changes and are wrting these changes to a log file. They then use this log file and want to join them to GP databases and then push this revised data out to the cloud database. Withoout seeing then actual queries I really cannot determine if this index additions are warranted. I have asked if they have done the same to other MS Dynamics GP databases plus I asked for a copy of the queries. Let's see how well that goes over.

Was this reply helpful? Yes No
Derek Albaugh on at

Like (0)

Report

Richard, for an official Microsoft Dynamics GP point of view, I refer to what is mentioned in our white paper for performance issues:

"Default indexes on Microsoft Dynamics GP tables should not be changed or removed. Careful planning is necessary when implementing new indexes to avoid performance decreases. Adding indexes can adversely affect performance due to the overhead of maintaining the index as well as negatively affecting other execution plans."

Thank you,

Was this reply helpful? Yes No
Richard Wheeler 75,850 Moderator on at

Like (0)

Report

Derek, I appreciat your input. This web development company is adamant about these indexes. I have reitirated your exact comments. My client wants someone from Microsoft on the phone with this developer explaining the pros and cons of adding indexes.

Was this reply helpful? Yes No
Verified answer

Derek Albaugh on at

Like (0)

Report

Hello Richard,

The client can open a support case to speak with our GP Development team who can address their questions on the Pros and Cons of adding indexes to our default Dynamics GP tables, but what I stated previously is the 'official' recommendation.

Thanks,

Was this reply helpful? Yes No