Re: Async SQL considerations
1. That's all you need. ExecuteNonQuery is synchronous, i.e. it doesn't return until the database operation is complete. BeginExecuteNonQuery is asynchronous, i.e. it returns immediately and allows you to do other things on the current thread while the database operation is performed in parallel. If the current thread is the UI thread then that means that your UI won't freeze while the UI performs the database operation.
2. You use a Stopwatch to measure the time taken to execute code segments. Create a Stopwatch object and Start it, do your work, Stop it and test its Elapsed property for the elapsed time. Compare that between synchronous and asynchronous calls.
Asynchronous methods are not really about improving speed unless you're making multiple calls so that multiple operations are performed in parallel. If you're just making one call then it's about maintaining a responsive UI while that work is done.
3. It would depend on how the MySQL provider and database are implemented and I don't know anything about that. It may serialise requests from the same source but I wouldn't have thought so. If it receives multiple requests and has the resources to service them in parallel then I would have thought that it would do so. Maybe not though, because some steps within a transaction may depend on others. I would think that it would be the application developer's responsibility to ensure that no step is executed before another that it depends on though.
Re: Async SQL considerations
Quote:
Originally Posted by
jmcilhinney
3. It would depend on how the MySQL provider and database are implemented and I don't know anything about that. It may serialise requests from the same source but I wouldn't have thought so. If it receives multiple requests and has the resources to service them in parallel then I would have thought that it would do so. Maybe not though, because some steps within a transaction may depend on others. I would think that it would be the application developer's responsibility to ensure that no step is executed before another that it depends on though.
I'd expect MySql like SQL Server to "parallelize" client requests only at the connection level meaning that within a single connection any asynchronous calls from ADO.Net would be serialized. Don't take this as gospel though. I'm not absolutely certain but I'd bet my left hand on it.
Re: Async SQL considerations
Thanks for such a quick reply, you answered my questions and more, that I have spent so long Googling.
My scenario is one where I am reading about 10,000 text files, and for each one I build a set of about 200 SQL statements. So my theory is that I read the next text file and build the SQL whilst the previous one is being committed to the database. If I build the next set before the previous SQL is completed, it waits for the cal.
Does this sound like a reasonable use of asynchronous processing?
Re: Async SQL considerations
That certainly sounds reasonable on the face of it. You'd want to do a bit of testing to see how it compares to doing it in serial, which it sounds like you're intending to do, to determine whether there was a benefit and you were placing undue stress on the server.
Re: Async SQL considerations
Quote:
Originally Posted by
batvink
3. I have read that it is only reading a database that takes advantage of asynchronous processing. However I couldn't find much evidence, is this true?
It depends on what you really mean by taking advantage of. Your application certainly can take advantage of the asynchronous processing by doing something else while the database engine updates the data, however if you mean if the server can take advantage of this and do things in parallel then that is really another question. While the data is being written to a table and the indexes are being updated the database engine might put a lock on that table so that another write to the same table might be queued to be executed later on, which means that it might not be able to perform both write requests in parallel. I'm not familiar with the inner workings of MySQL to determine if that is the case or not, but the advantage from your application's standpoint is still that it can do other things while the update is taking place.
Re: Async SQL considerations
Using the update batch size property of the SqlDataAdapter, you can significanlty improve the time for inserting large amounts of data into SQL Server. I've never had to interface with MySql, so I'm not sure if this property is available, but I thought it was worth mentioning so you could check it out.
Code:
da.UpdateBatchSize = 200
Re: Async SQL considerations
Well, UpdateBatchSize is about using the DataAdapter to create a batch of things you want to perform rather than making several asynchronous transactions calls. The difference is that with the DataAdapter you more or less scheduling changes to be performed later on but you're doing it on the client side and you will later tell the server: Here you go, can you perform all of these actions for me please. While with the async transactions you're telling the server that this is what I want you to do and can you please inform me when you're done. Async transactions and batch transactions are not the same thing.
