sp that runs slows. 3min - SQL Server Forums

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

sp that runs slows. 3min

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-01 : 16:49:06

i have a query joining 2 tables. One has 1M records the other has 100. i used the wizard to create the indexes and stastitics are update, no defragmentation. The query is taking 3 min to run. what else i can do to improve the performance.?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-01 : 17:02:15

Rewrite the query to not make calculations over indexed columns.
Have you checked the execution plan?

N 56°04'39.26"
E 12°55'05.63"

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-01 : 19:41:12

But i need the indexed columns to calculate. how is that possible?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-01 : 20:08:23

Post the query

Be One with the Optimizer
TG

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-01 : 23:26:42

CREATE TABLE #ttAllScores
(
studentID uniqueidentifier,
reportingGroupID uniqueidentifier,
schoolDistrictID uniqueidentifier,
courseID uniqueidentifier,
examScore decimal,
PRIMARY KEY (studentID, courseID)
)

-- Begin temp table value insert
INSERT
#ttAllScores
SELECT
sr.studentID,
sr.reportingGroupID,
sr.SchoolDistrictID,
sr.CourseID,
dbo.ComputeAstroScaledScore(SUM(sr.numCorrect), @assmtsetID, null) examScore
FROM
dbo.StudentResults sr (nolock)
INNER JOIN dbo.AssessmentSetAssessment assAss (nolock)
ON assAss.AssessmentID=sr.AssessmentID
WHERE
assAss.AssessmentSetID=@assmtsetID
GROUP BY
sr.studentID,
sr.reportingGroupID,
sr.SchoolDistrictID,
sr.SchoolID,
sr.CourseID

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-01 : 23:33:43

one table has 1M rows and the other has 100 rows.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-02 : 01:49:52

You don't write which table has which amount of records, so once again we have to guess...
Try this

INSERT		#ttAllScores
SELECT		sr.studentID,
		sr.reportingGroupID,
		sr.SchoolDistrictID,
		sr.CourseID,
		dbo.ComputeAstroScaledScore(SUM(sr.numCorrect), @assmtsetID, NULL) AS examScore
FROM		dbo.StudentResults AS sr (nolock) 
WHERE		EXISTS (SELECT * FROM dbo.AssessmentSetAssessment AS x WHERE x.AssessmentID = sr.AssessmentID AND AssessmentSetID = @assmtsetID)
GROUP BY	sr.studentID,
		sr.reportingGroupID,
		sr.SchoolDistrictID,
		sr.SchoolID, 
		sr.CourseID

Also post the code for the function.

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-09-02 : 15:55:29

quote:

Originally posted by Peso

You don't write which table has which amount of records, so once again we have to guess...
Try this

INSERT		#ttAllScores
SELECT		sr.studentID,
		sr.reportingGroupID,
		sr.SchoolDistrictID,
		sr.CourseID,
		dbo.ComputeAstroScaledScore(SUM(sr.numCorrect), @assmtsetID, NULL) AS examScore
FROM		dbo.StudentResults AS sr (nolock) 
WHERE		assAss.AssessmentSetID = @assmtsetID
		AND EXISTS (SELECT * FROM dbo.AssessmentSetAssessment AS x WHERE x.AssessmentID = sr.AssessmentID)
GROUP BY	sr.studentID,
		sr.reportingGroupID,
		sr.SchoolDistrictID,
		sr.SchoolID, 
		sr.CourseID

Also post the code for the function.

N 56°04'39.26"
E 12°55'05.63"

Hi Peso,

I don't think the code in red is correct. That alias doesn't exist?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 16:25:35

Yeah, come on Peso, you know that alias should have been "boobityboobity".

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-02 : 16:33:58

Nah. I would have aliased it "JLo".

N 56°04'39.26"
E 12°55'05.63"

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 16:40:39

Thanks for that image - Now I can't work - guess I'll have to go home early

Be One with the Optimizer
TG

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-03 : 12:27:22

ALTER FUNCTION [dbo].[ComputeAstroScaledScore]
(
@NumCorrect int
, @AssessmentSetId uniqueidentifier
, @SkillModuleId int = null
)
RETURNS decimal
AS
BEGIN

DECLARE @scaledScore decimal;

IF @NumCorrect >= (SELECT Possible FROM dbo.AssessmentScoreAstroMetadata (nolock)
WHERE AssessmentSetId = @AssessmentSetId
and (SkillModuleId = @SkillModuleId
OR (SkillModuleId IS NULL AND @SkillModuleId IS NULL)))

SELECT @scaledScore = max(ScaleEnd) + 1
FROM dbo.vwAssessmentScoreAstroMetadataExtended asme (nolock)
WHERE AssessmentSetId = @AssessmentSetId
AND (SkillModuleId = @SkillModuleId OR (SkillModuleId IS NULL AND @SkillModuleId IS NULL))

ELSE
SELECT TOP 1 @scaledScore = asm.ScaleStart + (
(@NumCorrect - asm.CutScore) *
(cast((asm.ScaleEnd + 1) - asm.ScaleStart as float) / (coalesce((select TOP 1 asmd.CutScore
from dbo.vwAssessmentScoreAstroMetadataExtended asmd
where (asmd.SkillModuleId = @SkillModuleId OR (asmd.skillModuleid IS NULL AND @SkillModuleId IS NULL))
and asmd.AssessmentSetId = asm.AssessmentSetId and asmd.LikertResponseLevelValue = (asm.LikertResponseLevelValue + 1)
), asm.Possible) - asm.CutScore)
)
)
FROM dbo.vwAssessmentScoreAstroMetadataExtended asm
WHERE asm.AssessmentSetId = @AssessmentSetId
AND (@NumCorrect - asm.CutScore) >= 0
AND (asm.skillModuleId = @SkillModuleId OR (asm.SkillModuleId IS NULL AND @SkillModuleId IS NULL))

ORDER BY ABS(asm.CutScore - @NumCorrect)

IF @scaledScore IS NULL
SET @scaledScore = 100

RETURN @scaledScore

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-03 : 15:54:20

Ouch!
You have a multistatment function, which is working with views, subquries and TOP 1 ORDER BY.

I'd say it's the function that is killing your performance...

N 56°04'39.26"
E 12°55'05.63"

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2009-09-03 : 17:18:13

yeah, do you think i can rewrite it to make it faster?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources