Performance guidelines for Pervasive 1

[trutta1]

Does anyone know a good whitepaper on how to tune the Pervasive sql process ( Windows XP OS). I currently have a
default sized server. given the amount of data that I am starting to process I know I have to expand the internal buffers. what I do not know is how or what to monitor to determin the optimal requirements for my installation.

 

[mirtheil]

How much data are you talking about?
What version of PSQL are you using?
Are you using the Workgroup or Server engine?
What interface are you using (SQL or Btrieve)?
What are the specs of the machine where the data / engine resides?
How many users are connecting to the data?

All of these things affect performance.




Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[trutta1]

The Version is 9

I am using SQL interface. The OS is Windows XP. I am looking for documentation telling me what to look for where to look ( What internal Paramenters init parameter etc...) to tune the Pervasive processes. As there are no tools ( Like with Oracle, MS SQL, MYSQL) that will automate the tuning process or show the resource utilization of indivigual SQL statements.

 

[mirtheil]

Here's one white paper I found:

http://www.pervasive.com/documentat...Practices_for_Improving_Relational_Access.pdf

Are you having a specific problem? The main cause I see for SQL performance problems is bad DDFs. If the DDFs don't accurately and completely describe the data files, you will have performance problems and possibly data consistency problems.

I'd also still like to see the answer to the following questions. With the information, we might be able to give you information that isn't in the white paper.

How much data are you talking about?
What exact version of PSQL are you using (v9.0, v9.1, v9.5)?
Are you using the Workgroup or Server engine?
What are the hardware specs of the machine where the data / engine resides?
How many users are connecting to the data?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[trutta1]

How much data are you talking about? 3 gigs
What exact version of PSQL are you using (v9.0, v9.1, v9.5)? V9.5
Are you using the Workgroup or Server engine? Server engine
What are the hardware specs of the machine where the data / engine resides? 1.8ghz P III with 2 gigs of RAM
How many users are connecting to the data? 10

 

[mirtheil]

3 gigs of data isn't too much. What kind of performance are you seeing right now? What kind of queries are you issuing? What kind of hard disk subsystem are you using?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[trutta1]

You are correct 3 gigs is not very much data. but a simple "SELECT count(*) from Table_name" of a 270K row table can take up to 5 minutes to run. The table has 435 columns and some of the columns are varchar(65000).

The DB is a thrid party product. So I can not redesign the it (The real answer to this issue). So my the only option I am left with is trying to maximize the performance of the SQL engine.

The current configuration is the default. what I need to find is documentation on how to monitor the resource utilization of the sql engine then what init parameters to change.

 

[mirtheil]

A "select count(*) from table" will go through the entire table. I haven't tried it but what happens if you change the select to "select count(<unique indexed field>) from table"?

I don't think changing parameters will help with that kind of query because the engine is just going through the table. If you had restrictions, you might be able to speed things up by making sure the indexes are being used. The SQL engine doesn't do any of the disk i/o. That's all handled by the MKDE. You might be able to look in the Pervasive monitor to see what's happening but it's only going to give you basic information.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com