(I thought I posted my update a while ago, but I forgot to hit send)
So I have original query working really fast now.
However, I still have a hard time (times out most of the time) with the history when I join it back to the query. Any ideas why that would be?
This might be above and I missed it but what does joining history back to the query mean? Can you post the BAQ file?
I have a sub query that groups and counts the number of unique pack statuses (like pack, assemble etc) this is table UD08. I want to look through the whole table and any time the child part and the parent are the same combination, I want to see how many different ways it has been set. Basically giving me a history. I am using that sub query to join to current job information and give suggestions whenever the same child parent combo is the same and there is only 1 unique status. So the “history” is simply a sub query which I would like to join to the top level to give the suggestions.
The BAQ is up above. It hasn’t changed, other than adding “company” to the criteria.
Gotcha - I will take a look again
So I’m back to try and figure out how to make this work well enough to use. The join of two values seems to bog it down to unusable speeds. I need to more ideas for what else I can try. Thoughts I’ve had so far.
Concatenate Parent part number and Child part number as populate a key field with these. It would speed up things I think, but potentially cause problems because I really only want the job number, assembly sequence, and material sequence as the identifying parts of the table. Part numbers can change to easily (for example a bolt length was wrong, and we need another part number for a 1/4" longer bolt)
Basically the same as above, but instead of a key field, just use another character field. This one I’ll probably try today.
Create some sort of update process that populates the pack suggestions in the table at regular intervals. This would take the load of when the end user is populating new jobs with pack status instructions, but would add another process to be managed. Also this managing process would use the same slow query, so it may still time out and break.
Any other ideas of things that I could try?
this post qualifies as TLDR today, however have you tried looking at the execution plan for this query and see where it is getting hung up? Sorry if it has already been discussed.
It has been discussed, but I don’t really know what I am looking at, or what to do about the results. It looks like there a couple of “Clustered Index Seek” at 20-30%. Let me get my test one set back up the way that I think it should work and I’ll recheck it and see if anyone can tell me what I should do with the results.
So this Hash Match at 31% is the highest one. The next closest is a 14% and 10% both clustered indexes. What can I do with this information?
The green text at the top of your screenshot is the recommendation. There is probably the option to create the missing index using a script that it’s already prepared for you.
How do I add an index to a BAQ?
You don’t, you add the index to the table/ db
So I would have to keep updating that index then as data is added? What if the data for that index changes, (see the part number change example above).
Well the index once added to your DB will be maintained like all other indexes in the DB. you shouldn’t have to do much (other than run the maintenance jobs)
So basically what this is saying to do? Create the index. How does E-10 know to use it then? Does this new column show up in the BAQ editor? Or do I have to make this an external query to make it work? Sorry about the dumb questions, remember I’m a manufacturing engineer, not an IT guy/software developer…
So an index is just (like in a book) it tells the DB where to find certain records. You run the create index command in the Epicor DB and it generates the index. From then on any time you run a query which makes use of this index (IE, it uses all the keys indexed by the index) it will use it to find the records faster.
So just to make sure I understand (in terms of my example), I would create the index on the columns ShortChar01 (part number) and ShortChar03(parent part number). Then when I am trying to join using those 2 columns, E10 will look at the index say “hey we have an index for those” and use that instead of the table scan, right?
Once the index script is run, it will create the index for existing records, then when new ones are added are the indexes added with new rows, Right? Then the maintenance job that you are talking about should reorganize the indexes after they have been added on a periodic basis to make it most efficient. Is that description accurate?
This article explains some general ideas about indexes and SQL maintenance. These ideas apply to what I am talking about right?
Correct.
The stuff in green in the execution plan is the “script” you need to run to create those indexes. (just give it a better name than
One thing to keep in mind don’t start creating indexes left and right for all your BAQs, only for things that are truly causing pain should you venture into index creation land.
Oh yeah and always try it in TEST first. By the way those “maintenance” jobs I spoke about aren’t magically there by default, your IT staff should already be running maintenance on your e10 DB (re-index, re-builds, stats updates, backups etc…) just throwing that out-there in case
So I added the index to the pilot database. Putting the same query in the same dashboard, running the same filter, (job number) and Production runs faster than pilot. Production should have more data than pilot, since pilot is older.
The index that I added is ShortChar01 (PartNum) and ShortChar03 (ParentPartNum). ShortChar02 is my pack status, and it’s what I need to look up the history for. I’m only joining by 01 and 03. The recommendation called for 02 as well. Not knowing how the indexing works I figured I would only want the joins to be in the index, so I only added 01 and 03. Should I add 02 as well? (I’m going to try it anyways, I’m just trying to learn about the theory of how indexing works)
So the index only works with the keys you gave it, adding 02 should not be necessary.
So I tried it out and without 02 in there, it takes a long time or times out. With 02 in there, it finishes in about 4 seconds. So I have the index order ShortChar03 (parent), ShortChar02(part), ShortChar02. I think this will be what I need to make it work well.
Funny thing is, I haven’t added any index to production, but production runs very quickly. Does SQL look at usage (like me constantly testing) and see that and make some adjustments?
The other theory that I have is that production would have fewer empty strings in ShortChar02, as they have been set in production since we copied over to Pilot. Could this have an impact on performance?
Could it potentially hurt performance?
