Database access and performance

[Yasmin_lady]

We are using Coldfusion MX with an MS Access database, our website has a very
long loading time .. specialy when we make a database access.
Can you tell me what is the problem here ..
Which database software would bring more performance
Is MySQL a good alternative

Tahnk you very much

[Dan Bracuk]

Turn on de-bugging and see how long your query takes. If the problem is the
query, see if you can make it run faster. MS Access is not necessarily slow,
unless you are attempting to pull 10,000 records where the where clause refers
to unindexed fields.

[Mountain Lover]

Unfortunately, the JDBC drivers that connect to Access in CFMX are of
type 1 and only allow one connection at a time, so any increase in load
means a decrease in performance...maybe even ending up with no response
from the server. It's happened to me! What ran perfectly on CF5 with
Access no longer works (under any load) on CFMX. Move to MySQL, the
performance increase is worth it! Also don't store client variables in
an Access database for the above mentioned reason. So the problem isn't
really Access, the problem is the JDBC drivers for Access.
My #DollarFormat(0.02)#. ;-)
HTH

On 9/8/2005 8:13 AM Yasmin_lady intelligently wrote:

> We are using Coldfusion MX with an MS Access database, our website has a very
> long loading time .. specialy when we make a database access.
> Can you tell me what is the problem here ..
> Which database software would bring more performance
> Is MySQL a good alternative
>
> Tahnk you very much
>

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[Yasmin_lady]

Thank you very much for your help.
Do you have any idea how to imigrate a MS Access database into a MySQL one.

[Mountain Lover]

This works pretty well
[url]http://www.bullzip.com//products/msa2mys/info.php[/url]

and the MySQL Migration Toolkit (beta), which didn't work very well the
last time I tried...but I haven't tried it for a couple of versions.
[url]http://dev.mysql.com/downloads/migration-toolkit/1.0.html[/url]

HTH

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[RobBurn]

You can use dbtools [url]http://www.dbtools.com.br/EN/index.php[/url]

I used access for my database and it actually does well using the odbc drivers
in mx6.1
I moved to mysql as it was very easy to setup. I used the dbtools - it has a
import tool that
works perfect with access databases. You can view the tables and make changes
etc like access
gui. Connecting to mysql 4.1 with 6.1 you could use odbc or the jdbc... if you
use the jdbc you might not be
able to use the latest connector as there have been reports on this forum of
problems. I'm using mysql-connector-java-3.0.16-ga-bin.jar on cf 7. I think .17
works ok as well but the later ones have some issues
depending on your code.

I routinely get files in excel etc and bring them into access etc and convert
them over to mysql with dbtools so
it works great as it creates all the column names etc etc... Oh yeah it's free
for there professional edition. I'd not use it for user administration, get
mysql's administration tool.

You need to look at what your using this for? Access can be good for up to 1
million records. Dan was correct to turn on all debugging and it will show you
where your slow queries are and go from there. You need to index your database
based on your search criteria and try to stay away from "select *" and "like"
as they can make things slow.

MySql has benefits and problems, but generally it works very well and the
install is pretty simple. With dbtools and
mysql admin tool you do not need to get into any dos commands with mysql :)

Rob