How to performance tune this query?

Need help in performance tuning the query. The execution plan shows the scan count is too high on SResourceHierarchy and grouprelationship tables around in millions. I created covering indexes on sresource and matrix goal that reduced the time majorly. But i want to know if there is any other way of writing query to improve the overall performance even by 1%.

query ::

select * from (SELECT sr.rsrcnum, sr.fllnm, isnull(mgr.fllnm,'') as mgrnm, d.actid, d.goaldesctxt , d.cmpltstscd, d.strtdt, d.enddt, dta, (select TOP 1 itmtxt from tm.Code where catcd = '12103' and itmcd = d.cmpltstscd and lngcd = 0 and delflg = 0) as Status from TM.matrixgoal d inner join TM.sresource sr on d.rsrcid=sr.rsrcid and sr.delflg=0 and sr.stscd<>1 and sr.rsrcid > 7 left join TM.sresource mgr on mgr.rsrcid=sr.mgr1id and mgr.delflg=0 and mgr.stscd<>1 inner join TM.resume1 r1 on r1.rsrcid=sr.rsrcid where d.stscd in (3,4) and d.typcd=5009 and d.activeflg=1 AND (sr.rsrcid IN (SELECT node.empid FROM tm.SResourceHierarchy node JOIN tm.SResourceHierarchy parent ON node.lft > parent.lft AND node.lft < parent.rgt AND node.mgrnum = 1 AND parent.mgrnum = 1 ) or sr.RsrcID IN (select r.rsrcid from tm.resume1 r join tm.grouprelationship g on (g.hierarchylvl0 = r.hierarchylvl0 or (g.hierarchylvl0 is null and r.hierarchylvl0 is null)) and (g.hierarchylvl1=r.HierarchyLvl1 or (g.hierarchylvl1 is null and r.HierarchyLvl1 is null)) and (g.hierarchylvl2=r.HierarchyLvl2 or (g.hierarchylvl2 is null and r.HierarchyLvl2 is null)) and (g.hierarchylvl3=r.HierarchyLvl3 or (g.hierarchylvl3 is null and r.HierarchyLvl3 is null)) and g.delflg=0 and g.AllowDenyCD='a')) ) temp ORDER BY fllnm, goaldesctxt

-------------Problems Reply------------

It should be use sub query in your query. So it should be performance wise poor. It is simple sub query run to per main query row. so it is taken much time. If you improve performance avoid sub query. You use join instead of sub query.

To me it look like one big ass single line statement, the only optimation I know if is to try to add a index to the varies tables you are using. that should speed up it's ability to find the data faster

Try to use EXISTS instead of IN.

Category:sql Views:2 Time:2019-01-04

Related post

  • How can I optimise this Query? 2010-08-28

    How can I optimize this query if given the following query returns either all entries in the table or entries that match only up to current date ? btw: The Query is targeted to a Oracle Linked Server on MS Sql 2005 as an Inline function.. Do not want

  • How to perform a joined query for a one-to-many tables with collection property? 2011-02-02

    I have two tables; t_users(user_id pk, user_name, password) and t_transactions(trans_id pk, user_id fk, item_name, amount). As we can see, t_users is the parent table while t_transactions is the child table so I created two classes for them: public c

  • How can I optimize this query? 2009-04-30

    How can I optimize this query? It seems there should be a much easier way to do this. The goal is that it will still be able to be readily turned into a delete statement. SELECT * FROM team WHERE team_id IN ( SELECT team_id FROM ( SELECT team.team_id

  • How can I do this query with NHibernate 2010-12-04

    How can I do this query with NHibernate select top 10 count(distinct classedition.createdby_id) as editions, class.id, class.name, class.createdon, class.createdby_id from class inner join classedition on class.id = classedition.class_id group by cla

  • How do i make this query sql injection proof? 2011-03-29

    How can i make this query sql injection proof? $sql=mysql_query("SELECT * FROM updates ORDER BY item_id DESC LIMIT 16"); while($row=mysql_fetch_array($sql)) { $msg_id=$row['item_id']; $message=$row['item_content']; } Or can someone show me some examp

  • How would I make this query run faster? 2011-06-30

    How would I make this query run faster...? SELECT account_id, account_name, account_update, account_sold, account_mds, ftp_url, ftp_livestatus, number_digits, number_cw, client_name, ppc_status, user_name FROM Accounts, FTPDetails, SiteNumbers, Clien

  • How to perform a this kind of Distinct operation with LINQ? 2011-10-11

    I have the following foreach loop: List<WorkingJournal> workingJournals = new List<WorkingJournal>(); foreach (WorkRoster workRoster in workRosters) { bool exists = workingJournals.Any(workingJournal => workingJournal.ServicePlan.Id ==

  • New at EntityFramework how do I write this query in linq to entity 2011-10-26

    SELECT i.answerc, d.answera, d.answerb FROM tableone AS u INNER JOIN tabletwo AS i ON u.OneId = i.OneId INNER JOIN tablethree AS l ON u.TwoId = l.TwoId INNER JOIN tablefour AS k ON u.ThreeId = k.ThreeId INNER JOIN tablefive AS p ON u.fourId = p.fourI

  • How would you write this query? 2009-01-28

    I'm looking to refactor the below query to something more readable and modifiable. The first half is identical to the second, with the exception of the database queried from (table names are the same though.) SELECT Column 1 AS c1, ... Column N AS cN

  • How to speed up this query? 2009-09-09

    ATM I am trying to learn how to efficiently use database inidices and would appreciate to get some expert input. I do not have any performance issues currently. I would just like to know, how you would handle your indices with this query: SELECT B.ev

  • How can you write this query? 2010-09-17

    I have two tables. The structure of tables is as follows. TRAILERS_INVENTORY TRAILER_TYPE VARCHAR2(100) TRAILER_LENGTH INT TRAILER_WIDTH INT YEAR_OF_MANUFACTURE INT NEW_INVENTORY NEW_INVENTORY_TYPE_ID INT, TRAILER_TYPE VARCHAR2(100), TRAILER_LENGTH I

  • How Can I optimize this query (OR inside AND validation)? 2011-11-29

    I'm having problems with this query: SELECT col1, col2,col3,...,coln FROM MyTable WHERE col1 = @value1 AND ( ISNULL(col2,'c') = ISNULL(@value2,ISNULL(col2,'c')) OR ISNULL(col3,'c') = ISNULL(@value2,ISNULL(col3,'c')) ) AND coln = 'valueN' I have to st

  • How do I extend this query to find valid combinations of three items? 2011-12-14

    I totally don't expect to get any answers here, but I'll try anyway. So this came out of playing Skyrim. I wanted an easy way to look up what ingredients can be combined to make different potions/poisons so I made an Ingredient table that has an ID a

  • How can I optimize this query with subquery? 2012-01-26

    I have the following MySQL query: SELECT value_1, ( SELECT value_4 FROM table_1 WHERE value_3 < value_1 ORDER BY value_3 DESC LIMIT 1 ) AS result_value FROM table_2 WHERE value_1 BETWEEN 1325372400000 AND 1328050800000 ORDER BY value_1 Which retur

  • How to perform a complex query using AS alias 2012-03-10

    First of all, I am new here and just learning sql, so bear with me. I have a simple question. Let's say I have a table called voting with the following columns: id, token(int), candidate(int), rank(int) .. and I want to perform a query like: SELECT *

  • How to Linq-ify this query? 2012-03-14

    I currently have this Linq query: return this.Context.StockTakeFacts .OrderByDescending(stf => stf.StockTakeId) .Where(stf => stf.FactKindId == ((int)kind)) .Take(topCount) .ToList<IStockTakeFact>(); The intent is to return every fact for

  • How can I make this query in SQL Server Compact Edition? 2009-01-22

    this subquery works in SQL Server: select systemUsers.name, (select count(id) from userIncidences where idUser = systemUsers.id ) from systemUsers How can It be made in SQL Compact? Thanks! --------------Solutions------------- Try this: SELECT su.Nam

  • How can I make this query to detect multiple accounts more efficient & play well w/ has_many? 2009-05-10

    In my User model, I want to search for whether a user has multiple accounts (aka 'multis'). Users have sessions; sessions are customized to set creator_id = ID of first user the session was logged in as, and updater_id = ID of last user the session w

  • How can I get this query to return 0 instead of null? 2009-05-19

    I have this query: SELECT (SUM(tblTransaction.AmountPaid) - SUM(tblTransaction.AmountCharged)) AS TenantBalance, tblTransaction.TenantID FROM tblTransaction GROUP BY tblTransaction.TenantID But there's a problem with it; there are other TenantID's th

  • How can I write this query in ZF? 2009-06-04

    I am trying to write this, query with zf select but without success SELECT * FROM `subscribers` WHERE id IN (Select subscriber_id From gs_relations Where group_id=55) I was trying with ssomething like this: $gs_relations = new GSRelations(); $part =

  • How can I optimize this query to execute faster? 2009-07-23

    This is for the friends module on my site, where users can make friends with each other. These are stored in a "friends:" table, with person who initiated the friendship being the friendship_inviter and the person on the approve/deny end of things is

  • How can I express this query in a way that will work on excel data? 2009-09-16

    I do have this final query (I've tried a lot of them to make this work), which i'm not able to get it run. SELECT customerNo, rating, dateApproved FROM ['worksheet$'] WHERE dateCreated = (SELECT MAX(dateCreated) FROM ['worksheet$'] ) AND status = 'Ap

  • How do I change this query to linq to sql? 2010-06-17

    Query: select emp.empname as Name, dep.depname as Department from Employee as emp inner join Department as dep on emp.depid=dep.depid where emp.id='2' How can I change this to linq to sql? --------------Solutions------------- var id = 2; var qry = fr

  • How do i optimize this query? 2010-07-31

    I have a very specific query. I tried lots of ways but i couldn't reach the performance i want. SELECT * FROM items WHERE user_id=1 AND (item_start < 20000 AND item_end > 30000) i created and index on user_id, item_start, item_end this didn't w

  • How do I do this query using JPA so that it is not very slow? 2010-08-30

    How do you join across multiple tables in an efficient way using JPQL select a.text,b.text,c.text, from Class1 a, Class2 b, Class3 c where a.id=b.b_id and b.id=c.b_id and a.text like ... and b.text like ... I am doing something like this, the tables

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.101 (s). 11 q(s)