SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

90 Posts

Posted - 01/14/2013 :  08:38:34  Show Profile  Reply with Quote
I was trying to determine if there is anything I can do to the following query for faster performance. There may be nothing but the users (the query drives a report) complain it take 15 to 20 minutes to generate. There is a *lot* of data and again, there may be nothing that can be done but out of due diligence, I said I'd look into it. I don't see anything obvious myself but maybe someone else will.

Also, the DB this query runs against is a DB for a 3rd party application and changes to the schema or indices is not an option.

Any ideas are appreciated.


-- Test Execution

SELECT

RUN.RN_RUN_NAME AS "Run Name",
RUN.RN_RUN_ID AS "Run ID",
RUN.RN_EXECUTION_DATE AS "Run Execution Date",
RUN.RN_EXECUTION_TIME AS "RunExecution Time",
RUN.RN_DURATION AS "Run Duration",
RUN.RN_STATUS AS "Run Status",
RUN.RN_ASSIGN_RCYC AS "Run Target Cycle",
RCY_RN.RCYC_NAME AS "Run Target Cycle Name",
RUN.RN_TESTER_NAME AS "Run Tester Name",

TESTCYCL.TC_ACTUAL_TESTER AS "Instance Actual Tester",
TESTCYCL.TC_PINNED_BASELINE AS "Instance Baseline",
TESTCYCL.TC_EXEC_DATE AS "Instance Execution Date",
TESTCYCL.TC_EXEC_TIME AS "Instance Execution Time",
TESTCYCL.TC_PLAN_SCHEDULING_DATE AS "Instance Planned Exec Date",
TESTCYCL.TC_PLAN_SCHEDULING_TIME AS "Instance Planned Exec Time",
TESTCYCL.TC_TESTER_NAME AS "Instance Responsible Tester",
TESTCYCL.TC_STATUS AS "Instance Status",
TESTCYCL.TC_ASSIGN_RCYC AS "Instance Target Cycle",
RCY_TC.RCYC_NAME AS "Instance Target Cycle Name",
TESTCYCL.TC_TESTCYCL_ID AS "Instance ID",
TESTCYCL.TC_USER_01 AS "Instance Responsible Tester Name",

CYCLE.CY_CYCLE AS "Test Set Name",
CYCLE.CY_CYCLE_ID AS "Test Set ID",
CYCLE.CY_USER_01 AS "Test Set Functional Area",
CONVERT(NVARCHAR(MAX),CYCLE.CY_COMMENT) AS "Test Set Description", --QC Memo fields must be converted to nvarchar
CYCLE.CY_ASSIGN_RCYC AS "Test Set Target Cycle ID",
RCY_CY.RCYC_NAME AS "Test Set Target Cycle Name",
CONVERT(NVARCHAR(MAX),CYCLE.CY_USER_07) AS "Test Set Full Folder Path", --QC Memo fields must be converted to nvarchar

TEST.TS_RESPONSIBLE AS "Plan Designer",
TEST.TS_USER_03 AS "Plan Business Value",
TEST.TS_USER_02 AS "Plan Complexity",
TEST.TS_USER_04 AS "Plan Functional Area"

--COUNT(DISTINCT STEP.ST_ID) AS "Number of Test Steps",
--COUNT(DISTINCT LT.LN_BUG_ID) AS "Number of Linked Defects",
--COUNT(DISTINCT BUG.BG_BUG_ID) AS "Number of Active Linked Defects"

FROM TESTCYCL

-- this restricts counts to last run execution date/time
LEFT JOIN
(
  SELECT
  RN_RUN_ID, RN_RUN_NAME, RN_EXECUTION_DATE, RN_EXECUTION_TIME, RN_DURATION, RN_STATUS,
  RN_ASSIGN_RCYC, RN_TESTER_NAME, RN_TESTCYCL_ID, ROW_NUMBER()
  OVER(PARTITION BY RN_TESTCYCL_ID ORDER BY RN_EXECUTION_DATE DESC,RN_EXECUTION_TIME DESC) AS LASTRUN
  FROM RUN
) RUN ON TC_TESTCYCL_ID = RN_TESTCYCL_ID

LEFT JOIN TEST ON TS_TEST_ID = TESTCYCL.TC_TEST_ID
LEFT JOIN CYCLE ON TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
LEFT JOIN RELEASE_CYCLES RCY_CY ON RCY_CY.RCYC_ID = CY_ASSIGN_RCYC
LEFT JOIN RELEASE_CYCLES RCY_TC ON RCY_TC.RCYC_ID = TC_ASSIGN_RCYC
LEFT JOIN RELEASE_CYCLES RCY_RN ON RCY_RN.RCYC_ID = RN_ASSIGN_RCYC

--INNER JOIN STEP ON ST_RUN_ID = RN_RUN_ID
--LEFT JOIN LINK AS LT ON LN_ENTITY_ID = ST_ID AND LN_ENTITY_TYPE = 'STEP'

---- this filters for active defects
--LEFT JOIN
--(
--  SELECT BG_BUG_ID
--  FROM BUG WHERE BG_STATUS <> 'Closed' AND BG_STATUS <> 'Fixed'
--) BUG ON LN_BUG_ID = BG_BUG_ID

-- this gets the last run execution
AND RUN.LASTRUN = 1 AND ISNULL(CAST(RCY_RN.RCYC_NAME as nvarchar(100)),'') LIKE  @Run_Release_Cycle_Name@

--GROUP BY RN_RUN_ID, RN_RUN_NAME,RN_EXECUTION_DATE, RN_EXECUTION_TIME, RN_DURATION, RN_STATUS, RN_ASSIGN_RCYC, RN_TESTER_NAME,
--TC_ACTUAL_TESTER,TC_PINNED_BASELINE,TC_EXEC_DATE,TC_EXEC_TIME,TC_PLAN_SCHEDULING_DATE,TC_PLAN_SCHEDULING_TIME,TC_TESTER_NAME,TC_STATUS,
--TC_ASSIGN_RCYC,TC_USER_01, CYCLE.CY_CYCLE, CAST (CYCLE.CY_COMMENT AS NVARCHAR(MAX)), --QC Memo fields must be converted to nvarchar
--CYCLE.CY_USER_01, CYCLE.CY_OPEN_DATE, CYCLE.CY_CLOSE_DATE, CYCLE.CY_REQUEST_ID, CYCLE.CY_STATUS, CYCLE.CY_PINNED_BASELINE, CYCLE.CY_CYCLE_ID, --CYCLE.CY_ASSIGN_RCYC, CYCLE.CY_VTS, TEST.TS_RESPONSIBLE, TEST.TS_USER_02, TEST.TS_USER_03, TEST.TS_USER_04,
--CAST (CYCLE.CY_USER_07 AS NVARCHAR(MAX)) --QC Memo fields must be converted to nvarchar

ORDER BY RUN.RN_RUN_ID

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/14/2013 :  08:44:08  Show Profile  Reply with Quote
whats the amount of data that this is dealing with?
Did you check execution plan and analyse costly steps?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

planetoneautomation
Yak Posting Veteran

90 Posts

Posted - 01/14/2013 :  09:08:39  Show Profile  Reply with Quote
Hundreds of thousands of rows possibly.

Don' really know what you mean about "execution plan" and "analyze costly steps" ... but if those are functions of some sort of SQL Server DB management tools, I do not have access to those. I am not a DB admin and have no tools whatsoever and can't get access to any tools or even a DB admin here at this huge company (I'm here on a short contract). This query is written inside HP Quality Center and essentially all I can do is write queries that drive reports against the application's data.

If the answer is, the perfomance can't be really looked at without access to SQL Server management tools, then that's what I'll tell them and they will have to decide if they want to locate a resource to do this kind of analyzis. I was just hoping someone might spot some inefficiency in the query and make a recommendation, though I didn't really hold much hope for that.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/14/2013 :  10:06:43  Show Profile  Reply with Quote
Yep. For execution plan analysis you need access to SQL Server Management Studio.
Without that and not knowing what your query is doing and what you're trying to do, I dont think anybody will be able to help you much

If you want to keep it simple and try, post some sample data and show us what output you're expecting out of it and then we might be able to come up with an alternative.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 01/14/2013 :  10:10:56  Show Profile  Reply with Quote
I would go out on a limb and assert that you are correct - that it is very hard, if not impossible, to do any serious evaluation or optimization without having access to SQL Server Management Studio. You can't look at the table schema (you probably can, but you have to write a query to do it), you can't look at the indexes (ditto), you can't look at the query plan (ditto), so it is almost like being asked to operate on a patient with your right hand tied behind your back.

I see lot of left joins, and no where clauses at all - is that really what is required? Are you trying to pull all the data from those tables?

Edited by - James K on 01/14/2013 10:11:32
Go to Top of Page

planetoneautomation
Yak Posting Veteran

90 Posts

Posted - 01/14/2013 :  11:18:18  Show Profile  Reply with Quote
Thanks for the feedback ... I am going to try to get a DB person with access to the tools to evaluate.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/14/2013 :  23:46:53  Show Profile  Reply with Quote
thats sounds like best thing to do

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000