We are currently undergoing an upgrade of the server software frome NT4 to Windows 2003 and Pervasive 2000i to Pervasive 9.5 and found that certain R & R reports appear to hang.

A series of 8 reports are run nightly under the old set up and they normally take between 4 to 6 hours to complete. However testing under the new set up, I have found that 7 of the reports run within in an hour, but the report looking for transaction between given dates does not stop, but appears to hang. The report has currently being run for the last 2 days to see if it will eventually return any info.

The database tables have all been rebuilt to the new version and they have all been re segmented. Joins have been checked and are aligned. If the join is changed from an "on Left" to an exact, the response is immediate, but this will not be correct as I need to have blank information coming in from the second table.

Hope you can assist.

Rgds
Manah

It shouldn't "hang". When it does "hang", are you able to make new connections using either Btrieve or ODBC (outside the R&R Report)? Is there still activity from the PSQL side? You can look at the activity in the Pervasive Monitor.

You might also try running a Database Consistency Check (http://cs.pervasive.com/files/folder...entry646.aspx). I have a feeling you'll find that there are inconsistencies that didn't affect PSQL 2000.

Thank you for that utility as when I ran it, I did find some inconsistencies. I have corrected the differences and kicked off the report again at about 11:00 this morning and has currently read 300 million records with no results showing, so it looks like that has not fixed this issue.

When I said that it hangs, I am more referring to the report writer R&R as I am able to run any pervasive SQL task while this report appears to be running. I can also access my databases through PCC and also through my database software.

Did you generate this SQL statement or is it generated by the report writer? if it's something you wrote, it might be worth optimizing it a bit to make sure that it's using Indexes. If the PSQL Monitor is showing that 300 million records are being read, it might be doing a table scan.

The SQL statement is developed by the report writer and I have checked the indexes to ensure that they are being utilised properly.

Can you explain why it would do a table scan.

Usually a table scan is caused when the engine can't use an index. How did you check to make sure the indexes are being used? Did you use the Query Plan Viewer?
Can you post the query? How many records are in each table in the query?

I've never heard of the Query Plan Viewer, however, I have since found a document on it and have followed the instructions to set it ie add to values in the Registry for QryPlan and QryPlanOutput, but it's not giving any output.

Is there something alse that needs to be set up other than the Registry?

As long as the query it going through ODBC, there's nothing that else that needs to be set. THe registry settings do need to be set at the Engine DSN level.