Sage 100

 View Only
Back to discussions
Expand all | Collapse all

V2016 To 2021 Slow Custom Crystal

  • 1.  V2016 To 2021 Slow Custom Crystal

    Posted 08-28-2021 08:44
    I have a 2016 ADV that I’m upgrading to 2021 ADV.

    They have one custom report that runs :30 in 2016 and over an hour in 2021.

    I’ve quizzed on antivirus. Checked the NIC on VMware and haven’t found anything unusual ( IT assures me there is no AV - stop me if you’ve heard this one .. )

    I’m looking for any “ah ha” type revelations in things to check. My plan b is to rewrite the report. The report does finish and it doesn’t throw any errors. Just super slow.

    On the surface I could blame the report as it is pulling from AR Invoice History. But having it run quickly in 2016 won’t help my case.

    Have tested on server. Have tested on user workstation. Run from Sage menu ( custom reports ). Run from Crystal. Seems to be the same result.

    Thoughts?

    ---------------------------------
    Wayne Schulz - Schulz Consulting - 860-516-8990
    ---------------------------------


  • 2.  RE: V2016 To 2021 Slow Custom Crystal

    Posted 08-28-2021 09:43
    Maybe the conversion screwed up the table joins??

    ------------------------------
    Jeff Schwenk
    FORMER 90M Board Member -140,000,000 is a TERRIBLE thing to waste!!!
    Bottomline Software, Inc.
    Waynesboro VA
    540-221-4444
    ------------------------------



  • 3.  RE: V2016 To 2021 Slow Custom Crystal

    Posted 08-28-2021 10:50
    That's interesting.  I have a client on 2019 who had a report with a subreport in AR_InvoiceHistoryDetail.  I don't know if it had always been this slow, but they complained about it the other week and I decided on a lark to rewrite the subreport to use the Invoice Transaction Detail History file instead (which, you would think, would be a similarly-sized file unless they have a slew of comments).  The time difference was STAGGERING.  It whipped through using the IM file like it wasn't even a subreport (this was a subreport on EACH LINE of the main report, summing up sales history for a date range).  So maybe sometime after 2016 the ODBC for AR Invoice History got tweaked - the wrong way?

    ------------------------------
    Beth Bowers
    Mom to Samson, Peanut, ChiChi, Canton, Cagney and Daisy (NO Oxford comma - shriek!)
    Tennessee Software Solutions
    269-445-1625
    ------------------------------



  • 4.  RE: V2016 To 2021 Slow Custom Crystal

    Posted 08-28-2021 17:47
    I'm still looking at this however one thing I've done which seems to restore the speed is to remove the "equals" and replace it with "like".
    Once I do that the report generates in 30 seconds again.

    I'm not sure if this is the ultimate resolution. I'm still looking at this. 


    ------------------------------
    Wayne Schulz - Schulz Consulting - 860-516-8990
    ------------------------------



  • 5.  RE: V2016 To 2021 Slow Custom Crystal

    Posted 08-29-2021 03:00
    Were you using the C/S ODBC on the 2016 install and not using it on the 2021?

    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    Corvallis OR
    541-224-6642
    ------------------------------

    Original Message


  • 6.  RE: V2016 To 2021 Slow Custom Crystal

    Posted 08-29-2021 03:57
    Edited by Alnoor Cassim 08-29-2021 03:59
    Are you by chance sorting or grouping by ItemCode in the report? If so it will automatically use the KITEM index (an alternate key in the table) for speed (https://bit.ly/3BiQ6Qb ). However, I know AR_InvoiceHistoryDetail itself goes thru several conversions in the upgrade you did, the point being you could try Rebuild Key Files on it as this would rebuild all the indexes too including KITEM.

    ------------------------------
    Alnoor Cassim

    Email: alnoor@asifocus.com
    Ph: 949-689-9887
    Orange County, CA
    ------------------------------

    Original Message


  • 7.  RE: V2016 To 2021 Slow Custom Crystal

    Posted 08-30-2021 11:21
    The only change I make in the report is to go from using an " = " to " like " as below.

    I'm not ruling anything out ( corruption, etc ) but wonder if maybe the " = " somehow uses that index while the "like" avoids it and is somehow faster?

    As you noted - the index for KITEM didn't exist in 2016



    ------------------------------
    Wayne Schulz - Schulz Consulting - 860-516-8990
    ------------------------------

    Original Message


  • 8.  RE: V2016 To 2021 Slow Custom Crystal

    Posted 08-30-2021 10:05
    One other thing to note is that you should ALWAYS use formulas for selections AND groups, not the actual data field. Crystal processes formulas first.​

    ------------------------------
    Therese Logeais, Technology Integrators
    ------------------------------

    Original Message


  • 9.  RE: V2016 To 2021 Slow Custom Crystal

    Posted 08-29-2021 20:38
    There isn't any C/S ODBC running.
    The report groups on Customer Number - no subreports or anything else which seems odd. 

    I can't rule out some type of corrupted data ( tried rebuilding the files and sorts ) but since the report does complete and does run very quickly when I change the select criteria I'm not thinking this is damaged data or damaged report.

    ------------------------------
    Wayne Schulz - Schulz Consulting - 860-516-8990
    ------------------------------



  • 10.  RE: V2016 To 2021 Slow Custom Crystal

    Posted 08-30-2021 09:54
    Try CS/ODBC.  It could be the Windows memory cache issue on Win Server 2019, running the report in Standard mode.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 11.  RE: V2016 To 2021 Slow Custom Crystal

    Posted 08-30-2021 10:57
    Thanks, everyone for the helpful suggestions/ideas.

    The only way I could get this report to work was by changing going into select export and changing the select on the item code field from " = " to " like ".

    I suspect that could cause an issue if the customer creates additional item codes that are very similar ( ie ITEM001, ITEM002, ITEM003 ).

    The size of the AR Invoice History and Detail files are both about 150 MB so not huge but large enough that the report running slowly did not see super strange to me.

    I did try "is one of" and that didn't seem to speed things up.

    ------------------------------
    Wayne Schulz - Schulz Consulting - 860-516-8990
    ------------------------------



Related Content