Dag �yvind Liodden wrote:
> We installed sapdb a week ago and have started porting one of our
> databases from Interbase to sapdb and are experincing some problems
> implementing a rather simple query.
>
> We have a table called STOCK and a table TRADE, with
> STOCK.SYMBOL as FK in
> TRADE. Often, selecting the information of the last trade is
> needed. For
> starters, I'll just try to get the last trade for each stock without
> joining in the STOCK table. Since subqueries aren't allowed after the
> SELECT keyword, this is how we're doing it now:
>
> select symbol, change, price, trade_time, volume from trade t1
> where trade_id = (select max(trade_id) from trade t2 where t2.symbol =
> t1.symbol)
Oops, this is not a simple subquery, but a correlated
subquery.
A select from t1 is done with all conditions applied which do
NOT
need a correlated subquery (in your case, no condition).
Sort the result according to the value needed in the
correlated subquery
(in your case: sort according to symbol).
For each different value of symbol found in t1 the subquery
is done,
max(trade_id) calculated and the the intermediate resultrows
from t1
which have the same symbol-value are checked and (if
fulfilling the
subquery-condition) put into the result which will be given
to the user.
That is hard work for the kernel. And if symbol in t2 is
neither an indexed column
nor the first keycolumn, for each different symbol found in
t1 the whole table t2
has to be scanned.
> However, the query is extremely slow (more the 3x slower than in
> Interbase). I'm not used to reading query plans in SapDB, but
> this is what
> EXPLAIN says (I apologize if this table is hard to read):
>
> OWNER TABLENAME COLUMN_OR_INDEX STRATEGY
> PAGECOUNT
> T1 TABLE SCAN
> 140
> INTERNAL TEMPORARY RESULT EQUAL CONDITION FOR KEY
> COLUMN 500
> TRADE_ID (USED KEY COLUMN)
> T2 NO STRATEGY NOW (ONLY
> AT EXECUTION TIME)140
> TROODON RESULT IS
> COPIED , COSTVALUE IS 1352
>
> The query runs in about 1,352 seconds (is this what the last
> pagecount means?)
No, costvalue does NOT mean seconds, it means: expected pages to
be read/written.
> I guess the ideal thing would be if the following had been
> possible (but
> it's not :):
>
> select symbol, last(change), last(price), last(trade_time),
> last(volume)
> from
> trade
> group by symbol
Do you mean
select symbol, MAX(change), MAX(price), MAX(trade_time) ... ?
> I've seen in the SQL reference (which I find a bit hard to read and
> navigate) that something like
>
> select last change into ? from trade where symbol = ?
>
> is possible, but I can't get it to work.
1. which client-tool (Perl/ODBC/C/...) do you use?
2. which versions of client and of kernel do you use?
3. but I can't get it to work: what does this mean?
which errors/results are returned?
4. !!!! with version 7.4 SELECT LAST/FIRST/...
will NOT exist any more ==> do not use it
in applications who are expected to live a little
bit longer
> What is the best way to do this? Any ideas?
one idea (don't ask if this is the best):
select symbol, change, price, trade_time, volume
from trade t1
where (trade_id,symbol) = (select max(trade_id),symbol
from trade t2
group by symbol)
The you do NOT have a correlation
Do you need the highest trade_id for ALL symbols at once
(ssems to be) or for exactly one symbol as in the other mail?
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
