I Hate Pervasive!

[smeyer56]

In SQL ServerI can create Select Statements that return results in 5-10 seconds. No matter what I do in PervasiveI cannot get any to return in under 5 minutes unless it is the most basic select.

SELECT "Z_KEY_0_COMP_1", "DAT"
FROM "SA_HDR"
WHERE "DAT" BETWEEN '20021231' and '20031231'
ORDER BY "Z_KEY_0_COMP_1" DESC

This one takes 5 minutes to return 80000 records from 200000 records

I try to create nested statement and I am still waiting 1 hour later.

Is it like this for everybody?
Am I expecting too much?

 

[TomKane]

It's a bit like that for us as well.

In our situation we have a legacy app that uses Btrieve and we use the SQL stuff to get at it rather than using the transactional stuff. The reason being that just about anyone in our department can use SQL but the transactional thing is a bit more "technical".

If it's speed you're after then maybe you should go the transactional route. Mirtheil posted an excellent link to the pervasive web site showing an example of this in another thread on this forum or forum321.

The things that might be slowing you down are the ORDER BY and the BETWEEN - if DAT isn't a key value then maybe you should use programming code to filter out the records that you don't want. Maybe you could look at your DDFs - maybe they could be fine tuned re indices.

I'm told that P.SQL8 is the quickest yet - maybe an upgrade might help?

 

[smeyer56]

Won't upgrade to Vers. 8 because application's newest version is being built on SQL Server and I don't think we will be able to use Pervasive 5 years from now.

If I change the indices doesn't that change the way the application looks for the data? I can't have that.

Could you explain how to "programming code to filter out the records", I am not certain I understand.

I am looking for the link Mirtheil left now and will check into that.

 

[TomKane]

The indices thing - I'm wondering if the DDF is defined in a way that it know where the indices are on the table. It doesn't realy matter if DAT is not an indexed field.

Programming code to filter out the records. What I meant there was that if it's a VB program or something that you're using the SQL statement in then it might be better if you leave out the WHERE clause and let the program conditionally ignore records that you don't need.

Do a search on

http://www.pervasive.com

for Btrieve API and see if you have any luck there.

Hope this helps,

 

[mirtheil]

TomKane is right. If the DDFs are not defining the indexes properly, performance will drop significantly.
Here's a couple of other things to check:
1. Tracing. Make sure MKDE tracing (in the PCC or Pervasive Setup depending on the version) and ODBC Tracing (in the ODBC Administrator) are turned OFF. I've seen this cause all kinds of performance problems.
2. Run a Database Consistency check. Make sure the DDFs and data files are in sync with each other, especially in terms of the indexes.
3. Take a look at the Query Plan for the query. This is dependant on the version of PSQL you're using. With v7, it's called PlanPath. With 2000 and V8, it's the Query Plan Viewer (

http://www.sys-con.com/tango/archives/0102/sneary/index.html).

Make sure you're using the latest versions of the DDFs from the vendor of the application. I've seen where users had old DDFs and as soon as they got the new ones, everything worked fine.
What version of the Pervasive engine are you using? If you're using 2000, you should at least be at SP4 plus the HotFixes. V8 would be ideal because it's the current version. If you're running anything older than 2000, then I'm not suprised that it's running slow. I saw a post on a different site that said their processing went from 4 hours with v7 to under an hour with V8.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[TomKane]

There is another solution that I've come across - it's a "best of both worlds" solution. Transoft have a driver that lets you query your Btrieve/Pervasive files with SQL but uses the transactional engine to get the data. We've looked at it but haven't used it in production (up to now). Not sure of the cost but the speed is excellent (provided your query is using a key field).