I am running pervasive v8.6 on Win2k Server. I am using an ASP web page to query the database, which admittedly is a large one, but an initial query is extremely slow. (> 30 sec.)

Once the initial query is made, if you do it again (i.e. reload the web page) it works very quickly.

I understand becuase the database is large it may take a while, but the same query runs immediately in the PCC.

Are there any settings I can adjust to make queries run faster? Would you expect it to work much faster through the PCC than through a web page in IIS?

I would expect a query to take close the same amount of time regardless of the application assuming each application is doing the same thing. For example in the PCC, if you run the query into the Grid it only retrieves the first 40 rows.
How long does the query take in the PCC if you execute it into "Text" mode?
Also, have you verified that the query is optimized and using indexes? I've seen that cause slow queries on the first run.
Also, what does your code look like? Is this a read only type query? If so, make sure you're using a Read Only, Forward Only cursor.

I see now in text mode it takes just as long. I didn't notice grid mode was only the first 40 rows.

It is a read-only query and using forward cursor.

The field is not an index, unfortunatley, becuase this database is part of a 4rd party software package. I tried to manually create an index for the field on a testing server, but it returns the error "page size cannot exceed record size."

I still notice the query works very fast after the initial run, even with different parameters.

Sorry, that error should be the opposite, "record size cannot exceed page size."

That's odd that a CREATE INDEX statement would change the record size.
Does your database pass a consistency check?
If there's not an index on the field, you're probably getting a temporary sort file and there's not a lot you can do speed up the query without adding an index.
What was your CREATE INDEX statement? Can you post a BUTIL -STAT report of the data file in question?

I tried to create the index in PCC using the edit table design panel. The database doesn't pass a consistency test, that and the stat report are below. Again, it's not my database, but one im stuck with from 3rd party software.

File Statistics for d:\db\tedata\d\twdata

File Version = Prior to version 6.00
Page Size = 1024
Page Preallocation = No
Key Only = No
Extended = No

Total Number of Records = 36221
Record Length = 21
Data Compression = Yes
Variable Records = Yes
Blank Truncation = Yes
Free Space Threshold = 20%
Log Key = 0
System Data = No

Total Number of Keys = 1
Total Number of Segments = 1

Key Position Type Null Values* ACS
Segment Length Flags Unique Values
0 1 1 15 String -- 36221 --

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

****************************

===============================================
Database consistency test results.
Test was run on: Wednesday, July 12, 2006 09:22:01 AM

DSN: TEDATA

Database name: TEDATA

Database Location: D:\DB\TEDATA
===============================================


===============================================
Database Check Statistics
===============================================
Pervasive.SQL Compatibility Mode = 8
Tables Checked = 1
Tables Passed = 0
Tables Failed = 1

Illegal Object Names = 20
Record Length Inconsistencies = 2
Variable Length Column Inconsistencies = 0
Data Type Inconsistencies = 0
Column Overlaps = 0
Full Path (instead of Relative) Table Locations = 0
Table Files Don't Exist = 0
Index Inconsistencies = 0
System Table Inconsistencies = 0
General Dictionary Inconsistencies = 0


===============================================
===============================================
Problems Detected with Database Definitions
===============================================
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name = TWDATA

illegal column name '1099Status'.
illegal column name '1001Months'.
illegal column name '1001PerMonth'.
illegal column name '1002Months'.
illegal column name '1002PerMonth'.
illegal column name '1003Months'.
illegal column name '1003PerMonth'.
illegal column name '1004Months'.
illegal column name '1004PerMonth'.
illegal column name '1005Months'.
illegal column name '1005PerMonth'.
illegal column name '1006Desc'.
illegal column name '1006Months'.
illegal column name '1006PerMonth'.
illegal column name '1007Desc'.
illegal column name '1007Months'.
illegal column name '1007PerMonth'.
illegal column name '1008Desc'.
illegal column name '1008Months'.
illegal column name '1008PerMonth'.
Fixed Record Length Mismatch.
Dictionary indicates length 5246
Data file indicates length 21.
Variable Data Type Column Mismatch.
The Data file indicates that a variable length column exists.
The Dictionary does not.


===============================================

Ouch.. Those are some pretty bad DDFs and I see why you are getting the error you get. It's the "Record length inconsistencies".
I doubt there's anything you can do to speed it up on the first run.
The problem is that your vendor has chosen to ignore relational database rules and has defined a fixed length structure to a variable length field. This is bad. This prevents indexes from being created on any of the fields in that variable portion (in this case after byte 21).
You need to go back to the vendor and tell them to make the database ODBC/SQL compliant.

Yeah this isn't the first time I've had problems like this; the vendor doesn't really support anything beyond their actual software program.

Anyway thank you for the information; it has been very helpful.

I'm trying a workaround...

Used a .bat file to schedule a sample query to run a simple SELECT query couple of minutes on those fields that I need. Seems to be working great so far. Our server doesn't get a tremendous amount of traffic, so it doesn't seem to be a problem so far.

Any unforseens problems I might run into with something like this?