90 Minds Community

 View Only

  • 1.  Custom Crystal Report - slow performance across network

    Posted 11-15-2021 11:08
    I have a similar issue with 2 clients on different versions of Sage - 1 is Standard 2020 the other Advanced 2021.  Both are running custom Crystal Reports from a Sage menu with several tables (no subreports). One is pulling from AR Invoice History tables and the other from Inventory - so number of transactions are relatively high.  The reports finish on the servers in a decent amount of time, given the volume of data.  However, the workstations for one client take up to 10 minutes to finish (10.28 seconds on the server) and the other client takes hours or doesn't finish at all (3 minutes on server).

    Both clients have had their IT departments involved - did all the usual things with anti-virus exclusions, disabled anti-virus for testing, etc. I've done all the Crystal tricks - formulas for fields used in selections and groupings, no left outer joins on tables, etc.  I'm assuming it's network performance but am out of suggestions.  Anyone have ideas I can pass along? Thanks!

    ------------------------------
    Dawn Kulbacki
    Capitol Computer Systems, Inc.
    ------------------------------


  • 2.  RE: Custom Crystal Report - slow performance across network

    Posted 11-15-2021 12:27
    Unsure if this is relevant to you but I had a similar issue and replaced an "=" select criteria with "like" and the report sped up significantly.


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



  • 3.  RE: Custom Crystal Report - slow performance across network

    Posted 11-15-2021 12:31
    Hmmmm.  There are definitely selection criteria in both reports where I have an "=".  I can give that a shot.  Thanks!

    ------------------------------
    Dawn Kulbacki
    Capitol Computer Systems, Inc.
    ------------------------------

    Original Message


  • 4.  RE: Custom Crystal Report - slow performance across network

    Posted 11-16-2021 09:58
    My first hunch would be network bandwidth (especially for Std), which you or IT can test using iPerf (https://iperf.fr/). 

    Depending on how often and how 'real-time' the reports need to be, I'll sometimes install SQL Server Express on the server and pull the tables into SQL each night.  Then you can re-point the custom reports to SQL and they'll run MUCH faster than Std/Adv.

    ------------------------------
    Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
    ------------------------------

    Original Message


  • 5.  RE: Custom Crystal Report - slow performance across network

    Posted 11-17-2021 10:32
    Another data-geek trick: Re-installing the ODBC drivers and the reporting tool (Crystal) on computers with issues.
    On a similar thread of @Steve Iwanowski, DataSelf ETL+ easily mirrows Sage 100 data into MS SQL (Express, Std, Ent - on-prem or cloud).
    It has delta refresh functionality to extract data frequently with minimal impact to Sage. System requirements: any Windows 10 or Win Server with access to Sage. 
    Joni Girardi
    DataSelf


    ​

    ------------------------------
    Joni Girardi
    Dataself Corporation
    ------------------------------



  • 6.  RE: Custom Crystal Report - slow performance across network

    Posted 11-18-2021 01:15

    Hi Everyone,

    We're new here but not particularly new to Sage 100 connectivity and the challenges with the Providex ODBC driver (sounds like this might be the issue?!?)   We've done extensive research into the failings of the driver when extracting large batches of data.   Probably too much to write here but essentially there is a need to control the batch size, amongst other driver level options. 

    While I started using Crystal 27 years ago :), I can't remark about capabilities available today (because I've been using the SD&A product for the last 13 years) but am more than happy to connect with anyone interested in learning more about Sage 100 ODBC connectivity, privately.

    We're excited to be here and won't wear out our welcome.   I'll just say, if you've not heard of Sage Data & Analytics, I'd encourage you to talk with your Sage PAM about us or let us know directly if you'd like to learn more.

    All the Best,

    Trey



    ------------------------------
    Trey Johnson
    Chief Evangelist & Americas Business Leader - Sage Data & Analytics
    ------------------------------

    Original Message


  • 7.  RE: Custom Crystal Report - slow performance across network

    Posted 11-18-2021 12:23
    @Dawn Kulbacki - With your advanced client, are they running via the CS ODBC setup?  Does the issue present with ALL clients on Sage?  Has this ALWAYS been an issue, or a recent occurrence.  Back in the day, NIC's, cabling, switches were a common issue.  Maybe @Dan Burleson might have a thought regarding Crystal tricks​.  Perhaps PTQ's via Access is the solution.........

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

    Original Message


  • 8.  RE: Custom Crystal Report - slow performance across network

    Posted 11-18-2021 16:08
    Depending on the reporting frequency I've had customers setup a "reporting company" in Sage, then if they need the report monthly (for example) you can just refresh the data using the Company Maintenance copy, then in the Reporting company aggressively purge AR invoice history.  If they are reporting on October 2020 then purge all Invoice History before the year 2020, for example.  If your report is doing comparisons to prior year then you can't purge that data either, so cutoff so you have 2 years.  Now run the report against the reporting company's data instead.

    If they never purged AR invoice history and they've been using Sage for 20 years this technique should speed the report by about 90% because you'll be removing ~90% of the records.  Of course you can also purge IM history, depending on how the report is designed that could get you better results.

    This trick doesn't scale well if the reporting requirement is frequent, but it's a great trick for once a month type reports that take forever.

    ------------------------------
    Jim Petrie
    Consultant
    DSD Business Systems
    ------------------------------



  • 9.  RE: Custom Crystal Report - slow performance across network

    Posted 11-19-2021 04:21
    Edited by Dan Burleson 11-19-2021 15:22
    My two cents:
    1. Definitely check out C/S ODBC on Advanced.
    2. On Standard or Advanced with large tables, I have success using WHERE clauses with ProvideX formatted date parameters passed in the form "{d'yyyy-mm-dd'}" that I use with MS Access Pass Through Queries or by with MS Excel Power Query in Excel 2013 or greater.  I definitely recommend going the Power Query route. Did  you know that you can copy the Crystal Report SQL statement directly into Power Query and pass date parameters from a worksheet or dialog box?
    3. Last option in my free MS Access Shadow database which is useful for easily creating Pass Through Queries and/or mirroring Sage 100 tables (without the date conversion issue of SQL Server). All Windows systems have built-in drivers for Access databases so clients need only one computer, preferably the Sage 100 server, with MS Access installed.  I know longer recommend it for unattended refreshing of mirrored tables, but it is simple to install, setup and run. It can also be used to append multiple company copies of the same table (think Positive Pay for management companies), adding a column for company code. PM me and I'll send you a link for the 32 or 64 bit database.
    ​

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

    Original Message


  • 10.  RE: Custom Crystal Report - slow performance across network

    Posted 11-22-2021 17:13
    Edited by Dan Burleson 11-22-2021 23:32
    After the above two cents I decided I need more objective measures so I tested various Sage 100 Advanced reporting methods across the network including Crystal Reports, MS Excel Power Query, and MS Query. I used both direct ODBC and C/S ODBC as well as indirect methods through MS Access using linked tables and Pass Through Queries (PTQ). These are my results. The details of the environment, notes and conclusions follow:

    Legend: PVX = ProvideX ODBC SQL Language

    1. MS POWER QUERY -> ODBC*: This MS Excel query was embedded as a Power Query with the Source connected to the ODBC DSN SOTAMAS90 with the "Odbc.Query" statement.
    2. MS POWER QUERY -> ODBC: Same as above but without the ProvideX formated date criteria and instead with the same condition filtered subsequently in Power Query
      The purpose of the above two queries is to show the benefit to embedding the ProvideX date format in the Power Query ODBC.Query statement.
    3. MS QUERY -> MS ACCESS PTQ -> ODBC: This uses the older MS Excel MS Query that can use an MS Access Pass Through Query that can present an embedded PVX date formatted criteria to the ODBC Driver
    4. CRW -> ODBC: This a Crystal Report using the standard method for Sage 100 reports where the data source is the ODBC DSN SOTAMAS90
    5. CRW -> MS ACCESS LINK -> ODBC: This is a Crystal Report that uses as its data source MS Access table Links to the SOTAMAS90 ODBC DSN
    6. CRW -> MS ACCESS MIRROR: This is a Crystal Report that uses as its data source an MS Access with local tables with data imported from the SOTAMAS90 ODBC DSN. Similar performance is seen with a SQL database with data imported from the SOTAMAS90 ODBC DSN or from a Sage 100 Premium SQL database.

    Environment:
    1. Sage 100 Advanced 2019: AR_InvoiceHistoryHeader 59K records, AR_InvoiceHistoryDetail 1,730K records
    2. Workstation H/W: 10th Generation Intel Core i7 32 GB RAM, vPro 2.70-5.10 GHz w/Turbo Boost 6 cores 12 Threads 12 MB Cache.
    3. Workstation S/W: Windows 10 Pro 64, Crystal 2016, MS Office 365, Excel 16, Access 16, 32/64 Bit-ODBC
    4. Server H/W: Intel Xeon E5, 40GB RAM, 3.2GHz 12 MB Cache
    5. Server O/S: Windows 10 Pro 64  (Yes are reading this right. I replace Windows Server with W10 on my server H/W to simplify my life)
    6. Re-installed Crystal from the 2019 WkSetup folder
    7. Shadowed Tables contain all 1.8M records, but only the 7 fields used in the report
    8. Power Queries and Pass Through Queries contain only the 7 fields used in the report
    Notes:
    1. Anyone running similar tests must be careful to cleared active connections when changing to/from C/S ODBC parameters (e.g. MS Query, PTQ, Linked Tables)
    2. Running 2019 Adv as Std the "CRW-> ODBC" test ran in 121 seconds versus 89 in advanced. A 36% degradation from Adv. Or a 26% improvement over Std
    3. I found no difference between running an external Crystal Report versus running it internally as a Custom Report.
    4. Some methods dis not allow for filtering at the ODBC level and only at the application level (MS Access Linked tables and local mirrored tables).
    5. Although MS Power Query can submit filters (PVX format date filters in these tests) to the ODBC, I tested it with and without them to show the benefit of the extra effort to do so.
    Conclusions:
    1. C/S ODBC is good and Pass Through Queries make it better
    2. Pass Through Queries are not available with Power Query (I would like to be told otherwise)
    3. Power Query is unable to access Microsoft Access Pass Through Queries as data sources
    4. Nothing beats Premium, but mirrors like SQL Server with a Linked Server and Stored Procedures or MS Access perform comparably well, albeit with drawbacks


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

    Original Message


Related Content