Hi!
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)
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?)
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
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.
What is the best way to do this? Any ideas?
Cheers,
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
