Query not scalable? Any ideas? - 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)

Query not scalable? Any ideas?

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2010-06-17 : 23:19:15

This query takes 5 seconds to run when there are 500 employees and 20 seconds to run when there are 2000 employees. It simply doesn't scale. I've run it through the database tuning adviser and it didn't make any recommendations in terms of indexes. Is there a better way to write this?


SELECT DISTINCT 'View...'
                AS
                [View],
                students.studentsid,
                lastname,
                firstname,
                location,
                employeenum,
                username,
                password,
                email,
                address,
                city,
                province,
                country,
                postalcode,
                phonenumber,
                dateadded,
                (SELECT CASE totalcourses.totalcount
                          WHEN 0 THEN 0
                          ELSE CAST(( completedcourses.completedcount * 1 ) /
                                           ( totalcourses.totalcount * 1.00 ) *
                                                100 AS DECIMAL(5, 2))
                        END
                 FROM   (SELECT COUNT(subscription.courseid) AS totalcount
                         FROM   subscription
                         WHERE  subscription.studentsid = students.studentsid)
                        AS totalcourses,
                        (SELECT COUNT(subscription.courseid) AS completedcount
                         FROM   subscription
                         WHERE  subscription.studentsid = students.studentsid
                                AND subscription.completed IS NOT NULL) AS
                        completedcourses) AS
                percentcompleted,
                (SELECT TOP 1 departments.departmentname
                 FROM   departments
                 WHERE  departments.departmentid = students.departmentid)
                AS department,
                (SELECT COUNT(subscription.courseid)
                 FROM   subscription
                 WHERE  subscription.studentsid = students.studentsid
                        AND students.activestatus IN ( 1 ))
                AS subscriptions,
                (SELECT COUNT(subscription.courseid)
                 FROM   subscription
                 WHERE  subscription.studentsid = students.studentsid)
                AS totalsubscriptions,
                (SELECT SUM(educationalunits)
                 FROM   courses
                        INNER JOIN subscription
                          ON courses.courseid = subscription.courseid
                 WHERE  subscription.studentsid = students.studentsid
                        AND subscription.status = 2)
                AS units,
                (SELECT '$' + CONVERT(VARCHAR(12), SUM(CONVERT(MONEY, seatprice)
                              ), 1)
                 FROM   courses
                        INNER JOIN subscription
                          ON courses.courseid = subscription.courseid
                 WHERE  subscription.studentsid = students.studentsid)
                AS seatprice,
                (SELECT '$' + CONVERT(VARCHAR(12), SUM(CONVERT(MONEY,
                              companycost)), 1)
                 FROM   courses
                        INNER JOIN subscription
                          ON courses.courseid = subscription.courseid
                 WHERE  subscription.studentsid = students.studentsid)
                AS companycost,
                (SELECT '$' + CONVERT(VARCHAR(12), SUM(CONVERT(MONEY,
                              companycost)), 1)
                 FROM   courses
                        INNER JOIN subscription
                          ON courses.courseid = subscription.courseid
                 WHERE  subscription.studentsid = students.studentsid
                        AND students.activestatus IN ( 1 ))
                AS completedcompanycost,
                (SELECT SUM(companytime) / 60
                 FROM   courses
                        INNER JOIN subscription
                          ON courses.courseid = subscription.courseid
                 WHERE  subscription.studentsid = students.studentsid)
                AS companytime,
                (SELECT SUM(companytime) / 60
                 FROM   courses
                        INNER JOIN subscription
                          ON courses.courseid = subscription.courseid
                 WHERE  subscription.studentsid = students.studentsid
                        AND students.activestatus IN ( 1 ))
                AS completedcompanytime,
                (SELECT SUM(employeetime) / 60
                 FROM   courses
                        INNER JOIN subscription
                          ON courses.courseid = subscription.courseid
                 WHERE  subscription.studentsid = students.studentsid)
                AS employeetime,
                (SELECT SUM(employeetime) / 60
                 FROM   courses
                        INNER JOIN subscription
                          ON courses.courseid = subscription.courseid
                 WHERE  subscription.studentsid = students.studentsid
                        AND students.activestatus IN ( 1 ))
                AS completedemployeetime,
                (SELECT '$' + CONVERT(VARCHAR(12), SUM(CONVERT(MONEY,
                              employeecost)), 1)
                 FROM   courses
                        INNER JOIN subscription
                          ON courses.courseid = subscription.courseid
                 WHERE  subscription.studentsid = students.studentsid)
                AS employeecost,
                (SELECT '$' + CONVERT(VARCHAR(12), SUM(CONVERT(MONEY,
                              employeecost)), 1)
                 FROM   courses
                        INNER JOIN subscription
                          ON courses.courseid = subscription.courseid
                 WHERE  subscription.studentsid = students.studentsid
                        AND students.activestatus IN ( 1 ))
                AS completedemployeecost,
                (SELECT COUNT(*)
                 FROM   subscription
                 WHERE  subscription.studentsid = students.studentsid
                        AND status = 2
                        AND students.activestatus IN ( 1 ))
                AS numcompleted,
                students.extra1,
                students.extra2,
                students.extra3,
                students.extra4,
                students.extra5,
                students.extra6,
                students.extra7,
                students.extra8,
                students.extra9,
                students.extra10,
                students.extra11,
                students.extra12,
                students.extra13,
                students.extra14,
                students.extra15,
                students.extra16,
                students.extra17,
                students.extra18,
                students.extra19,
                students.extra20,
                students.extra21,
                students.extra22,
                students.extra23,
                students.extra24,
                students.extra25,
                students.extra26,
                students.extra27,
                students.extra28,
                students.extra29,
                students.extra30,
                ( students.lastloggedin )
                AS lastloggedin
FROM   students
       INNER JOIN subscription
         ON subscription.studentsid = students.studentsid
WHERE  clientid = 0306
       AND students.activestatus IN ( 1 )
ORDER  BY department ASC;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources