Joinutility seperatorLogin utility separator Infobright.com
Loading
   
 
Is there any way to “concat” tables without creating a temporary table?
 
Chuu
Posted: 20 February 2013 03:34 PM   [ Ignore ]  
Newbie
Rank
Total Posts:  4
Joined  2013-02-06

I have a table with ~300M rows, and perform a simple select over it

SELECT serialno from me_0; 

This query is blazing fast, taking 0.156s with nothing cached.

So then I split the data up into five chunks with ~60M rows each, and perform the conceptually equivalent query:

SELECT serialno FROM(
    (SELECT serialno FROM me_0) UNION ALL
    (SELECT serialno FROM me_1) UNION ALL
    (SELECT serialno FROM me_2) UNION ALL
    (SELECT serialno FROM me_3) UNION ALL
    (SELECT serialno FROM me_4))x; 

This took about 80 seconds.  I was thinking that this could be optimized because of the columnar nature of IB (since conceptually you’re just appending columns to each other) but I can see from the execution log that this results in the creation of temporary tables.

My question is, for queries where the result set will be extremely large,  is there a way to make queries over unions of identical schema tables as fast as “one big table”, or will “one big table” always be faster?

Profile
 
Jakub Wroblewski
Posted: 22 February 2013 05:31 AM   [ Ignore ]   [ # 1 ]  
Super Duper Member
Avatar
RankRankRankRankRank
Total Posts:  822
Joined  2008-08-18

Hi,

Usually “one big table” will be faster.

UNION ALL as a data source may make sense if the intermediate results are small. E.g. all WHERE limiting conditions should be applied inside subqueries, as well as aggregations or LIMIT clauses. Sometimes it’s tricky.

Regards,

Signature 
Profile
 
   
 
 
‹‹ tpch1      I there a way I can use Select Roughly to determine which value has the most occurrences? ››