I have an issue where I'm trying to sort a massive result set and it's very slow even though everything is indexed. The issue is that the sort is based on a value that a foreign key points to and it looks like Firebird needs to pull a lot of data into a temporary area so it can do the index + sort on the result set. This is very slow and uses a lot of temporary space. The query runs very fast if I don't sort it so this is totally due to the ORDER BY clause.

What I think I need to do is create a phantom column in one table that is indexed by the values in another table. Basically I want to index the foreign key column based on the *value* in the foreign table instead of the foreign key ID. I hope that makes sense.

MS SQL Server supports what they call "Indexed Views" which I think would do what I'm talking about. Or if I could index a Computed Column that might work as well (maybe not though because I'm not sure of the efficiency computing a column based on a foreign table).

I can post a simplified version of my schema if that would help in understanding what my issue is. Otherwise, any ideas?

Thanks

_________________________________________________________________
Hotmail: Trusted email with Microsoft's powerful SPAM protection.
http://clk.atdmt.com/GBL/go/177141664/direct/01/
http://clk.atdmt.com/GBL/go/177141664/direct/01/


[Non-text portions of this message have been removed]