May 22, 2012 at 9:32 pm
This function gets the score up to now for students (we are using categories and some of the categories have deletelowest to specify drop lowest of assessments in this category.
Thanks in advance
ALTER FUNCTION [dbo].[fn_UpToNowPercentage_dropL_table]
( @SectionID int,
@StudentID int
)
RETURNS table
AS
return
(
select SUM( (catweight*totalscore*1.0)/(case when TotalMaxPoints =0
then 1
else TotalMaxPoints
end))*100.0/SUM(catweight) as perc1
from
( select catweight,assessmentcategory,
sum(score) as totalscore,sum(
(case when gradestatus='C' then 0
else maxpoint
end)) as totalmaxpoints
from (select
AP.AssessmentID,
GradeStatus,
A.points
as maxpoint,
AP.points as score,
ROW_NUMBER()
over(
partition by studentid,A.sectionid,Assessmentcategory
order by (ap.points*1.0/a.points)) as rownumber ,
assessmentcategory,
CatWeight,
deletelowest
fromGradeBook.Assessment A
inner join
GradeBook.AssessmentPoint AP
on
A.AssessmentID=AP.assessmentid
inner join
GradeBook.GBCategory GC
on
A.AssessmentCategory=GC.CategoryID
where A.SectionID=@sectionid
and studentid=@studentid
and a.points >0
and ap.points is not null
and a.deleted=0
and
ap.points >=0
)S
where S.rownumber >deletelowest
group by assessmentcategory,catweight) T
May 23, 2012 at 5:41 am
Can you post the execution plan for the query? It's hard to suggest improvements not knowing the structure of your system, tables, indexes, constraints, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2012 at 7:11 am
Grant Fritchey (5/23/2012)
Can you post the execution plan for the query? It's hard to suggest improvements not knowing the structure of your system, tables, indexes, constraints, etc.
Actually Grant I don't see even the slightest hint of a question from the OP - it is just a bunch of code. No request about what if anything needs to be fixed or addressed! 🙂
I will say that if this is used the performance of the query will likely be horrendously bad given the complexity there.
OP:
1) tell us what you need help with
2) give us table and index definitions - including for the table you hit with this UDF. We also need row counts per table at least
3) as Grant said, give us the query plan, although I am not sure if the guts of the UDF will be in it
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 24, 2012 at 8:23 am
TheSQLGuru (5/24/2012)
Actually Grant I don't see even the slightest hint of a question from the OP - it is just a bunch of code. No request about what if anything needs to be fixed or addressed! 🙂
The question is in the title of the thread.
Please I would like to improve the performance of the following function
To the OP, take a look at this article about posting performance problems.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2012 at 9:16 am
Sean Lange (5/24/2012)
TheSQLGuru (5/24/2012)
Actually Grant I don't see even the slightest hint of a question from the OP - it is just a bunch of code. No request about what if anything needs to be fixed or addressed! 🙂
The question is in the title of the thread.
DOH!! :hehe:
I am betting this one goes well beyond a forum thread for assistance though. that is a nasty UDF, and that doesn't take into account the query it is used in either...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 24, 2012 at 9:33 am
TheSQLGuru (5/24/2012)
Sean Lange (5/24/2012)
TheSQLGuru (5/24/2012)
Actually Grant I don't see even the slightest hint of a question from the OP - it is just a bunch of code. No request about what if anything needs to be fixed or addressed! 🙂
The question is in the title of the thread.
DOH!! :hehe:
I am betting this one goes well beyond a forum thread for assistance though. that is a nasty UDF, and that doesn't take into account the query it is used in either...
I tend to agree with you. It is rather nasty. Looks like a potential candidate for breaking into pieces to make it easier to work with. Certainly could be a big time sink through the forums. 😎
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2012 at 9:37 am
Sarsoura (5/22/2012)
This function gets the score up to now for students (we are using categories and some of the categories have deletelowest to specify drop lowest of assessments in this category.Thanks in advance
ALTER FUNCTION [dbo].[fn_UpToNowPercentage_dropL_table]
( @SectionID int,
@StudentID int
)
RETURNS table
AS
return
(
select SUM( (catweight*totalscore*1.0)/(case when TotalMaxPoints =0
then 1
else TotalMaxPoints
end))*100.0/SUM(catweight) as perc1
from
( select catweight,assessmentcategory,
sum(score) as totalscore,sum(
(case when gradestatus='C' then 0
else maxpoint
end)) as totalmaxpoints
from (select
AP.AssessmentID,
GradeStatus,
A.points
as maxpoint,
AP.points as score,
ROW_NUMBER()
over(
partition by studentid,A.sectionid,Assessmentcategory
order by (ap.points*1.0/a.points)) as rownumber ,
assessmentcategory,
CatWeight,
deletelowest
fromGradeBook.Assessment A
inner join
GradeBook.AssessmentPoint AP
on
A.AssessmentID=AP.assessmentid
inner join
GradeBook.GBCategory GC
on
A.AssessmentCategory=GC.CategoryID
where A.SectionID=@sectionid
and studentid=@studentid
and a.points >0
and ap.points is not null
and a.deleted=0
and
ap.points >=0
)S
where S.rownumber >deletelowest
group by assessmentcategory,catweight) T
How abut that, if you put the [ code="sql" ] [ /code ] tags around what was posted, it is somewhat formatted.
May 24, 2012 at 10:00 am
Sean Lange (5/24/2012)
TheSQLGuru (5/24/2012)
Sean Lange (5/24/2012)
TheSQLGuru (5/24/2012)
Actually Grant I don't see even the slightest hint of a question from the OP - it is just a bunch of code. No request about what if anything needs to be fixed or addressed! 🙂
The question is in the title of the thread.
DOH!! :hehe:
I am betting this one goes well beyond a forum thread for assistance though. that is a nasty UDF, and that doesn't take into account the query it is used in either...
I tend to agree with you. It is rather nasty. Looks like a potential candidate for breaking into pieces to make it easier to work with. Certainly could be a big time sink through the forums. 😎
Definitely looks like a fun one to tackle professionally though!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 24, 2012 at 10:24 am
For the OP. We really could use more information from you to help you improve this function. We need the DDL (CREATE TABLE statements) for each of the tables including all indexes defined on the tables, sample data for the tables (sample data, not real production data) as a series of INSERT INTO statements for each table (only about 5 to 10 rows of data each), expected results based on the sample data.
Last, but not least, the actual execution plan when this function is run.
Just a note, I have been looking at the code a bit and I have a small suggestion. You should use the table alias on all columns in the code, not just those that would be ambiguous without it. This helps others looking at the code to know which tables the columns are being pulled from without have to know this by experience (working with the database) or having to look at the table definitions (access to the database).
May 24, 2012 at 10:27 am
Thanks a lot.
Those links were helpfull.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply