Printing too slow Pervasive.SQL v8.6/ACCPAC 5.3

Where the problem lies depends on what's actually slow. If you can read those Pervasive data files through other means and it is fast, and the problem only occurs when printing, then the problem may be in ACCPAC.
I have never used ACCPAC so I can't tell you how it does things.
- In terms of Pervasive, I would verify that MKDE tracing is disabled.
- Have you recently defragmented the disk where the data resides?
- Usually a rebuild of the Pervasive won't help unless there's corruption. Corruption usually doesn't cause slow performance though.
- What's different about the databases? Are the slow ones significantly bigger than the fast ones?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

Thank you for your prompt response.

The databases that are slow are bigger than the fast ones because the companies/projects are busier.

Can you give me a clue on how to disable the MKDE tracing?

The data is on the Server running Win 2000 Server, and we haven't run the defrag

I presume the other means of accessing the data you're referring to would be through the Control Centre by directly opening the tables. I will try to access it if thats what you mean and will let you know.

Rgds,
SeisaT

How much bigger are the files?

Can you give me a clue on how to disable the MKDE tracing?

Click to expand...

The Tracing is set at the server level. Here's a link to the docs with the setting: http://www.pervasive.com/library/docs/psql/900/advops/advops-06-4.html#wp68848.

I presume the other means of accessing the data you're referring to would be through the Control Centre by directly opening the tables. I will try to access it if thats what you mean and will let you know.

Click to expand...

Yes, the COntrol Center would be a good test.



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

SeisaT -

If a Pervasive Rebuild doesn't fix it, then it's time to move to MSSQL, Oracle, or DB2. Pervasive's relational engine just can't match the others.

Jay Converse
IT Director
Systemlink, Inc.

Jay,
Without knowing what's really happening, making a blanket statement like that isn't very helpful. A rebuild normally won't affect performance. Granted, if ACCPAC hasn't optimized their queries in the Pervasive engine then no amount of tweaking will help.

To really help in this case, there needs to be more information. Specifically, what's the actual query that's being performed. I know nothing about ACCPAC but it's always possible the problem is in the application not the database.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

Mirtheil,
I tend to agree with Jay here. In fact, I did convert the database in question to MS.SQL and the result is phenomenal. Although I am not a guru of any of the two databases (Pervasive.SWL & MS.SQL),I believe it's a Pervasive issue. One cannot afford to waste time (and time is money in this business) troubleshooting beyond the norm to get to the bottom of the matter. If MS.SQL has resolved this problem withouth any tweaking on the ACCPAC queries, then I also vote for MS.SQL.

But thanks all the same. Pervasive has to get its act together.

Rgds,
SeisaT

Mirtheil -

I've been using Pervasive since it was Btrieve on Netware 3, and Accpac since 1989, so my "blanket" statement comes from experience. Pervasive is great for inserting transactions, but lousy for big reports. I did a conversion a few weeks ago from Pervasive to Oracle, and on tables over 2 Gb, report time went from 45 minutes to 1 minute.

Jay Converse
IT Director
Systemlink, Inc.

Jay,
You've got me beat, I've only been developing and supporting Pervasive since 1995.
I'll admit that the SQL engine with Pervasive isn't the best but I know of a lot of companies that use it successfully and some have even replaced SQL Server.

Your comment about moving from PSQL to Oracle is interesting but I'm curious, how much more does Oracle (or SQL Server) cost to operate? Granted, the only queries I've seen on a 2 GB table that took over 45 minutes were one that weren't optimized. Even a simple "SELECT * FROM TABLE" shouldn't take that long with any database.

In your experience, how long do the ACCPAC queries take outside the application (through ODBC Test or PCC)? Are they the same speed? Have you done any Pervasive work outside the ACCPAC database structure?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

Definitely the cost is higher, but this was a government account where cost doesn't matter. Their setup had grown from 5 users to 50 users in the last 2 years, and Pervasive just couldn't handle the multi-million row tables and joins any more. Cross-tabs could take days to complete. We had even started dumping the data into MSAccess to get reports out.

Don't get me wrong, I like Pervasive, and it's my choice for small to medium-sized businesses. But it just doesn't scale up like the big boys.

Jay Converse
IT Director
Systemlink, Inc.

Greetings! I have been using Btrieve since 1988; and it’s a great database engine for data entry -- but painfully slow even when using Btrieve DDF and not ODBC.

I have always had to DTS the Pervasive tables to MS Access/MS SQL to facilitate Crystal Reporting. Trust me -- it’s a win/win for client reporting needs. The customer experiences not performance hits on their production database and the Crystal Reports(R) comes from a 'reporting' database with the utmost of efficiency.

Like Jay, my reports now run in minutes as opposed to several hours! Trust me -- it’s the best way especially if you have tens of tables joined.

Thanks


James Keep, PMP
Crystal Reports(tm) Certified Consultant 8.5 (CRCC)
Authorized Crystal Engineer (ACE)
CMRC
Crystal Decisions Business Partner
Montreal, Qc, Canada