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