How to improve query efficiency

GROUP BY grouping Work recently encountered a performance problem that he still did not get out for a long time , get up forum , hope prawn to the point .
SQL query is as follows:
SELECT A, B, C, D, COUNT (1) FROM T WHERE C = @ C AND D IN (@ D) GROUP BY A, B
because of large amount of data , so this SQL efficiency becomes particularly slow , and this is used in the query page , page after each query must be well spent 8-9 seconds , the page get stuck in the there, hope that the experts who helped answer brother , little brother thanked him here .
------ Solution ---------------------------------------- ----

Here is the actual use of SQL, please help me look at what the index has been added too, not much effect   
SELECT   
DEALER_ID,   
DEALER_BUSCODE,   
DEALER_NAME,   
DEALER_STATUS,   
OFFER_ID,   
OFFER_NAME,   
CHANNEL_ID,   
SUB_CHANNEL_ID,   
DISTY_ID,   
DISTY_BUSCODE,   
DISTY_NAME,   
REGION_CODE,   
REGION_NAME,   
O_ACTIVATION_TIME,   
P_ACTIVATION_TIME,   
ACTIVATION_DATE,   
STATE_CODE,   
STATE_NAME,   
PACKAGE_ID,   
PACKAGE_NAME,   
SALES_PIC,   
MSISDN,   
COUNT (1)   
FROM UM2_ACTI_DTL_201305   
WHERE OFFER_TYPE IN (1,3)   
AND ACTIVATION_TYPE = 3   
AND OFFER_ID IN (20000001,20000031,20010010)   
AND PACKAGE_ID IN (322,197,326,566,318,576,277,374,377,581)   
AND CHANNEL_ID IN (1,2,4)   
GROUP BY DEALER_ID, PACKAGE_ID, ACTIVATION_DATE  

sql like you can not perform it. In addition to group by the column , the column must have other options statistical functions of it, similar to the sum, average sort of
------ Solution --------- -----------------------------------

I've tried this , add index had little effect , the effect is still the same slow query .    

  

  Paste SQL
  

SELECT
   DEALER_ID,
   DEALER_BUSCODE,
   DEALER_NAME,
   DEALER_STATUS,
   OFFER_ID,
   OFFER_NAME,
   CHANNEL_ID,
   SUB_CHANNEL_ID,
   DISTY_ID,
   DISTY_BUSCODE,
   DISTY_NAME,
   REGION_CODE,
   REGION_NAME,
   O_ACTIVATION_TIME,
   P_ACTIVATION_TIME,
   ACTIVATION_DATE,
   STATE_CODE,
   STATE_NAME,
   PACKAGE_ID,
   PACKAGE_NAME,
   SALES_PIC,
   MSISDN,
   COUNT(1)
 FROM UM2_ACTI_DTL_201305
 WHERE OFFER_TYPE IN(1,3)
     AND ACTIVATION_TYPE = 3
     AND OFFER_ID IN(20000001,20000031,20010010)
     AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)
     AND CHANNEL_ID IN(1,2,4)
     GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE

 

OFFER_ID CHANNEL_ID build an index , then try this:

like few in the content you use = try, if not I have no idea of the amount , do not understand the business application scenarios do not know , hard to say .


Another: This query returns you how much data ?
------ Solution ---------------------------------------- ----

I've tried this , plus the index had little effect , the effect is still the same slow query .        

      

      Paste SQL
      

SELECT
   DEALER_ID,
   DEALER_BUSCODE,
   DEALER_NAME,
   DEALER_STATUS,
   OFFER_ID,
   OFFER_NAME,
   CHANNEL_ID,
   SUB_CHANNEL_ID,
   DISTY_ID,
   DISTY_BUSCODE,
   DISTY_NAME,
   REGION_CODE,
   REGION_NAME,
   O_ACTIVATION_TIME,
   P_ACTIVATION_TIME,
   ACTIVATION_DATE,
   STATE_CODE,
   STATE_NAME,
   PACKAGE_ID,
   PACKAGE_NAME,
   SALES_PIC,
   MSISDN,
   COUNT(1)
 FROM UM2_ACTI_DTL_201305
 WHERE OFFER_TYPE IN(1,3)
     AND ACTIVATION_TYPE = 3
     AND OFFER_ID IN(20000001,20000031,20010010)
     AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)
     AND CHANNEL_ID IN(1,2,4)
     GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE

     

    

    
    
OFFER_ID CHANNEL_ID build an index , then try this:     
    
like few in the content you use = try, if not I have no idea of the amount , do not understand the business application scenarios do not know , hard to say .     
    
    
Another: This query returns you how much data ?    

  

  
in the statement where I also tried using = , no effect .   
The data returned by a query about 17w  

17W , then slow is normal, not slow but not normal , oh ~ ~
------ For reference only ---------------------------------------
their first grab the sofa
------ For reference only --------------------------------------- < br> Are you sure you can perform this statement ? ? ? Not in the aggregate function column , all to appear in the group by statement, you are now in the group by less C, D two , being given it
------ For reference only - -------------------------------------
for this SQL statement in C, D field construction index , A, B building composite index
------ For reference only ---------------------------- -----------
construction of the index , and then filter out as much as possible does not meet the conditions of data , and then grouped
------ For reference only ------ ---------------------------------

you're right , I'm just an example , the actual SQL is more complicated no stickers.
But the assumption is correct that how SQL optimize it ? Actual requirements are grouped according to several fields , and then each packet statistics the number of records , how to achieve that ?
------ For reference only -------------------------------------- -

I've tried this , plus the index had little effect , the effect is still the same slow query .
------ For reference only -------------------------------------- -

Here is the actual use of SQL, please help me look at what the index has been added too, not much effect
SELECT
DEALER_ID,
DEALER_BUSCODE,
DEALER_NAME,
DEALER_STATUS,
OFFER_ID,
OFFER_NAME,
CHANNEL_ID,
SUB_CHANNEL_ID,
DISTY_ID,
DISTY_BUSCODE,
DISTY_NAME,
REGION_CODE,
REGION_NAME,
O_ACTIVATION_TIME,
P_ACTIVATION_TIME,
ACTIVATION_DATE,
STATE_CODE,
STATE_NAME,
PACKAGE_ID,
PACKAGE_NAME,
SALES_PIC,
MSISDN,
COUNT (1)
FROM UM2_ACTI_DTL_201305
WHERE OFFER_TYPE IN (1,3)
AND ACTIVATION_TYPE = 3
AND OFFER_ID IN (20000001,20000031,20010010)
AND PACKAGE_ID IN (322,197,326,566,318,576,277,374,377,581)
AND CHANNEL_ID IN (1,2,4)
GROUP BY DEALER_ID, PACKAGE_ID, ACTIVATION_DATE
------ For reference only ----------------- ----------------------

Here is the actual use of SQL, please help me look at what has been added to the index over the , not much effect     
SELECT     
DEALER_ID,     
DEALER_BUSCODE,     
DEALER_NAME,     
DEALER_STATUS,     
OFFER_ID,     
OFFER_NAME,     
CHANNEL_ID,     
SUB_CHANNEL_ID,     
DISTY_ID,     
DISTY_BUSCODE,     
DISTY_NAME,     
REGION_CODE,     
REGION_NAME,     
O_ACTIVATION_TIME,     
P_ACTIVATION_TIME,     
ACTIVATION_DATE,     
STATE_CODE,     
STATE_NAME,     
PACKAGE_ID,     
PACKAGE_NAME,     
SALES_PIC,     
MSISDN,     
COUNT (1)     
FROM UM2_ACTI_DTL_201305     
WHERE OFFER_TYPE IN (1,3)     
AND ACTIVATION_TYPE = 3     
AND OFFER_ID IN (20000001,20000031,20010010)     
AND PACKAGE_ID IN (322,197,326,566,318,576,277,374,377,581)     
AND CHANNEL_ID IN (1,2,4)     
GROUP BY DEALER_ID, PACKAGE_ID, ACTIVATION_DATE    

  

  
  
sql like you can not perform it. In addition to group by the column , the column must have other options statistical functions of it, similar to the sum, average and the like  

can perform , relatively poor efficiency
------ For reference only --------------------------- ------------

I've tried this , plus the index had little effect , the effect is still the same slow query .  

Paste SQL

SELECT
   DEALER_ID,
   DEALER_BUSCODE,
   DEALER_NAME,
   DEALER_STATUS,
   OFFER_ID,
   OFFER_NAME,
   CHANNEL_ID,
   SUB_CHANNEL_ID,
   DISTY_ID,
   DISTY_BUSCODE,
   DISTY_NAME,
   REGION_CODE,
   REGION_NAME,
   O_ACTIVATION_TIME,
   P_ACTIVATION_TIME,
   ACTIVATION_DATE,
   STATE_CODE,
   STATE_NAME,
   PACKAGE_ID,
   PACKAGE_NAME,
   SALES_PIC,
   MSISDN,
   COUNT(1)
 FROM UM2_ACTI_DTL_201305
 WHERE OFFER_TYPE IN(1,3)
     AND ACTIVATION_TYPE = 3
     AND OFFER_ID IN(20000001,20000031,20010010)
     AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)
     AND CHANNEL_ID IN(1,2,4)
     GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE

------ For reference only ----------------------------------- ----
you group by field ...... so much to me that I have no idea how the database allows you to quickly
------ For reference only -------- -------------------------------

I've tried this , plus the index had little effect , the effect is still the same slow query .      

    

    Paste SQL
    

SELECT
   DEALER_ID,
   DEALER_BUSCODE,
   DEALER_NAME,
   DEALER_STATUS,
   OFFER_ID,
   OFFER_NAME,
   CHANNEL_ID,
   SUB_CHANNEL_ID,
   DISTY_ID,
   DISTY_BUSCODE,
   DISTY_NAME,
   REGION_CODE,
   REGION_NAME,
   O_ACTIVATION_TIME,
   P_ACTIVATION_TIME,
   ACTIVATION_DATE,
   STATE_CODE,
   STATE_NAME,
   PACKAGE_ID,
   PACKAGE_NAME,
   SALES_PIC,
   MSISDN,
   COUNT(1)
 FROM UM2_ACTI_DTL_201305
 WHERE OFFER_TYPE IN(1,3)
     AND ACTIVATION_TYPE = 3
     AND OFFER_ID IN(20000001,20000031,20010010)
     AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)
     AND CHANNEL_ID IN(1,2,4)
     GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE

   

  

  
  
OFFER_ID CHANNEL_ID build an index , then try this:   
  
like few in the content you use = try, if not I have no idea of the amount , do not understand the business application scenarios do not know , hard to say .   
  
  
Another: This query returns you how much data ?  

in the statement where I also tried using = , no effect .
This query returns data about the 17w
------ For reference only -------------------------- -------------

this is no way ah , customers need such grouping
------ For reference only --------- ------------------------------

I've tried this , plus the index had little effect , the effect is still the same slow query .          

        

        Paste SQL
        

SELECT
   DEALER_ID,
   DEALER_BUSCODE,
   DEALER_NAME,
   DEALER_STATUS,
   OFFER_ID,
   OFFER_NAME,
   CHANNEL_ID,
   SUB_CHANNEL_ID,
   DISTY_ID,
   DISTY_BUSCODE,
   DISTY_NAME,
   REGION_CODE,
   REGION_NAME,
   O_ACTIVATION_TIME,
   P_ACTIVATION_TIME,
   ACTIVATION_DATE,
   STATE_CODE,
   STATE_NAME,
   PACKAGE_ID,
   PACKAGE_NAME,
   SALES_PIC,
   MSISDN,
   COUNT(1)
 FROM UM2_ACTI_DTL_201305
 WHERE OFFER_TYPE IN(1,3)
     AND ACTIVATION_TYPE = 3
     AND OFFER_ID IN(20000001,20000031,20010010)
     AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)
     AND CHANNEL_ID IN(1,2,4)
     GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE

       

      

      
      
OFFER_ID CHANNEL_ID build an index , then try this:       
      
like few in the content you use = try, if not I have no idea of the amount , do not understand the business application scenarios do not know , hard to say .       
      
      
Another: This query returns you how much data ?      

    

    
in the statement where I also tried using = , no effect .     
The data returned by a query about 17w    

  

  
  
17W , then slow is normal, not slow but not normal , oh ~ ~     

Thank you, I do a little at night , the results came out

SELECT  DEALER_ID,  DEALER_BUSCODE,  DEALER_NAME,  DEALER_STATUS,  OFFER_ID,  OFFER_NAME,  CHANNEL_ID,  SUB_CHANNEL_ID,  DISTY_ID,  DISTY_BUSCODE,  DISTY_NAME,  REGION_CODE,  REGION_NAME,  O_ACTIVATION_TIME,  P_ACTIVATION_TIME,  ACTIVATION_DATE,  STATE_CODE,  STATE_NAME,  PACKAGE_ID,  PACKAGE_NAME,  SALES_PIC,  MSISDN,  COUNT(distinct DEALER_ID,PACKAGE_ID,ACTIVATION_DATE),count(*) FROM UM2_ACTI_DTL_201305WHERE OFFER_TYPE IN(1,3)    AND ACTIVATION_TYPE = 3    AND OFFER_ID IN(20000001,20000031,20010010)    AND PACKAGE_ID IN(322,197,326,566,318,576,277,374,377,581)    AND CHANNEL_ID IN(1,2,4)    GROUP BY DEALER_ID,PACKAGE_ID,ACTIVATION_DATE

so efficiency is a lot higher