current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

I am using this code to get data:

 Dim connetionString As String
    Dim connection As SqlConnection
    Dim sqlq As String

    sqlq = "select top 1 * from table1 where smth"

    Dim ds As New DataSet
    connetionString = "Data Source=db;Initial Catalog=ic;User ID=id;Password=pass"
    connection = New SqlConnection(connetionString)

    Try
        Using connection
            Dim command As SqlCommand = New SqlCommand(sqlq, connection)
            connection.Open()
            Dim reader As SqlDataReader = command.ExecuteReader()
            If reader.HasRows Then
                Do While reader.Read()
                    x = reader.GetString(5)
                Loop
            End If
            reader.Close()
        End Using
    Catch ex As Exception
    End Try

This type of connection (with different sqlq [query]) I use a lot in diffenrent functions and every time I close the connection. I want to optimize it so it would take less time to get data. How do I do that?

share|improve this question
2  
That's a rreally, horribly bad idea. Do not keep the database connection open for longer than you absolutely need. Best practice is: open as late as possible, do your work, close as soon as possible again. –  marc_s Nov 26 '12 at 11:19
    
I am thinking to keep connection open as long as application is running as I need to get data as soon as possible, time in here is very important. –  babboon Nov 26 '12 at 11:24
    
As I said: that's a horribly bad idea - don't do it. Seriously. –  marc_s Nov 26 '12 at 11:25

2 Answers 2

up vote 2 down vote accepted

It is best practise to always dispose/close a connection as soon as you're finished with it. Actually the connection-pool will not close the underlying physical connection but only flag this connection as reusable if it is closed. So if you don't close a connection it cannot be reused, hence the pool needs to create a new physical connection which is very expensive.

So only some minor improvements:

Const sql = "select top 1 * from table1 where smth"
Dim table As New DataTable()

Using con = New SqlConnection("Data Source=db;Init ....")
    Using command = New SqlCommand(sql, con)
        Using da= New SqlDataAdapter(command)
            da.Fill(table)
        End Using
    End Using
End Using

You should use Using for any object implementing IDisposable. You should not use an empty Catch block.

share|improve this answer
up vote 2 down vote

Use connection pooling instead.

By default as long as the connection string is the same, connections will be taken from the same connection pool.

Shortly connection pool keeps not needed connections open, and return that connections on the next request, so you don't need to think about the time it take to open connection.

Keeping connections open for longer than you needed is bad idea, because it costs resources, and in general some servers can accept limited connections.

share|improve this answer
    
"Use connection pooling instead" is a bit misleading since by default connection pooling is enabled in ADO.NET. –  Tim Schmelter Nov 26 '12 at 11:48
    
agree, that is on by default, i mean don't turn off ! :) –  ArsenMkrt Nov 26 '12 at 15:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.