Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VB.NET / ADO.NET Suggestions

Posted on 2006-11-10
5
Medium Priority
?
1,026 Views
Last Modified: 2008-01-09
I have an VB.NET 2003/2005 program that is a report server.  It's currently using classic ADO and has no problems... when I stress test it runs select statements every 3 seconds to check a queue on a connection that's global and never closed (if it happens to lose connection, it reconnects).  

Now, I've updated my code to use ADO.NET's SqlConnection, SqlCommand and SqlDataReader (used to use ADODB.Connection and ADODB.Recordset).  Same theory, I create a global connection and set MultipleActiveResultSets=True so the connection can handle multiple data readers (using SQL Server 2005).  The problem is, now the program crashes under heavy load with a not enough system resources error (when there is plenty of RAM left and plenty of disk space left)... I can only surmise this is a leak of some sort.

I believe I'm disposing of all of my commands and then setting them equal to nothing... the connection however is constant so I'm not disposing of it at all unless it should lose connection for some reason, then it re-establishes.  I've tried with and without connection pools...  My brain is fried, I need suggestions to get me back on track.  

I could try opening and closing a connection every three seconds when it runs the query but I thought it would be more effiecient if I didn't, if that assumption is incorrect, also let me know.  I'm putting 500 on this because my brain hurts, it's Friday and I'm going to obsess over this until I figure it out.
0
Comment
Question by:IUFITS
5 Comments
 
LVL 3

Author Comment

by:IUFITS
ID: 17917773
1.)  Does the order I dispose in things make a difference?
2.)  Is it better to close and re-establish with each query, every 3 seconds?
3.)  Is the global connection causing this leak somehow?
4.)  

Additional Code Info:

    '  Conn is then opened in a timer event if it's not established... that event also re-establishes the connection if it's lost
    Public ConnectionString As String = "server=<server>;database=<db>;MultipleActiveResultSets=True;uid=<user>;pwd=<password>"
    Public Conn As New SqlConnection(ConnectionString)

     ' These are my two SQL Command objects
     Dim SqlCommand1 As New SqlCommand("", Conn)
     Dim SqlCommand2 As New SqlCommand("", Conn)

     ' This is how I dispose and remove them
     SqlCommand1.Dispose()
     SqlCommand2.Dispose()
     SqlCommand1 = Nothing
     SqlCommand2 = Nothing
     Dr = Nothing
0
 
LVL 13

Accepted Solution

by:
iHadi earned 2000 total points
ID: 17918513
Hi  IUFITS

When you use Dispose method or when you dereference an object by setting its value to nothing, it stays in memroy until the garbage collector (GC) scans the memory for unused objects and deletes them. You cannot predict when the GC will scan the memory. The GC decides so in a way to optimize performance and resources, so your objects that you disposed might stay in memory from milliseconds to a few days or more depending on the resources and other issues regarding your system. To force the GC to scan the memory and clean it you can use: GC.Collect(). Be carefull though where to use this method because it can cause problems if implemented badly like reducing performance or even raising exceptions in some cases.

Database Connection objects have a lot of overhead, and it takes some time and processor muscles to establish a connection so disconnecting and reconnecting has some performance impact. Instead ADO.NET provides Connection pooling.

The idea behind connection pooling is simple - instead of incurring a large amount of overhead each time a connection to database server is established and closed, once a connection has been opened, it remains open for the lifetime of the process, available to be used again if needed. Pooling database connections can significantly enhance the performance and scalability of your application. The .NET data providers automatically pool connections for you. However, to take maximum advantage of these, you should take note of the following:

- Connections are only pooled if they have the same connection string; so ensure this is always constant.
- When finished with a SqlConnection object, call Dispose() or Close() to release the connection back to the pool.
- In order to keep the maximum number of connections available, you should keep connections open for a short as period as possible - remembering that thanks to connection pooling, re-opening a database connection will incur little overhead.

For more information on connection pooling, take a look at this MSDN page:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

0
 
LVL 6

Expert Comment

by:Jayadev Nair
ID: 17921471
Hi
DataReaders SHOULD be Closed & Disposed. You are doing so right?

Thanks.
0
 
LVL 3

Author Comment

by:IUFITS
ID: 17921796
I'll triple check and make sure that my data readers are being closed and disposed of.  It always crashes on the same line where it loads a crystal report but the reason I don't think that is the problem is because that code hasn't changed and the old engine had no memory issues... the only thing that has changed are the database connections from ADO to ADO.NET.  

iHadi - You brought up something interesting... I was calling gc.collect for reasons I can't remember and it's never been an issue in the past (I would call it during non-report genereating time)...  I wonder if now that I've changed to ADO.NET if it's behaving differently with the new objects... I'll try and comment it out and see how it performs.

The odd thing is that when it says the system it out of resources, the executables aren't really overly large and there's typically plenty of system memory left... I'll post the statistics that I log at crash time (logged the systems virtual and physical memory used and total).
0
 

Expert Comment

by:peterdevadoss
ID: 17928603
you can also try to close the connection when you finished loading the record.

Conn.close
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month13 days, 1 hour left to enroll

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question