How to get the difference between two lists?

jbv jbv.silences at Club-Internet.fr
Mon Apr 4 17:10:40 EDT 2005


I must confess that I'm much more fluent in Transcript than in
SQL, so productivity-wise, I feel more confortable writing
complex transcript than complex SQL...
Nevertheless I'm working on a project involving a table with
23,000 entries (so far) and 35 fields in each (total weight 5Mb)
that makes heavy use of indexes. I have SQL requests involving
at least 5 to 7 fields, that extract 12 to 20 flds, and on which I
perform successive sorts.

for instance :
SELECT A, B, C, D, E, F, G FROM myTable WHERE A>10 AND B>A AND H>G
etc...

the selection can reach 1000 to 1500 lines

and then :
sort lines of myRequest ascending international by item 6 of each
sort lines of myRequest ascending numeric by item 5 of each
sort lines of myRequest ascending numeric by item 4 of each
sort lines of myRequest ascending international by item 2 of each
etc

and then further processing, like grouping lines in which item 1
contains similar values to be displayed in different tables in a
HTML page and / or comparing values of item 6 of each line with
a set of values already stored in a field of another table, etc.

then build (rather complex) HTML pages by reading template
txt files and replacing certain kew words with HTML code build
in the same script around data from the db...
---------------

I didn't perform any speed test, but the general feeling is that cgi
requests are performed significantly faster when most sorting /
processing is done in Transcript than in SQL...

I also have a client web app (made with Rev) that accesses the
same db / server, and the feeling is the same...

JB

> Do you mean that the transcript sorting code executes faster than the
> mySQL doing the sorting (with something like 'SELECT ID, FirstName,
> LastName FROM people ORDER BY LastName') or that it is just easier for
> you to handle everything in transcript rather than figuring out the SQL
> syntax to perform some of the sorts, joins, etc.?



More information about the use-livecode mailing list