SQL Server Performance

Stored procudure without using cursors

Discussion in 'General Developer Questions' started by timbyng, Sep 16, 2003.

  1. timbyng New Member

    Hello,

    I'm having some problems designing a stored procedure for a report that I need. Basically, what I need to show on the report is client info (name, birth date, etc.), along with the historical information for that client based on a date range. The format of the data should be something like this (without the line feeds):


    Name , Birth Date, ..., Program , Team , ...
    --------------------------------------------
    Name1, 01/01/1970, ..., Program1, Team2, ...
    Name1, 01/01/1970, ..., Program2, Team3, ...
    Name1, 01/01/1970, ..., Program3, , ...

    Name2, 01/01/1980, ..., Program2, Team3, ...
    Name2, 01/01/1980, ..., , Team4, ...

    Name3, 01/01/1975, ..., Program1, , ...

    Name4, 01/01/1965, ..., , , ...
    The data will come from five main tables. The first table has the client information. The other 4 tables have supporting historical information. These 4 tables have 4 fields each. The fields are ID(auto), FromDate, ToDate, and ProgramID (the last field is different for each of the four tables).

    The problem I'm having is putting the information from the 4 historical tables in the correct rows. For example, if I have 3 records for one historical table, 2 for another and 0 for yet another, I want to show only 3 rows in total (with the empty fields at the bottom). Right now, my query returns more rows than I want.

    I was thinking of using cursors to accomplish this, but there must be a way to get the data I need without using cursors. Does anyone know how I can accomplish this without the use of cursors?

    Thanks,
    Tim
  2. gaurav_bindlish New Member

    Use full outer join to join the data from the tables. This way the fields for which no data is present will show NULL for the particular record.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. Twan New Member

    Hi Tim,

    I don't think that this is actually possible using a simple SQL query...

    Are you able to post the actual structure of the four tables, and how you are currently attempting to join them? I can't quite work out how you are joining the tables together (in terms of which fields are the joining fields...)

    Cheers
    Twan
  4. timbyng New Member

    Thanks for the unbelievably quick response Gaurav!<br /><br />(quick note: I forgot about the client ID field in the historical fields, duh!)<br /><br />I just tried the full outer join, but I'm still having the same problem as before (without using cursors). I created a test database with three tables: Client, ClientH1 and ClientH2 (I'll just deal with two of the 4 historical tables for now). The Client table has three fields: ClientID, FullName and BirthDate. The ClientH* tables have 5 fields: ID, ClientID, FromDate, ToDate and H*ID (H* = Historical table 1 and 2). I entered data similar to the data in my previous example in these tables.<br /><br />Here's my query with the full outer joins:<br /><pre><br />SELECT<br />C.FullName,<br />C.BirthDate,<br />H1.H1ID,<br />H2.H2ID<br />FROM<br />Client C<br />FULL OUTER JOIN ClientH1 H1 ON C.ClientID = H1.ClientID<br />FULL OUTER JOIN ClientH2 H2 ON C.ClientID = H2.ClientID<br /></pre><br />Here are the results (again, I'm showing the ID instead of the description for the historical tables):<br /><pre><br />FullName, BirthDate, H1ID, H2ID<br />Name1, 1970-01-01 00:00:00, 1, 2<br />Name1, 1970-01-01 00:00:00, 1, 3<br />Name1, 1970-01-01 00:00:00, 2, 2<br />Name1, 1970-01-01 00:00:00, 2, 3<br />Name1, 1970-01-01 00:00:00, 3, 2<br />Name1, 1970-01-01 00:00:00, 3, 3<br />Name2, 1980-01-01 00:00:00, 2, 3<br />Name2, 1980-01-01 00:00:00, 2, 4<br />Name3, 1975-01-01 00:00:00, 1, NULL<br />Name4, 1965-01-01 00:00:00, NULL, NULL<br /></pre><br />The results I would like to see would be something like this:<br /><pre><br />FullName, BirthDate, H1ID, H2ID<br />Name1, 1970-01-01 00:00:00, 1, 2<br />Name1, 1970-01-01 00:00:00, 2, 3<br />Name1, 1970-01-01 00:00:00, 3, NULL<br />Name2, 1980-01-01 00:00:00, 2, 3<br />Name2, 1980-01-01 00:00:00, NULL, 4<br />Name3, 1975-01-01 00:00:00, 1, NULL<br />Name4, 1965-01-01 00:00:00, NULL, NULL<br /></pre><br />P.S. Twan: I hope I was able to supply you with information you asked for. You probably couldn't figure out the joins because I forgot about the client ID. Let me know if you need any more info. I didn't think it was possible to do this without cursors at first, but I've recently heard that it was proven some time ago that anything you can do with cursors, you can do without. This will be a good test for me! [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Also, I can email anyone my test database if anyone wants to try it out.<br /><br />Thanks again,<br />Tim
  5. Twan New Member

    Hi Tim,

    To achieve this you need to have a sequence number for the client... either permanently in the table or by copying data into a temporary table and manipulating it...



    create table #client(
    clientid int not null,
    fullname varchar(20) not null,
    birthdate datetime not null
    )

    create table #ClientH1(
    clientid int not null,
    clientseq int not null,
    h1ID int not null
    )

    create table #ClientH2(
    clientid int not null,
    clientseq int not null,
    h2ID int not null
    )

    go

    insert into #client values ( 1, 'name1', '1970-01-01' )
    insert into #client values ( 2, 'name2', '1980-01-01' )
    insert into #client values ( 3, 'name3', '1975-01-01' )
    insert into #client values ( 4, 'name4', '1965-01-01' )

    insert into #ClientH1 values( 1, 1, 1 )
    insert into #ClientH1 values( 1, 2, 2 )
    insert into #ClientH1 values( 1, 3, 3 )
    insert into #ClientH1 values( 2, 1, 2 )
    insert into #ClientH1 values( 3, 1, 1 )

    insert into #ClientH2 values( 1, 1, 2 )
    insert into #ClientH2 values( 1, 2, 3 )
    insert into #ClientH2 values( 2, 1, 3 )
    insert into #ClientH2 values( 2, 2, 4 )
    go


    SELECT
    C.FullName,
    C.BirthDate,
    H1.H1ID,
    H2.H2ID
    FROM
    #Client C
    FULL OUTER JOIN #ClientH1 H1
    FULL OUTER JOIN #ClientH2 H2
    ON H2.ClientID = H1.ClientID
    and H2.clientseq = H1.clientseq
    ON C.ClientID = H1.ClientID
    or C.ClientID = H2.ClientID


    go
    drop table #client
    drop table #clientH1
    drop table #clientH2
    go

    This gives


    Fullname birthdate H1ID H2ID
    name1 1970-01-01 00:00:00.000 1 2
    name1 1970-01-01 00:00:00.000 2 3
    name1 1970-01-01 00:00:00.000 3 NULL
    name2 1980-01-01 00:00:00.000 2 3
    name2 1980-01-01 00:00:00.000 NULL 4
    name3 1975-01-01 00:00:00.000 1 NULL
    name4 1965-01-01 00:00:00.000 NULL NULL

    Cheers
    Twan
  6. timbyng New Member

    Thanks for the great solution and example, Twan! I've been testing it here and it seems to return the results I'm looking for. At the very least, it's opened my eyes to doing things a little differently.

    Actually, I've taken your solution and modified it a bit. Unfortunately, when I implemented your solution in my project, the performance was very poor. It took 8 seconds to return the results when just joining 2 history tables. When I increased the number of history tables to 3, it increased the time to 1 minute and 10 seconds.

    I ended up using your idea for the sequence numbers, but the way I got my results was a bit different. This method reduced my time from 1 minute and 10 seconds to just less than 2 seconds. Here's the code I used (note: I generate the sequence numbers on the fly here because in my final code I'll be filtering the history tables based on date parameters).


    -- Code to create tables and data for this example
    create table client(
    clientid int not null,
    fullname varchar(20) not null,
    birthdate datetime not null
    )

    create table ClientH1(
    id int identity(1,1) not null,
    clientid int not null,
    h1ID int not null
    )

    create table ClientH2(
    id int identity(1,1) not null,
    clientid int not null,
    h2ID int not null
    )

    go

    insert into client values ( 1, 'name1', '1970-01-01' )
    insert into client values ( 2, 'name2', '1980-01-01' )
    insert into client values ( 3, 'name3', '1975-01-01' )
    insert into client values ( 4, 'name4', '1965-01-01' )

    insert into ClientH1 values( 1, 1 )
    insert into ClientH1 values( 1, 2 )
    insert into ClientH1 values( 1, 3 )
    insert into ClientH1 values( 2, 2 )
    insert into ClientH1 values( 3, 1 )

    insert into ClientH2 values( 1, 2 )
    insert into ClientH2 values( 1, 3 )
    insert into ClientH2 values( 2, 3 )
    insert into ClientH2 values( 2, 4 )
    go

    -- Set up a table variable to hold results
    DECLARE @ClientHistory TABLE
    (
    ClientID int not null,
    ClientSeq int not null,
    H1ID int null,
    H2ID int null
    )

    -- Insert all history from the first table, along with the sequence
    INSERT
    @ClientHistory
    SELECT
    ClientID,
    ClientSeq =
    (
    SELECT
    COUNT(*)
    FROM
    ClientH1 H1b
    WHERE
    H1a.ID >= H1b.ID
    AND H1a.ClientID = H1b.ClientID
    ),
    H1ID,
    NULL
    FROM
    ClientH1 H1a

    -- Update all existing client history with the history table # 2 info
    -- using the client id and sequence
    UPDATE
    @ClientHistory
    SET
    H2ID = H2a.H2ID
    FROM
    @ClientHistory C,
    ClientH2 H2a
    WHERE
    C.ClientID = H2a.ClientID
    AND (SELECT COUNT(*) FROM ClientH2 H2b WHERE H2a.ID >= H2b.ID AND H2a.ClientID = H2b.ClientID) = C.ClientSeq

    -- Insert the remaining history table #2 records, including the sequence
    INSERT
    @ClientHistory
    SELECT
    ClientID,
    ClientSeq =
    (
    SELECT
    COUNT(*)
    FROM
    ClientH2 H2b
    WHERE
    H2a.ID >= H2b.ID
    AND H2a.ClientID = H2b.ClientID
    ),
    NULL,
    H2ID
    FROM
    ClientH2 H2a
    WHERE
    NOT EXISTS(SELECT NULL FROM @ClientHistory C WHERE C.ClientID = H2a.ClientID AND C.H2ID = H2a.H2ID)

    -- Return final results
    SELECT
    FullName,
    H1ID,
    H2ID
    FROM
    Client C
    LEFT JOIN @ClientHistory CH ON C.ClientID = CH.ClientID
    ORDER BY
    FullName

    -- Drop the tables used in this example
    go
    drop table client
    drop table clientH1
    drop table clientH2
    go
    Here are the results:


    name112
    name123
    name13NULL
    name223
    name2NULL4
    name31NULL
    name4NULLNULL
    As you can see, I've done basically the same thing, but I've broken it down into multiple steps. I don't understand why there's such a big difference in the processing time, but I'm happy that I now have something that works and is relatively quick. I still have some testing to do, though. Also, I know that two seconds isn't the best, but it's much better than the 7 minutes I was getting with Crystal Reports using subreports!

    Thanks again for your help Twan. I really appreciate it!

    Regards,
    Tim
  7. Twan New Member

    Coolie<br /><br />must admit that I didn't look at the performance side of things... full outer joins are expensive since they probably end up using hash joins (I'm guessing)<br /><br />Glad it works ok <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Cheers<br />Twan<br /><br />PS once you add on your where clause(s) you may be able to optimise the single query using additional indexes... Depends on how much data will be inserted into the @ tables. NOTE that the @ tables will always result in a table scan, which is obviously not good for large numbers of rows...

Share This Page