×
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

Derived table syntax question

thread318-1521690

Derived table syntax question

Derived table syntax question

(OP)
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:

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

RE: Derived table syntax question

(OP)

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!

 

RE: Derived table syntax question

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
http://www.mirtheil.com

RE: Derived table syntax question

(OP)
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.

 

RE: Derived table syntax question

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

RE: Derived table syntax question

(OP)

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

RE: Derived table syntax question

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

RE: Derived table syntax question

(OP)

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.
 

RE: Derived table syntax question

(OP)

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

RE: Derived table syntax question

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
http://www.mirtheil.com

RE: Derived table syntax question

(OP)

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

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