sub query hell - 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

SQL Server 2005 Forums

Transact-SQL (2005)

sub query hell

Author

Topic

DiskJunky
Starting Member

4 Posts

Posted - 2009-01-29 : 13:34:10

Ok, basically I'm trying to create a report query where I cannot do a JOIN to a table that I need to return data from because it will introduce duplicates. Without getting into the complexities of WHY I can't do this, let's just take it as a given here. In order to extract information from this table and use it in my report query, I'm calling a table function dbo.GetCoachQualifications(). Unfortunately, in order to return several fields I have to call this function multiple times in the field list section of the SELECT clause and also in the WHERE clause where I need to filter it. An example of what I need is something like;

SELECT *,
       (SELECT TOP 1 NGBID, PersonID, CoachLevelID, NGBCoachSpecialityID, DateTimeAwarded
            FROM dbo.GetCoachQualifications(nm.NGBID, nm.PersonID))
    FROM NGBMembers nm

The idea being that I want to return the most recent historical record for each person in NGBMembers - which may or may not exist. You'd think that I'd be able to LEFT JOIN on it but SQL Server doesn't let you. I cannot LEFT JOIN the table directly as it's storing historical data for each person and I only want the most recent to come out. At the moment, I'm calling this function once every time I reference a single field out of the most recent record for a given person. Needless to say, performance has gone right down the drain. Examples of what I currently have are;


               n.ShortName AS NGBShortName,
               n.LongName AS NGBLongName,
               (SELECT cl.Name
                    FROM (SELECT TOP 1 CoachLevelID
                            FROM dbo.GetCoachQualifications(m.NGBID, m.PersonID)) cq
                    JOIN CoachLevels cl
                      ON cl.CoachLevelID = cq.CoachLevelID
               ) AS CoachLevel,
               (SELECT ncs.Name
                    FROM (SELECT TOP 1 NGBCoachSpecialityID
                            FROM dbo.GetCoachQualifications(m.NGBID, m.PersonID)) cq
                    JOIN NGBCoachSpecialities ncs
                      ON ncs.NGBCoachSpecialityID = cq.NGBCoachSpecialityID) AS CoachSpeciality,
               (SELECT tl.Name
                    FROM (SELECT TOP 1 TutorLevelID
                            FROM dbo.GetTutorQualifications(m.NGBID, m.PersonID)) tq
                    JOIN TutorLevels tl
                      ON tl.TutorLevelID = tq.TutorLevelID) AS TutorLevel
            FROM People p

--in the WHERE clause;...
              AND (@CoachLevelID IS NULL OR @CoachLevelID = (SELECT TOP 1 CoachLevelID
                                                                 FROM dbo.GetCoachQualifications(m.NGBID, m.PersonID)))
              AND (@TutorLevelID IS NULL OR @TutorLevelID = (SELECT TOP 1 TutorLevelID
                                                                 FROM dbo.GetTutorQualifications(m.NGBID, m.PersonID)))
              AND (@CoachCertDateFrom IS NULL OR EXISTS(SELECT DateTimeAwarded 
                                                        FROM (SELECT TOP 1 DateTimeAwarded
                                                                FROM dbo.GetCoachQualifications(m.NGBID, m.PersonID)) As cq
                                                        WHERE @CoachCertDateFrom <= cq.DateTimeAwarded
                                                          AND (@CoachCertDateTo is NULL OR @CoachCertDateTo >= cq.DateTimeAwarded))
                   )
              AND (@TutorCertDateFrom IS NULL OR EXISTS(SELECT DateTimeAwarded
                                                            FROM (SELECT TOP 1 DateTimeAwarded
                                                                    FROM dbo.GetTutorQualifications(m.NGBID, m.PersonID)) As tq
                                                        WHERE @TutorCertDateFrom <= tq.DateTimeAwarded
                                                          AND (@TutorCertDateTo is NULL OR @TutorCertDateTo >= tq.DateTimeAwarded))
                   )

Hideous. Any thoughts on optomizing this monster?

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-29 : 14:15:43

Some possibilities:
1) You can copy the code out of the functions and populate temp tables or table variables from the queries. You can then perform the SELECT TOP 1 on the temp tables instead of the table functions.

2) Create two new functions that are basically copies of the existing function and use SELECT TOP 1 in the new functions. You could then remove the SELECT TOP 1 from your subqueries on the table functions.

DiskJunky
Starting Member

4 Posts

Posted - 2009-01-30 : 04:23:38

Can you give an example of the Temp Table approach? I've been having a bit of trouble in figuring out how I go about that

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-30 : 04:34:47

see books online for temporary table
#table, ##table (global) are temporary tables
@table is table variable
syntax:- for declartion of tables
create table #temptable(id int,name varchar(32)
declare @tab table (id int,ename varchar(32))

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-30 : 08:46:07

It's really hard to make some sense out of this when we don't know what the function definitions look like and don't have any data to test on. Is it possible for you to create a functional test scenario which breaks down your problem in to smaller chunks? You might also want to look in to common table expressions (CTE), they might be helpful.

- Lumbago

DiskJunky
Starting Member

4 Posts

Posted - 2009-01-30 : 10:24:48

The problem is a little complex in that the Qualifications tables are historical holding information for each NGB+Person. The query is returning all historical data and I'm only looking at the most recent record for each person. Effectively, I want to build a table (temporary/derived/cte) that has a list of each NGB+Person+Qualification were each record relates ONLY to the most recent qualification for that NGB+Person. This table, when calculated is then joined into the a larger report query. My problem is building this table of most recent qualifications. The existing function (ignore the Specialities in this example) as it stands is;


ALTER FUNCTION [dbo].[GetCoachQualifications] 
    (@NGBID int,
     @PersonID int)
RETURNS @CoachQualifications TABLE
(
    PKID int PRIMARY KEY (PKID DESC),
    NGBID int,
    PersonID int,
    CoachLevelID int,
    CoachLevelName nvarchar(256),
    CoachLevelOrdinal int,
    NGBCoachSpecialityID int,
    NGBCoachSpecialityName nvarchar(256),
    DateTimeAwarded datetime,
    DateTimeRecorded datetime
)
AS
BEGIN

	INSERT @CoachQualifications 
        (PKID, NGBID, PersonID, CoachLevelID, 
         CoachLevelName, CoachLevelOrdinal, NGBCoachSpecialityID, 
         NGBCoachSpecialityName, DateTimeAwarded, DateTimeRecorded)
        SELECT a.PKID, a.NGBID, a.PersonID, a.CoachLevelID, 
               cl.Name, cl.Ordinal, a.NGBCoachSpecialityID, s.Name,
               a.DateTimeAwarded, a.DateTimeRecorded
            FROM AwardedCoachQualifications a
            JOIN CoachLevels cl
              ON a.CoachLevelID = cl.CoachLevelID
            LEFT JOIN NGBCoachSpecialities s
              ON s.NGBCoachSpecialityID = a.NGBCoachSpecialityID
            WHERE a.NGBID = @NGBID AND a.PersonID = @PersonID
    
    RETURN
END

Sample data in the table would be like;


PKID | NGBID | PersonID | CoachLevel 
-----+-------+----------+-------------------
1    | 1     | 1        | 1
2    | 1     | 1        | 2
3    | 1     | 2        | 1
4    | 2     | 1        | 1
5    | 2     | 2        | 1
6    | 2     | 2        | 2
7    | 2     | 2        | 3

What I'd want returned is the most recent qualification for each NGB/Person so that I get;


PKID | NGBID | PersonID | QualificationType 
-----+-------+----------+-------------------
2    | 1     | 1        | 2
3    | 1     | 2        | 1
4    | 2     | 1        | 1
7    | 2     | 2        | 3

I hope this makes it a little clearer. The data in my table may have many qualifications in many different NGBs stored historically even though I only have 2 in the example above.

(note, the names above in the proc are delibrately changed but should be compilable)

DiskJunky
Starting Member

4 Posts

Posted - 2009-01-30 : 11:03:20

never mind, I think I solved the problem by turning the problem on its head and performing the query without the function so that I get;


WITH CoachQualifications(NGBID, PersonID, CoachLevelID, NGBCoachSpecialityID, DateTimeAwarded) AS
(
    SELECT acq.NGBID, acq.PersonID, acq.CoachLevelID, acq.NGBCoachSpecialityID, 
           acq.DateTimeAwarded
        FROM NGBMembers nm
        JOIN AwardedCoachQualifications acq
          ON acq.NGBID = nm.NGBID AND acq.PersonID = nm.PersonID
        WHERE (acq.AwardedCoachQualificationID = 
                    (SELECT TOP 1 cq.AwardedCoachQualificationID
                       FROM AwardedCoachQualifications cq
                       WHERE cq.NGBID = nm.NGBID AND cq.PersonID = nm.PersonID
                       ORDER BY cq.AwardedCoachQualificationID DESC
                     )
               )
)

SELECT *
    FROM CoachQualifications
    ORDER BY PersonID, NGBID

I guess the breakthrough was instead of thinking "how do I extract the data I need", I thought "how do I exclude the data I DON'T need". The query runs with the function but takes approx 29 seconds. Removing the function with a nested SELECT gets the same results in < 1 second

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 12:21:26

wont this be enough?


SELECT PKID, NGBID, PersonID, CoachLevelID, 
               Name, Ordinal,NGBCoachSpecialityID, Name,
               DateTimeAwarded, DateTimeRecorded
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY a.NGBID,a.PersonID ORDER BY a.PKID DESC) AS Seq,
a.PKID, a.NGBID, a.PersonID, a.CoachLevelID, 
               cl.Name, cl.Ordinal, a.NGBCoachSpecialityID, s.Name,
               a.DateTimeAwarded, a.DateTimeRecorded
            FROM AwardedCoachQualifications a
            JOIN CoachLevels cl
              ON a.CoachLevelID = cl.CoachLevelID
            LEFT JOIN NGBCoachSpecialities s
              ON s.NGBCoachSpecialityID = a.NGBCoachSpecialityID
            WHERE a.NGBID = @NGBID AND a.PersonID = @PersonID
)t
WHERE Seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources