Results 1 to 3 of 3

Thread: Slow queries

  1. #1
    Join Date
    Oct 2003

    Question Unanswered: Slow queries

    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)
    Thanks for any help!


  2. #2
    Join Date
    Dec 2001
    Provided Answers: 6
    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 ( to make sure the query is optimizing properly.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Oct 2003


    It's SP4, which I believe is the latest for 2000i. I think all hotfixes are installed, but am not entirely sure.

    The Check utility returns some errors:
    Index ACS Flag Inconsistency.
    The dictionary index 0 seg 0 indicates Yes
    Data file index 0 seg 0 indicates No
    You got any idea what these means?
    Recreating the DDFs from the 3rd party app does not amend this.

    However, I have noticed that Pervasives query optimizer does not work well with ANSI joins, and it first joins all rows before filtering.
    By using old fashioned joins performance is much better!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts