MySQL Forums
Forum List  »  Performance

Re: Dramatic performance drop when table > 100MB
Posted by: Patrick Questembert
Date: November 02, 2004 06:46AM

I should have mentioned which operations are slow ... here is more information:

There is no issue with updates actually, i.e. "INSERT INTO mytable ...." - that works just fine and add rows to the table just as fast whether table > 100MB or not.
The problem is with the queries, this is the code:

IDbCommand catCMD = DatabaseConnection.CreateCommand();
catCMD.CommandText = "SELECT msgid FROM mytable WHERE msgid="+W.MsgID+";";
IDataReader myReader = catCMD.ExecuteReader(); // This is the slow command
myReader.Read();
...
myReader.Close();
return;

The statement which takes a very long time is the catCMD.ExecuteReader() call, not accessing the reader after that. It takes between 4 and 5 (!) seconds to complete.
Note also that if I run the program twice in a row, this statement will not be slow for the rows already accessed the previous time I ran the program, i.e. the results seem to be cached and it's only when MySQL actually needs to go and run the query that it takes time.

And it's not an issue of updating the table and reading from it at the same time - even a simple series of queries such as above with no updates shows that problem (for tables > 100MB).

Table is InnoDb, I am using MySQL 4.1/WinXP and the ODBC 3.51 driver.

Thanks!
Patrick

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Dramatic performance drop when table > 100MB
2482
November 02, 2004 06:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.