World: r3wp

my queries are currently taking 2 seconds and I need subsecond performance

You might look at EXTRACT or MAP-EACH then. For simple queries with 
no joins, SQL is overrated.

If you give us your actual script we can find some improvment rooms. 
Despite beeing lazy, we are not bad at such game.

(if it's not a bloated one)

I'm hacking into FreeDiams .. a free French drug interaction database 
:)

Don't trust French commercials

It's a GPL app to do drug prescribing and drug interactions .. I 
am just using their database

Once the database is in memory you don't necessarily need to do set 
operations (SQL select). You can do iterative operations much more 
quickly.

source is here https://fd.cloud-ehr.net/drugreactions.txt

huge !

/me dazzled !

It looks like you are using the iterative approach anyways, on the 
results of the SQL queries. If you cache the data in RAM you can 
do iterative stuff with FIND and loops, or even maps in R3 if you 
want real speed.

This is a RSP app .. we've already had the discussion why Cheyenne 
is not ported to R3 :)

Hashes then :)

huge?  Only 250 lines where a line is 2-3 rebol words :)

It's late here, and if see more than a dozen of lines, I could die

there is probably a better method than the one I use to generate 
the permutations

sorry , combinations

we have something like this

drug1 [ id1 id2 id3 .. ] drug2 [ id1 id5 id6 .. ] drug3 [ id4 id7 
.. ]

I have to generate all the possible combinations of each drug id 
with all the others but not with those in the same set

so at present I think I generate all possible combinations and check 
to see if they're in the same set and if not do the sql query

that's my contribution to lazy programming :)

Would you kind enough and give us the expecting output from the input. 
I can't read all your lines currently.

drug1 [ id1 id2 id3 .. ] drug2 [ id1 id5 id6 .. ] drug3 [ id4 id7 
.. ]
-->

combinations = pairs

If you use these 
 855348
211885
541713
849339
108911

as input to this page https://fd.cloud-ehr.net/drugrx.rsp

you'll see the output of the original script which produces html 
and not json

Well I was doing reference to this input ->

drug1 [ id1 id2 id3 .. ] drug2 [ id1 id5 id6 .. ] drug3 [ id4 id7 
.. ]

["IBUPROFEN" [1113 2214 3359 3441 200017 200085] "HEPARIN" [706 1397 
5027 200078 200081 200082 200085] "ABCIXIMAB" [730 200018] "WARFARIN" 
[698 200023] "ASPIRIN" [40 724 1707 1733 3411 3688 3689 3704 3709 
4339 4876 200018]]

interaction found on ATC pair 40 200017 Knowledge IDs	: 6 7 289 

interaction found on ATC pair 40 200023 Knowledge IDs	: 3 4 5 164 
interaction found on ATC pair 40 200081 Knowledge IDs	: 15 1159 
interaction found on ATC pair 40 200082 Knowledge IDs	: 16 1164 
interaction found on ATC pair 724 200017 Knowledge IDs	: 6 7 289 
....

I suspect optimizing this part of the code is only going to save 
ms

the real cost is the sql queries

And what about purchasing a faster computer ? 

(I'm in a deep shit right know, I don't understand what he's talking 
about)

There's a big difference in price between the various CPU configurations 
on amazon

We have find/only for this

a: [ [ 1 2 ] [ 3 4 ] [ 5 6 ]]
find/only a [ 3 4 ]


but what if we wanted to find [ 4 3 ] where we want [ 3 4 ] to be 
equal [ 4 3 ] ie. same set?

Store sorted in a and sort the query before find.

seems expensive to sort each element first

Cheaper than sorting it during each find.

intersect I guess

Sort the elements on insert and keep them sorted. Then sort the value 
you are comparing to before looking for it.

Thanks, Brian, for reformulating what I wrote above.

Ah yes :)

Interesting R3 "bug" btw:
>> intersect [[1 2] [3 4] [5 6]] [[4 3]]
** Script error: block! type is not allowed here

Works fine in R2.

There is a ticket for that.

very odd ... I changed to using blocks instead of sql, and the query 
increased from 1.7 s to 7 seconds!

I guess the sql indices are just faster than using a brute force 
search thru blocks

does a REBOL tar dearchiver exist? I can find an archiver in rebol.org, 
but not one the other way around.

Graham, you can do your own indices using hash!, but that might be 
pushing your complexity budget. Try it and see.

Henrik, just run the program backwards.
...... sorry.