Massive joins

viktoras didziulis viktoras at ekoinf.net
Fri Nov 16 16:28:34 EST 2007


Hi Ruslan,
> 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.
>   
V: Yes, I can imagine this and Valentina is in my considerations for a 
very special project which has already started... What keeps me from 
purchasing it now is actually the price tags in a list of all the very 
useful things and software I would need to purchase for my work :-).. 
Anyway, I guess for the end user there is no big difference whether a 
single query executes in 0.1, 0.3 or 3 secs. The vital part is to ensure 
that she/he is not forced to wait any longer then ~10 secs. And this is 
already achievable.
>   
>> I rewrote joins (as
>> described in "SQL" by Martin Gruber) in a form like this:
>>
>>     
>
>
> 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)
>   
> Well, IMHO its very hard in real life write for each join
> such MANUAL optimizations :-)
>
>   
V: True, but in this particular situation Rev script makes such a query 
from user's selections in lists. So A) is executed only once, when 
program initializes its list fields (where it takes the magic numbers 
from) from lookup tables, then B) takes the exactly same amount of time 
the end-user defines the selections and C) executes within 7 secs at max 
(when there are thousands of matching cases to be returned) but usually 
within 1-2 sec. This is quite acceptable.

Best wishes!
Viktoras



More information about the use-livecode mailing list