I'm experiencing awfully slow performance when executing queries using SQL Data Manager or through ODBC.
I have tried directly on the server but with no noticeable improvement.
The query joins a couple of tables with group by and some summing, and I never even get a hint that the query is even executing, it's that slow!
Also I wonder why it doesn't even start to exploit the server's resources, it barely uses a few percent CPU and merely some 700MB memory (out of 2GB).
I did read about some limitations in memory handling in the 200i, but what about CPU?
When I import the tables to MS SQL Server (which I'm more used to using) I get the result in seconds without indexing.
With another query I cannot do this though, as it aborts the import after running for some 8 hours or so. I noticed it copying only a few hundred rows/second.
Is there some special relational engine or something I have to activate?
Does it require indexes to run joins?
Example of query I try to run (somewhat simplified):
SELECT O.OrderId, O.Row, SUM(O.Cost), SUM(T.Cost)
FROM OrderRow O INNER JOIN Transaction T
ON O.OrderId = T.OrderId AND O.Row = T.Row
WHERE O.OrderDate >= '20040401'
GROUP BY O.OrderId, O.Row
HAVING SUM(O.Cost) <> SUM(T.Cost)
First off, make sure you're using the latest Service Pack and Hot FIxes for the Pervasive engine.
Second, run a database consistency check (using the Check Database Wizard). Does it pass for the tables you're querying? If not, fix the DDFs or get new ones (if the data is from a third party application).
Third, use the Query Plan View (http://www.sys-con.com/tango/archives/0102/sneary/) to make sure the query is optimizing properly.
Certified Pervasive Developer
Certified Pervasive Technician
Custom Btrieve/VB development http://www.mirtheil.com
I do not answer questions by email. Please post on the forum.