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 write this query?

Author  Topic 

ferrethouse
Constraint Violating Yak Guru

352 Posts
Posted - 2010-05-16 : 23:17:47
It runs fine when the number of records involved aren't too big but it does not scale well...


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
                                )                                  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 SUM(seatprice)
                         FROM   courses
                                INNER JOIN subscription
                                  ON courses.courseid = subscription.courseid
                         WHERE  subscription.studentsid = students.studentsid)                       AS seatprice,
                        (SELECT SUM(companycost)
                         FROM   courses
                                INNER JOIN subscription
                                  ON courses.courseid = subscription.courseid
                         WHERE  subscription.studentsid = students.studentsid)                       AS companycost,
                        (SELECT SUM(companycost)
                         FROM   courses
                                INNER JOIN subscription
                                  ON courses.courseid = subscription.courseid
                         WHERE  subscription.studentsid = students.studentsid
                                )                                  AS completedcompanycost,
                        (SELECT SUM(companytime)
                         FROM   courses
                                INNER JOIN subscription
                                  ON courses.courseid = subscription.courseid
                         WHERE  subscription.studentsid = students.studentsid)                       AS companytime,
                        (SELECT SUM(companytime)
                         FROM   courses
                                INNER JOIN subscription
                                  ON courses.courseid = subscription.courseid
                         WHERE  subscription.studentsid = students.studentsid
                                )                                  AS completedcompanytime,
                        (SELECT SUM(employeetime)
                         FROM   courses
                                INNER JOIN subscription
                                  ON courses.courseid = subscription.courseid
                         WHERE  subscription.studentsid = students.studentsid)                       AS employeetime,
                        (SELECT SUM(employeetime)
                         FROM   courses
                                INNER JOIN subscription
                                  ON courses.courseid = subscription.courseid
                         WHERE  subscription.studentsid = students.studentsid
                                )                                  AS completedemployeetime,
                        (SELECT SUM(employeecost)
                         FROM   courses
                                INNER JOIN subscription
                                  ON courses.courseid = subscription.courseid
                         WHERE  subscription.studentsid = students.studentsid)                       AS employeecost,
                        (SELECT SUM(employeecost)
                         FROM   courses
                                INNER JOIN subscription
                                  ON courses.courseid = subscription.courseid
                         WHERE  subscription.studentsid = students.studentsid
                                )                                  AS completedemployeecost,
                        (SELECT COUNT(*)
                         FROM   subscription
                         WHERE  subscription.studentsid = students.studentsid
                                AND status = 2
                                )                                  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 )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-17 : 12:18:00
can you replace the subqueries with joins and then try?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-05-17 : 22:20:27
[code]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,

D1.department,
D2.subscriptions,
D3.totalsubscriptions,
D4.units,
D5.seatprice,
D6.companycost,
D7.completedcompanycost,
D8.companytime,
D9.completedcompanytime,
D10.employeetime,
D11.completedemployeetime,
D12.employeecost,
D13.completedemployeecost,
D14.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
OUTER APPLY
(SELECT TOP 1 departments.departmentname
FROM departments
WHERE departments.departmentid = students.departmentid)D1(department)
OUTER APPLY
(SELECT COUNT(subscription.courseid)
FROM subscription
WHERE subscription.studentsid = students.studentsid)D2(subscriptions)
OUTER APPLY
(SELECT COUNT(subscription.courseid)
FROM subscription
WHERE subscription.studentsid = students.studentsid)D3(totalsubscriptions)
OUTER APPLY
(SELECT SUM(educationalunits)
FROM courses
INNER JOIN subscription
ON courses.courseid = subscription.courseid
WHERE subscription.studentsid = students.studentsid
AND subscription.status = 2)D4(units)
OUTER APPLY
(SELECT SUM(seatprice)
FROM courses
INNER JOIN subscription
ON courses.courseid = subscription.courseid
WHERE subscription.studentsid = students.studentsid)D5(seatprice)
OUTER APPLY
(SELECT SUM(companycost)
FROM courses
INNER JOIN subscription
ON courses.courseid = subscription.courseid
WHERE subscription.studentsid = students.studentsid)D6(companycost)
OUTER APPLY
(SELECT SUM(companycost)
FROM courses
INNER JOIN subscription
ON courses.courseid = subscription.courseid
WHERE subscription.studentsid = students.studentsid)D7(completedcompanycost)
OUTER APPLY
(SELECT SUM(companytime)
FROM courses
INNER JOIN subscription
ON courses.courseid = subscription.courseid
WHERE subscription.studentsid = students.studentsid)D8(companytime)
OUTER APPLY
(SELECT SUM(companytime)
FROM courses
INNER JOIN subscription
ON courses.courseid = subscription.courseid
WHERE subscription.studentsid = students.studentsid)D9(completedcompanytime)
OUTER APPLY
(SELECT SUM(employeetime)
FROM courses
INNER JOIN subscription
ON courses.courseid = subscription.courseid
WHERE subscription.studentsid = students.studentsid)D10(employeetime)
OUTER APPLY
(SELECT SUM(employeetime)
FROM courses
INNER JOIN subscription
ON courses.courseid = subscription.courseid
WHERE subscription.studentsid = students.studentsid)D11(completedemployeetime)
OUTER APPLY
(SELECT SUM(employeecost)
FROM courses
INNER JOIN subscription
ON courses.courseid = subscription.courseid
WHERE subscription.studentsid = students.studentsid)D12(employeecost)
OUTER APPLY
(SELECT SUM(employeecost)
FROM courses
INNER JOIN subscription
ON courses.courseid = subscription.courseid
WHERE subscription.studentsid = students.studentsid)D13(completedemployeecost)
OUTER APPLY
(SELECT COUNT(*)
FROM subscription
WHERE subscription.studentsid = students.studentsid
AND status = 2
)D14(numcompleted)
WHERE clientid = 0306
AND students.activestatus IN ( 1 )


[/code]

______________________
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-05-18 : 11:06:39
I don't think exchanging a correlated sub query for an outer apply is going to help as they are, more or less, the same thing. Since all the correlated sub queries seem to use Courses and Subscription (or most do) you might be able to join to those to eliminate the sub queries.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-18 : 14:09:30
quote:
Originally posted by Lamprey

I don't think exchanging a correlated sub query for an outer apply is going to help as they are, more or less, the same thing. Since all the correlated sub queries seem to use Courses and Subscription (or most do) you might be able to join to those to eliminate the sub queries.

Yeah...That was what i thought too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-05-18 : 16:02:26
But I think the multiple OUTER APPLY table operator have a better performance as scalar correlated subqueries in SELECT clause.

______________________
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-05-18 : 16:41:28
quote:
Originally posted by ms65g

But I think the multiple OUTER APPLY table operator have a better performance as scalar correlated subqueries in SELECT clause.

______________________

That may or may not be true. In some cases the sub query will perform better in some cases (I assume) the outer apply will. But they are so close in performance that it's pretty much a wash. Personally, I've never seen an outer apply actually perform better, but I'm guessing there is a case where it does.

If you want a sample I can post one. But, you might want to perform your own tests and see what kind of results you get.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site