An interesting conversation came out of our EUG meeting today where MRP was getting bogged down by a condition in the change log. They wanted to filter by the first character of the job number. If they run it wide open MRP runs fine. If they add a condition so the table doesn’t get bloated, it slows way down.
If they were to add a UD field and a BPM to house those first characters, then idex that field, would it speed up the processing of the data directive and MRP overall? Or does the indexing only help things like queries?
Brandon,
Firstly let me qualify my comment, I have never had a need to use indexed views, but if you were using a ud field I would expect that is where you would want to put the index. On that note I have not done a profiler to see what happens when you add a UD table, I know it adds a new table and the result is view created in the dbo schema in the target database for the resultant table eg erp.part and erp.part_UD are joined to create the view dbo.part.
I did a little bit of research, and in order to have a view that can be indexed is it needs to be created with the SCHEMABINDING option, when the view is created in E10 that option is not used, so you would have to drop the view and recreate it…Probably somethng I wouldn’t want to be doing, as you don’t really know what it might break. (Maintenance agreement breach yada yada yada.)
It might be that putting an index on the UD table might suffice.
It’s one of those things if you have the time you could experiment.
Here are a few links to Indexed Views and their limitations
Please note the last post is dated 2013, so I don’t know how much has changed in 5 years, but I the MS says SQL starting with 2008, so it may not be that much different in the current versions of SQL. Hopefully there are others with some more intimate exposure to the things I have mentioned will be able to set us straight.
You are correct. As I mentioned turning the table view into an indexed one might not be a good idea. I really wanted to point out that you can put indexes on views instead of the base table.
I’m slightly guessing here, but it sounds to me like indexing isn’t the main issue.
Indexing specifically helps with retrieval of data from the database. So anywhere data is being pulled for use, whether in a BPM or anywhere else, it’s going to be faster as long as it was the pulling of the data that was slow.
In this case, it sounds more like a line-by-line processing thing. You might well find that if you did store the key character in a UD field and made sure it was always populated before it’s needed, skipping the processing step where it needs to strip it away from the job number is enough to restore the speed all by itself. We have found that in some similar situations.