ODBC and MySQL

[Confused]

2 questions really.
My app needs to pull records from a MySQL DB using the MyODBC driver. There
can possibly be upwards of 10000 rows returned.

1) How can you make the queries execute (or appear to execute) faster. For
comparison...PHP and MySql < 3 secs VB and ODBC on same MySql DB > 45 secs
unless the recordset is over approx 1500 rows in which case it fails due
to low memory. Small queries are well within tolerances but the large queries
are far to slow. Id like to avoid using the LIMIT statement if possible in
case the DB is eventually ported to an MS DB.

2) How can you determine the number of rows without having to return the
entire query? I wish to display "Records 1-100 of 5763" or "Page 1 of 58".

Thanks in advance
LT

[Bernie]

Hi LT,

1) You don't say anything about number of columns, columntype, content and
so on. Have you specified the columns you really need or do you use 'SELECT
* ...' by laziness? Have you tried if there are any differences when using
OLE DB? The ISAM style tables also executes faster than the InnoDB style
but I assume you keep MySQL somewhere else than on the same machine running
VB...

2)Execute 'SELECT Count' with the same where clause. This returns only one
row consisting of the number of rows and executes much faster.

Bernie

"Confused" <yakbutter@mailcity.com> wrote:
>
>2 questions really.
>My app needs to pull records from a MySQL DB using the MyODBC driver. There
>can possibly be upwards of 10000 rows returned.
>
>1) How can you make the queries execute (or appear to execute) faster. For
>comparison...PHP and MySql < 3 secs VB and ODBC on same MySql DB > 45 secs
>unless the recordset is over approx 1500 rows in which case it fails due
>to low memory. Small queries are well within tolerances but the large queries
>are far to slow. Id like to avoid using the LIMIT statement if possible
in
>case the DB is eventually ported to an MS DB.
>
>2) How can you determine the number of rows without having to return the
>entire query? I wish to display "Records 1-100 of 5763" or "Page 1 of 58".
>
>Thanks in advance
>LT

[confused]

Thanks Bernie but it turns out the problem is my own stupidity. I timed from
the query issue to results displayed. The hold up was in the grid display
not the DB drivers. My bad! Sooo, it turns out I can use the Recordset .recordcount
instead of using two queries for the record counts.

Anywhoo, you got any good websites about the ISAM stuff though. Never used
it before but have heard it can make importing text files much faster which
could be very useful in this other project I have going on.


"Bernie" <magnus.bernroth@rejlers.se> wrote:
>
>Hi LT,
>
>1) You don't say anything about number of columns, columntype, content and
>so on. Have you specified the columns you really need or do you use 'SELECT
>* ...' by laziness? Have you tried if there are any differences when using
>OLE DB? The ISAM style tables also executes faster than the InnoDB style
>but I assume you keep MySQL somewhere else than on the same machine running
>VB...
>
>2)Execute 'SELECT Count' with the same where clause. This returns only one
>row consisting of the number of rows and executes much faster.
>
>Bernie
>
>"Confused" <yakbutter@mailcity.com> wrote:
>>
>>2 questions really.
>>My app needs to pull records from a MySQL DB using the MyODBC driver. There
>>can possibly be upwards of 10000 rows returned.
>>
>>1) How can you make the queries execute (or appear to execute) faster.
For
>>comparison...PHP and MySql < 3 secs VB and ODBC on same MySql DB > 45
secs
>>unless the recordset is over approx 1500 rows in which case it fails due
>>to low memory. Small queries are well within tolerances but the large queries
>>are far to slow. Id like to avoid using the LIMIT statement if possible
>in
>>case the DB is eventually ported to an MS DB.
>>
>>2) How can you determine the number of rows without having to return the
>>entire query? I wish to display "Records 1-100 of 5763" or "Page 1 of 58".
>>
>>Thanks in advance
>>LT
>

[Bernie]

Isn't 'our own stupidity' wonderful? How many hours of extra work it has caused...


I recommend you to download the PDF file of MySQL Documentation found at
www.mysql.com/documentation/index.html. I printed it and put it in a folder
(It's BIG so printing on both sides is recommended). You can't manage without
it...

Bernie

"confused" <yakbutter@mailcity.com> wrote:
>
>Thanks Bernie but it turns out the problem is my own stupidity. I timed
from
>the query issue to results displayed. The hold up was in the grid display
>not the DB drivers. My bad! Sooo, it turns out I can use the Recordset .recordcount
>instead of using two queries for the record counts.
>
>Anywhoo, you got any good websites about the ISAM stuff though. Never used
>it before but have heard it can make importing text files much faster which
>could be very useful in this other project I have going on.
>
>
>"Bernie" <magnus.bernroth@rejlers.se> wrote:
>>
>>Hi LT,
>>
>>1) You don't say anything about number of columns, columntype, content
and
>>so on. Have you specified the columns you really need or do you use 'SELECT
>>* ...' by laziness? Have you tried if there are any differences when using
>>OLE DB? The ISAM style tables also executes faster than the InnoDB style
>>but I assume you keep MySQL somewhere else than on the same machine running
>>VB...
>>
>>2)Execute 'SELECT Count' with the same where clause. This returns only
one
>>row consisting of the number of rows and executes much faster.
>>
>>Bernie
>>
>>"Confused" <yakbutter@mailcity.com> wrote:
>>>
>>>2 questions really.
>>>My app needs to pull records from a MySQL DB using the MyODBC driver.
There
>>>can possibly be upwards of 10000 rows returned.
>>>
>>>1) How can you make the queries execute (or appear to execute) faster.
>For
>>>comparison...PHP and MySql < 3 secs VB and ODBC on same MySql DB > 45
>secs
>>>unless the recordset is over approx 1500 rows in which case it fails due
>>>to low memory. Small queries are well within tolerances but the large
queries
>>>are far to slow. Id like to avoid using the LIMIT statement if possible
>>in
>>>case the DB is eventually ported to an MS DB.
>>>
>>>2) How can you determine the number of rows without having to return the
>>>entire query? I wish to display "Records 1-100 of 5763" or "Page 1 of
58".
>>>
>>>Thanks in advance
>>>LT
>>
>