Is there a way to improve the performance of this query..

Mr. Pabloj,

Thanks for your response. I am using Oracle 9i. I am not sure what do you mean by EXPLAIN plan?

Thanks!

I have the following query which runs fine, but if I remove the comments, it takes 3 minutes to run, as the table t_topics has about 4000 records. Is there any way to improve the performance of the query (after removing the comments):

SELECT distinct tm.canonical_mapping_topic_id,
tm.rule_id, tm.taxonomy_mapping_operand, tm.TAXONOMY_MAPPING_VERSION_ID,
ct.canonical_topic_name,
chv.CANONICAL_VERSION_ID, chv.CANONICAL_HIERARCHY_NAME, chv.CANONICAL_MAPPING_HIERARCHY_ID
-- t.topic_id, t.TOPIC_NAME, t.TOPIC_DESCRIPTION,
-- t.TOPIC_FACET, t.TOPIC_SOURCE,
FROM t_taxonomy_mapping tm, t_canonical_topics ct, t_canonical_hierarchy_version chv
--, t_topics t
WHERE tm.taxonomy_mapping_version_id = 439
AND ct.canonical_mapping_hierarchy_id = 41
AND chv.CANONICAL_MAPPING_HIERARCHY_ID = 41
AND tm.canonical_mapping_topic_id = ct.canonical_mapping_topic_id
-- AND t.PRESENT_TAXONOMY_TYPE_ID = 0
-- AND tm.TOPIC_ID = t.TOPIC_ID
ORDER BY
--t.TOPIC_ID,
tm.rule_id, tm.canonical_mapping_topic_id ASC;


Any help will be greatly appreciated!!

Thanks a lot in advance!

Regards,
Mamta.

Just staring at a query is not the way to optimize it!
Tell us on which database you are working, and, most important, show us an EXPLAIN plan for that query.

Also maybe a little more of the structure of your tables and what you are trying to accomplish.

4000 rows is nothing to a db, something has to be wrong for it to be taking that long.

Do you have indexed the tables?
EXPLAIN is a way of having the db show you how it's executing the query in order to find out problems.
To answer don't edit the first post, but post a reply instead

Thanks to all of you for your kind responses.

Brief summary of what I am trying to accomplish:

I have two different kinds of topic hierarchies, presentation topics (table t_topics) and canonical topics (table t_canonical_topics). There are mapping rules defined between these topics in
the table t_taxonomy_mapping. t_canonical_hierarchy_version has the canonical_mapping_hierarchy_id,
which tells the version number of canonical topics hierarchy. I need to pick the taxonomy mapping rules,
the corresponding canonical_topic_id, canonical_topic_name, topic_id and some other information on presentation topic from the t_topics table, for a given canonical_mapping_hierarchy_id and taxonomy_mapping_version_id (there are different mapping versions in the t_taxonomy_mapping table).

t_taxonomy_mapping is the child table of t_topics. topic_id is the PK in t_topics and is FK in t_taxonomy_mapping.

t_taxonomy_mapping (canonical_mapping_topic_id FK) is also the child table of t_canonical_topics (canonical_mapping_topic_id PK, canonical_mapping_hierarchy_id FK) which is the
child table of t_canonical_hierarchy_version (canonical_mapping_hierarchy_id PK)


The indexes defined are as follows:

CREATE INDEX IX_TOPIC_NAME ON T_TOPICS
(TOPIC_NAME)

CREATE UNIQUE INDEX PK_TOPICS ON T_TOPICS
(TOPIC_ID)

CREATE INDEX TYPE_IDX ON T_TOPICS
(PRESENT_TAXONOMY_TYPE_ID)

CREATE UNIQUE INDEX PK_TAXONOMY_MAPPING ON T_TAXONOMY_MAPPING
(TAXONOMY_MAPPING_ID)

CREATE UNIQUE INDEX UNIQUEIDX ON T_TAXONOMY_MAPPING
(CANONICAL_MAPPING_TOPIC_ID, TOPIC_ID, TAXONOMY_MAPPING_VERSION_ID, RULE_ID)

CREATE UNIQUE INDEX IDX_CANONICAL_UNIQUE ON T_CANONICAL_TOPICS
(CANONICAL_TOPIC_ID, CANONICAL_MAPPING_HIERARCHY_ID)

CREATE UNIQUE INDEX PK_CANONICAL_TOPICS ON T_CANONICAL_TOPICS
(CANONICAL_MAPPING_TOPIC_ID)

CREATE UNIQUE INDEX PK_CANONICAL_HIERARCHY_VERSION ON T_CANONICAL_HIERARCHY_VERSION
(CANONICAL_MAPPING_HIERARCHY_ID)


I hope, I am clear enough in explaining the problem. Please let me know if I need to explain anything further.

Thanks again for your help!

Sincerely,
Mamta.




Also maybe a little more of the structure of your tables and what you are trying to accomplish.

4000 rows is nothing to a db, something has to be wrong for it to be taking that long.


Do you have indexed the tables?
EXPLAIN is a way of having the db show you how it's executing the query in order to find out problems.
To answer don't edit the first post, but post a reply instead

Mr. Pabloj,

Thanks a lot for the link for EXPAIN plan! It's very helpful.

Sincerely,
Mamta Kansal.