Is there a more efficient way to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Is there a more efficient way to do this?

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-12-24 : 00:45:12

I have a query that called a function called FuncDepartmentParents. The problem is that if there are many records returned it could call this function 100,000 times and that appears to be stressing the server. I'll include the query and the function code below. Is there a better way to go about this?

Query...

SELECT   *
FROM     (SELECT   TOP 24 *
          FROM     (SELECT   TOP 50 6055                                                                                          AS courseid,
                                    'View...'                                                                                     AS [View],
                                    subscription.studentsid,
                                    Cast((Cast((SELECT Count(DISTINCT lessonid)
                                                FROM   activity
                                                WHERE  studentsid = subscription.studentsid
                                                       AND courseid = 6055
                                                       AND status = 2
                                                       AND lessonid IS NOT NULL) AS DECIMAL(32,2)) / CASE
                                                                                                       WHEN ((SELECT Count(DISTINCT lessonid)
                                                                                                              FROM   lessons
                                                                                                              WHERE  courseid = 6055) = 0)
                                                                                                       THEN 1
                                                                                                       ELSE (SELECT Count(DISTINCT lessonid)
                                                                                                             FROM   lessons
                                                                                                             WHERE  courseid = 6055)
                                                                                                     END) * 100 AS DECIMAL(32,2)) AS percentcomplete,
                                    Max(subscription.subscriptionnotes)                                                           AS subscriptionnotes,
                                    Max(subscription.subid)                                                                       AS subid,
                                    Max(students.lastname)                                                                        AS lastname,
                                    Max(students.firstname)                                                                       AS firstname,
                                    Max(students.username)                                                                        AS username,
                                    Max(students.departmentid)                                                                    AS departmentid,
                                    (SELECT departmentname
                                     FROM   departments
                                     WHERE  Max(students.departmentid) = departments.departmentid) AS departmentname,
                                    Max(students.location)                                                                        AS location,
                                    Max(students.employeenum)                                                                     AS employeenum,
                                    Max(students.email)                                                                           AS email,
                                    Max(students.password)                                                                        AS password,
                                    Max(students.address)                                                                         AS address,
                                    Max(students.city)                                                                            AS city,
                                    Max(students.province)                                                                        AS province,
                                    Max(students.postalcode)                                                                      AS postalcode,
                                    Max(students.country)                                                                         AS country,
                                    Max(students.phonenumber)                                                                     AS phonenumber,
                                    Max(subscription.completed)                                                                   AS completed,
                                    Max(subscription.status)                                                                      AS status,
                                    Max(lessons.lessontitle)                                                                      AS lessontitle,
                                    Max(subscription.status) - 1                                                                  AS complete,
                                    Max(subscription.startdate)                                                                   AS startdate,
                                    Min(acceptedtermsdate)                                                                        AS acceptedtermsdate,
                                    Max(certificates.certificateid)                                                               AS certificateid,
                                    Max(certificates.valid)                                                                       AS CERTIFICATE,
                                    Max(certificates.expires)                                                                     AS expires,
                                    Max(activity.score)                                                                           AS exam,
                                    Count(activity.activityid)                                                                    AS examattempts,
                                    Avg(activity.score)                                                                           AS avgexamscore,
                                    Max(statusdesc)                                                                               AS statusdesc,
                                    Max(students.activestatus)                                                                    AS activestatus,
                                    Max(activity.loginid)                                                                         AS loginid,
                                    (SELECT TOP 1 adminlogins.contactlastname + ', ' + adminlogins.contactfirstname
                                     FROM   adminlogins
                                     WHERE  adminlogins.loginid = Max(activity.loginid)) AS proctor,
                                    Max(bulkname)                                                                                 AS bulkname,
                                    NULL                                                                                          AS progress,
                                    Max(students.extra1)                                                                          AS extra1,
                                    Max(students.extra2)                                                                          AS extra2,
                                    Max(students.extra3)                                                                          AS extra3,
                                    Max(students.extra4)                                                                          AS extra4,
                                    Max(students.extra5)                                                                          AS extra5,
                                    Max(students.extra6)                                                                          AS extra6,
                                    Max(students.extra7)                                                                          AS extra7,
                                    Max(students.extra8)                                                                          AS extra8,
                                    Max(students.extra9)                                                                          AS extra9,
                                    Max(students.extra10)                                                                         AS extra10,
                                    Max(students.extra11)                                                                         AS extra11,
                                    Max(students.extra12)                                                                         AS extra12,
                                    Max(students.extra13)                                                                         AS extra13,
                                    Max(students.extra14)                                                                         AS extra14,
                                    Max(students.extra15)                                                                         AS extra15,
                                    Max(courses.expiretype)                                                                       AS expiretype,
                                    Max(courses.daystoexpire)                                                                     AS daystoexpire,
                                    Max(courses.dateexpires)                                                                      AS expiredate,
                                    Max(students.lastloggedin)                                                                    AS lastloggedin,
                                    Max(desctypes.typedesc)                                                                       AS typedesc
                    FROM     subscription
                             LEFT JOIN descstatus
                               ON descstatus.status = subscription.status
                             LEFT JOIN certificates
                               ON subscription.studentsid = certificates.studentsid
                                  AND certificates.courseid = 6055
                             LEFT JOIN activity
                               ON activity.courseid = subscription.courseid
                                  AND activity.studentsid = subscription.studentsid
                                  AND activity.lessonid IN (SELECT lessons.lessonid
                                                            FROM   lessons
                                                            WHERE  lessons.TYPE = 2
                                                                   AND lessons.courseid = 6055)
                             LEFT JOIN lessons
                               ON lessons.lessonid = subscription.lessonid
                             INNER JOIN students
                               ON students.studentsid = subscription.studentsid
                             INNER JOIN desctypes
                               ON students.activestatus = desctypes.TYPE
                                  AND desctypes.typegroup = 'User'
                             LEFT JOIN subscriptionbulk
                               ON subscriptionbulk.bulkid = subscription.bulkid
                             LEFT JOIN courses
                               ON courses.courseid = subscription.courseid
                    WHERE    subscription.courseid = 6055
                             AND students.clientid = 0130
                             AND Charindex('|0|',dbo.Funcdepartmentparents(students.departmentid)) > 0
                             AND (0 = 0
                                   OR subscription.lessonid = 0)
                             AND students.activestatus IN (1)
                    GROUP BY subscription.studentsid,
                             students.firstname,
                             students.lastname,
                             students.employeenum
                    HAVING   (Max(descstatus.status) = Max(subscription.status))
                    ORDER BY avgexamscore ASC,
                             studentsid ASC) AS firstsub
          ORDER BY avgexamscore DESC,
                   studentsid DESC) AS secondsub
ORDER BY avgexamscore ASC,
         studentsid ASC;

Function...


ALTER  FUNCTION [dbo].[FuncDepartmentParents] (@DeptID int)
RETURNS varchar(500)

AS

BEGIN

DECLARE @tmp varchar(500)
Set @tmp = '|' + Convert(varchar(10), @DeptID) + "|"

WHILE (@DeptID > 0)

BEGIN

	Set @DeptID = (SELECT A.DepartmentID FROM Departments A INNER JOIN Departments B ON A.DepartmentID = B.ParentDeptID AND B.DepartmentID = @DeptID)
	If @DeptID = NULL Set @DeptID = 0
	SET @tmp = @tmp + '|' + Convert(varchar(10), @DeptID) + "|"


END


RETURN @tmp
END

Sachin.Nand

2937 Posts

Posted - 2009-12-24 : 01:08:22

Just a wild guess.Try this



select * from
(
Your long query without the function

)as seconsub where Charindex('|0|',dbo.Funcdepartmentparents(seconsub.departmentid)) > 0
ORDER BY avgexamscore ASC,
         studentsid ASC;

PBUH

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-12-24 : 02:05:34

Interesting idea but it results in the same IO stats and the execution plans are almost identical and both have a cost of 50% so it doesn't seem to yield a performance improvement.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-12-24 : 04:00:53

try this

SELECT   *
FROM     (SELECT   TOP 24 *
          FROM     (SELECT   TOP 50 6055                                                                                          AS courseid,
                                    'View...'                                                                                     AS [View],
                                    subscription.studentsid,
                                    Cast((Cast((SELECT Count(DISTINCT lessonid)
                                                FROM   activity
                                                WHERE  studentsid = subscription.studentsid
                                                       AND courseid = 6055
                                                       AND status = 2
                                                       AND lessonid IS NOT NULL) AS DECIMAL(32,2)) / CASE
                                                                                                       WHEN ((SELECT Count(DISTINCT lessonid)
                                                                                                              FROM   lessons
                                                                                                              WHERE  courseid = 6055) = 0)
                                                                                                       THEN 1
                                                                                                       ELSE (SELECT Count(DISTINCT lessonid)
                                                                                                             FROM   lessons
                                                                                                             WHERE  courseid = 6055)
                                                                                                     END) * 100 AS DECIMAL(32,2)) AS percentcomplete,
                                    Max(subscription.subscriptionnotes)                                                           AS subscriptionnotes,
                                    Max(subscription.subid)                                                                       AS subid,
                                    Max(students.lastname)                                                                        AS lastname,
                                    Max(students.firstname)                                                                       AS firstname,
                                    Max(students.username)                                                                        AS username,
                                    Max(students.departmentid)                                                                    AS departmentid,
                                    (SELECT departmentname
                                     FROM   departments
                                     WHERE  Max(students.departmentid) = departments.departmentid) AS departmentname,
                                    Max(students.location)                                                                        AS location,
                                    Max(students.employeenum)                                                                     AS employeenum,
                                    Max(students.email)                                                                           AS email,
                                    Max(students.password)                                                                        AS password,
                                    Max(students.address)                                                                         AS address,
                                    Max(students.city)                                                                            AS city,
                                    Max(students.province)                                                                        AS province,
                                    Max(students.postalcode)                                                                      AS postalcode,
                                    Max(students.country)                                                                         AS country,
                                    Max(students.phonenumber)                                                                     AS phonenumber,
                                    Max(subscription.completed)                                                                   AS completed,
                                    Max(subscription.status)                                                                      AS status,
                                    Max(lessons.lessontitle)                                                                      AS lessontitle,
                                    Max(subscription.status) - 1                                                                  AS complete,
                                    Max(subscription.startdate)                                                                   AS startdate,
                                    Min(acceptedtermsdate)                                                                        AS acceptedtermsdate,
                                    Max(certificates.certificateid)                                                               AS certificateid,
                                    Max(certificates.valid)                                                                       AS CERTIFICATE,
                                    Max(certificates.expires)                                                                     AS expires,
                                    Max(activity.score)                                                                           AS exam,
                                    Count(activity.activityid)                                                                    AS examattempts,
                                    Avg(activity.score)                                                                           AS avgexamscore,
                                    Max(statusdesc)                                                                               AS statusdesc,
                                    Max(students.activestatus)                                                                    AS activestatus,
                                    Max(activity.loginid)                                                                         AS loginid,
                                    (SELECT TOP 1 adminlogins.contactlastname + ', ' + adminlogins.contactfirstname
                                     FROM   adminlogins
                                     WHERE  adminlogins.loginid = Max(activity.loginid)) AS proctor,
                                    Max(bulkname)                                                                                 AS bulkname,
                                    NULL                                                                                          AS progress,
                                    Max(students.extra1)                                                                          AS extra1,
                                    Max(students.extra2)                                                                          AS extra2,
                                    Max(students.extra3)                                                                          AS extra3,
                                    Max(students.extra4)                                                                          AS extra4,
                                    Max(students.extra5)                                                                          AS extra5,
                                    Max(students.extra6)                                                                          AS extra6,
                                    Max(students.extra7)                                                                          AS extra7,
                                    Max(students.extra8)                                                                          AS extra8,
                                    Max(students.extra9)                                                                          AS extra9,
                                    Max(students.extra10)                                                                         AS extra10,
                                    Max(students.extra11)                                                                         AS extra11,
                                    Max(students.extra12)                                                                         AS extra12,
                                    Max(students.extra13)                                                                         AS extra13,
                                    Max(students.extra14)                                                                         AS extra14,
                                    Max(students.extra15)                                                                         AS extra15,
                                    Max(courses.expiretype)                                                                       AS expiretype,
                                    Max(courses.daystoexpire)                                                                     AS daystoexpire,
                                    Max(courses.dateexpires)                                                                      AS expiredate,
                                    Max(students.lastloggedin)                                                                    AS lastloggedin,
                                    Max(desctypes.typedesc)                                                                       AS typedesc
                    FROM     subscription
                             LEFT JOIN descstatus
                               ON descstatus.status = subscription.status
                             LEFT JOIN certificates
                               ON subscription.studentsid = certificates.studentsid
                                  AND certificates.courseid = 6055
                             LEFT JOIN activity
                               ON activity.courseid = subscription.courseid
                                  AND activity.studentsid = subscription.studentsid
                                  AND activity.lessonid IN (SELECT lessons.lessonid
                                                            FROM   lessons
                                                            WHERE  lessons.TYPE = 2
                                                                   AND lessons.courseid = 6055)
                             LEFT JOIN lessons
                               ON lessons.lessonid = subscription.lessonid
//----	Added this                               
                             INNER JOIN (SELECT * FROM students
										WHERE Charindex('|0|',dbo.Funcdepartmentparents(students.departmentid)) > 0) students
//----	Added this
                               ON students.studentsid = subscription.studentsid
                             INNER JOIN desctypes
                               ON students.activestatus = desctypes.TYPE
                                  AND desctypes.typegroup = 'User'
                             LEFT JOIN subscriptionbulk
                               ON subscriptionbulk.bulkid = subscription.bulkid
                             LEFT JOIN courses
                               ON courses.courseid = subscription.courseid
                    WHERE    subscription.courseid = 6055
                             AND students.clientid = 0130
                             AND (0 = 0
                                   OR subscription.lessonid = 0)
                             AND students.activestatus IN (1)
                    GROUP BY subscription.studentsid,
                             students.firstname,
                             students.lastname,
                             students.employeenum
                    HAVING   (Max(descstatus.status) = Max(subscription.status))
                    ORDER BY avgexamscore ASC,
                             studentsid ASC) AS firstsub
          ORDER BY avgexamscore DESC,
                   studentsid DESC) AS secondsub
ORDER BY avgexamscore ASC,
         studentsid ASC;

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-12-24 : 11:47:52

Thanks for the suggestions. The latest post also results in the same performance. Maybe it just can't be improved. Thanks for trying guys.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2009-12-24 : 12:00:06

This query can surely be improved. Can you post some DDL, a small sample data set, and the desired output which would allow folks to play with different approaches to optimization?

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2009-12-25 : 01:03:04

quote:
Originally posted by ehorn

This query can surely be improved. Can you post some DDL, a small sample data set, and the desired output which would allow folks to play with different approaches to optimization?

Absolutely. I'll do that on Monday when I'm back at work. Thanks. I'm less concerned about this query specifically as I am about the use of the specific function in the query (the one in bold). That function is used in many similar queries and seems to cause a lot of I/O. I'm really interested in knowing if there is a better way to approach what that function is doing (restricting access to records based on department permissions). But I would be curious to see what kind of optimizations you guys could come up with for the query as a whole. I love learning new optimization techniques.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources