Timeline for C# - SQL - Reading every row in a database, datamining the row, then saving results in another database - How to increase speed
Current License: CC BY-SA 3.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 27, 2015 at 16:48 | vote | accept | Larklen | ||
| Aug 27, 2015 at 16:48 | comment | added | Larklen | Hey Russ, okay I have combined both your suggestion and PaulF's, and I think I am at the limit in terms of how fast this can go. It is slightly faster than what I was looking at originally, which is a win in my books. I will have to come up with a method to start where I left of if I end this process early. Thanks! | |
| Aug 27, 2015 at 16:34 | comment | added | Larklen |
Sounds good - i've updated the reader portion to reflect a using statement. I'm sitting about about 2 records per second if they are non-existing about about 1.4 records a second if they are both existing (reading and updating). I think, connection string-wise, this is the best it'll get for me. There is another suggestion above to pull all records from the messages table then sort, rather than getting each individually. I'll work out how I need it down and hopefully it'll add improvement on what you've provided me :)
|
|
| Aug 27, 2015 at 16:27 | comment | added | Russ |
Your error is probably caused by this line: MySqlDataReader reader = cmd2.ExecuteReader();. Since you don't have it within a using statement, the datareader never gets closed and disposed, so it locks the connection until the connection is closed.
|
|
| Aug 27, 2015 at 16:25 | comment | added | Russ | The reason you are getting that error is because you are trying to execute a SQL Command on the connection while the connection is locked by a datareader. One way to resolve this is to use a second connection that you also keep open until done that you use only for the datareader. The other way is to close the datareader (not the connection) before attempting any other SQL command. | |
| Aug 27, 2015 at 16:22 | comment | added | Larklen |
Hey Russ, thanks for the advice! The reason for the Open() and Close() of the connection strings is that, if I leave it open, I get the error There is already an open DataReader associated with this Connection which must be closed first., and closing then reopening resolved that. There may be an underlying issue. -- However, I have been able to remove some of these unneeded open/close (as well as the using portion), but the speed remains the same. I currently now only have two sets of connection.Open()/Close(). Note: It is slightly faster , from 1.3 seconds per record to about 0.9 secs
|
|
| Aug 27, 2015 at 16:17 | history | edited | Russ | CC BY-SA 3.0 |
added 17 characters in body
|
| Aug 27, 2015 at 16:10 | history | answered | Russ | CC BY-SA 3.0 |