If you're getting these kind of issues for a single
channel:entries loop with no other "heavy" tags nested inside, and caching of any kind really isn't an option for you, then my first question would be, what are you doing in that template? If you're doing nothing funky (no embeds, complex relationships etc) then I'd start further down the stack, but without seeing some code it's pretty tough to tell where to start.
But looking generally, you've essentially got three possible areas of attack:
- Template optimisation
- Stack optimisation
- Bypassing channel entries
First though I'd get some lower level benchmark data - the core one being to see where you are becoming resource contrained - ie is it CPU or memory usage that's holding you back. Munin is you're friend.
I've found graphite to be a really useful way of finding bottlenecks in the template parsing process. If you haven't used it, it basically replaces the default template debugger with a graph, which really helps to visualise where things are getting held up.
If your benchmarking shows the issue is slow PHP execution during your entries loop, take a close look at any data manipulation / plugin tags being executed for each result. Ideally post some of your template code so we can have a look. If you're not doing anything crazy move on to stack optimisation instead.
If you're having to do any jumping through hoops to navigate the parse order take a look at the very wonderful Stash, which has completely changed the way many of us code EE templates. It sounds as if you're already using If/Else, have a look at Switchee as well.
Of both PHP & MySQL, but also your webserver. From on your benchmarking/debugger output you should be able to tell whether the issue is PHP execution time or MySQL query execution time. Use that to inform where to start.
With MySQL even basic tuning can produce serious performance gains. If you have access to a dba/sysadmin type set them loose, otherwise if you're happy in the shell take a crack at using MySQLTuner. Does your data need to be literally real time or could you cope with a few seconds of lag? If you're getting a large number of parallel requests for the same complex data then even very short lived caching in MySQL can help considerably.
Also take a look at what PHP handler you're using. If it's an option for you in terms of cost, you might also want to look at your hardware setup, I've switched a couple of high traffic EE sites to SSD drives and the speed gains have been unbelievable (especially when using Ngnix + FastCGI).
Bypassing channel entries
The reason I've put this last, is that I've gone down this route in the past, and while the performance gains can be considerable, the downside is a massive increase in the fragility of your code and an accompanying decrease in its readabilty. Do bear in mind that it also does not escape values on it's own, so can open SQL injection vulnerabilities if using dynamic/user inputted data.
To mitigate against these factors I'd always recommend using the Active Record plugin in preference to the native query module, as it escapes vars and is far more readable. You've still got the issue of hard coding your
channel_data column names into your template though, which is icky to say the least. Either comment the heck out of your code or consider using snippets/low varialbes to substitute in readable/easily updateable variable names instead (eg map
Alternatively do have a look at the Data API module Ben suggests, though it's pretty alpha at current.