Quick Search not so quick

We just installed 10.1.600, coming from 10.0.700.4, in our test environment, but we’re seeing very slow performance in our quick searches. Has anyone else experienced this problem? I’ve run the ZFW…GetById stored procedure and it returns instantly, but I run the Quick Search GetByID method, on the server, in the BL Tester and it takes 20 seconds. This happens even with base Quick Searches (starts with z). The same GetByID on 10.0.700.4 and 10.1.500.x were all fast.

I should also mention, this is not happening when we search using the quick searches, but the slow speeds occur just pulling up the quick search screen.

The issue is related directly to the number of criteria in the quick search. It re-queries the BAQ designer and quick search criteria information for each criterion.

It sounds like this is a known issue. It is scheduled to be resolved in Epicor 10.2. Now I just need to find out where to download 10.2 :stuck_out_tongue:

We are having the same experience with Quick Search in E 10.1.600.5
What a disappointment.
The standard part search is crap, not bringing up first the closest match, so we tried to replace with a quick search and it takes 10 X longer for the dialog to come up.

Nancy

Our strategy for quick searches now is to limit the fields that are displayed. So in our BAQ I return a field with PartNum, Description, and some other fields delimited, and then do a MACTHES search from the quick search. Not ideal, but it makes it a little faster. We have about 15 different parameters for our quick search on Orders. We’re thinking of taking a completely different approach on that search, because I don’t think I can limit that one down to 2 or 3 parameters.

I was able to workaround the issue by creating a (GASP) Base method directive on QuickSearch.GetByID and filling the data myself from the available tables. Quick searches now open within half a second as opposed to some which were taking 30+ seconds. Once this is fixed, we’ll remove this, but for now, we have to do this because we need to upgrade. We’re stuck on 10.0.700.4 otherwise.

2 Likes

image

2 Likes

@Stephen_Booth We are noticing a similar issues with zPOLine quick search. Would you be willing to share the fix you came up with on the base bpm for quick search?

What I’ve done works in our environment, but I can’t guarantee it will work in anyone else’s environment. We don’t get extremely fancy with the quick searches. Also, if you ILSpy the code for quicksearch getbyid you’ll notice Epicor does many more things than what I do in this Base BPM. I also do not suggest you keep this once Epicor has this fixed. I agree that Base BPMs should be avoided if another workaround is available.

I would encourage anybody who is considering this to look at the tables used. This code does not produce a 100% identical dataset as the normal GetByID method. We are able to save our quicksearches having been retrieved from this dataset though.

Having said all that:

   // add the quick search row
var qs = Db.QuickSearch.FirstOrDefault(row => row.QuickSearchID == quickSearchID && (row.Company == null || row.Company == "" || row.Company == Session.CompanyID));
if(qs != null)
{
    var addedQS = new Ice.Tablesets.QuickSearchRow();
    addedQS.Company = qs.Company;
    addedQS.QuickSearchID = qs.QuickSearchID;
    addedQS.GlbCompany = qs.GlbCompany;
    addedQS.Description = qs.Description;
    addedQS.UserID = qs.UserID;
    addedQS.ExportID = qs.ExportID;
    addedQS.LikeDataFieldTableID = qs.LikeDataFieldTableID;
    addedQS.LikeDataFieldName = qs.LikeDataFieldName;
    addedQS.GlobalSearch = qs.GlobalSearch;
    addedQS.ForValidation = qs.ForValidation;
    addedQS.IsShared = qs.IsShared;
    addedQS.SystemFlag = qs.SystemFlag;
    addedQS.ReturnFieldTableID = qs.ReturnFieldTableID;
    addedQS.ReturnFieldName = qs.ReturnFieldName;
    addedQS.CallFrom = qs.CallFrom;
    addedQS.ContextDefault = qs.ContextDefault;
    addedQS.BaseDefault = qs.BaseDefault;
    addedQS.Version = qs.Version;
    addedQS.HotKey = qs.HotKey;
    addedQS.CGCCode = qs.CGCCode;
    addedQS.IsPredictive = qs.IsPredictive;
    addedQS.SourceSystemCode = qs.SourceSystemCode;
    addedQS.SourceTableID = qs.SourceTableID;
    addedQS.SourceFieldName = qs.SourceFieldName;
    addedQS.SearchOnFieldSystemCode = qs.SearchOnFieldSystemCode;
    addedQS.SearchOnFieldTableID = qs.SearchOnFieldTableID;
    addedQS.SearchOnFieldName = qs.SearchOnFieldName;
    addedQS.SuppressBaseSearch = qs.SuppressBaseSearch;
    addedQS.SysRevID = BitConverter.ToInt64(qs.SysRevID.Reverse().ToArray(), 0);
    addedQS.SysRowID = qs.SysRowID;
    addedQS.ReturnField = string.Format("{0}_{1}", qs.ReturnFieldTableID, qs.ReturnFieldName);
    addedQS.LikeField = string.Format("{0}.{1}", qs.LikeDataFieldTableID, qs.LikeDataFieldName);
    var t = Db.Table.FirstOrDefault(row => row.Name == qs.ReturnFieldTableID);
    if(t != null)
    {
        var c = Db.Column.FirstOrDefault(row => row.Name == qs.ReturnFieldName && row.TableID == t.TableID);
        if(c != null)
        {
            addedQS.ReturnDataType = c.DataType;
        }
    }
    result.QuickSearch.Add(addedQS);
    
    // add the criteria
    foreach(var qsc in Db.QuickSearchCriteria.Where(row => row.QuickSearchID == quickSearchID && (row.Company == null || row.Company == "" || row.Company == Session.CompanyID)))
    {
        var addedQSC = new Ice.Tablesets.QuickSearchCriteriaRow();
        addedQSC.Company = qsc.Company;
        addedQSC.QuickSearchID = qsc.QuickSearchID;
        addedQSC.GlbCompany = qsc.GlbCompany;
        addedQSC.Seq = qsc.Seq;
        addedQSC.DataTableID = qsc.DataTableID;
        addedQSC.FieldName = qsc.FieldName;
        addedQSC.DataType = qsc.DataType == "logical" ? "bit" : qsc.DataType;
        addedQSC.FieldLabel = qsc.FieldLabel;
        addedQSC.CompOp = qsc.CompOp;
        addedQSC.CriteriaType = qsc.CriteriaType;
        addedQSC.IsReturnCol = qsc.IsReturnCol;
        addedQSC.CriteriaValue = qsc.CriteriaValue;
        addedQSC.FilterOnNull = qsc.FilterOnNull;
        addedQSC.SystemFlag = qsc.SystemFlag;
        addedQSC.SysRevID = BitConverter.ToInt64(qsc.SysRevID.Reverse().ToArray(), 0);
        addedQSC.SysRowID = qsc.SysRowID;
        addedQSC.CriteriaField = string.Format("{0}_{1}", qsc.DataTableID, qsc.FieldName);
        var zd = Db.ZDataField.FirstOrDefault(row => row.DBTableName == qsc.DataTableID && row.DBFieldName == qsc.FieldName);
        if(zd != null)
        {
            addedQSC.CriteriaLikeFieldName = zd.LikeDataFieldName;
            addedQSC.CriteriaLikeTableID = zd.LikeDataFieldTableID;
            var tc = Db.Table.FirstOrDefault(row => row.Name == qsc.DataTableID);
            if(tc != null)
            {
                var c = Db.Column.FirstOrDefault(row => row.Name == qsc.FieldName && row.TableID == tc.TableID);
                if(c != null)
                {
                    addedQSC.FieldFormat = c.Format.Replace("shortyear", "");
                }
            }
            addedQSC.LikeField = zd.LikeDataFieldName == string.Empty ? "" : string.Format("{0}.{1}", zd.LikeDataFieldTableID, zd.LikeDataFieldName);
        }
        result.QuickSearchCriteria.Add(addedQSC);
    }
    
    // add quick search values
    foreach(var qsv in Db.QuickSearchValueList.Where(row => row.QuickSearchID == quickSearchID && (row.Company == null || row.Company == "" || row.Company == Session.CompanyID)))
    {
        var addedQSV = new Ice.Tablesets.QuickSearchValueListRow();
        addedQSV.Company = qsv.Company;
        addedQSV.QuickSearchID = qsv.QuickSearchID;
        addedQSV.GlbCompany = qsv.GlbCompany;
        addedQSV.Seq = qsv.Seq;
        addedQSV.ValueSeq = qsv.ValueSeq;
        addedQSV.DisplayMember = qsv.DisplayMember;
        addedQSV.ValueMember = qsv.ValueMember;
        addedQSV.SystemFlag = qsv.SystemFlag;
        addedQSV.SysRevID = BitConverter.ToInt64(qsv.SysRevID.Reverse().ToArray(), 0);
        addedQSV.SysRowID = qsv.SysRowID;
        result.QuickSearchValueList.Add(addedQSV);
    }
}
1 Like

A product as complex as Epicor it’s understandable but it’s always fun running into these “Undocumented features”. :slight_smile:

We just upgraded to 10.1.600 series too, so I’ll keep an eye out for our quick searches. Most of ours are only 1 - 3 parameters and seem to run acceptably. But users have mentioned it’s slower.

Thanks!

Stephen -
Sorry to revise an old thread but I am curious if you saw any development on this front? We are on 10.2.200.11 and are certainly feeling the pain on "quick search"es. I have attempted to employ your code and while it does work well on some searches it seems it breaks others so unfortunately, it is not going to be a solution for us.

Thanks,
Brad

Brad,

We’re currently on 10.2.200.11 in our test environment and see the same issues. It seems to be related to a BAQ performance issue (see Logging or debugging performance on REST BAQs (baqsvc))

I ran a trace on our most parameter happy quick search. It takes 40 seconds to load. It has around 40 parameters. For each parameter there is a line in the trace log that says something like this

[TablesetEvent tsName=“BAQDesignerTableset” method=“AfterGetRows” duration=“963.9158” /]

So it looks like we’re running into the same problem as my other issue.

As far as the BPM code, our quick searches aren’t terribly complex, so the code above doesn’t completely recreate the data Epicor will send back. If you post the error or the quick search I can see if I can help you figure out what is going on. Maybe we can make this work until Epicor can find the problem.

Thanks

Stephen -
I did not get an error message although after reloading the form in question, it does seem to be working properly! I also opened a ticket with support who confirmed there is already a problem assigned to this issue (PRB0193992) but I can’t tell if there has been much movement.

Thanks again!

Our team just installed 10.2.200.14 in our test environment and I turned off this BPM. Quicksearch popup is now much quicker.
Thanks to everyone at Epicor who worked on this. This solved two of our major performance problems relating to BAQs.

I have the final solution for this and I have submitted for resolution. Unfortunately, it is graded as a “cosmetic / documentation” issue so may take some time to be completed and it is unlikely to be back-ported to previous releases.

Requested resolution: Change all occurrences of the term “Quick Search” and replace with “Leisurely Search”.

6 Likes

FYI, we’ve been struggling with this since go-live on E10 and aren’t ready to upgrade to 10.2 yet. Since Stephen had noted the problem being with BAQ performance (thank you!) I thought to try an External query as the data source for the Quick search. Voila! It works. There’s no lag. Of course, there’s lag on the quick search save after creating it, but no lag on the usage of the search.
Rich, you’re proposal for solution was tempting :slight_smile:

Nancy