> > 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. >
The Firebird optimizer seems to notice this and do something about it. Symbol is part of the PK and also has a separate index. > > > 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. OK, just a coincidence, then. :) Why does it say "COSTVALUE IS" but no real value? > > 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) ... ? No, I want the last change, price and trade_time. With MAX I will will get the last trade_time (since they are always increasing) but not the last change and price. > > > 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 SQL Studio. select last change into :temp from trade where symbol = 'OPC' gives me "Invalid cursor state". I guess SQL Studio cannot do this. > > > > 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 This gives me an error: "More than one result row not allowed". > 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? These are two different questions because they are two different problems. E.g when a client logs on, I want the last trade only for a limited set of stocks and when calculating todays winners or losers, I need the last trade for all stocks and then sort them by change (descening or ascending). :) Dag _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
