January 24, 2005

3:47 AM

Database Query [+]
posted by benjh to (11 comments total)

I started a mySQL database server in hopes of migrating a large database from Access to an SQL server to speed things up, and allow more people access to it.

However, my company wants to retain the Access interface. I installed the Access/mySQL ODBC drivers, and did all that stuff. But I'm still having some issues, primarily with the speed it is retrieving data. It seems like it should be going faster than it is, but doesn't seem to be going any faster than Access did.

I'm also having an issue of administration. I have a freeware copy of DBTools I am trying, but I can't seem to import data into an existing table, only create new ones when I import.

Each day I will receive an Excel spreadsheet with data in it, and will need to add the additional data to the database.

Any suggestions on getting this thing up, tools to use, and the easiest way to get this thing running as quickly as possible?
posted by benjh at 3:47 AM on January 24, 2005

Also, mySQL is the only server running on this box. It does not have Apache or any other web server software applications installed.

If I were to try to convince the management to go to a web based interface for the database, would it be hugely difficult to write in PHP in dreamweaver. (I don't have PHP experience, I'd have to learn as I go.)
posted by benjh at 3:49 AM on January 24, 2005

I've had to do this a few times... and of course, it depends a lot on the data you're trying to work with.

First, Access is VERY sensitive to the way you index data. Do what you can to optimize queries in terms of what data you pull in and how you grab it. If you can get the indexing right, then it can make all the difference in the world.

I'm not necessarily an expert on this, but here are couple of things that have worked for me. First, in some cases I periodically pull data into access using php as kind of a cache, generally once a night. (Access DB's can be added as odbc data sources, same as Mysql...) From there, it becomes a lot quicker to spit out data, because it doesn't have to query the external db.

When I put the actual Access db frontend right on people's desktops instead of on a server, I've generally noticed a huge speed boost, even when querying an external db. The only weirdness there is that you have to install the mysql drivers on the client machine and set up the connection appropriately... which can be a pain in the butt if you're still fiddling with things.

At any rate, this is a good excuse to learn how to program. PHP.net has some good docs/examples on how to use odbc -- it's not really any different than querying a mysql db.

On the excel front, you can use COM to grab data from a native excel doc -- again, there are good examples on php.net as to how to do it. Some programming/tweaking is required, but it's not too evil. (This assumes you've got a copy of excel on your server.) An alternative, if you want to intervene manually and do a quick conversion every day, is to take the excel doc, save it as a tab delimited file, and import it that way...
posted by ph00dz at 4:32 AM on January 24, 2005

By the way... if you have specific questions as to how to do something, let me know and I might be able to point you in the right direction.

Recently, we had to convert an old foxpro db into a lamp-based db, and while the process was a little quirky, it was well worth the effort. At least for us. Now, I just maintain a small cadre of access db applications that I've distributed to people who need to spit out various kinds of formatted reports, but all the data itself lives in a mysql db.
posted by ph00dz at 4:37 AM on January 24, 2005

Some problems I ran into:

-When I imported the old Access database into mySQL, it made one of the fields memo, which shouldn't be.

-It added all sorts of weird indexes in places where I didn't want indexes.

-It wouldn't allow me to import data into an existing table.

Is there a really good GUI tool for Windows for database manipulation?

Note, all of this stuff in running in a Windows environment.
posted by benjh at 5:01 AM on January 24, 2005

phpMyAdmin is a reasonable tool for database manipulation (it's just a directory full of php scripts, so easy to install). I don't think it has to be installed on the same server as MySQL, so if you have PHP running on your own computer you could run it from there. There are probably GUI tools too.
posted by cillit bang at 5:17 AM on January 24, 2005

How did you do the import? Generally, I'll write my own to ensure data integrity... but yeah, I've been doing this stuff for awhile. Here's my process in a nutshell:

1) Build the db the way you want in mysql. Set up the fields to the appropriate type and whatnot.

2) Read the data out of access using php/odbc. You can just dump the data to the screen to make sure you're getting the right stuff that mysql expects (you'll have to reformat the data, typically...)

3) Compare the incoming data to what's already in the mysql db... If it's there already, continue on... otherwise...

4) Insert the data into the mysql db. Make sure it doesn't choke on the data... if it does, go back to step one and make sure the incoming stuff is formatted properly. (Remember, quote marks need to be escaped with slashes, for instance, not an extra quote mark.)

I'm sure there are gui's out there, but I'll second cb's recommendation of phpMyAdmin. While it's not a true windows gui, it does pretty much everything one might want... I generally prefer it to the mssql equivalent.
posted by ph00dz at 5:26 AM on January 24, 2005

I have resolved many issues, including speed. However, I have a new problem:

I use Microsoft Access to connect to the mySQL server using myODBC. However, when I close Access and open it back up, it loses the connection to myODBC/mySQL and I have to remove the linked table and re-add it in order to have it work. Any ideas on how to resolve?
posted by benjh at 7:06 AM on January 24, 2005

It seems like it should be going faster than it is, but doesn't seem to be going any faster than Access did.

You can (and should) tweak the settings of mySQL to balance out the size of the database with the size of the user pool. Unfortunately, I can't help you with specifics since I generally go with the default installation, but I'm sure you'll be able to get some advice on, say, experts-exchange. Here's a pertinent book review on Slashdot -- perhaps some of the 5-Informative comments can point you in the right direction.

The other thing to consider is that MySQL (usually, but not always) locks the entire table when doing row inserts. From their documentation:

Currently, MySQL supports table-level locking for ISAM, MyISAM, and MEMORY (HEAP) tables, page-level locking for BDB tables, and row-level locking for InnoDB tables.
[...]
To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and BDB.

Is there a really good GUI tool for Windows for database manipulation?

Unfortunately, not really. phpMyAdmin is absolutely terrible in terms of UI, but then, it DOES do the job and has never bugged-out on me, which is better than nothing.

we had to convert an old foxpro db into a lamp-based db

I had to convert a HUGE FileMaker Pro database to mySQL. I don't think I've swore more for longer periods of time. The FM author seems to think it's the bees knees, so he adamately refuses to support, say, FM->Access conversion. When that project was through I felt like tracking the guy down and beating him over the head with a mySQL desk reference or two.

Anyway, if the original author is really concerned about multi-user speed with updates, they might want to consider PostgreSQL. It even has a semi-decent GUI frontend.
posted by Civil_Disobedient at 7:11 AM on January 24, 2005

In general, ODBC drivers run slower than native drivers. That's possibly one of the factors here.
posted by Zed_Lopez at 8:29 AM on January 24, 2005

Re: a Windows admin GUI, last time I was mucking about with MySQL I downloaded a tool called MySQL Control Center (I've got 0.92 beta) that gives me a reasonable Windows admin interface. I can view all my databases (all one of them), browse the objects and run SQL scripts against them.

I don't follow MySQL development so I don't know where the tool is now, but I got it from mysql.com, so perhaps it got rolled into the administration center or something.
posted by grahamspankee at 8:54 AM on January 24, 2005