Massive joins

[Ruslan Zasukhin]

On 16/11/07 4:46 PM, "viktoras didziulis" <viktoras at ekoinf.net> wrote:

Hi Victor,

> The query needed to left join 3 tables (with 10 000 (species), 30 000
> (donor regions) and 200 (region definitions) records.) on the forth with
> 40 000 records (species introduction events) and filter the result list
> of regions using constraints (WHERE...) on 1 or 20 fields. Using LEFT
> JOINs in this case was very slow - up to 10 minutes.

Well, it seems to me this is simple task, and small tables.
Valentina should resolve such joins in e.g. 0.01 - 0.1 sec or less.

> I rewrote joins (as
> described in "SQL" by Martin Gruber) in a form like this:
> 
> SELECT DISTINCT sir.country, sir.start_year, sir.end_year
> FROM species_in_region sir WHERE sir.idspecies IN(
> SELECT sp.idspecies FROM species sp WHERE sp.ordo IN(1,10,23,15)
> )
> AND sir.id_sp_region IN(
> SELECT da.id_sp_region FROM donor_areas da WHERE da.regionid
> IN(2,3,4,115,23,21)
> )
> AND /etc..../
> ORDER BY sir.end_year

 
> Implemented in this form it takes less than a second.

> SQLite is a very small database engine and likely (???) does not perform any
> query optimizations internally.

But this looks like nightmare!

Where from you get this "magic" numbers
        IN(1,10,23,15)

A) You did before this many other small queries to collect them?
B) And later you build this big SQL string?  (also time at last of end).

If yes, then you need also count time of (A) + (B) + (C)


> In comparison with MySQL, which does optimise joins, the same unoptimised
> query with joins in MySQL database server is executed just a little (~0.2
> sec.) slower than manually-optimised query in SQLite.

Well, IMHO its very hard in real life write for each join
such MANUAL optimizations :-)


> Viktoras
> 
> Ruslan Zasukhin wrote:
>> Can you explain this?
>> 
>>     JOIN on RAM db/tables SqlLite do so slowly ???
>>     why? What reason?

-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]