locked
ASP.NET slower than ASP RRS feed

  • Question

  • User1570828477 posted

    Hello.

    As the subject say, I'm working on an ASP.NET project.

    The DBMS assigned is Oracle 10g.

    There is a previous version or project made in ASP.

    So the problem is: In my ASP.NET project the queries are executed so much slower than in ASP. The queries are the same in both cases, even in the ASP.NET project the queries are in stored procedures they are slower and I really don't know why, because it is supposed that SP's are faster than SQL Injection. 

    Thank you in advance :)

    Tuesday, June 28, 2011 12:22 PM

Answers

  • User269602965 posted

    Use OracleDataAccess (ODP.NET) and not ASP.NET default System.Data.OracleClient

    ODP.NET is much faster and System.Data.OracleClient is deprecated (no longer supported) by Microsoft.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 4, 2011 3:03 PM

All replies

  • User-451260051 posted

    Can you post the code you are using?

    Tuesday, June 28, 2011 1:40 PM
  • User-1694870838 posted

    Hi,

    we can't guess what issue in your project without you codes,you can create index to improve your query speed if it is a big number of datas in your table.

    Best Regards,

    Damon

    Sunday, July 3, 2011 9:12 PM
  • User269602965 posted

    Use OracleDataAccess (ODP.NET) and not ASP.NET default System.Data.OracleClient

    ODP.NET is much faster and System.Data.OracleClient is deprecated (no longer supported) by Microsoft.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 4, 2011 3:03 PM
  • User1570828477 posted

    I'm using the Oracle.DataAccess provided by Oracle.

    And the code in .Net:

    This is the method that I use in the webform to call the method in my MethodsLib (which has all the methods that calls the stored procedures)

    Private Sub Consult()
            Dim access As New MethodsLib.RestrictedAccess()
            Try
                _data = acess.consultexped(_ced, _exn, _idJ, _idM, _mat, _idTip)
            Catch ex As MethodsLib.BdException
                RegEr.RegError(ex)
                Response.Redirect("../Error.aspx", True)
            End Try
        End Sub
    And this is the method in MethodsLib:
    Public Function consultexped(ByVal ced As String, ByVal exn As String, ByVal idJ As Integer, ByVal idM As Integer, ByVal mat As String, ByVal idTip As Integer) As DataTable
            Dim lista As New DataTable()
            Dim connection As New OracleConnection(ConnectionString)
            Dim command As New OracleCommand()
            Dim dataAdapter As New OracleDataAdapter()
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = "SP_CONSULTEXPED"
            command.Parameters.Add("PIN_CED", OracleDbType.Int32, ParameterDirection.Input).Value = ced
            command.Parameters.Add("PIN_EXPN", OracleDbType.Varchar2, ParameterDirection.Input).Value = exn
            command.Parameters.Add("PIN_IDJ", OracleDbType.Int32, ParameterDirection.Input).Value = idJ
            command.Parameters.Add("PIN_IDM", OracleDbType.Int32, ParameterDirection.Input).Value = idM
            command.Parameters.Add("PIN_MAT", OracleDbType.Varchar2, ParameterDirection.Input).Value = ma
            command.Parameters.Add("PIN_IDTIP", OracleDbType.Int32, ParameterDirection.Input).Value = idTip
            command.Parameters.Add("POUT_LISTA", OracleDbType.RefCursor, ParameterDirection.Output)
            dataAdapter.SelectCommand = command
            Try
                connection.Open()
                dataAdapter.Fill(lista)
            Catch ex As OracleException
                RegistroDeErrores.RegistrarError(ex)
                Throw New BdException("Error al realizar consulta a la base de datos.", ex)
            Finally
                connection.Close()
                connection.Dispose()
                command.Dispose()
                dataAdapter.Dispose()
            End Try
            Return lista
        End Function
    And I don't know why, in this SP I have a select, defining exactly the fields in contrast with the ASP classic that the query is a select *. 
    I'd been debugging and I found that when it executes the dataAdapter.fill(lista) takes a looooot of time (sometimes minutes for a query that it is executed in seconds in ASP classic).
    I executed the SP in the Oracle SQLDeveloper Client and did not take so much time like when it is called by .NET. Sorry for my english.
    
    
    Tuesday, July 5, 2011 10:08 AM
  • User269602965 posted

    I find using an Oracle stored procedure in PL/SQL is slower than a direct use of SQL statement in server side application .NET code.

    Especially is the PL/SQL statement processes business rules, or worse, has LOOPs (PL/SQL loops are sooooo slllloooowwww)

    I process my business rules in .NET (fast) and use SQL statements to fill application objects.

    You can gain additional speed avantage using SQL Oracle Analytics and 11g PIVOT clause.

    Friday, July 8, 2011 7:06 PM