×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Linking tables crashing Pervasive

thread318-1793069

Linking tables crashing Pervasive

Linking tables crashing Pervasive

(OP)
Hey all,

Not sure what I am doing wrong with this join for some reason I am unable to link 2 different tables that have a matching linked field.

I used the following queries to make sure that there were matching fields..

CODE --> sql

select * from table1 where cust = 27070

(returns 2738 rows - showing customer sales data)

CODE --> sql

select * from view1 where cust = 27070

(returns 1 row - showing additional customer information)

But once I try to create a view or even just combine the data via a select statement...

CODE --> SQL

select * from Table1 left outer join View1 on Table1.Cust = View1.Cust

Pervasive just shows me the progress bar until it finally crashes and I have to close the PCC.

What am I doing wrong?

Thanks,

Stan

RE: Linking tables crashing Pervasive

What version of Pervasive are you using? Have you tried using the table that View1 references directly bypassing the view? Can you reproduce the crash using DEMODATA? I tried something similar and it worked for me. I'm using PSQL v11.30. The query I tried is:

CODE --> SQL

create view view1 as select * from class where id > 100;
select * from class left join View1 on class.id = View1.id;

Mirtheil
http://www.mirtheil.com

RE: Linking tables crashing Pervasive

(OP)
Hey Mirtheil,

Thanks for the reply. I'm using Pervasive 11.3,

I tried bypassing the view and linking to the table directly but that didn't have any effect either. Other joins that I have done have worked but just not this one so I was thinking that maybe I was using the wrong join at first but the main table I am trying to link with is a table that has sales by line item and has a few years worth of sales...

I extracted 2018's sales (over 2 million records still) into a new view and tried to join with that with no success but extracting 2019's sales (just over 57000 records so far) worked... Are there any limits on these joins? is there a better way to do something like this with this much data, like combining the 2 tables into a table instead? I would also need it to update with new sales though so I'm not sure how that would work... The problem is I need to make a report that allows me to filter by the criteria that is not in the main table but in that other view (which came from 2 different tables)and that they need the sales by line item instead of by invoice so they can have everything sorted by category and part number.

Thanks again,

Stan

RE: Linking tables crashing Pervasive

I'm not aware of any limitations in the number of rows. That being said, the PCC is based on Java (Eclipse framework) and has it's own limitations. Have you tried in another tool that can connect to the PSQL engine? When the crash occurs, what do you have to do to recover? Restart the engine? Restart the PCC? Is there anything in the PVSW.log (I think it is in C:\ProgramData\Pervasive Software\PSQL\logs)?
Is the Cust field an index on the table (and table in the View)? How many records are you expecting to be returned? Do you have more records in Table1 or View1? If you limit the number of rows in the Join query, does it start working?

Mirtheil
http://www.mirtheil.com

RE: Linking tables crashing Pervasive

(OP)
Thanks again for the response,

I've tried using Crystal Reports, Crystal will bring up the preview for the first page fast once the databases are linked, but it will still take a while for it to get through all of the data and it takes around 10+ minutes to run the report once the report is grouped by part category and part number (I'm guessing because it has to get through all of the data to do it's sorting.)

There is nothing in that log file other than some Frozen and Thawed notifications at night, nothing when the tables are attempting to be compiled. Just the PCC needs to be restarted after it crashes, the engine is still running.

I've been trying to join the data using different tables and views to see if I can find one that is fast but haven't been able to find anything yet. I created 2 views that each has only the columns I need and each of them individually are quick to bring up in Pervasive but stall out until I have to restart PCC once I try to join and do a large query.

View 1 - v__salesWsmsa - Has sales by line item 557,189 rows
View 2 - v__inv_cats - Has inventory and inventory categories 23,124 rows

I need to get the two views joined by part number so I can add the item categories to the sales data but that is when the query is timing out, either in Pervasive or in Crystal

I tried to run the query using a single part and the query was able to complete (returning 1796 rows of data) but when I try to run it for everything then thats when it is unable to complete.

The query I tried is...

CODE --> SQL

select * from V__salesWsmsa 
left join V__inv_cats
on V__salesWsmsa.part = V__inv_cats.part


Thanks,

Stan





RE: Linking tables crashing Pervasive

You said the query is timing out. Is that the message you are getting? Or is it just taking a long time? In some cases you can adjust the timeout. I'm not sure how to do it in Crystal though.
What indexes are on the tables you are trying to join? That would be an issue. Have you used the Query Plan Viewer to see what the query is doing? Here's a link to an old article about Query Plan Viewer. Here's a link to the PSQL v13 documentation on QUery Plan Viewer. It should be similar enough to get you started with it.

Mirtheil
http://www.mirtheil.com

RE: Linking tables crashing Pervasive

(OP)
Hey again, think I got it figured out... I tried using another part number with less sales and a shorter time frame and I noticed that every line item was getting duplicated for every location we had due to my join statement. I changed it to

CODE --> SQL

select * from V__salesWsmsa 
left join V__inv_cats
on V__salesWsmsa.part = V__inv_cats.part and V__salesWsmsa.part.location = V__inv_cats.location

and there werent millions of additional rows being created causing my query to even stall out overnight.

Thanks again for everything!

Stan

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close