Derived table syntax question

[SuperComputing]

I am attempting to join 2 tables, but for optimization purposes, I want to "filter" one of the tables down.

I posted this originally in the standard SQL forum, you can read about it here:

http://www.tek-tips.com/viewthread.cfm?qid=1521676&page=1

What I am trying is this:

SELECT Items.ItemNumber, Items.Description1,  ItemsMonthly.QtySold_1
FROM Items
LEFT JOIN 
  (SELECT * FROM ItemsMonthly
   WHERE (ItemsMonthly.Type='IT' AND ItemsMonthly.YearId='2008')) b
ON Items.ItemNumber = b.ItemNumber
ORDER BY Items.ItemNumber

And what I am getting back is this:

ODBC Error: SQLSTATE = 37000, Native error code = 0 Syntax Error:
...
LEFT JOIN
  (SELECT <<???>> * FROM ItemsMonthly
   WHERE ...

Any help on my syntax please...

 

[SuperComputing]

In another thread I learned that subqueries are not supported in Pervasive V8.

Does anyone have an idea on how it would be possible to optimize my query without performing a subquery within the Join?

Thanks again!

 

[mirtheil]

A few questions:
- What version of PSQL are you using? In your other post, you say V8 but is it V8.1, v8.5, v8.6, ot v8.7?
- On the original query, what's "forever"? Have you let it run completely? If not, how long have you let it run?
- How many records and how quickly does the following query return:

SELECT * FROM ItemsMonthly WHERE ItemsMonthly.Type='IT' AND ItemsMonthly.YearId='2008')

- What about

SELECT * FROM ItemsMonthly WHERE ItemsMonthly.YearId='2008' AND ItemsMonthly.Type='IT')

I don't believe V8 supports the "derived table" syntax you're wanting to use.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[SuperComputing]

I'm pretty sure the version is 8.6. That is what the Monitor and Control center report.

"Forever" explained:

SELECT * FROM "ItemsMonthly"; 39K rows in 10 seconds

The 2 examples you had me try, 1300 rows in less than 2 seconds each.

The following query:

SELECT Items.ItemNumber, Items.Description1,  ItemsMonthly.QtySold_1
FROM Items 
LEFT JOIN ItemsMonthly 
ON Items.ItemNumber = ItemsMonthly.ItemNumber
AND (ItemsMonthly.Type='IT' AND ItemsMonthly.YearId='2008')
ORDER BY Items.ItemNumber

Takes 35 seconds to return the first 41 rows, and another 35 seconds every time I attempt to pull another 40 rows.

 

[mirtheil]

Are the times given when running the query in the Pervasive Control Center? If so, make sure the query is running "into Text mode" rather than the grid.
Is there an index on the Type and YEarID fields? You'll need to "Edit Table Design" (I think that's what it's called, I don't have a V8 box to check), to find the indexes on the table.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[SuperComputing]

Column 'ItemsMonthly.Type' is indexed, key0, ascending. YearID and ItemNumber are not indexed. Items.ItemNumber is indexed key0 also.

I ran the queries into text and the time for the Join query was approximately 40 mins. The other times were the same as before. So, forever, in my case is around 40 mins

 

[mirtheil]

Can you change the database? If so, add an index for both the ItemNumber field. This can be done with a CREATE INDEX statement.
The lack of an index on the ItemNumber is probably the main reason for the poor performance.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[SuperComputing]

I didn't know what I was viewing before, when I 'Edit Table Design' and go to the 'Indexes' tab, ItemNumber IS listed, for both tables, under key0. Sorry for the mis-information.

 

[SuperComputing]

OK, now that the weekend is over, does anyone have an idea how I can optimize this? Our inventory software will generate a text report with the same data in under a minute. We can shave 2 hours out of the process if I can get Crystal Reports or Excel to pull this.

THANKS!!!

 

[mirtheil]

I would suggest generating a Query Plan of the query to see what it's actually doing. Here's a link to the Query Plan setting:

http://www.pervasive.com/library/docs/psql/10/sqlref/sqlref-13-2.html

Post the file that's generated and we can take a look at it.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[SuperComputing]

I read your link and the additional info from here:

http://cs.pervasive.com/forums/p/4332/4332.aspx

The file is attached, and thanks again for addressing this.

 

[mirtheil]

Well looking at the Query Plan, it's not using an index on the ItemsMonthly table at all. That means it's scanning through all of the records in the ItemsMonthly table.
If you can modify the database, you can issue a CREATE INDEX statement like:

CREATE INDEX idxIMTypeYear ON ItemsMontly (Type, YearId)

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[SuperComputing]

The command worked and the indexes were added, however, performance was unchanged. I re-ran the Query Plan and attached it with a screenshot of my indexes. To me, the indexes look duplicated and that maybe I should change the existing ones???