Poor table performance

[robert04]

Hello folks,

I'm fairly new to Pervasive (coming from SQL Server background). Is there any way to tune the performance of a table? I have a table that has a relatively low number of records (~1000), and I'm doing some simple selects against it. The queries always take a long time to execute. Other tables in the system seem to respond pretty good, depending on their size, but this one seems to be excessively slow.

Any ideas on where I might start looking for the problem?

Thanks

 

[robert04]

Oh, by the way, I'm using Pervasive SQL 2000 and it's running on NT.

Thanks.

 

[mirtheil]

Are you running Pervasive.SQL 2000i SP4? If not, then that's the first thing I would recommend. Secondly, what's the SQL query that's slow? Also, how long is "excessively slow" (how long in minutes does it take)?
Does the database pass a database consistency check (in the PCC)? You might look at the Query Plan Viewer to see the query plan to see if it's using any indexes.

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

 

[robert04]

Not too sure which service pack I'm running. Like I said, I'm pretty new to Pervasive. Where would I look for such information?

As far as the query is concerned, it's basically a "SELECT CALLID FROM DISPATCH". The table has about a thousand records in it. The query is taking 15-20 seconds to return. Granted, it's not "minutes", but I would think such a small table should return almost instantly.

The database does pass a consistency check.

So, I'm still wondering why.

 

[mirtheil]

The version can be seen in the W3ODBCEI.DLL and the W3ODBCCI.DLL. If it's 7.90.230.36, it's SP3, if it's 7.94.251.xx, it's SP4. If it's 7.82, it's SP2a and if it's 7.5x, it's SP1 or the original release. What tool are you using to issue the query? Where's the data? Is the data on the same machine as the engine? Are you accessing the data across the network? If so, what protocol (SPX or TCP/IP) are you using? Can you tell us a little more about the setup?

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

 

[robert04]

The version is 7.82.198.001, so I guess it's SP2a. I know my first step is going to be upgrading to SP4 at least, and looking into getting V8. Probably that's the best thing to do before digging into this problem too deeply, unless there is some quick tweaks I can do in the interim.

I've used a few methods to issue the SQL statement, such as Query Analyzer, ADO call from VB, and ADO call from ASP.

The data and the engine reside on the same machine. The response time is about the same if I execute the query at the server itself, or run it across the network via VB.

We have a single network segment, and we access using TCP.