I can't really speak to OLEDB optimization, but I can speak to changing over to PHP.
The MySQL database connector in PHP is pretty good from a performance standpoint, and PHP itself is blazing fast in my experience. I've seen massive performance gains in PERL by creating an abstraction that PREPAREd repetitive queries, but the big shortcoming in PHP is that it doesn't actually support the ability to PREPARE queries (though the PEAR DB module will emulate it). There may be other good reasons to use PHP, but database performance probably isn't chief among them. Somewhat unrealted, are you sure it's the network link that's the bottleneck? ________________________________ From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Thursday, February 12, 2004 11:28 AM To: mysql Subject: Size v Speed Afternoon all, This is my first post and I have a quick question for you MySQL experts out there. I am running a Red Hat 9 Linux box (2GHz, 1GB of RAM) specifically designed for running a MySQL database. This database is already over 3GB in size with over 120M records in 1 table (with 5 tables at the moment, averaging 50K records per table) and is going to grow exponentially over the next few months. The issue that I am having is that the MyODBC connection that I am using with my ASP system is starting to really slow down to the point of having to limit my connections. I cannot get My OLEDB connections to work at all and I am going to transfer this system to ASP.NET using VB.NET, however if anyone could help now I would greatly appreciate it. Here's the connection string at the moment: conn.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=*****;DATABASE=*****;USER=*****;PASSWORD=*****;OPTION=163 87;" In the /etc/my.cnf file I have: (See Attached) I've tried using different connection options, including FORWARD-ONLY CURSOR. I have already used MYISAMPACK to decrease my size, as well as PROCEDURE ANALYSE() for optimizing the tables. We won't be pulling more than a few thousand records per transaction, but multiple transactions. With this info, here are the questions: Is there anything that I can do to speed up my connection to this server? At the moment, we are still in the testing phase and I am running this over a 100Mbps connection, so any changes are up for grabs. Should I change this to PHP? Will that help any at all? Is this database just going to get too big for it to be quick over the Web? Any information needed will be immediately posted. Thank you so much! J.R. Bullington Innovatim Technical Support [EMAIL PROTECTED] <<...>> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
