SQL Server Performance Forum – Threads Archive

Group by clause

Hi.. Is there any other alternative way for selecting group by records..my qry is taking little bit time for retreiveing records… ex..
select
b.dealer_id,
b.entity_id,
b.pay_to_dealer_group_flag,
b.payment_required_flag,
b.preferred_payment_type,
b.bank_account_id
from
#dealer_branch a,
dealer b
where
a.dealer_id = b.dealer_id
group by
b.dealer_id,
b.entity_id,
b.pay_to_dealer_group_flag,
b.payment_required_flag,
b.preferred_payment_type,
b.bank_account_id SURYA

---

Does the #dealer_branch temporary table have a primary key, or at least an index on dealer_id? If it contains a reasonable amount of rows, you’ll need one.

---

Yes, its there primary key on dealer_id..i want output little bit faster..
the present one is working..but thinking that any alternative way.. SURYA

---

Okay, just looked at your query again. Dealer_id is the PK in both tables, right? Then you don’t need the GROUP BY, as there won’t be any duplicates returned anyway. Removing the GROUP BY should speed things up noticeably.

---

thanx..adriaan…i wll chk it and let u know….
SURYA

---

When you dont use any aggregate functions, why do you use Group by Clause? Madhivanan Failing to plan is Planning to fail

---

Removing duplicates? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

Then, isnt Distinct enough? [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

I’m not sure????
Isn’t it that the execution plan for select DISTINCT and select columns group by columns will be same??????

---

first query i have removed entire group by cluase its working fine..thanks all just now i found some other query its taking time, have to write alternative qry..
please find below query once and let me know…for any aleternative way the data is like this table: adv_acc adv_acc_id 1
2
3
4
5 table: adv_trans adv_trans_id adv_acc_id 62632882
147112882
83222882
46412890
8542890
24142890
23062911
43662911
1167512911 I wrote the qry like this… selecta.adv_acc_id,
a.branch_id,
from
adv_acca
adv_transb,
where
a.adv_acc_id = b.adv_acc_id
group by
a.adv_acc_id adv_acc_id col is uniquey but second table having many records for same id, that is why i have used group by cluase
cab we write any other way.. thanks in advance.. SURYA

---

Hi Surya, The above query definetly wont work as your are selecting 2 columns and using only one column in the group by clause. As you are not using the Aggregate functions you can go for order by clause… Alternate:
selecta.adv_acc_id,
a.branch_id,
from
adv_acca
adv_transb,
where
a.adv_acc_id = b.adv_acc_id
order by
a.adv_acc_id

---

Yes. It will be same. Test it with sample data Madhivanan Failing to plan is Planning to fail

---

but i have to eliminate group by clause and distinct in query.. selecta.adv_acc_id,
a.branch_id,
from
adv_acca
adv_transb,
where
a.adv_acc_id = b.adv_acc_id
group by
a.adv_acc_id,
a.branch_id SURYA

---

Hi,
Run execution plan & index the tables.

---

What kind of results do you want to see returned by the query? In a one-to-many relationship, when you join the two tables and you want to see the "one" value with the minimum/maximum/average … of the matching "many" values, then the simplest way is to use a GROUP BY. If you don’t need details from the "one" table, you can base the query on only the "many" table and group on the foreign key column. This might improve performance.

---

]]>