www.webdeveloper.com
Results 1 to 2 of 2

Thread: Help! 50sec for one command???

  1. #1
    Join Date
    Sep 2004
    Location
    Kanj, Slovenia
    Posts
    4

    Unhappy Help! 50sec for one command???

    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?
    www.SloMusic.com

  2. #2
    Join Date
    Nov 2003
    Posts
    655
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles