Linking tables crashing Pervasive 1

[bojimuncher]

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

select * from table1 where cust = 27070

(returns 2738 rows - showing customer sales data)

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

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

 

[mirtheil]

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:

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

 

[bojimuncher]

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

 

[mirtheil]

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

 

[bojimuncher]

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

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

Thanks,

Stan

 

[mirtheil]

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

 

[bojimuncher]

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

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

 

[mirtheil]

Glad you got it figured out.

Mirtheil

http://www.mirtheil.com