ODBC and MySQL


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: ODBC and MySQL

  1. #1
    Confused Guest

    ODBC and MySQL


    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

  2. #2
    Bernie Guest

    Re: ODBC and MySQL


    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



  3. #3
    confused Guest

    Re: ODBC and MySQL


    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

    >



  4. #4
    Bernie Guest

    Re: ODBC and MySQL


    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

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center