How to optimise query?

[YogeshM]

Hi,

I'm currently running a daily schedular that copies records from a Microsoft
Access database and dumps them into a MySQL db. There are 2 options actually, 1
where only the latest records from the Access db are appended to the MySQL db,
and 2nd, where the users want to re-interface all the records.

The problem is, there are more than 30,000 records in the Access db (and it's
expanding day-by-day) and it's taking more than 3-5 mins to select all and dump
it into the MySQL db and the users are complaining that this is much too slow.

Is there a way to do this faster? Any way to optimise the query? Any tool that
already exists for that?

I would also like to show a progress bar while the program is executing. Could
somebody please explain to me how to do this?

Thanks and regards,
Yogesh Mahadnac

[Neculai Macarie]

YogeshM wrote:

> Hi,
>
> I'm currently running a daily schedular that copies records from a
> Microsoft Access database and dumps them into a MySQL db. There are 2
> options actually, 1 where only the latest records from the Access db
> are appended to the MySQL db, and 2nd, where the users want to
> re-interface all the records.
>
> The problem is, there are more than 30,000 records in the Access db
> (and it's expanding day-by-day) and it's taking more than 3-5 mins to
> select all and dump it into the MySQL db and the users are
> complaining that this is much too slow.
>
> Is there a way to do this faster? Any way to optimise the query? Any
> tool that already exists for that?

A few tips:
- if you have a lot of indexes on the MySQL table disable them before
inserting the rows and enable them after;
- try using MySQL's extension to INSERT:
INSERT INTO table(title)
VALUES ('t1'), ('t2'), ....;

> I would also like to show a progress bar while the program is
> executing. Could somebody please explain to me how to do this?

A very crude sample:

<div style="width:400px;"><div id="progress" style="width:0px;"></div></div>

and cfflush these js bits every 100 records or so:
<script>
document.getElementById("progress").style.width = (#CurrentRow#*400) /
#RecordCount# + "px";
</style


--
<mack />