Having difficulty running a query through Pervasive 9.5...script always times out and CPU usage on server goes to 100% (99 for ntdbsmgr.exe) until I stop the services.

Trying to pull several fields from a large table (about 168,000 rows) through a web script.

When I run the querys through PCC individually they all complete very quickly, a couple seconds. The total number of separate queries from the web script are about 95, so it definitely should not take that long.

I don't believe it's a problem with the script as there were no changes and it seemed to be working fine until recently.

I also notice that ntdbmgr.exe locks the data file for this table. Is that normal?

I am by no means a pervasive expert, so any suggestions as to how to begin to investigate the issue here would be helpful.

I'm confused. Are you running one query or 95? A couple of things about running queries through the PCC. First, PCC disables the timeout. Second, running queries through the PCC into Grid mode (default) will only return the first few records.

If you SQL query is causing the engine to go to 100% CPU usage, you're probably not using an index.

Is your web script disabling the ODBC time out?

Currently, it is an ASP script that loops through about 95 file numbers that it then separately queries from the pervasive database. It is not timing out in PCC, but the ASP script is timing out and seemingly runs forever if I disable the ODBC timeout.

I don't really have control over the database structure itself; it is part of propriotary software we are using. When I try to look at the properties of that particular table it gives me "unknown error -19" It says there are 4 indexes, but nothing shows up on the indexes tab.

Can you narrow down which one of the statements is actually sending the CPU usage to 100%?
Once you've narrowed them down, post them here, and run them in the PCC into "Text" mode. You can also turn on the Query Plan to see whether the queries are using indexes.

Any statement on that particular table seems to make it go to 100%. For example,

SELECT EVV_ORDER_NUMBER, EVV_CODE FROM EVV WHERE EVV_COMPLETED_DATE = '20090706'

I also notice ntdbsmgr.exe locks the actual data file after a query is run, is that normal?

My suspicion is the files are corrupt, but not sure if it would be the data files themselves or the DDFs.

Corrupt files usually return status 2 errors. Does the EVV table have an index defined on the EVV_COMPLETED_DATE field?
My guess is that it doesn't. Mot of the time when I see 100% CPU usage from an SQL statement, it's caused by a lack of optimization either because there isn't an index or the index is not usable.

When I try to select the properties for that table it gives me: "unknown error : -19" so I can't see any indexes in the properties window. Is there another way of finding out?

P.S. Just tried the same query again, this time after running for about a min returned:
[LNA][Pervasive][ODBC Engine Interface][Data Record Manager]The application encountered an I/O error(Btrieve Error 2)

Sounds like you've got two new problems. The status 2 is indicative of corruption. You should rebuild the file using the Rebuild Utility or the Maintenance Utility. The "-19" is indicative of bad DDFs. If these are from a third party application, you should contact the vendor and get updated DDFs that are ODBC compliant. You might be able to use the psp_indexes (all system stored procedures are documented at Pervasive PSQL System Stored Procedures) system stored procedure to get a list of indexes.

Will work on getting updated DDFs, meanwhile...When I tried to rebuild that data file, I got the following error log:


The rebuild operation start time is 07/09/09 15:09:15.
rbldcli -c -s -f95 D:\inetpub\db\TEDATA\d\EVV

REBUILD-20: The utility is processing D:\inetpub\db\TEDATA\d\EVV.
REBUILD-68: Status code 2 was returned while copying records from the following file:
D:\inetpub\db\TEDATA\d\EVV.
REBUILD rejected a total of 57508 records.
REBUILD copied a total of 110320 records.
REBUILD rebuilt a total of 0 indexes.
The rebuild operation end time is 07/09/09 15:11:14.

Sounds like the corruption is fairly significant. You might try BUTIL like:
BUTIL -CLONE <newfile> <oldfile>
and then
BUTIL -COPY <oldfile> <newfile>
If that doesn't work, then you can take the cloned file and use BUTIL -RECOVER to save an unformatted file and BUTIL -LOAD to load that unformatted file. RECOVER/LOAD does the same thing as COPY but in two steps and doesn't use any indexes.

Tried both COPY AND RECOVER on the existing data file (EVV)...both times got to about 112,800 records and then:

--------------
BUTIL-57: BUTIL has copied 112800 records so far.
BUTIL-100: MicroKernel error = 2. The application encountered an I/O error.

BUTIL-9: The command did not complete due to an unrecoverable error.
--------------
BUTIL-71: BUTIL has recovered 112800 records so far.

BUTIL-14: The file that caused the error is d:\inetpub\db\TEDATA\d\EVV.
BUTIL-100: MicroKernel error = 2. The application encountered an I/O error.

BUTIL-9: The command did not complete due to an unrecoverable error.

How many records are there in the file? You can see this in the BUTIL -STAT.

File Statistics for d:\inetpub\db\TEDATA\d\EVV

File Version = 8.00
Page Size = 1024
Page Preallocation = No
Key Only = No
Extended = No

Total Number of Records = 167833
Record Length = 152
Record Compression = No
Page Compression = No
Variable Records = No

Available Linked Duplicate Keys = 0
Balanced Key = No
Log Key = 0
System Data = No
Total Number of Keys = 4
Total Number of Segments = 6

Key Position Type Null Values* ACS
Segment Length Flags Unique Values
0 1 1 4 AutoInc -- 167833 --
1 1 5 18 String MD -- 22818 --
2 1 23 7 String MD -- 8571 --
2 2 30 4 Unsigned MD -- 8571 --
2 3 34 1 String MD -- 8571 --
3 1 35 13 String MD -- 37 --

Legend:
< = Descending Order
D = Duplicates Allowed
I = Case Insensitive
M = Modifiable
R = Repeat Duplicate
A = Any Segment (Manual)
L = All Segments (Null)
* = The values in this column are hexadecimal.
?? = Unknown
-- = Not Specified

The command completed successfully.

If you need a file repair you can try Nelson Software Solutions DDF and Data Editor for Pervasive Btrieve. There is no cost if the file cannot be repaired.

Gil