Solved

Please check the SQL

Posted on 2007-11-20
4
218 Views
Last Modified: 2010-03-20
Can I simplify the query(faster)?

SELECT   Distinct CATGRPDESC.CATGROUP_ID,
         CATGRPDESC.NAME
FROM     CATGRPATTR,
         CATGRPREL,
         CATGRPDESC,
         CATGRPREL CATGRPREL2,
         IMCAPPLICATIONS,
         IMCCATLNREL rel
WHERE    CATGRPATTR.CATGROUP_ID=CATGRPREL.CATGROUP_ID_PARENT
AND      CATGRPDESC.CATGROUP_ID=CATGRPREL.CATGROUP_ID_PARENT
AND      CATGRPREL.CATGROUP_ID_CHILD IN ()
AND      CATGRPATTR.DESCRIPTION IN ()
AND      CATGRPREL2.CATGROUP_ID_PARENT = CATGRPREL.CATGROUP_ID_CHILD
AND      CATGRPREL2.CATGROUP_ID_CHILD = IMCAPPLICATIONS.LINE_ID
AND      IMCAPPLICATIONS.VID IN ()
AND      rel.Line_ID = IMCAPPLICATIONS.LINE_ID
AND      rel.LINE_ID IN ()
AND      rel.Catalog_ID = CATGRPDESC.CATGROUP_ID
ORDER BY IMCCRP.CATGRPDESC.NAME FOR FETCH ONLY

Thanks
Krishna
0
Comment
Question by:vvsrk76
4 Comments
 
LVL 19

Accepted Solution

by:
NickUpson earned 125 total points
ID: 20321195
mostly likely you need to add one or more indexes onto the tables, start with the fields used to join between tables
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 125 total points
ID: 20322517
that is as simplified as the query gets. you can't achieve performance gains by modifying the select further. as Nick has said, the key is to have an index for all the join conditions

i.e.
CATGRPATTR.CATGROUP_ID,
CATGRPREL.CATGROUP_ID_PARENT,
CATGRPDESC.CATGROUP_ID,
CATGRPREL.CATGROUP_ID_PARENT,
CATGRPREL.CATGROUP_ID_CHILD
etc
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20953234
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
New and Previous Values in a Query 7 30
Sql Server group by 10 44
MS SQL Server time between records 14 46
average of calculation (TSQL) 4 26
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question