Results 1 to 3 of 3

Thread: Performance::

  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Question Unanswered: Performance::

    hello friends,

    am facing a query speed issue. below my sql statment.
    Code:
    SELECT AccID, SUM(Debit) as Debit,SUM(Credit) as Credit FROM
    tbltransactionac where eDate<=CURDATE() GROUP BY AccID
    am make video for better understanding.
    YouTube - MySQL Query Speed Slow

    and this is my table and database informate

    Code:
    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)
    am working with vb.net 2005 and mysql .net connector 6.2.2

    my vb.net code

    Code:
    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

    :: tellme any suggestion ::tellme any suggestion ::tellme any suggestion ::


  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ze goggles! zey do nossink!!!

    add an index on your eDate column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2011
    Posts
    2

    Question

    thanks

    Quote Originally Posted by r937 View Post
    ze goggles! zey do nossink!!!

    add an index on your eDate column
    am add index but
    still take long time 59s.
    difference --
    without index it's 91s.
    it's much better

    can much faster
    is it posible ???????

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •