MySQL performance [question]

[peter.versnee]

Hi there,

First of all I'd like to say I'm quite new to programming with DB/C. I've learned a lot from my colleagues for now. But I seem to be running into some performance issues. I've read everything available on this site and dbcsoftware.com about MySQL and DB/C.

I'm used to PHP/MySQL so the fact that ISAM files can be faster was a big surprise to me:

Question: How can I convert DB/C programs to use SQL instead of DB/C files? What problems might I encounter? http://www.dbcsoftware.com/dbcfaq.html#SQL

We're willing to accept this - the application will be deployed in large organizations. One of the major things about the ISAM structure for a database is that it has to be shared to all users (i.e. a network share) where each and every user has the right to alter the database. (And thus able to destroy all the data). We've tried to find a solution to this problem but nothing did the job.

So now we're - let's say - stuck with MySQL, although i'm convinced the data could be transferred faster. We've found out that the SELECT statement takes like 1ms, but then the FETCH takes way to long in my opinion. I'm quite used writing SQL, therefore the problem probably isn't that 'simple'.

So I started testing:
Server A: online database server (dedicated, personally owned)
Server B: local database webserver (WAMP)
Client A: my office workstation (a good one) using our DB/C app.
Client B: Actually Server B, PHP script
Client C: Actually Server B, using our DB/C app.

Server A - Client A : slow
Server A - Client B: fast
Server A - Client C: slow
Server B - Client A: slow
Server B - Client B : fast
Server B - Client C : slow

So even network speed (100mbit) can't take the heat, don't think so. The problem - the only thing I can come up with - must be in the communication between the ODBC driver and DB/C.

Now we've 'found' the problem how can we optimize the ODBC driver with all the options / flags we can set? I'm using the MySQL ODBC 5.1 Driver. (See image for all the options. http://geolock.me/mysql_odbc_driver_5.1_flags.pdf.

I now noticed I might got carried away with this long story (especially for a first post on the whole forum) but I hope someone can assist us with the right answers!

In short: we need a centralized database solution that users cannot (willingly) copulate up. Any other solution is welcome as well!

Thanks in advance!

Peter

[birchfld]

In my personal experience, db/c can run significantly faster using mysql, than with isam files. It just depends on how you structure your data and queries.

I've converted thousands of db/c programs and hundreds of db/c isam based tables to mysql exclusive use. It works, and works very well .... in my personal experience.

[birchfld]

Be sure to read the tips section, and pay special attention to this tip:
http://dbcusers.com/showpost.php?p=160&postcount=5

[peter.versnee]

Thanks Birchfld!

birchfld @01:38:
I must confess I didn't mention we already built the application, and then ran into the security problem. So we've made a 1:1 copy of the ISAM database and converted to MySQL.

However the statement made on dbcsoftware.com

More importantly, performance may also be a problem. At best, even with restructuring of your programs, expect to run 3 or more times slower when using SQL compared to the DB/C file system on the same hardware.

should be referring to our speed problem, but not when you develop the software with MySQL in mind, I presume?

birchfld @01:40:
It takes like 1ms to have the SELECT statement executed, logically this is including connection time. (There is no open connection during execution of the application, but connection is made when needed). So the connection time isn't the problem, I expect?

Conclusion: the database structure isn't optimized for the use of MySQL.

Last but not least: are you running a Windows computer with ODBC? Then please advise me what flags to set in the ODBC configuration...!

Thanks!

[birchfld]

I'm running all flavors of Windows, and have also ran several flavors of Linux.

I use the default flags, unmodified in the odbc driver.
But, I use the older mysql-connector-odbc-3.51.22-win32.msi driver.

In my personal experience, I found that two things make the biggest impact to performance:
1. - Make the connection to the database as soon as the application opens, and do not attempt to close it again.
2. - Make sure the queries optimally uses existing mysql indexes ( this can make an absolutely huge performance impact), if not, then create indexes that will be automatically selected by mysql's optimizer.

I do not expect db/c to run as fast as php, java or c#, it will not. But you can get very , very good results by optimizing your queries, and the use of mysql indexes.

[birchfld]

p.s. I can not comment on statements made on dbcsoftware.com ... except that my personal experience may not agree.