This one is another SY07110 (syMenuMstr) table issue.
For the past 1 week, users were complaining about GP slow down as soon as they select a company and click OK. The delay was anywere between 30 seconds to 1 minute, which is quite huge, considering the fact that GP is suppose to get initiated within 3-5 seconds maximum.
We have two different launch files; those users who are not suppose access AA and those who access AA. This delay was reported by only AA users. And that eased my debugging task out.
Troubleshooting started by taking a SQL Profile exactly at the time of selecting a company and clicking on OK. After painfully long time, GP got initiated with all Menu and other startup objects. I stopped SQL Profiler and noticed the following piece of SQL query being executed for as many as 143 times, which in total resulted in 4730 records:
The above query is same except some of the fields. But all these SQL SELECTs where targeting the same product; AA (CmdDictID = 3180).
Quite shockingly, I had 4730 records in SY07110 for Dictionary ID 3180, i.e. AA.
So how to solve this? Simple. I took a backup of this table (just for precaution) and executed the following SQL query:
WHERE CmdDictID = 3180
It removed all 4730 records for AA module. I then launched GP. Bingo. It’s getting initiated in as long as 3 seconds.
The story does not end here. For the first time SY07110 table gets inserted with 33 records for AA module. As and when a user launch GP, with AA module, it inserts exactly ONE additional row onto SY07110 table.
Now this certainly looks like a bug.
So what’s the solution? Time being, I have to write a scheduled SQL job which will execute the above DELETE query on a weekly or daily basis.
I am not sure who else is currently facing this issue. If any of you people have this issue, then at least you have a workaround.
I am planning to raise a Support Ticket with MSFT team if no one out there gets back to me with a solution and make sure that this is not a bug as such.