SEO

#1 (**permalink**) 02-28-2008, 08:15 AM

I am using MS Access through the 3.51 ODBC connector with MySQL.

I have quite a few speed issues and I was wondering if (other than not using
Access

) I was missing something.

If I try to run a delete query from Access to delete every row in a table it
is incredibly slow. If I run the same thing directly on the MySQL box from
the Query Browser it is near instantaneous. Is there anything I can do to
make the Access queries run faster? I am using Access 2003 SP2 and MySQL
4.1. I can't yet use 5 as my ISP doesn't support it yet.

I have written some scripts on the MySQL side and these are very quick. Is
there anyway I can trigger these from Access?

TIA.

#2 (**permalink**) 02-28-2008, 08:15 AM

Post the query you use in MSAccess.

"Sam Smith" <bob@bobinogs.bob> wrote in message
news:efb26e$lof$1$830fa7a5@news.demon.co.uk...
>I am using MS Access through the 3.51 ODBC connector with MySQL.
>
> I have quite a few speed issues and I was wondering if (other than not
> using Access

) I was missing something.
>
> If I try to run a delete query from Access to delete every row in a table
> it is incredibly slow. If I run the same thing directly on the MySQL box
> from the Query Browser it is near instantaneous. Is there anything I can
> do to make the Access queries run faster? I am using Access 2003 SP2 and
> MySQL 4.1. I can't yet use 5 as my ISP doesn't support it yet.
>
> I have written some scripts on the MySQL side and these are very quick. Is
> there anyway I can trigger these from Access?
>
> TIA.
>

#3 (**permalink**) 02-28-2008, 08:15 AM

On Tue, 26 Sep 2006 12:16:27 +0100, Sam Smith wrote:

> I am using MS Access through the 3.51 ODBC connector with MySQL.
>
> I have quite a few speed issues and I was wondering if (other than not using
> Access

) I was missing something.
>
> If I try to run a delete query from Access to delete every row in a table it
> is incredibly slow. If I run the same thing directly on the MySQL box from
> the Query Browser it is near instantaneous. Is there anything I can do to
> make the Access queries run faster? I am using Access 2003 SP2 and MySQL
> 4.1. I can't yet use 5 as my ISP doesn't support it yet.
>
> I have written some scripts on the MySQL side and these are very quick. Is
> there anyway I can trigger these from Access?
>
> TIA.

Use the customization options on your query object to define it as a
PassThrough query. Right click on the query, select "SQL Specific", then
Select PassThrough query. Then make sure you use strict MySQL syntax
as defined in the MySQL docs.

This will bypass the Microsoft libraries (almost!) completely and let
MySQL do it's query thing. MySQL is much better at it and everything
works faster when Microsoft just butts out ;-)

Thomas Bartkus

#4 (**permalink**) 02-28-2008, 08:16 AM

"Thomas Bartkus" <thomasbartkus@comcast.net> wrote in message
news

an.2006.09.27.02.57.10.330577@comcast.net...

> Use the customization options on your query object to define it as a
> PassThrough query. Right click on the query, select "SQL Specific", then
> Select PassThrough query. Then make sure you use strict MySQL syntax
> as defined in the MySQL docs.
>
> This will bypass the Microsoft libraries (almost!) completely and let
> MySQL do it's query thing. MySQL is much better at it and everything
> works faster when Microsoft just butts out ;-)
>
> Thomas Bartkus

Thanks. I'll try that.

---
Sam

#5 (**permalink**) 02-28-2008, 08:16 AM

"Duane Phillips" <duane.phillips@askme.askme> wrote in message
news:GJmdnctVPrBLP4TYnZ2dnUVZ_uidnZ2d@giganews.com ...
> Post the query you use in MSAccess.

It can be anything. Even exporting tables. Everything is very slow. But I
will try the Passthrough technique.

Even a simple delete query will take a very long time over thousands of rows
but the same query in MySQL itself takes less than a second.

---
Sam

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

MS Access with MySQL - Speed Issues