Thanks, Elke I had the feeling that I'm missing something simple. This approach works good too.
Alexei. --- "Zabach, Elke" <[EMAIL PROTECTED]> wrote: > Alexei Novakov wrote: > > > > Hi list. > > > > This is the problem formulation: > > > > a) I have some query to select a number of ids, like this: > > > > select id_1_tbl1.id as id_1 > > from tbl1 id_1_tbl1, tbl2 id_1_tbl2,... > > where <id_1 condition> > > > > Let's say this query runs 10 sec and returns 1000 records in the > result > > set. > > > > b) Now I have another query for different set if ids: > > > > select id_2_tbl1.id as id_2 > > from tbl1 id_2_tbl1, tbl2 id_2_tbl2,... > > where <id_2 condition> > > > > This query runs 100 millisec and returns 25 ids. > > > > c) And now I want to get multiplication of id_1 and id_2 (expecting in > the > > result set 1000*25 id > > pairs). I can do streightforward as follows: > > > > select id_1_tbl1.id as id_1, id_2_tbl1 as id_2 > > from tbl1 id_1_tbl1, tbl2 id_1_tbl2,... > > tbl1 id_2_tbl1, tbl2 id_2_tbl2,... > > where <id_1 condition> > > and <id_2 condition> > > > > It is important to note here that there is no connection in this query > > between id_1 and id_2 in > > the conditions - they are completely independent. This query returns > > expected result set but runs > > 240 sec. From time analysis I can assume that condition for id_1 is > > evaluated 25 times (result set > > for id_2 condition). This is very slow. Interesting that if I do have > some > > connecting condition > > between id_1 condition and id_2 condition the resulting query will be > > executed in same 10 sec, > > which is fast. > > > > For now the only workaround I've found so far is following: > > 1) Create temporary table with id_1 result set: > > > > create table TEMP.tmp01 > > as > > select id_1_tbl1.id as id_1 > > from tbl1 id_1_tbl1, tbl2 id_1_tbl2,... > > where <id_1 condition> > > > > 2) Create another temporary table for id_2: > > > > create table TEMP.tmp02 > > as > > select id_2_tbl1.id as id_2 > > from tbl1 id_2_tbl1, tbl2 id_2_tbl2,... > > where <id_2 condition> > > > > 3) Run multiplication query from temporary tables: > > > > select id_1, id_2 > > from TEMP.tmp01, TEMP.tmp02 > > > > 4) Drop temporary tables: > > > > drop table TEMP.tmp01 > > drop table TEMP.tmp02 > > > > This whole scenario runs about 10-11 sec which is good, but the > solution > > itself looks pretty ugly. > > The ideal solution would be if MaxDB could execute the initial > > multiplication query with > > reasonable performance, but I didn't succeed on this path. Does anyone > > have any ideas how it can > > be done in a less-nasty manner? > > > > Thanks in advance. > > Alexei Novakov. > > > > Instead of using TEMP tables, you could use from-selects, which will do > similar things, but implicitly: > select id_1, id_2 > from > ( select id_1_tbl1.id as id_1 > from tbl1 id_1_tbl1, tbl2 id_1_tbl2,... > where <id_1 condition> > ) fromtab1, > ( select id_2_tbl1.id as id_2 > from tbl1 id_2_tbl1, tbl2 id_2_tbl2,... > where <id_2 condition> > ) fromtab2 > > Elke > SAP Labs Berlin > > > -- > > MaxDB Discussion Mailing List > > For list archives: http://lists.mysql.com/maxdb > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
