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
 General SQL Server Forums
 New to SQL Server Programming
 Query Performance

Author  Topic 

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-14 : 08:38:34
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

52326 Posts

Posted - 2013-01-14 : 08:44:08
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
Posting Yak Master

105 Posts

Posted - 2013-01-14 : 09:08:39
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

52326 Posts

Posted - 2013-01-14 : 10:06:43
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-14 : 10:10:56
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?
Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2013-01-14 : 11:18:18
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

52326 Posts

Posted - 2013-01-14 : 23:46:53
thats sounds like best thing to do

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

Go to Top of Page
   

- Advertisement -