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.
| Author |
Topic |
|
raghuveer125
Constraint Violating Yak Guru
285 Posts |
 Posted - 2013-04-15 : 19:45:47
|
| Hi I don't have much knowledge on T-SQL.I have a query who's execution time is >10 min. I need help to improve my below query performance.I have checked all indexes(Clust and noclust) are there, for columns which are used in where clauses. And I have also checked index fragmentation is <30%.If I run select statement against each tables which are used in my below mentioned query I am getting output in <2 sec.My Query IS:-SELECT Table__62.sw_name, DT_CA_DISCOVERED_HARDWARE.host_name FROM ( SELECT * FROM ca_itrm.BI_SWCategory1 WHERE category_uuid in (select ca_category_def.category_uuid from ca_category_def where category_name in ('*') OR '*' IN ('*') ) UNION SELECT * FROM ca_itrm.BI_SWCategory2 WHERE category_uuid in (select ca_category_def.category_uuid from ca_category_def where category_name in ('*') OR '*' IN ('*') ) ) Table__62 RIGHT OUTER JOIN ( SELECT * FROM bi_dt_ca_discovered_hardware WHERE bi_dt_ca_discovered_hardware.DIS_HW_UUID IN (SELECT OBJECT_DEF_UUID FROM CA_LINK_DIS_USER_SEC_PROFILE AS USRPROF, CA_OBJECT_ACE AS ACE WHERE USRPROF.USER_UUID = (SELECT USER_UUID FROM CA_DISCOVERED_USER WHERE uri like ('winnt://'+'amer'+'/'+ 'uspgh.del.sreddy') and domain_uuid = (select set_val_uuid from ca_settings where set_id = 1) ) AND ACE.SECURITY_PROFILE_UUID = USRPROF.SECURITY_PROFILE_UUID AND ((ACE & 65) = 65) UNION SELECT OBJECT_UUID AS OBJECT_DEF_UUID FROM CA_LINK_OBJECT_OWNER AS OWNERTABLE WHERE OWNERTABLE.OWNER_UUID IN (SELECT USER_UUID FROM CA_DISCOVERED_USER WHERE uri like ('winnt://'+'amer'+'/'+ 'uspgh.del.sreddy') AND CA_DISCOVERED_USER.DOMAIN_UUID IN (SELECT DOMAIN_UUID FROM CA_DISCOVERED_HARDWARE) and domain_uuid = (select set_val_uuid from ca_settings where set_id = 1) ) UNION SELECT OBJECT_DEF_UUID FROM OLS_AREA_ACE WHERE (AREA_MASK & AREA_ACE & (SELECT DBO.OLS_FN_GETAREAACEBYUSER( ( (select user_uuid from ca_discovered_user where uri like ('winnt://'+'amer'+'/'+ 'uspgh.del.sreddy') and domain_uuid = (select set_val_uuid from ca_settings where set_id = 1)) ) ) )) != 0 AND (SELECT COUNT(USER_UUID) FROM OLS_V_USER WHERE uri like ('winnt://'+'amer'+'/'+ 'uspgh.del.sreddy') AND IS_AREA_ENABLED = 1) >= 1 ) ) DT_CA_DISCOVERED_HARDWARE ON (Table__62.asset_source_uuid=DT_CA_DISCOVERED_HARDWARE.dis_hw_uuid) INNER JOIN v_user_hr_details ON (DT_CA_DISCOVERED_HARDWARE.host_name=v_user_hr_details.ComputerName) WHERE v_user_hr_details.FunctionalOrganization IN ('CCG Shared Services')In Love... With Me! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-16 : 02:14:01
|
| its very difficult to go through entire query, analyse and suggest something without even knowing your dataMy suggestion would be to analyse execution plan, identify the costly steps and then post it here with some sample data so that somebody might be able to fine tune it or suggest an alternative------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-04-16 : 08:30:38
|
| To me it seems that the query is not even executable on SQL server. E.g. First portion of union is somehow like this SELECT Table__62.sw_name ,DT_CA_DISCOVERED_HARDWARE.host_name FROM (SELECT * FROM ca_itrm.BI_SWCategory1 WHERE category_uuid in (select ca_category_def.category_uuid from ca_category_def where category_name in ('*') OR '*' IN ('*')) Not sure how it is running on your machine. and many other factors that are suggesting the query is not executable. Any how .. either come up with sample data and the desired output (based on sample data) Or A couple of things (per my understanding) when you write SQL queries 1) While making sub data sets e.g. FROM (select * ....), do not specify *, instead name the columns explicitly, unless there is a really good reason to do so. 2) If possible use joins, Instead of using "SELECT ColumnName from TableName Where ColX in (select ....)".CheersMIK |
|
|
|
|
|
|
How to reduce the time