ADO.NET: spawn a thread and sleep?
-
Does anyone know if executing a query in ado.net automatically puts the executing thread to sleep behind the scenes until a result comes back, or could I possibly benefit by spinning off a thread to execute my queries and explicitly waiting for that thead to complete? I'd also be interested to know if running in asp.net makes a difference as opposed to a system service or console app.
-
Nope, executing a query actually uses your thread. You can't use it until ADO.Net is done with it. If you want to do it asychronously, spawn a thread and call ADO.Net from that thread.
Asynchronous programming is more complex in web applications. It's a bit tricky to give a client a "waiting" message and then have the refresh reconnect to the thread that client spawned previously. Console applications rarely use threads unless you want to make a little spinner.
-
@Oscar L said:
Does anyone know if executing a query in ado.net automatically puts the executing thread to sleep behind the scenes until a result comes back, or could I possibly benefit by spinning off a thread to execute my queries and explicitly waiting for that thead to complete? I'd also be interested to know if running in asp.net makes a difference as opposed to a system service or console app.
In some situations it may be beneficial to run your queries in a separate thread and let your main thread wait for the result. This will free the thread to handle other requests while waiting for the database and will allow your application to scale better. At least according to this article: http://msdn.microsoft.com/msdnmag/issues/05/10/WickedCode/
What kind of difference are you thinking of? An ASP.Net application uses the same libraries as a .Net service or console application, so the performance should be more or less identical. When your code is in a webpage several instances of it can run simultaneously in separate threads, but that is also possible with services and console applications.
-
One of the big differences with Asp.Net concerning data access is connection pooling. Under most circumstances, IIS keeps a number of connection to the database open. Therefore when queries are executed, IIS resuses an existing connection and you don't have to pay for all the overhead associated with establishing a new connection. There are cases when this does not apply though such as if the database driver doesn't support pooling, the existing connections have been idle for a long time, connection pooling has been disabled, etc.
If you want to spawn another thread to execute your queries, you could create a web service that returns your data and call the web method asynchrously from the client. Just another option if you don't want to do it fron the main app.
Larry
-
I believe that ADO.NET 2.0 provides asynch DB queries, using events to notify when the results are retreived. If you're not using .NET2 as your platform, you may want to use reflector to see how they do it and implement similar logic in your system.
-
@jsmith said:
Nope, executing a query actually uses your thread. You can't use it until ADO.Net is done with it. If you want to do it asychronously, spawn a thread and call ADO.Net from that thread.
Asynchronous programming is more complex in web applications. It's a bit tricky to give a client a "waiting" message and then have the refresh reconnect to the thread that client spawned previously. Console applications rarely use threads unless you want to make a little spinner.
I'm comfortable with multi-threaded programming, and I was afraid that ado.net blocked while running queries. It would also be a nice notch on my belt to successfully multi-thread our web apps.
-
@Alex Papadimoulis said:
I believe that ADO.NET 2.0 provides asynch DB queries, using events to notify when the results are retreived. If you're not using .NET2 as your platform, you may want to use reflector to see how they do it and implement similar logic in your system.
Thanks for the tip. We're running 2.0, so I'll look into any asynchronous facilities provided with the framework instead of rolling my own.
-
Thanks for the tip Alex. For any of the readers of the thread, here is the link to an appropriate msdn article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/async2.asp
Larry
