| Subject | RE: [firebird-support] Is there something like an Indexed View or indexed Computed Column? |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2009-11-03T14:40:11Z |
Seeing the query, view and index definitions (so that we know which fields are involved) and the plan normally helps quite a bit. Also, it would be good to know which version of Firebird you're using and how selective the indexes in the fields are. And how many rows does your query return?
Set
-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Jorge Martin
Sent: 3. november 2009 15:25
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Is there something like an Indexed View or indexed Computed Column?
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]
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links
Set
-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Jorge Martin
Sent: 3. november 2009 15:25
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Is there something like an Indexed View or indexed Computed Column?
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]
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !
Also search the knowledgebases at http://www.ibphoenix.com
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links