Datareader speed problem in vs.2008

Question

Hi,

I have 20 different methods and use datareader to read and get result from these functions in the same event.In top of page I create datareader and then begin to load it step by step(It uses same connection and same data access function).Till 15.function datareader loads without problem but after 15,it loads slowly(record count is about 20-30).When i close datareader after 15.function,this problem doesnt occur.But now after 15.function,i should close datareader if i execute some function.Why does this problem occur,I dont know.I posted sample code here.

'Trying method 1

strSQL.ToString="Select * from A"

dr = DB_Gateway.ReadAndBind(strSQL.ToString)


'Trying method 2

strSQL.ToString="Select * from B"

dr = DB_Gateway.ReadAndBind(strSQL.ToString)

'Trying method 15

strSQL.ToString="Select * from K"

dr = DB_Gateway.ReadAndBind(strSQL.ToString)

AFTER 15. EXECUTION,DATAREADER BEGINS TO LOAD DATA SLOWLY.WHEN I ADD DR.CLOSE AND EXECUTE IT,I DONT HAVE PROBLEM.IF I DONT DO IT,IT LOADS 20 RECORDS WITHING 5 SECONDS.THIS IS MY READANDBIND FUNCTION.I AM CONNECTING ORACLE 11 G.WHAT CAN CAUSE THIS PROBLEM?

Public Shared Function ReadAndBind(ByVal SQL As String) As OracleDataReader 




Dim oraCommand As New OracleCommand 




With oraCommand 

.Connection =


New OracleConnection(CONN_NAME) 

.CommandText = SQL




Dim dtreader As OracleDataReader 




Try

.Connection.Open()

dtreader = .ExecuteReader(CommandBehavior.CloseConnection)



Catch ex As Exception 

Exception_Save(ex.Message, oraCommand.ToString)




Throw



Finally



'.Connection.Close()



'.Connection.Dispose()

oraCommand.Dispose()

oraCommand =

Nothing



End Try



Return dtreader 




End With 






End Function

Answer 1

No, you are not using the same connection for all the commands, you are opening a new connection for each one. As you fail to close them, at the end of the code you will be having 20 database connections open at once.

Also, you are not using a single data reader, you are creating a new data reader for each query. When you assign the method result to the dr variable it's not reusing the data reader, it's throwing away the reference to one reader and replaces it with a new one. It's normal to use one reader for each result, but it means that you have to close each data reader before getting the next, or you will get an unreachable object that holds on to a database connection until the garbage collector removes it.

If you close each reader before getting the next, the database connection will be closed and returned to the connection pool so that it can be reused for the next query. Slightly better would be to create a single connection object for the page and use that for each command, that will save a few round trips to the database.