Crystal Report perfromance degradation seen after migrating a Pervasive.SQl - based application from server-client configuration to a Citrix environment.

Posted on 2008-11-12
Last Modified: 2013-12-25
After a recent acquisition, we migrated a pervasive.SQl - based application called FPA (Forest Products Accounting) from its existing server - client configuration to our Citrix based environment. We installed the Pervasive client on two load-balanced Citrix servers (Windows Server 2003) and we installed the Pervasive server engine on a separate server at the same site (also Windows Server 2003). Since the migration, users have reported significant degradation in performance when loading reports (the app uses Crystal Reports). I have monitored all three servers during operation and have noticed no significant spikes in resources while users are loading large reports. The vendor suggested configuring the micrkernal on the Pervasive server to not use local microkernal engine. This has made no positive impact. I am thinking this configuration change should be made on the Citrix boxes as the engine does not reside on those. I am hesitant to make more changes. Figured I'd rech out.
Question by:wolverangel
    LVL 28

    Expert Comment

    by:Bill Bach
    Has the database server been changed, or are you using the same server now that you were using before, and only the clients were changed?  If the server is different, try comparing the database configuration (Start/Run/PCC, then click on Configure Local Engine in the Welcome Screen) of the old server with the new one.  Also, you can do the same on the two Citrix boxes (PCC/Configure Microkernel Requester) and compare them with the old server.  If you find major differences, you might want to try changing the settings and rebooting.

    The online manuals contains lots of information about tuning of the environment, but much depends on available resources, so you have to read a lot into it.  

    Companies exist that can review your client and server environments for a small fee remotely via the Internet, and if you don't want to learn how to become a Windows/Pervasive tuning person, then you may wish to look into this as an alternative to doing it yourself.

    Also, verify your networking configuration, which will be outside of the Pervasive tools.  You can use the Pervasive System Analyzer to perform a network communications test as the best option.  Go to and see the white paper that talks about using the PSA to test your network performance for additional details on how to run it.

    Let me know what you get from the tuning/stress tests, and we can decide where to go from here.

    Author Comment

    Thank you for the response. Per your suggestion, I tried the relational and transactional stress test from one of my Citrix machines. The transactional test produced this result:

    Local engine not accessible to the microkernel router.

    Status code = 3012

    This result combined with the fact that the PCC reveals that the microkernal router is configured to use the local microkernal engine indicates that there is no engine installed locally on the Citrix box. Could this be correct? and could this contribute to my report lag?
    LVL 28

    Expert Comment

    by:Bill Bach
    When you ran the test, did you point it to the SERVER for the sample data file?  If you left it on C:\, then you would indeed get a 3012.  Please specify the corresponding directory on the server when you run this test.

    More importantly, I am curious as to the result of the Communications Test, not the MKDE or SRDE tests.  What did you see?  Were there any delays, as indicated by the paper?

    Author Comment

    Ah yes. I had pointed it at the local samples directory. Once pointed at the server, It was successfull.

    The communications test was successfull. These were the results:

    Friday, November 14, 2008 2:13:32 PM  ---

    *** Checking if user specifies first available protocol for the test...

    First available protocol for the test is specified

    *** Checking for installed protocols on local machine...

    Winsock TCP/IP is available.

    Winsock SPX is not available.

    Winsock (null) socket error = 0.

    Winsock SPX is not available.

    NetBIOS is available.

    *** Checking if any of the installed protocols are configured as Pervasive.SQL Supported Protocols on the local machine...

    Pervasive.SQL requester is configured to use SPX transport protocol.

    Pervasive.SQL requester is configured to use TCP/IP transport protocol.

    Pervasive.SQL requester is configured to use NetBIOS transport protocol.

    *** Establishing connection using previously used protocol based on SAT entry...

    Target machine name : hnrgnocprod

    Resolving name to address using Server Address Table (SAT)...

    Trying to use named pipe request to resolve name to address...

    Attempting to connect via WinSock TCP/IP...

    Successfully connected to via TCP/IP protocol.

    *** Determines if the SAT entry for the target machine is valid...

    The connection information stored in the SAT entry for the target machine is verified to be still accurate.

    *** Stress test the engine using the connected protocol...

    Performing TCP/IP Stress Test...

    Attempting to transmit 75 test messages...

    Btrieve Version is 9.50 Windows NT.

    Friday, November 14, 2008 14:13:34

    ******Stress test started******

    Successfully transmitted 75 test messages.

    Friday, November 14, 2008 14:13:34

    ******Stress test ended******

    Number of test messages sent per minute during stress test is 4500.

    *** Terminate any active TCP, SPX and NetBIOS connections...

    Successfully terminated TCP/IP connection.

    Network Test Summary
    Successfully connected to hnrgnocprod via TCP/IP protocol.
    Successfully transmitted 75 test messages via TCP/IP protocol during stress test.
    LVL 28

    Expert Comment

    by:Bill Bach
    Sounds like the network performance is great.  No pauses, right?   If not, then we can exclude the network from being involved in the performance side.

    When querying with CR, are you using their built-in Btrieve drivers, or are you using the Pervasive ODBC driver?

    Author Comment

    I will look into the drivers. In the meantime, more evidence. A large report that took almost an hour to load via Citrix from a remote workstation, took only about 20 minutes to load when the app was launched directly on the Citrix machine. I'm starting to think our issue isn't with Pervasive. We have numerous applications running on these Citrix machines. Although the resources don't appear to max out during operation. Perhaps the Citrix load balancing thresholds are the limiting factor? But then we would see sessions denied rather than a performance issue.

    Author Comment

    We are using the Btrieve drivers.
    LVL 28

    Accepted Solution

    That would explain it.  The Btrieve-level drivers must pull all of the data down to the local machine for processing.  For large queries that do not have a suitable index for optimization, this can take a LOT of time, even for a small resulting data set.

    If possible, switch it over to using the Pervasive ODBC drivers instead.  These drivers leverage a MUCH more powerful SQL engine and will run the query on the server (i.e. local to the data), resulting in much faster response time overall (usually).

    If this is NOT possible, then you may have to manually optimize your queries by evaluating what CR is doing at the record-by-record level and change the query (or add indices) to further optimize the environment.  This is a non-trivial exercise and requires an understanding of the Btrieve API interface and some advanced analysis tools.  

    Author Closing Comment

    I will run that by the vendor's engineers who have been lending assistance on this issue as well. Thank you for all your help.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Citrix XenDesktop, gold image, VMware, vSphere.
    #Citrix #Citrix Policies #XenDesktop #VDI #POC #Citrix Univeral Printer Driver #Citrix UPD
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now