Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Derived table syntax question

Status
Not open for further replies.
SuperComputing

SuperComputing

IS-IT--Management
Oct 11, 2007
57
US
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:
What I am trying is this:

Code: 
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:

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

Any help on my syntax please...
 
Sort by date Sort by votes

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!

 
Upvote 0 Downvote
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:
Code: 
SELECT * FROM ItemsMonthly WHERE ItemsMonthly.Type='IT' AND ItemsMonthly.YearId='2008')
- What about
Code: 
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
 
Upvote 0 Downvote
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:
Code: 
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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote

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 :)
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote

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.
 
Upvote 0 Downvote

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!!!
 
Upvote 0 Downvote
Upvote 0 Downvote
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:
Code: 
CREATE INDEX idxIMTypeYear ON ItemsMontly (Type, YearId)


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
 
Upvote 0 Downvote

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???
 
 http://www.froggtoggs.com/temp/Items1.zip
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Ino Mart
  • Locked
  • Question
How to use a variable as table name in a procedure 1
Replies
2
Views
439
Ino Mart
Ino Mart
michellepace
  • Locked
  • Question
Basic SQL: Add column to select statement (ie value from another table)
Replies
1
Views
410
michellepace
michellepace
mSportsman
  • Locked
  • Question
C# Pervasive Data Provider Update command causing Syntax error, but not on development machine.
Replies
5
Views
405
mirtheil
mirtheil
bojimuncher
  • Locked
  • Question
Linking tables crashing Pervasive 1
Replies
7
Views
529
mirtheil
mirtheil
soocallmebrady
  • Locked
  • Question
Pervasive SQL Update with Joins - Error: Unknown table or correlation name
Replies
0
Views
250
soocallmebrady
soocallmebrady

Part and Inventory Search

Sponsor

Back
Top