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: h ttp://www. tek-tips.c om/viewthr ead.cfm?qi d=1521676& amp;page=1
What I am trying is this:
And what I am getting back is this:
Any help on my syntax please...
I posted this originally in the standard SQL forum, you can read about it here: h
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
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 ...
...
LEFT JOIN
(SELECT <<???>> * FROM ItemsMonthly
WHERE ...
Any help on my syntax please...
RE: Derived table syntax question
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
- 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
CODE
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
"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
FROM Items
LEFT JOIN ItemsMonthly
ON Items.ItemNumber = ItemsMonthly.ItemNumber
AND (ItemsMonthly.Type='IT' AND ItemsMonthly.YearId='2008')
ORDER BY Items.ItemNumber
RE: Derived table syntax question
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
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
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
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
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
http://ww
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
RE: Derived table syntax question
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.
RE: Derived table syntax question
If you can modify the database, you can issue a CREATE INDEX statement like:
CODE
Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com
RE: Derived table syntax question
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???