SQL Server Performance

Select Query

Discussion in 'General Developer Questions' started by ranjitjain, Oct 25, 2006.

  1. ranjitjain New Member

    Hi,
    My table structure is like this:
    declare @t1 table(col1 int,col2 int)
    INSERT @t1
    SELECT 1,3 UNION ALL
    SELECT 1,4 UNION ALL
    SELECT 2,4 UNION ALL
    SELECT 3,3 UNION ALL
    SELECT 3,4 UNION ALL
    SELECT 3,5 UNION ALL
    SELECT 4,3 UNION ALL
    SELECT 4,6

    My query is, i need list of all the col1 where
    col1 is only assigned with col2 values 3 and 4.
    In the above case it should return
    col1
    1

    I tried using INTERSECT but looking for a better query
    How do i write this more effectively.
  2. Adriaan New Member

    Couple of solutions:

    SELECT DISTINCT t1.col1
    FROM @t1 t1
    INNER JOIN @t1 t2 ON t1.col1 = t2.col1
    WHERE t1.col1 = 3 and t2.col1 = 4

    or

    SELECT @t1.col1 FROM @t1
    WHERE @t1.col1 = 3
    AND @t1.col1 IN (SELECT t2.col1 FROM @t1 t2 WHERE t2.col1 = 4)

    or

    SELECT t1.col1
    FROM @t1 t1
    WHERE t1.col1 = 3
    AND EXISTS (SELECT t2.col1 FROM @t1 t2 WHERE t2.col1 = 4 AND t2.col1 = t1.col1)
  3. ranjitjain New Member

    Hi Adriaan,
    I tried all your approaches but no result is being returned.
    Also the values which i'm going to check will be dynamic and comma separated.
  4. FrankKalis Moderator

    Will this work?


    SELECT col1
    FROM @t1
    WHERE col2 IN (3, 4)
    GROUP BY col1
    HAVING COUNT(*) = 2


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  5. ranjitjain New Member

    quote:Originally posted by FrankKalis

    Will this work?


    SELECT col1
    FROM @t1
    WHERE col2 IN (3, 4)
    GROUP BY col1
    HAVING COUNT(*) = 2


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    I had already tried this but it returns
    two values i.e.
    col1
    1
    3

    my desired output is
    col1
    1

  6. FrankKalis Moderator

    Aah, I see, I've misinterpreted "only assigned with col2 values 3 and 4." in our question. <br />Will go home now and think about it. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  7. Adriaan New Member

    Sorry for the error: the criteria for column value 3 or 4 must be for col2 (not col1).

    SELECT DISTINCT t1.col1
    FROM @t1 t1
    INNER JOIN @t1 t2 ON t1.col1 = t2.col1
    WHERE t1.col2 = 3 and t2.col2 = 4

    or

    SELECT @t1.col1 FROM @t1
    WHERE @t1.col2 = 3
    AND @t1.col1 IN (SELECT t2.col1 FROM @t1 t2 WHERE t2.col2 = 4)

    or

    SELECT t1.col1
    FROM @t1 t1
    WHERE t1.col2 = 3
    AND EXISTS (SELECT t2.col1 FROM @t1 t2 WHERE t2.col2 = 4 AND t2.col1 = t1.col1)
  8. Adriaan New Member

    Ranitjain - you are close to 1500 postings, and you don't know how to find out small errors like that?
  9. ranjitjain New Member

    Hi Adriaan,
    I had already mentioned, i have got the result using intersect but was not happy with it.
    I thought there might be some better way.
    here is my way:

    select col1,count(*) from @t1
    where col2 in (3,4)
    group by col1
    intersect
    select col1,count(*) from @t1
    group by col1
    having count(col2) = 2

    Also, if i consider your approaches, it will be quite difficult because the criteria i.e.(3,4)
    will be dynamic and comma separated, and sometimes can be only 4 or (4,2,3.....)
    In this case i can not build so many correlated queries.

    Also i feel everyone should have approach as looking for better always irrespective of post count,
    one should be hungry for knowledge,
    What do you say?
    I also feel, the more you are approaching towards 5000 mark,
    you are becoming more harsh
  10. Madhivanan Moderator

    quote:
    select col1,count(*) from @t1
    where col2 in (3,4)
    group by col1
    intersect
    select col1,count(*) from @t1
    group by col1
    having count(col2) = 2
    Are you using SQL Server 2005?
    Also, in the above code, how do you replace (3,4) by your csv?

    Madhivanan

    Failing to plan is Planning to fail
  11. ranjitjain New Member

    Hi Madhivanan,
    Yes, it true that this query i ran on 2005, but was looking for the one which will run on both using some other approach.

    Anyways i modified in this way

    declare @locs table(locid int)
    INSERT @locs
    SELECT aaiLocationID from dbo.[fn_LocHierarchy](@aaiLocationID)

    SELECT tmp.aaiProfileID FROM
    (SELECT aaiProfileID,COUNT(*) c FROM aaiprofiletolocation
    WHERE aaiLocationID IN (SELECT locid FROM @locs) GROUP BY aaiProfileID)tmp
    INNER JOIN
    (SELECT aaiProfileID,COUNT(*) c FROM aaiprofiletolocation
    GROUP BY aaiProfileID HAVING COUNT(*) = (SELECT COUNT(*) FROM @locs))tmp1
    ON tmp.aaiProfileID=tmp1.aaiProfileID and tmp.c=tmp1.c

    here, @aaiLocationID will return me all the csv child locations
  12. Adriaan New Member

    Why the derived tables?

    declare @locs table(locid int)
    DECLARE @ROWS INT

    INSERT @locs
    SELECT aaiLocationID from dbo.[fn_LocHierarchy](@aaiLocationID)
    SET @ROWS = @@ROWCOUNT

    SELECT aaiProfileID,COUNT(*) c
    FROM aaiprofiletolocation
    GROUP BY aaiProfileID
    HAVING COUNT(*) = @rows

    Don't know what the fn_LocHierarchy function does - but probably no reason why it couldn't be done locally in this procedure.
  13. ranjitjain New Member

    Hi Adriaan,
    Thanks for this catch, yes i could do this without doing count(*) again on derived table.
    I missed it completely.
    really good catch.
    Now you can see, how things becomes easier after discussion even for smaller queries like this.

    BTW, fn_lochierarchy function return me comma separated list of all the childs of @aaiLocationID
    parameter value
  14. Adriaan New Member

    And you are probably looking up those child aaiLocationId values in another column, or another table? The solution seems to be over-engineered, where you should just have worked out the basic query that joins the relevant tables. Don't use UDFs just because they appeal to the programmer in you!

    Also note that the query returns all aaiProfileID values that happen have the same number of entries in the aaiProfileToLocation table, as has the @aaiLocationId parameter value. You probably have to add the @aaiLocationId parameter to the shorter query - but only you know the logic behind the query.

    (You may not see a problem with the current data, but it may come up later when there is more varied data in the aaiProfileToLocation table.)
  15. ranjitjain New Member

    Hi Adriaan,
    i agree on what you are saying.

    Well the function not just returns child's of a single location but it also returns child's of those childs which means the complete list of locations which appears in the hierarchy.

    In my code i want to return not just single profileid but all the profileid's which has been assigned only 3,4 locations or may be some other list.

    The idea is if a profile has been assigned with only those locations than that parent location can not be dropped whereas if a profile has also got childs/parents of some other locations then it can be dropped. So i'm intersecting two queries.

    Anyways, i feel this should work and thanx for all your support and contribution.
  16. Adriaan New Member

    You could also have the function return the number of rows matching the criteria.

    Anyway, your filtering is still based on the assumption that any @aaiLocationId parameter you choose will have a unique number of matches, among all possible @aaiLocationId values and their matches. This is not really life-like.
  17. Madhivanan Moderator

    My general approach(not sure about performance)


    Declare @t table(c1 char(1), c2 int)
    Declare @c2list varchar(10), @len int

    Insert into @t
    Select 'a',3 union all
    Select 'a',4 union all
    Select 'b',3 union all
    Select 'b',4 union all
    Select 'b',5 union all
    Select 'c',2 union all
    Select 'c',3

    --If values are static then
    Select t1.c1 from @t t1 inner join
    (select c1 from @t
    group by c1
    having count(*)=2
    )t2 on t1.c1=t2.c1 where t1.c2 in (3,4)
    group by t1.c1 having count(*)=2

    --If values are dynamic then
    set @c2list='3,4'
    Set @len=len(@c2list)-len(replace(@c2list,',',''))+1

    Select t1.c1,@c2list as value from @t t1 inner join
    (select c1 from @t
    group by c1
    having count(*)=@len
    )t2 on t1.c1=t2.c1 where '%,'+@c2list+',%' like '%,'+cast(t1.c2 as varchar(10))+',%'
    group by t1.c1 having count(*)=@len

    set @c2list='3,4,5'
    Select t1.c1,@c2list as value from @t t1 inner join
    (select c1 from @t
    group by c1
    having count(*)=@len
    )t2 on t1.c1=t2.c1 where '%,'+@c2list+',%' like '%,'+cast(t1.c2 as varchar(10))+',%'
    group by t1.c1 having count(*)=@len



    Madhivanan

    Failing to plan is Planning to fail
  18. Madhivanan Moderator

    I dont know why new lines are not working with Code tag

    Madhivanan

    Failing to plan is Planning to fail
  19. ranjitjain New Member

    Hi Madhivanan,
    I tried your approach and it's working the same way i desired.
    The execution plan is same as of code which i had posted
    and i feel my approach of using intersect i.e. INNER JOIN is very much similar to what you have posted.
    Still i will go with this as it looks much simpler.
    Select t1.c1 from @t t1 inner join
    (select c1 from @t
    group by c1having count(*)=2)t2
    on t1.c1=t2.c1
    where t1.c2 in (3,4)
    group by t1.c1 having count(*)=2

    Thanks alot for your support
  20. Madhivanan Moderator

    If you use intersect compare it with this solution and see the performance. I didnt use SQL Server 2005

    Madhivanan

    Failing to plan is Planning to fail
  21. ranjitjain New Member

    Hi Madhivanan,
    This solution and Using INTERSECT solution shows the same execution plan and have almost no difference in performance.
    At the moment i have very less data, but will test it afterwards under load as well.
  22. mmarovic Active Member

    Hi Ranjit,

    is it what you are looking for:


    declare @t1 table(col1 int,col2 int)
    INSERT @t1
    SELECT 1,3 UNION ALL
    SELECT 1,4 UNION ALL
    SELECT 2,4 UNION ALL
    SELECT 3,3 UNION ALL
    SELECT 3,4 UNION ALL
    SELECT 3,5 UNION ALL
    SELECT 4,3 UNION ALL
    SELECT 4,6

    select a.col1
    from @t1 a
    join @t1 b on a.col1 = b.col1
    where a.col2 = 3 and
    b.col2 = 4 and
    not exists(select *
    from @t1 c
    where c.col1 = a.col1 and
    c.col2 not in (3,4)
    )
    It also can be done using left join instead of exists.

Share This Page