Results 1 to 8 of 8

Thread: Slow query

  1. #1
    Join Date
    Aug 2002
    Posts
    6

    Unanswered: Slow query

    I'm using MS Access to query a Pervasive ODBC DSN. Because Pervasive cannot handle nested query what I did was to create two queries, one to extract the data (unions are involved) and the other to group/sum the results. When I run the first query to extract the data, it took barely 2 minutes. But when I run the group/sum query, it takes a long time for the results to be returned (about 10 mins). Any idea how to improve the query performance?

    Thanks!

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    First off, what happens if you run the slow query in the PCC? Also are you using a SQL PAss Through Query? If not, try that. Do your Pervasive Tables pass a Database Consistency check? Are you fully patched on the version of Pervasive you're using?
    Mirtheil Software
    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.

  3. #3
    Join Date
    Aug 2002
    Posts
    6
    Yes, I am using a pass-through query. I've also tried running the query in PCC as well and it is also slow!

    I am currently using Pervasive SQL 2000i. I have not done any database consistency check nor patching. Should I do that? Will it help improve the query performance?

    Thanks!

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    If your database fails a consistency check, fixing those problems should give you some better performance. You ODBC driver (W3ODBCEI.DLL or W3ODBCCI.DLL) should be 7.94.xxx. What version do you have?
    Mirtheil Software
    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.

  5. #5
    Join Date
    Aug 2002
    Posts
    6
    Both of my W3ODBCES.DLL and W3ODBCCI.DLL are of version 7.94.251.2. I have also installed SP4 but the query still takes 10 minutes to complete...

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Have you run the database consistency check? Do the tables pass? If not, that might be part of the problem. Post any failures on the data tables and system tables.
    Mirtheil Software
    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.

  7. #7
    Join Date
    Aug 2002
    Posts
    6
    hi Mirtheil,

    I have ran the dbcc tests. Out of the 6 tables, 4 failed because they have the field named "DESC". I have changed them to something else and reran the query. It did not help in the performance too

  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    If the "Desc" was the only failure, it wouldn't affect performance. At this point, I would suggest using the Query Plan Viewer to see what indexes are being used. Here's a link to an article about it and how to use it:
    http://www.sys-con.com/tango/archive...ary/index.html

    If that doesn't help, I would suggest downloading the trial version of Pervasive.SQL V8 from Pervasive's site (http://www.pervasive.com/downloads and trying it to see if performance is better.
    Mirtheil Software
    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.

Posting Permissions

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