how to improve the query with joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to improve the query with joins

Author

Topic

venkatesh05vits
Starting Member

2 Posts

Posted - 2011-04-01 : 08:12:29

i have the below query help me to improve the performance

SELECT Issue_Key,Issue_Open_Time,changed_Status,Issue_Status_Change_Time, priority, ServiceName,BusinessUnit,Location,Reopen FROM(SELECT ji.id, pkey AS Issue_Key,created AS Issue_Open_Time,st.pname AS Current_Status FROM jiraissue AS ji, issuestatus st WHERE project = 10290 AND ji.issuestatus=st.id AND ji.created<='<toDate>' AND ji.created>='<fromDate>'ORDER BY ji.id ) AS _t1 LEFT JOIN ( SELECT issueid, created AS Issue_Status_Change_Time, st.pname changed_Status FROM changegroup cg, changeitem ci, issuestatus st WHERE cg.id = ci.groupid AND issueid IN (SELECT id FROM jiraissue WHERE project = 10290 AND created<='<toDate>' AND created>='<fromDate>'ORDER BY id) AND FIELD='status' AND newvalue=st.id ORDER BY Issue_Status_Change_Time) AS _t2 ON _t1.id= _t2.issueid LEFT JOIN (SELECT cfo.customvalue AS priority, issue FROM customfieldvalue cfv, customfieldoption cfo WHERE cfv.customfield = 10370 AND cfv.stringvalue = cfo.ID AND cfo.parentoptionid IS NOT NULL AND issue IN (SELECT id FROM jiraissue WHERE project = 10290 AND created<='<toDate>' AND created>='<fromDate>'ORDER BY id)) AS _t3 ON _t1.id= _t3.issue LEFT JOIN (SELECT cfo.customvalue AS ServiceName, issue FROM customfieldvalue cfv JOIN customfieldoption cfo ON cfv.stringvalue=cfo.id WHERE cfv.customfield = 10026 AND cfo.customfieldconfig= 11454 AND parentoptionid IS NULL AND issue IN (SELECT id FROM jiraissue WHERE project = 10290 AND created<='<toDate>' AND created>='<fromDate>'ORDER BY id)) AS _t4 ON _t1.id=_t4.issue LEFT JOIN (SELECT cfv.stringvalue AS BusinessUnit,cfv.issue FROM customfieldvalue cfv WHERE cfv.customfield=10665 AND cfv.issue IN (SELECT id FROM jiraissue WHERE project = 10290 AND created<='<toDate>' AND created>='<fromDate>'ORDER BY id)) AS _t5 ON _t1.id=_t5.issue LEFT JOIN (SELECT CONCAT(a.customvalue,'-',b.customvalue) AS location,issue FROM customfieldvalue c,customfieldoption a, customfieldoption b WHERE c.stringvalue=b.id AND b.parentoptionid=a.id AND a.customfield=11354 AND issue IN (SELECT id FROM jiraissue WHERE project = 10290 AND created<='<toDate>' AND created>='<fromDate>'ORDER BY id)) AS _t6 ON _t1.id=_t6.issue LEFT JOIN (SELECT cfv.textvalue AS Reopen,cfv.issue FROM customfieldvalue cfv WHERE cfv.customfield=10025 AND cfv.issue IN (SELECT id FROM jiraissue WHERE project = 10290 AND created<='<toDate>' AND created>='<fromDate>' ORDER BY id)) AS _t7 ON _t1.id=_t7.issue

There are 7 left joins in it. its taking more time. Any way to improve the speed

venkatesh

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-04-01 : 10:46:25

1. read the FAQ's - help us read the code by posting it with [CODE] tags.

2. remove ORDER BY in subselect queries.
3. why the same query in each of the subselects? what are you tring to gain from it?
4. indices on the tables/columns used in the joins and where clauses are your friend.
post the execution plan (actual or estimated) (+ tables sizes) and we can assist more.

5. define "it's taking more time".
what is an acceptable/not acceptable response time - esp in view of your record volumes.

venkatesh05vits
Starting Member

2 Posts

Posted - 2011-04-05 : 00:25:00

3.the select statement will get the data needed for only the ids which are taken from the sub query
4.Each table contains 1 lakh records
5. its taking around 8 to 10 minutes when tried with dates as one month period

venkatesh

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-06 : 17:10:32

1 lakh = 100 000

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources