Slooow access to MySql db form vb6 ado - MySQL

[Henning]

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.

[Jerry]

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
==================

[Henning]

"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

[Jerry]

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
==================

[Henning]

"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.