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]
