Performance issue of group by clause.

Article View: borland.public.interbase.sql

Article #465

Back to borland.public.interbase.sql Previous Next

#465

From: Monalee Bhandge
Date: Thu, 29 Nov 2007 11:49

32 lines
1485 bytes

Dear,
    I have one table "trans_acc" like

trans_  	trans	acc_name	dr_amt	cr_amt
acc_id          _id
1		1	A                 10	0				
2		1	B	           0	20	
3		1	C 		   30	0
4		1	D		   0	40
5		2	B		   45	0
6		2	C		   0	45
7		3	A		   40	0
8		3	B		   0	90	
9		3	C		   60	0	
10		3	D		   0	10

My Query is:

	1.      SELECT SUM(dr_amt), SUM(cr_amt) FROM trans_acc WHERE acc_name='A' GROUP BY acc_name;
 	
	Output:  A		50

	2.     SELECT SUM(dr_amt), SUM(cr_amt) FROM trans_acc GROUP BY acc_name;

   Even I create index on dr_amt, cr_amt, acc_name columns then also I doesn't get that much speed.In this table there are 90,00000 rows.

Thanking you.

Monalee Bhandge.
Database Administrator.
Sungrace Infotech.

Technical Details

Message-ID: <474eb557$1@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news1.google.com!news1.google.com!news.glorb.com!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail

Show Full Headers

Complete Article Headers

X-Google-Thread: 114a0e,5460ac00bbec9e3,start
X-Google-Attributes: gid114a0e,public,usenet
X-Google-Language: ENGLISH,ASCII-7-bit
From: "Monalee Bhandge" <monaleeb@gmail.com>
Sender: "Monalee Bhandge" <monaleeb@gmail.com>
Reply-To: "Monalee Bhandge" <monaleeb@gmail.com>
Subject: Performance issue of group by clause.
Newsgroups: borland.public.interbase.sql
X-User-Info: 59.91.214.169     
NNTP-Posting-Host: 207.105.83.66
Message-ID: <474eb557$1@newsgroups.borland.com>
Date: 29 Nov 2007 04:49:27 -0700
X-Trace: newsgroups.borland.com 1196340567 207.105.83.66 (29 Nov 2007 04:49:27 -0700)
Lines: 32
Path: arc1-ca.pugleaf.net!g2news1.google.com!news1.google.com!news.glorb.com!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail