Rev cgi & mySQL (again)

[Bill Marriott]

> I'm curious to know if anyone already faced the need to (almost)
> completely drop SQL in favor of Transcript for DB data search...

Without seeing the SELECT command and knowing how the database is set up, 
it's hard to know whether dropping MySQL is prudent or not.

It's not too difficult for technology A to outshine technology B when one 
knows A much more intimately than B. For example, someone who is an expert 
in SQL might be able to form a highly-optimized query that would blow the 
doors off a novice-authored Rev handler. You knew to use "repeat for each" 
to take advantage of that structure's performance advantage over "repeat 
with i = 1 to n." And how many times have we seen this list accelerate a 
routine by orders of magnitude?

I suspect there is a way to organize the database such that queries perform 
quite a bit faster. I know that on a project of mine that had a 3.5 GB data 
set with 4.6 million rows, using SQLite was markedly higher performance than 
Rev. (And I don't consider myself an expert on either!)

Perhaps the problem lies in the large amount of data you're extracting with 
your query? The complexity of the SELECT generally is not the bottleneck. 
But, the result essentially has to be transferred from the DB engine to Rev, 
whereas when you process within Rev, no handoff is required. Try to further 
refine your query with the LIMIT start,howmany clause so the minimal amount 
of data required for display is requested, and see how that works.

- Bill