how can you control the SQL in a View directly? or code a View "from scratch"?

I wonder if someone can enlighten me on this.

1) is there ANY hook in Views 2.0 that enables you to alter the SQL query before it is executed? Note that this SQL query cannot be saved as a fixed string anywhere, as I have some exposed filters that can be changed by the user.

2) what methodology would one use instead of Views, if you wanted an exposed filter and pagination displaying a view of resulting nodes? would you have to:

a) define a form for your exposed filters, and all the necessary form validation and submit handling
b) write all the code to translate the filter values into an SQL query
c) and after executing the query... then what? is there some way to get a Views-like display of the output of a query (assuming that the field being selected was the node id)? or would I have to code that from scratch too?

Here's my problem. We have a View with three exposed filters (all of them optional):

- one of them just searching for a word in the node body,

- one of them matching on multivalued CCK field with an "allowed values" list (where all values checked off in the exposed filter must be set for the record to match, so ANDing the values, not ORing)

- and one of them a "taxonomy depth search", implemented with this code patch here: http://drupal.org/files/issues/taxonomy_filter_with_depth.patch

This View was working fine in develoment, but now that it's in production, with hundreds of users registered, we are getting cases where the queries from this view are just too complex and are bogging down the MySQL server, and the web hosting company is not happy.

I looked at some of these queries and it seems that as far as the multivalued CCK field goes, having too many possible matches checked off will increase the number of join instances in the query, which is definitely part of our problem.

I am also aware that the taxonomy depth search may be the culprit, and that warnings were attached to that patch that indicated that it might not always be efficient. Well, our taxonomy is pretty small (about 100 terms split over four levels of nesting) and the depth search is a MUST-HAVE requirement, so if I can prove to myself that this is the root of our problem, I could use a temp table approach that would help me get the descendants of the term selected in the filter and turn them into possible match values... but again, I still need the ability to hand-tool my own SQL to do this.

Any help on this topic would be much appreciated, we are in a bit of a bind here!

cheers
Peter 'Fish' Fisera
GoatVirus Technologies
http://goatvirus.com/resume