SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 sub query hell
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DiskJunky
Starting Member

4 Posts

Posted - 01/29/2009 :  13:34:10  Show Profile  Reply with Quote
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

USA
90 Posts

Posted - 01/29/2009 :  14:15:43  Show Profile  Reply with Quote
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.
Go to Top of Page

DiskJunky
Starting Member

4 Posts

Posted - 01/30/2009 :  04:23:38  Show Profile  Reply with Quote
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
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 01/30/2009 :  04:34:47  Show Profile  Reply with Quote
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))
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 01/30/2009 :  08:46:07  Show Profile  Reply with Quote
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
Go to Top of Page

DiskJunky
Starting Member

4 Posts

Posted - 01/30/2009 :  10:24:48  Show Profile  Reply with Quote
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)

Edited by - DiskJunky on 01/30/2009 10:30:57
Go to Top of Page

DiskJunky
Starting Member

4 Posts

Posted - 01/30/2009 :  11:03:20  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/30/2009 :  12:21:26  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000