Re: Dramatic performance drop when table > 100MB
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
Subject
Views
Written By
Posted
3995
November 01, 2004 09:48PM
Re: Dramatic performance drop when table > 100MB
2738
November 02, 2004 06:46AM
2565
November 02, 2004 10:38AM
3022
November 02, 2004 04:14PM
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.