Without lookign at a query plan, the first thing I would concentrate on is removing the two CASE statements from the sql.
Since it's a given you're going to be modifying the core code to improve performance, here's what I would suggest:
1)  Run an update on the IconFile column so that all references are the filename, and not the fileId, and then remove the first case statement.  I expect this is for backwards compatibility purposes,  rather than any other reason.  You might have to periodically run this update for new portals/pages with icons.  This will eliminate the expensive subquery on the Files table, if it is being used (your references might all be direct, so the query is not being run)  There will also be a cost in running a substring operation on the IconFile column, so eliminating this alone may well yield decent returns.

If that doesn't improve things, consider how to restructure the 'HasChildren' value.  You could do this by creating a new table which contained the 'hasChildren' column in it, and run an update based on a insert/update/delete trigger (which calculates both parent/chidl relationships on the change in a Tab record).  It would then be easy to join to the 'hasChildren' table and retrieve this value without the expensive subquery and this would greatly improve the speed of the query.

Bear in mind these are only guesses and you should have a test database that you can experiment on for changes.

Ok, I'm not sure what your trying to do, but the first thing I would say is: Find a way to get the Portal ID in there. 

When a client makes a request for a page from a portal, why would you pull back all the tabs for ALL the portals? You should only concern yourself with the tabs on the portal currently being requested. 

On my database: 384 portals, 94k tabs. That query pulls back a HUGE dataset, and even if the query itself didn't take a huge amount of time to run, it's going to be sending a big chunk of data to the webserver which takes network AND memory resources.  I already found one bug (which I fixed in my instance, and according to the core team, is no longer an issue) where the code wasn't using the portal id to restrict the recordset, resulting in the webserver being unusable. I traced the code down and fixed it, and the webserver went from over 1 minute (if ever) on initial load) to like 15 seconds. 

Hope this helps.