Professional Web Applications Themes

strange subquery behaviour? - Microsoft SQL / MS SQL Server

Hi All. first thanks to Umachandar for suggesting the use of NewID() . I understand this is undoented and unsupported etc... Any ideas or insight into this would be most welcome. Please see DDL below The goal is to select 5 items per department and assign it for audit. When I use a simple Top5 query, I get exactly 5 random rows every time. select top 5 vic_id from vic t where t.id = 5 order by Newid() However, if I use it in a subquery, I get a varying number of results, select v.vic_id, v.Auditor, id from vic as ...

  1. #1

    Default strange subquery behaviour?

    Hi All.
    first thanks to Umachandar for suggesting the use of NewID() .
    I understand this is undoented and unsupported etc...
    Any ideas or insight into this would be most welcome.

    Please see DDL below

    The goal is to select 5 items per department and assign it for audit.

    When I use a simple Top5 query, I get exactly 5 random rows every time.
    select top 5 vic_id from vic t where t.id = 5 order by Newid()

    However, if I use it in a subquery, I get a varying number of results,

    select v.vic_id, v.Auditor, id from vic as v
    where v.vic_id in (select top 5 vic_id
    from vic t where v.id = t.id order by Newid())
    and v.id = 5
    order by v.id

    Why is the performance different in a sub query?

    Regards
    Habib



    ------------------------------------------

    use pubs
    -- drop table vic
    create table vic
    (vic_id int identity,
    Auditor varchar(50),
    DeptName varchar(50),
    id int)

    set identity_Insert vic on
    insert vic (vic_id,Auditor,DeptName,id) values (2416701
    ,'ALGmycompany.com', 'Boys ',51)
    insert vic (vic_id,Auditor,DeptName,id) values (2416703
    ,'ALG2mycompany.com', 'Boys ',51)
    insert vic (vic_id,Auditor,DeptName,id) values (2416704
    ,'ALG3mycompany.com', 'Boys ',51)
    insert vic (vic_id,Auditor,DeptName,id) values (2416705
    ,'ALG4mycompany.com', 'Boys ',51)
    insert vic (vic_id,Auditor,DeptName,id) values (2416706
    ,'ALG5mycompany.com', 'Boys ',51)
    insert vic (vic_id,Auditor,DeptName,id) values (2416707
    ,'ALG6mycompany.com', 'Boys ',51)
    insert vic (vic_id,Auditor,DeptName,id) values (2416708
    ,'ALG7mycompany.com', 'Boys ',51)
    insert vic (vic_id,Auditor,DeptName,id) values (2416668
    ,'avonmycompany.com', 'Boys ',51)
    insert vic (vic_id,Auditor,DeptName,id) values (2416695
    ,'novamycompany.com', 'Boys ',51)
    insert vic (vic_id,Auditor,DeptName,id) values (2416700
    ,'ALGmycompany.com', 'Wares ',17)
    insert vic (vic_id,Auditor,DeptName,id) values (2416667
    ,'avonmycompany.com', 'Wares ',17)
    insert vic (vic_id,Auditor,DeptName,id) values (2416694
    ,'novamycompany.com', 'Wares ',17)
    insert vic (vic_id,Auditor,DeptName,id) values (2416698
    ,'ALGmycompany.com', 'Home ',5)
    insert vic (vic_id,Auditor,DeptName,id) values (2416672
    ,'avonmycompany.com', 'Home ',5)
    insert vic (vic_id,Auditor,DeptName,id) values (2416691
    ,'novamycompany.com', 'Home ',5)
    insert vic (vic_id,Auditor,DeptName,id) values (2416709
    ,'1mycompany.com', 'Home ',5)
    insert vic (vic_id,Auditor,DeptName,id) values (2416710
    ,'2mycompany.com', 'Home ',5)
    insert vic (vic_id,Auditor,DeptName,id) values (2416711
    ,'3mycompany.com', 'Home ',5)
    insert vic (vic_id,Auditor,DeptName,id) values (2416712
    ,'4mycompany.com', 'Home ',5)
    insert vic (vic_id,Auditor,DeptName,id) values (2416713
    ,'5mycompany.com', 'Home ',5)
    insert vic (vic_id,Auditor,DeptName,id) values (2416714
    ,'6mycompany.com', 'Home ',5)
    insert vic (vic_id,Auditor,DeptName,id) values (2416715
    ,'7mycompany.com', 'Home ',5)
    insert vic (vic_id,Auditor,DeptName,id) values (2416696
    ,'ALGmycompany.com', 'Shoe ',1697)
    insert vic (vic_id,Auditor,DeptName,id) values (2416676
    ,'avonmycompany.com', 'Shoe ',1697)
    insert vic (vic_id,Auditor,DeptName,id) values (2416699
    ,'ALGmycompany.com', 'Cars ',7)
    insert vic (vic_id,Auditor,DeptName,id) values (2416677
    ,'avonmycompany.com', 'Cars ',7)
    insert vic (vic_id,Auditor,DeptName,id) values (2416692
    ,'novamycompany.com', 'Cars ',7)
    insert vic (vic_id,Auditor,DeptName,id) values (2416702
    ,'ALGmycompany.com', 'Other ',1839)
    insert vic (vic_id,Auditor,DeptName,id) values (2416683
    ,'avonmycompany.com', 'Other ',1839)
    insert vic (vic_id,Auditor,DeptName,id) values (2416697
    ,'ALGmycompany.com', 'Toddler ',4)
    insert vic (vic_id,Auditor,DeptName,id) values (2416686
    ,'avonmycompany.com', 'Toddler ',4)
    insert vic (vic_id,Auditor,DeptName,id) values (2416690
    ,'novamycompany.com', 'Toddler ',4)


    select top 5 vic_id from vic t where t.id = 5 order by Newid()

    select v.vic_id, v.Auditor, id from vic as v
    where v.vic_id in (select top 5 vic_id
    from vic t where v.id = t.id order by Newid())
    and v.id = 5
    order by v.id


    /*Create proc RandomAudits
    startID int, endid int
    as*/
    Declare startID int, Endid int
    Select startID = 4, EndID = 1839
    Declare LoopCtl int
    Set loopCtl = StartID

    drop table #Auditors
    Create table #Auditors
    (vic_id int,
    Auditor varchar(50),
    DeptName varchar(50),
    id int)

    While loopCtl <= EndID
    Begin
    insert #Auditors
    select top 5 vic_id, Auditor, DeptName, id
    from vic t where id = loopCtl order by Newid()

    Set loopctl = loopctl +1
    End

    select * from #Auditors


    HSalim Guest

  2. #2

    Default Re: strange subquery behaviour?

    it's behaviour is the same as if you would use this join :
    select v.vic_id, v.Auditor, id from vic as v
    inner join (select top 5 vic_id
    from vic t where v.id = t.id order by Newid()) t2
    on v.vic_id = t2. vic_id
    where v.id = 5
    order by v.id

    hope this clarifies.

    jobi
    "HSalim" <hsalimspamhole.msn.com> wrote in message
    news:uBMURLfQDHA.2176TK2MSFTNGP12.phx.gbl...
    > Hi All.
    > first thanks to Umachandar for suggesting the use of NewID() .
    > I understand this is undoented and unsupported etc...
    > Any ideas or insight into this would be most welcome.
    >
    > Please see DDL below
    >
    > The goal is to select 5 items per department and assign it for audit.
    >
    > When I use a simple Top5 query, I get exactly 5 random rows every time.
    > select top 5 vic_id from vic t where t.id = 5 order by Newid()
    >
    > However, if I use it in a subquery, I get a varying number of results,
    >
    > select v.vic_id, v.Auditor, id from vic as v
    > where v.vic_id in (select top 5 vic_id
    > from vic t where v.id = t.id order by Newid())
    > and v.id = 5
    > order by v.id
    >
    > Why is the performance different in a sub query?
    >
    > Regards
    > Habib
    >
    >
    >
    > ------------------------------------------
    >
    > use pubs
    > -- drop table vic
    > create table vic
    > (vic_id int identity,
    > Auditor varchar(50),
    > DeptName varchar(50),
    > id int)
    >
    > set identity_Insert vic on
    > insert vic (vic_id,Auditor,DeptName,id) values (2416701
    > ,'ALGmycompany.com', 'Boys ',51)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416703
    > ,'ALG2mycompany.com', 'Boys ',51)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416704
    > ,'ALG3mycompany.com', 'Boys ',51)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416705
    > ,'ALG4mycompany.com', 'Boys ',51)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416706
    > ,'ALG5mycompany.com', 'Boys ',51)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416707
    > ,'ALG6mycompany.com', 'Boys ',51)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416708
    > ,'ALG7mycompany.com', 'Boys ',51)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416668
    > ,'avonmycompany.com', 'Boys ',51)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416695
    > ,'novamycompany.com', 'Boys ',51)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416700
    > ,'ALGmycompany.com', 'Wares ',17)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416667
    > ,'avonmycompany.com', 'Wares ',17)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416694
    > ,'novamycompany.com', 'Wares ',17)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416698
    > ,'ALGmycompany.com', 'Home ',5)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416672
    > ,'avonmycompany.com', 'Home ',5)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416691
    > ,'novamycompany.com', 'Home ',5)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416709
    > ,'1mycompany.com', 'Home ',5)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416710
    > ,'2mycompany.com', 'Home ',5)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416711
    > ,'3mycompany.com', 'Home ',5)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416712
    > ,'4mycompany.com', 'Home ',5)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416713
    > ,'5mycompany.com', 'Home ',5)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416714
    > ,'6mycompany.com', 'Home ',5)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416715
    > ,'7mycompany.com', 'Home ',5)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416696
    > ,'ALGmycompany.com', 'Shoe ',1697)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416676
    > ,'avonmycompany.com', 'Shoe ',1697)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416699
    > ,'ALGmycompany.com', 'Cars ',7)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416677
    > ,'avonmycompany.com', 'Cars ',7)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416692
    > ,'novamycompany.com', 'Cars ',7)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416702
    > ,'ALGmycompany.com', 'Other ',1839)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416683
    > ,'avonmycompany.com', 'Other ',1839)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416697
    > ,'ALGmycompany.com', 'Toddler ',4)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416686
    > ,'avonmycompany.com', 'Toddler ',4)
    > insert vic (vic_id,Auditor,DeptName,id) values (2416690
    > ,'novamycompany.com', 'Toddler ',4)
    >
    >
    > select top 5 vic_id from vic t where t.id = 5 order by Newid()
    >
    > select v.vic_id, v.Auditor, id from vic as v
    > where v.vic_id in (select top 5 vic_id
    > from vic t where v.id = t.id order by Newid())
    > and v.id = 5
    > order by v.id
    >
    >
    > /*Create proc RandomAudits
    > startID int, endid int
    > as*/
    > Declare startID int, Endid int
    > Select startID = 4, EndID = 1839
    > Declare LoopCtl int
    > Set loopCtl = StartID
    >
    > drop table #Auditors
    > Create table #Auditors
    > (vic_id int,
    > Auditor varchar(50),
    > DeptName varchar(50),
    > id int)
    >
    > While loopCtl <= EndID
    > Begin
    > insert #Auditors
    > select top 5 vic_id, Auditor, DeptName, id
    > from vic t where id = loopCtl order by Newid()
    >
    > Set loopctl = loopctl +1
    > End
    >
    > select * from #Auditors
    >
    >

    jobi Guest

Similar Threads

  1. Strange Template Behaviour
    By ROGM in forum Macromedia Contribute General Discussion
    Replies: 1
    Last Post: December 26th, 11:16 PM
  2. Strange behaviour by LILO
    By Doug Laidlaw in forum Linux Setup, Configuration & Administration
    Replies: 2
    Last Post: September 11th, 07:43 AM
  3. strange SQL behaviour
    By platho in forum IBM DB2
    Replies: 1
    Last Post: September 6th, 04:05 PM
  4. Very strange behaviour
    By Mark in forum AIX
    Replies: 0
    Last Post: September 1st, 04:19 PM
  5. Strange CLI behaviour
    By Frank Passek in forum PHP Development
    Replies: 0
    Last Post: August 10th, 07:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139