Results 1 to 8 of 8

Thread: Async SQL considerations

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2007
    Posts
    36

    Question Async SQL considerations

    I have a couple of questions about writing to a MySQL database using asynchronous transactions...

    1. I am using BeginExecuteNonQuery to insert data, and I have my callback in place. Is this enough, or do I need to do anything else to ensure that it is truly working asynchronously?

    2. Is there any way to measure the effectiveness and improved efficiency?

    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?
    Thanks in advance
    Steve V
    ten-net consulting limited

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  3. #3
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    8,598

    Re: Async SQL considerations

    Quote Originally Posted by jmcilhinney View Post
    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.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell | I'm not wanted

    C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter

    There's just no reason to use garbage like InputBox. - jmcilhinney

    The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber

  4. #4

    Thread Starter
    Member
    Join Date
    Jun 2007
    Posts
    36

    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?
    Thanks in advance
    Steve V
    ten-net consulting limited

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  6. #6
    I'm about to be a PowerPoster! Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,649

    Re: Async SQL considerations

    Quote Originally Posted by batvink View Post
    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.

  7. #7
    Code Monkey wild_bill's Avatar
    Join Date
    Mar 2005
    Location
    Montana
    Posts
    2,993

    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
    That is the very essence of human beings and our very unique capability to perform complex reasoning and actually use our perception to further our understanding of things. We like to solve problems. -Kleinma

    Does your code in post #46 look like my code in #45? No, it doesn't. Therefore, wrong is how it looks. - jmcilhinney

  8. #8
    I'm about to be a PowerPoster! Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,649

    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.
    Last edited by Joacim Andersson; May 7th, 2013 at 02:11 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width