Help! 50sec for one command???

[pinkpanter]

Helo!

I have aplication in ASP.NET and uses Oracle Database.
I have this function:

Public Shared Function ExecuteDataset(ByVal storedProcedure As String, ByVal dbParam() As Oracle.DataAccess.Client.OracleParameter, Optional ByVal connString As String = "") As DataSet 'ByVal dbParam As Oracle.DataAccess.Client.OracleParameterCollection) As DataSet
If connString = "" Then connString = ConnectionString 'bere iz property-a!
Dim conn As New Oracle.DataAccess.Client.OracleConnection(connString) 'naredimo connection do baze
Dim comm As New Oracle.DataAccess.Client.OracleCommand 'nov oracle command
Dim da As New Oracle.DataAccess.Client.OracleDataAdapter(comm) 'naredimo data adapter, ki podatke prebere iz baze
Dim ds As New DataSet 'naredimo nov dataset
Dim param As New Oracle.DataAccess.Client.OracleParameter 'deklaracija parametra
Dim cursorParameter As New Oracle.DataAccess.Client.OracleParameter("registriWeb_cursor", Oracle.DataAccess.Client.OracleDbType.RefCursor) 'cursor
Dim i As Integer

comm.Connection = conn
comm.CommandText = storedProcedure
comm.CommandType = CommandType.StoredProcedure

'vsak parameter v arrayu dodamo v command
For i = 0 To UBound(dbParam)
comm.Parameters.Add(dbParam(i))
Next

'dodamo cursor
cursorParameter.Direction = ParameterDirection.InputOutput
comm.Parameters.Add(cursorParameter)

conn.Open()
da.Fill(ds) 'z data adapterjem napolnimo dataset
conn.Close()

comm.Parameters.Clear()

'vrnemo dataset
Return ds
End Function

and command da.Fill(ds) takes 50sec to execute. Call of the same Oracle procedure takes 3 sec, so I don't know what to look for...

Any Idea?

[CardboardHammer]

Disclaimer: I don't use .NET with Oracle, so I don't claim to have knowledge specific to Oracle.

My bet is the problem is that you're using a cursor. I'm also guessing that the code is not being run on the database server, so the cursor overhead is even worse than it otherwise would be. And read/write cursors have a bigger performance hit than read only cursors. By the looks of your code, a cursor isn't necessary at all. Cursors are OK within stored procedures, but can be the devil when used in other situations, and generally should only be used when necessary.