How to set Primary/Foreign keys & Referential Integrity

[TonyG]

Hello,

I'm working on an AccessXP program that uses externally linked tables thru ODBC from Pervasive.SQL 2000i. The access program runs on a workstation attached to a Netware 6 server. The data resides on the server. When it runs, it goes for over 24 hours and never finishes. We have to terminate it using alt-ctrl-del. The access program uses an SQL SELECT with 2 INNER JOINS a GROUP BY and 1 ORDER BY.

I don't know anything about this, but another programmer said that i should find out if the "Primary and Foreign Keys" are set and whether "Proper Referential Integrity" is being used. Also are appropriate indexes set for other fields that are searched or sorted on.

I tried running this same program on a standalone computer with the same set of tables(residing on the workstation) using the Pervasive workstation engine with SP3. It did the same thing. Here are the tables and their respective record counts:
Customers 27,069
Items 2,698
Order Headers 92,502
Order lines 159,366

I'm running it on a new DELL Dimension 2350, 2.0ghz, 256meg RAM, 18gig HDD

The program is pretty simple. It creates an export file and optional report using:
DoCmd.OpenReport strcReportName, acViewPreview
DoCmd.TransferText acExportDelim, , strcQueryName, strFile

Any help that anybody can provide would be greatly appreciated.

Thanks,
Tony

 

[mirtheil]

What's the actual SQL statement being used? Also, have you considered updating to SP4 on the Pervasive engine? Does the Database pass a Consistency check (right click on the database name in PCC and select Tasks then Check Database)? If not, post the fails for the four tables in your query here.



info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[TonyG]

Hi Mirtheil.

I really appreciate your help with this problem.

At the bottom is the SQL statement used.

I don't think SP4 is a possibility, since the system that this data comes from is an accounting package that we have no control over. I will ask them if we update our pervasive to SP4, will they still support it ?

I'm not sure what you mean by "post the fails for the four tables in your query here.". I ran the Check the Database for the four tables and they all failed.

Thanks,
Tony

***********************************************************
Const strcStub = "SELECT Trim(cust.nam) AS TrimNam, " & _
"Trim(cust.adrs_1) AS TrimAdrs_1, " & _
"Trim(cust.adrs_2) AS TrimAdrs_2, " & _
"Trim(cust.city) AS TrimCity, " & _
"Trim(cust.state) AS TrimState, " & _
"Trim(cust.zip_cod) AS TrimZip_cod, " & _
"Trim(cust.email_adrs) AS TrimEmail_adrs, " & _
"Trim(cust.phone_no_1) AS TrimPhone_no_1, " & _
"CLng(sa_lin.item_no,) AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no "
" WHERE (sa_hdr.post_dat Between "20031101" And "20031115&quot"
"GROUP BY Trim(cust.nam), " & _
"Trim(cust.adrs_1), " & _
"Trim(cust.adrs_2), " & _
"Trim(cust.city), " & _
"Trim(cust.state), " & _
"Trim(cust.zip_cod), " & _
"Trim(cust.email_adrs), " & _
"Trim(cust.phone_no_1), " & _
"CLng(sa_lin.item_no) " & _
"ORDER BY Trim(cust.nam);"
***********************************************************

 

[TonyG]

Sorry, but i didn't realise that there was more to the test.

===============================================
Database consistency test results.
Database Location: C:\CP755\GUYS
===============================================


===============================================
Database Check Statistics
===============================================
Pervasive.SQL Compatibility Mode = 7
Tables Checked = 4
Tables Passed = 0
Tables Failed = 4

Illegal Object Names = 0
Record Length Inconsistencies = 0
Variable Length Column Inconsistencies = 4
Data Type Inconsistencies = 0
Column Overlaps = 0
Full Path (instead of Relative) Table Locations = 0
Table Files Don't Exist = 4
Index Inconsistencies = 0
System Table Inconsistencies = 0
General Dictionary Inconsistencies = 12


===============================================
===============================================
Problems Detected with Database Definitions
===============================================
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name = CUST

DCM error 0x510.
This table data file doesn't exist.
Table data file = ''
Encountered an engine error while reading the table location.
Encountered an error while reading the columns information.
Column Definition Expected.
A table must have at least one column defined with
a fixed length data type.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name = ITEM

DCM error 0x510.
This table data file doesn't exist.
Table data file = ''
Encountered an engine error while reading the table location.
Encountered an error while reading the columns information.
Column Definition Expected.
A table must have at least one column defined with
a fixed length data type.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name = SA_HDR

DCM error 0x510.
This table data file doesn't exist.
Table data file = ''
Encountered an engine error while reading the table location.
Encountered an error while reading the columns information.
Column Definition Expected.
A table must have at least one column defined with
a fixed length data type.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name = SA_LIN

DCM error 0x510.
This table data file doesn't exist.
Table data file = ''
Encountered an engine error while reading the table location.
Encountered an error while reading the columns information.
Column Definition Expected.
A table must have at least one column defined with
a fixed length data type.


===============================================

 

[mirtheil]

Without even looking at the SQL statement, the fact the DDFs fail is possibly some of the problem. SP4 is ajust a patch to SP3, most vendors will support it and if they don't then I would question why they don't support it.
When you call to ask about SP4 support, I would also ask about updated DDFs that are ODBC compliant. The DDFs are so bad, it doesn't even look like you can see the tables properly. Can you try a SELECT * FROM "X$FILE" within the Pervasive Control Center? Does that display the table names and paths (in XF$LOC)? If so, what is the X$FLOC for X$FILE?


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[TonyG]

Hi Mirtheil.

I have a question out to our vendor about the SP4.

Not sure if i did this right. I right clicked on the database i'm using in the PCC selected Tasks/Execute SQL Query. In the query box i put your select statement and clicked execute query. Here are the results:
Xf$Id Xf$Name Xf$Loc Xf$Flags Xf$Reserved
----- -------------------- ---------------------------------------------------------------- -------- -----------
1 X$File file.ddf 16
2 X$Field field.ddf 16
3 X$Index index.ddf 16

(3 row(s) affected)

 

[TonyG]

I just got word that it is OK to use SP4 for Pervasive.

Will that speed things up for my Access program ?

Thanks,
Tony

 

[mirtheil]

SP4 might help, there were a number of fixes to the SQL engine. That being said, if the DDFs don't pass a consistency check, then the performance won't be as good as it could be.


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[TonyG]

Did my post showing the results of SELECT * FROM "X$FILE" tell you anything more ?

What can i tell the creators of these tables to do to make them pass the consistency check ?

Also, do you have any answers to my questions posted in the original message ?

The Access program does work fine if i use a limited amount of data(a couple hundred records in each table).

Thanks,
Tony

 

[mirtheil]

As far as what you can tell the creators, I would send them the output from the Check Database Wizard and if they have questions about it, they should call Pervasive. I've not seen the DCM error message you're seeing so I don't have any other suggestions.
The Select from X$FILE didn't show what I had hoped it would show which was an X$FILE.DDF in the XF$LOC.
Primary and Foreign keys and referential integrity, I don't believe would help you here. What I would suggest would be to verify indexes exist on the following fields:
cust.nbr
sa_hdr.cust_no
sa_hdr.ticket_no
sa_lin.hdr_ticket_no
sa_hdr.post_dat
Ideally there should be an index on each of those fields although, if there's an index where those fields are the first segment that would work too.
THe fact that it's working when there's a smaller data set tells me that it's building a temporary sort file. If you can limit or eliminate that, then your performance will be better.
Also, one thing to make sure of, not that I think it's a problem, is to make sure both ODBC tracing (in ODBC Administrator) and MKDE tracing (in the PCC Configuration/Debugging section) are both turned Off. If either or both are turned on, performance will suffer and your free disk space will very quickly disappear.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician