SQL Server Performance

Heavy Syntax, need assistance :)

Discussion in 'General Developer Questions' started by siskhoalanka, Sep 29, 2006.

  1. siskhoalanka New Member

    Hi, I have an issue with a tsql query.<br /><br />I do something like that.<br /><br />CREATE SP<br />...<br />AS<br />...<br />DECLARE @TempTable AS TABLE(&lt;tabledef&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /><br />INSERT INTO @TempTable(SELECT &lt;columns&gt; FROM RealTable WHERE &lt;condition&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />--Now I loaded the filtered data into the @TempTable variable.<br /><br />SELECT<br /> FT.column_1<br />FROM<br /> @TempTable FT<br />WHERE<br /> &lt;conditions&gt;<br />GROUP BY<br /> FT.column_1 <br /><br />--Until here, all works fine, but what I really need to do is show in the select columns from the TempTable ("FT") with more conditions. An example would be something like this:<br /><br />SELECT<br /> FT.column_1,<br /> (SELECT COUNT(FT.Column_2 WHERE FT.Column4 = @INPUT_PARAM1)),<br /> (SELECT COUNT(FT.Column_3 WHERE FT.Column4 = @INPUT_PARAM2))<br />FROM<br /> @TempTable FT<br />WHERE<br /> &lt;conditions&gt;<br />GROUP BY<br /> FT.column_1 <br /><br /><br /><br /><br />So, here you got. What I'm trying to do.<br /><br />I show ou how I resolved it, but this solution is too heavy, I'd like to know if there is an other way to do what I want.<br /><br />SELECT<br /> FT.column_1,<br /> (SELECT COUNT(FT.Column_2 From @TempTable FT2 INNER JOIN FT WITH FT2 WHERE FT.Column4 = @INPUT_PARAM1)),<br /> (SELECT COUNT(FT.Column_3 From @TempTable FT2 INNER JOIN FT WITH FT2 WHERE FT.Column4 = @INPUT_PARAM2))<br />FROM<br /> @TempTable FT<br />WHERE<br /> &lt;conditions&gt;<br />GROUP BY<br /> FT.column_1<br /><br />Thanks in advance!!
  2. Madhivanan Moderator

    Read about Cross-tab Reports in sql server help file

    Madhivanan

    Failing to plan is Planning to fail
  3. Adriaan New Member

    The whole syntax seems to be wrong, from the INSERT statement to the COUNT statements?!

    You could try SUM with embedded CASE expressions:

    SELECT FT.column_1,
    SUM(CASE WHEN FT.Column4 = @INPUT_PARAM1 THEN 1 ELSE 0 END),
    SUM(CASE WHEN FT.Column4 = @INPUT_PARAM2 THEN 1 ELSE 0 END)
    FROM @TempTable FT
    GROUP BY FT.column_1
  4. siskhoalanka New Member

    Thanks Adriaan!<br /><br />This seems much more coherent indeed [<img src='/community/emoticons/emotion-2.gif' alt=':D' />].
  5. siskhoalanka New Member

    I'm adaptating it to your solution that is much more performant.
    How could I attach a join in one of those SUM?

    I have to SUM all beds that are occuped in an hospital.
    That FT Table have all beds, but I need to join it with CS. (If the bed appears in CS, then it is used).

    Thanks!!
  6. Adriaan New Member

    If you;ve included the "bed" column in the table variable:

    SELECT FT.column_1,
    SUM(CASE WHEN FT.Column4 = @INPUT_PARAM1 THEN 1 ELSE 0 END),
    SUM(CASE WHEN FT.Column4 = @INPUT_PARAM2 THEN 1 ELSE 0 END)
    FROM @TempTable FT
    WHERE FT.bed IN (SELECT bed FROM CS)
    GROUP BY FT.column_1

    If the numebr of records goes into thousands, you may want to use a temp table instead, and add an index on the bed column of the temp table.
  7. siskhoalanka New Member

    The problem with that solution of putting the condition in the WHERE is that it will apply for all the SUM, and that would return wrong numbers to me. Because the first SUM gives me all Available Beds and the second one all the ocupped.
    If you put this condition, the temptable will only have ocupped beds, am I right?

    After that I will have to calculate some more columns. A percent of bed occupancy level and the totals.

    But basically my problem is what I've said, I need to apply different conditions in each SUM, is it why I've done it how I showed you the first time.
  8. Adriaan New Member

    Sorry, you're right: CS lists the beds that are occupied, so my previous solution counts only occupied beds. If it is enough to check whether the bed occurs in CS, then a LEFT JOIN might be the solution:

    SELECT FT.column_1,
    SUM(CASE WHEN FT.Column4 = @INPUT_PARAM1 AND CS.bed IS NOT NULL THEN 1 ELSE 0 END) Occupied,
    SUM(CASE WHEN FT.Column4 = @INPUT_PARAM2 AND CS.bed IS NULL THEN 1 ELSE 0 END) Unoccupied
    FROM @TempTable FT
    LEFT JOIN CS ON FT.bed = CS.bed
    GROUP BY FT.column_1

    Occupied: Column_4 = @INPUT_PARAM1 and bed is listed in CS (NOT NULL).

    Unoccupied: Column_4 = @INPUT_PARAM2 and bed is not listed in CS (NULL).
  9. Adriaan New Member

    Double-check the data in CS - if you have multiple entries there for the same bed, you should do an EXISTS subquery inside the CASE:

    SELECT FT.column_1,
    SUM(
    CASE WHEN FT.Column4 = @INPUT_PARAM1
    AND EXISTS (SELECT CS.bed FROM CS WHERE CS.bed = FT.bed)
    THEN 1 ELSE 0 END) Occupied,
    SUM(
    CASE WHEN FT.Column4 = @INPUT_PARAM2
    AND NOT EXISTS (SELECT CS.bed FROM CS WHERE CS.bed = FT.bed) THEN 1 ELSE 0 END) Unoccupied
    FROM @TempTable FT
    GROUP BY FT.column_1
  10. siskhoalanka New Member

    Ok, here is what I got:
    SELECT
    FT.NurseStationId,
    SUM(CASE WHEN FT.OwerFlow = 1 AND EXISTS (SELECT CS.bed FROM CS WHERE CS.bed = FT.bed) THEN 1 ELSE 0 END) AS 'Occupied',
    SUM(CASE WHEN FT.OverFlow = 1 AND NOT EXISTS (SELECT CS.bed FROM CS WHERE CS.bed = FT.bed) THEN 1 ELSE 0 END) AS 'Unoccupied'
    FROM
    MstAdtBedbyCompFac FT
    LEFT JOIN
    AppAdtCensusByCompFac CS
    ON
    CS.Room = FT.Room
    ANDCS.Bed = FT.Bed
    ANDCS.CompanyID = 1
    AND CS.FacilityId = 2
    AND CS.ServiceArea = 'EGY'
    AND CS.NurseStationID = FT.NurseStationId
    GROUP BY
    FT.NurseStationId



    Here's the result:
    Server: Msg 130, Level 15, State 1, Line 3
    Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
    Server: Msg 156, Level 15, State 1, Line 4
    Incorrect syntax near the keyword 'THEN'.
  11. Adriaan New Member

    That's what you get from not testing ...

    Okay, we can take the LEFT JOIN solution, but on a derived table that makes sure there are only distinct beds listed:

    SELECT FT.column_1,
    SUM(CASE WHEN FT.Column4 = @INPUT_PARAM1 AND X.bed IS NOT NULL THEN 1 ELSE 0 END) Occupied,
    SUM(CASE WHEN FT.Column4 = @INPUT_PARAM2 AND X.bed IS NULL THEN 1 ELSE 0 END) Unoccupied
    FROM @TempTable FT
    LEFT JOIN (SELECT CS.bed FROM CS GROUP BY CS.bed) X ON FT.bed = X.bed
    GROUP BY FT.column_1

    Note that this is relevant only if you can have more than one row in CS for the same bed.
  12. siskhoalanka New Member

    The beds are not repeated.
    But something like this could appear

    COMPANY FACILITY SERVICEAREA NURSESTATION ROOM BED
    1 2 'EGY' 'CAT' 700 1
    1 2 'EGY' 'CAT' 701 1
    1 2 'EGY' 'CAT' 702 1
    1 2 'EGY' 'CAT' 702 2
    1 2 'EGY' 'CAT' 702 3
    1 2 'EGY' 'CAT' 702 4
    1 2 'EGY' 'ER ' 702 2
    1 2 'EGY' 'ER ' 702 3
    1 2 'EGY' 'ER ' 702 4

    ALL OF THOSE FIELDS ARE THE PRIMARY KEY
  13. siskhoalanka New Member

    Ok Adriaan.<br />Now I have something solid to continue working with.<br />I appreciated your help, thanks a lot for your time, really.<br /><br />Hope some day I could return the help <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Not sure but...<br /><br />Have a nice day!
  14. Adriaan New Member

    Okay, then the LEFT JOIN should be on all matching fields between @TempTable and CS.

    By the way, is the data in @TempTable a selection from that other table?
  15. siskhoalanka New Member

    The data on TempTable is the result of the selection of all the beds but filtered by company, facility and servicearea. The idea was to minimize the impact of counting on a huge table.
  16. Adriaan New Member

    Perhaps with the SUM syntax the impact on the table will already be reduced?

    The biggest issue is whether the underlying table has the right indexes for this query. It seems like this query is of major importance, so if there is no appropriate index there, then it may be worth adding a separate index to cater for this query.
  17. siskhoalanka New Member

    I hope the table has the right indexes. I could create the indexes that miss if I see that the perf is on the floor, but as you said with your solution we reduced a lot the impact because now I have 1 table less that I was querying for almost each column. That was heavy <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Anyway, thanks again!<br />Does your work is related with databases? Just curious.<br />Mine not, I am developping a Framework in .NET now, yes it is related, but it is not directly my work <img src='/community/emoticons/emotion-1.gif' alt=':)' />.
  18. Adriaan New Member

    Databases, but also a lot of work in Access front-end apps.
  19. siskhoalanka New Member

    Ok, nice.
    Well, Thanks Again!

Share This Page