Professional Web Applications Themes

why does "group by" slow down my query - Microsoft SQL / MS SQL Server

Group by's usually have to do a fair amount of work to determine the dupes. I assume you have proper indexes on both those columns? Is this any faster: select DISTINCT(ai.ai_acct_key) from account_info_ai ai WHERE EXISTS (SELECT * FROM join stock_position_current_spc spc WHERE spc.spc_ai_acct_key = ai.ai_acct_key) -- Andrew J. Kelly SQL Server MVP "chris" <com> wrote in message news:01b701c36042$2d3c9940$gbl...  > > > >You shouldn't need distinct and group by in this query.[/ref] > Just use the group [/ref]...

  1. #1

    Default Re: why does "group by" slow down my query

    Group by's usually have to do a fair amount of work to determine the dupes.
    I assume you have proper indexes on both those columns? Is this any
    faster:


    select DISTINCT(ai.ai_acct_key)
    from account_info_ai ai
    WHERE EXISTS (SELECT * FROM join stock_position_current_spc spc WHERE
    spc.spc_ai_acct_key = ai.ai_acct_key)

    --

    Andrew J. Kelly
    SQL Server MVP


    "chris" <com> wrote in message
    news:01b701c36042$2d3c9940$gbl... 
    > >
    > >You shouldn't need distinct and group by in this query.[/ref]
    > Just use the group [/ref]


    Andrew Guest

  2. #2

    Default Re: why does "group by" slow down my query

    Sorry, I cut and pasted from your query and missed removing the JOIN. Just
    remove the word JOIN in the EXISTS (... and it should be ok.

    --

    Andrew J. Kelly
    SQL Server MVP


    "chris" <com> wrote in message
    news:0ee001c360e3$49cdc290$gbl... 
    > determine the dupes. 
    > Is this any 
    > stock_position_current_spc spc WHERE [/ref]
    > wasnt) 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Andrew Guest

  3. #3

    Default Re: why does "group by" slow down my query

    They are identicle.

    |--Merge Join(Right Semi Join, MANY-TO-MANY MERGE:([spc].
    [spc_ai_acct_key])=([ai].[ai_acct_key]), RESIDUAL:([spc].
    [spc_ai_acct_key]=[ai].[ai_acct_key]))
    |--Index Scan(OBJECT:([adv084].[dbo].
    [stock_position_current_spc].
    [IX_stock_position_current_spc_1] AS [spc]), ORDERED
    FORWARD)
    |--Index Scan(OBJECT:([adv084].[dbo].
    [account_info_ai].[PK_account_info_ai] AS [ai]), ORDERED
    FORWARD)


     
    so its not a lot of 
    with 14K you still 
    both. When you group 
    disk many times. It 
    like? [/ref]
    alot [/ref]
    distinct/Group [/ref]
    But [/ref]
    here? 
    >> removing the JOIN. Just [/ref][/ref]
    be 
    >> what [/ref][/ref]
    to [/ref][/ref]
    columns? [/ref][/ref]
    mistake. [/ref][/ref]
    (it 
    >> query. [/ref]
    >
    >
    >.
    >[/ref]
    chris Guest

Similar Threads

  1. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  2. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  3. "save", "selective color" menu go too slow. Please help!
    By bmjun20 in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 1
    Last Post: August 12th, 08:56 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