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!

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?

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!

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?

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...

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.

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

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.