Rev cgi & mySQL (again)

[Ruslan Zasukhin]

On 1/2/09 2:49 PM, "jbv" <jbv.silences at club-internet.fr> wrote:

> Hi there
> 
> I have a mySQL table with about 40000 entries. I also have a Rev cgi 2.5
> 
> script that sends (very) complex SELECT requests; in which the "WHERE" part
> can feature as much as 50 nested booleans winth "and", "or", "binary" etc
> 
> For quite some time I realized that requests to mySQL slow down my scripts in
> a terrible way, both because of the complexity of the requests, and also
> because of (imho) a buffer size problem when the amount of data returned is
> huge...
> 
> I've tried several tricks to improve the speed of my scripts (like "one big
> request" vs "several small requests in a loop"), but with no luck...
> 
> finally, I tried the following : I dumped the content of myTable as a text
> file, opened it in the Rev script, and did the selection of records inside a
> "repeat for each line" loop. And to my surprise, the speed of the script
> improved to almost 40% (which is a lot for a script that used to take 5 to 6
> sec, and now takes 3.5 to 4 sec)...
> 
> Well, I don't know what conclusion to draw from this... Besides the obvious
> superiority of Transcript...

I doubt about this conclusion. :)

* mySQL server is on localhost?
     even if yes -- it still have overhead of packing data to sockets.
     if no -- then this is absolutely not comparable tasks.


> I guess some wise and experienced guys will tell
> me that for sophisticated DB processing I should have switched to a better
> product (like Valentina) long time ago...
> 
> But nevertheless I'm curious to know if anyone already faced the need to
> (almost) completely drop SQL in favor of Transcript for DB data search...

I wonder how this can be considered at all?

If you need only load this 40K records and use them in READ only way, then
may be yes.

But if your app also update data, and if app now or in the future may
require multi-user way then DBMS must be used.


50 nested bools query really looks complex...
Should be checked ...



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]