Professional Web Applications Themes

Slooow access to MySql db form vb6 ado - MySQL

Hi grp, Anyone have an idea why this is so extremely slow? The table has some 30 fields and some 3000 records. Using VB6, ADO, MySQL 5.0.27, MyODBC 3.51.14 In a .bas module Public conn As New ADODB.Connection Sub DBConnect() conn.Open "DSN=MyODBC3" End Sub Sub GetOrder(nr As String, scn As Integer) Dim strSQL As String Dim tmp As Integer Dim i As Integer Dim MyRetValue As Integer Dim rs2 As New ADODB.Recordset Set rs2.ActiveConnection = conn ' rs2.CursorLocation = adUseServer ' rs2.CursorType = adOpenDynamic tmp = Val(nr) strSQL = "SELECT * FROM orders WHERE ordernr = " & tmp & ...

  1. #1

    Default Slooow access to MySql db form vb6 ado

    Hi grp,

    Anyone have an idea why this is so extremely slow? The table has some 30
    fields and some 3000 records.
    Using VB6, ADO, MySQL 5.0.27, MyODBC 3.51.14

    In a .bas module
    Public conn As New ADODB.Connection

    Sub DBConnect()
    conn.Open "DSN=MyODBC3"
    End Sub

    Sub GetOrder(nr As String, scn As Integer)
    Dim strSQL As String
    Dim tmp As Integer
    Dim i As Integer
    Dim MyRetValue As Integer

    Dim rs2 As New ADODB.Recordset

    Set rs2.ActiveConnection = conn
    ' rs2.CursorLocation = adUseServer
    ' rs2.CursorType = adOpenDynamic

    tmp = Val(nr)
    strSQL = "SELECT * FROM orders WHERE ordernr = " & tmp & ";"

    rs2.Source = strSQL
    rs2.Open
    ' ** This will take 5.5 seconds to execute! **
    ...
    snipped code
    ...
    rs2.Close
    Set rs2 = Nothing

    End Sub

    /Henning


    --
    Time is present only to prevent everything from happening at once.
    Still it seems that everything happens at once.
    Then there must be a bug in time.
    To find the bug in time, isn't that what we all hope for.


    Henning Guest

  2. #2

    Default Re: Slooow access to MySql db form vb6 ado

    Henning wrote: 

    "Mr. McChanick, my car won't start. What's wrong?"

    Way too little information to have any idea hear.

    For instance, what's the table definition? What indexes do you have
    defined? Is the database local or remote? What do you get when you
    EXPLAIN the query?

    We need a lot more detailed info to help you determine the cause.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Slooow access to MySql db form vb6 ado


    "Jerry Stuckle" <net> skrev i meddelandet
    news:com... 
    >
    > "Mr. McChanick, my car won't start. What's wrong?"
    >
    > Way too little information to have any idea hear.
    >
    > For instance, what's the table definition? What indexes do you have
    > defined? Is the database local or remote? What do you get when you
    > EXPLAIN the query?
    >
    > We need a lot more detailed info to help you determine the cause.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    CREATE TABLE `orders` (
    `ordernr` smallint(3) unsigned NOT NULL,
    `Pnr` varchar(10) NOT NULL,
    `Tel` varchar(11) NOT NULL,
    `adress` varchar(20) NOT NULL,
    `Comment` varchar(20) NOT NULL,
    `Datum` varchar(6) NOT NULL,
    `Antal` tinyint(2) unsigned NOT NULL default '0',
    `Sko1` smallint(4) unsigned NOT NULL default '0',
    `Sko2` smallint(4) unsigned NOT NULL default '0',
    `Sko3` smallint(4) unsigned NOT NULL default '0',
    `Sko4` smallint(4) unsigned NOT NULL default '0',
    `Sko5` smallint(4) unsigned NOT NULL default '0',
    `Sko6` smallint(4) unsigned NOT NULL default '0',
    `Sko7` smallint(4) unsigned NOT NULL default '0',
    `Sko8` smallint(4) unsigned NOT NULL default '0',
    `Sko9` smallint(4) unsigned NOT NULL default '0',
    `Sko10` smallint(4) unsigned NOT NULL default '0',
    `status` tinyint(2) unsigned NOT NULL default '0',
    PRIMARY KEY (`ordernr`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    From EMS SQL Manager SQL Editor
    EXPLAIN SELECT * FROM orders WHERE ordernr = 5;
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE orders const PRIMARY PRIMARY 2 const 1

    1 rows fetched (31 ms)

    From commandline
    mysql> explain select * from orders where ordernr = 5;
    +----+-------------+--------+-------+---------------+---------+---------+---
    ----
    +------+-------+
    | id | select_type | table | type | possible_keys | key | key_len |
    ref
    | rows | Extra |
    +----+-------------+--------+-------+---------------+---------+---------+---
    ----
    +------+-------+
    | 1 | SIMPLE | orders | const | PRIMARY | PRIMARY | 2 |
    const
    | 1 | |
    +----+-------------+--------+-------+---------------+---------+---------+---
    ----
    +------+-------+
    1 row in set (0.00 sec)

    I also did a test with just 5 records in the table, same timing !?

    TIA /Henning


    Henning Guest

  4. #4

    Default Re: Slooow access to MySql db form vb6 ado

    Henning wrote: 
    >> "Mr. McChanick, my car won't start. What's wrong?"
    >>
    >> Way too little information to have any idea hear.
    >>
    >> For instance, what's the table definition? What indexes do you have
    >> defined? Is the database local or remote? What do you get when you
    >> EXPLAIN the query?
    >>
    >> We need a lot more detailed info to help you determine the cause.
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================[/ref]
    >
    > CREATE TABLE `orders` (
    > `ordernr` smallint(3) unsigned NOT NULL,
    > `Pnr` varchar(10) NOT NULL,
    > `Tel` varchar(11) NOT NULL,
    > `adress` varchar(20) NOT NULL,
    > `Comment` varchar(20) NOT NULL,
    > `Datum` varchar(6) NOT NULL,
    > `Antal` tinyint(2) unsigned NOT NULL default '0',
    > `Sko1` smallint(4) unsigned NOT NULL default '0',
    > `Sko2` smallint(4) unsigned NOT NULL default '0',
    > `Sko3` smallint(4) unsigned NOT NULL default '0',
    > `Sko4` smallint(4) unsigned NOT NULL default '0',
    > `Sko5` smallint(4) unsigned NOT NULL default '0',
    > `Sko6` smallint(4) unsigned NOT NULL default '0',
    > `Sko7` smallint(4) unsigned NOT NULL default '0',
    > `Sko8` smallint(4) unsigned NOT NULL default '0',
    > `Sko9` smallint(4) unsigned NOT NULL default '0',
    > `Sko10` smallint(4) unsigned NOT NULL default '0',
    > `status` tinyint(2) unsigned NOT NULL default '0',
    > PRIMARY KEY (`ordernr`)
    > ) ENGINE=InnoDB DEFAULT CHT=latin1;
    >
    > From EMS SQL Manager SQL Editor
    > EXPLAIN SELECT * FROM orders WHERE ordernr = 5;
    > id select_type table type possible_keys key key_len ref rows Extra
    > 1 SIMPLE orders const PRIMARY PRIMARY 2 const 1
    >
    > 1 rows fetched (31 ms)
    >
    > From commandline
    > mysql> explain select * from orders where ordernr = 5;
    > +----+-------------+--------+-------+---------------+---------+---------+---
    > ----
    > +------+-------+
    > | id | select_type | table | type | possible_keys | key | key_len |
    > ref
    > | rows | Extra |
    > +----+-------------+--------+-------+---------------+---------+---------+---
    > ----
    > +------+-------+
    > | 1 | SIMPLE | orders | const | PRIMARY | PRIMARY | 2 |
    > const
    > | 1 | |
    > +----+-------------+--------+-------+---------------+---------+---------+---
    > ----
    > +------+-------+
    > 1 row in set (0.00 sec)
    >
    > I also did a test with just 5 records in the table, same timing !?
    >
    > TIA /Henning
    >
    >[/ref]

    Ok, how fast is it if you access MySQL directly instead of through the
    ODBC drivers (from the command line)? How about if you use the ODBC
    drivers but don't use ADODB?

    And is this local or remote?

    You've got a lot of products involved here - MySQL, ODBC and ADODB, to
    start. You need to determine which is causing the problem. I doubt
    very much it's MySQL. I don't know about the drivers or ADODB, though.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Slooow access to MySql db form vb6 ado


    "Jerry Stuckle" <net> skrev i meddelandet
    news:com... 
    >
    > "Mr. McChanick, my car won't start. What's wrong?"
    >
    > Way too little information to have any idea hear.
    >
    > For instance, what's the table definition? What indexes do you have
    > defined? Is the database local or remote? What do you get when you
    > EXPLAIN the query?
    >
    > We need a lot more detailed info to help you determine the cause.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Seems I found the cure. In the ODBC driver, Advanced, Flags2, I checked the
    Enable Dynamic Cursor.
    Sounds logical, since I use dynamic cursors ;)
    Now the response is as expected, ie immediate.

    Thanx for your efforts, they led me to the suloution. The timings in the
    tests you proposed, indicated something but sql server was the source to the
    bad timings.

    /Henning

    PS. I have biiig problems with my ISP at the moment, cant even post here ok!
    Timeout, started sending this some 5+ hours ago.
    Sorry for any inconv.








    Henning Guest

Similar Threads

  1. Slooow Build time
    By jlopes151 in forum Macromedia Flex General Discussion
    Replies: 7
    Last Post: May 8th, 01:42 PM
  2. CFMX 7.1 Multi-server SLOOOW Load
    By DCS Admin in forum Coldfusion Server Administration
    Replies: 1
    Last Post: April 8th, 04:59 AM
  3. localhost slooow
    By manauz in forum ASP
    Replies: 1
    Last Post: September 5th, 08:00 PM
  4. Replies: 3
    Last Post: August 25th, 12:23 PM
  5. Open Access form without Access behind it
    By Ray Maas in forum Microsoft Access
    Replies: 1
    Last Post: July 25th, 03:49 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139