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

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
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.
wolverangelAuthor Commented:
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?
Bill BachPresident and Btrieve GuruCommented:
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?
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

wolverangelAuthor Commented:
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.
Bill BachPresident and Btrieve GuruCommented:
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?
wolverangelAuthor Commented:
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.
wolverangelAuthor Commented:
We are using the Btrieve drivers.
Bill BachPresident and Btrieve GuruCommented:
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.  

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wolverangelAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.