SELECT AccID, SUM(Debit) as Debit,SUM(Credit) as Credit FROM
tbltransactionac where eDate<=CURDATE() GROUP BY AccID

mysql> SHOW CREATE TABLE tbltransactionac\G -- engine, indexes
*************************** 1. row ***************************
Table: tbltransactionac
Create Table: CREATE TABLE `tbltransactionac` (
`ID` int(11) NOT NULL,
`eDate` date DEFAULT NULL,
`DocNo` int(11) DEFAULT '0',
`Type` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
`AccID` int(11) DEFAULT '0',
`PayDesc` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
`Debit` decimal(19,2) DEFAULT '0.00',
`Credit` decimal(19,2) DEFAULT '0.00',
`EntryGroup` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `AccID` (`AccID`,`Type`) USING BTREE,
CONSTRAINT `AccID` FOREIGN KEY (`AccID`) REFERENCES `tblaccounts` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)


mysql> SHOW TABLE STATUS LIKE 'tbltransactionac'\G -- size
*************************** 1. row ***************************
Name: tbltransactionac
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 114368
Avg_row_length: 142
Data_length: 16269312
Max_data_length: 0
Index_length: 4734976
Data_free: 11534336
Auto_increment: NULL
Create_time: 2011-01-07 14:43:19
Update_time: NULL
Check_time: NULL
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.18 sec)


mysql> EXPLAIN SELECT AccID, sum(Debit) as pDebit, sum(Credit) as pCredit

FROM t
bltransactionac where eDate<=CURDATE() GROUP BY AccID\G -- clues of

inefficienci
es
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbltransactionac
type: index
possible_keys: NULL
key: AccID
key_len: 158
ref: NULL
rows: 114368
Extra: Using where
1 row in set (0.08 sec)

mysql> SHOW VARIABLES LIKE '%buffer%'; --cache size
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 134217728 |
| join_buffer_size | 131072 |
| key_buffer_size | 8384512 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 2097144 |
| sql_buffer_result | OFF |
+-------------------------+-----------+
12 rows in set (0.00 sec)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.Cursor = Cursors.WaitCursor

'DB Connection for MySqlServer
Dim DB = New MySqlConnection()
Dim dbConString As String = "server=127.0.0.1; user=root; password=root;database=dbtest; Character Set=utf8;"
DB.ConnectionString = dbConString

'Variable Declaration
Dim da As New MySqlDataAdapter
Dim ds As New DataSet
Dim dv As New DataView
DataGridView1.DataSource = Nothing
Dim sql As String = TextBox1.Text
'-----------------------------------------------------
Static start_time As DateTime
Static stop_time As DateTime
Dim elapsed_time As TimeSpan
Dim time1, time2 As String
'-----------------------------------------------------

da.SelectCommand = New MySqlCommand(sql, DB)
da.SelectCommand.CommandTimeout = 480

'Timer Start Data Access from MySqlServer
start_time = Now

'Data Access from MySqlServer
Try
DB.open()
da.Fill(ds)
da.Dispose()
DB.close()
Catch ex As MySqlException
MsgBox(ex.Message, , "Error Connection")
End Try

'Timer Stop Data Access from MySqlServer
stop_time = Now
elapsed_time = stop_time.Subtract(start_time)
time1 = elapsed_time.TotalSeconds.ToString("0.000")

'Timer Start Data Load in DataGridView
start_time = Now

'Load in DataGridView
dv = New DataView(ds.Tables(0))
DataGridView1.DataSource = dv

'Timer Stop Data Load in DataGridView
stop_time = Now
elapsed_time = stop_time.Subtract(start_time)
time2 = elapsed_time.TotalSeconds.ToString("0.000")

Label1.Text = dv.Count.ToString & " rows, fetched in " & time1 & "s, and load in GridView " & time2 & "s ."
Me.Cursor = Cursors.Default

End Sub